Universidade Federal de Santa Maria
Transcrição
Universidade Federal de Santa Maria
70 Universidade Federal de Santa Maria Anexo I Construção de Aplicações de Acesso a Banco de Dados – Parte I (Cadastro Simples) O objetivo deste material é demonstrar possíveis códigos (enxutos e seguros) para realizar operações de acesso/manutenção em uma tabela com uma ou mais relações do tipo 1xn (um para muitos) utilizando componentes DataWare (componentes que representam automaticamente informações contidas em uma origem de dados. O modelo de dados utilizado neste exemplo utiliza duas tabelas relacionadas da seguinte forma: CREATE TABLE UNIDADES ( SIGLA CHAR(3) NOT NULL, NOME_UNIDADE VARCHAR(60) NOT NULL ); CREATE TABLE INGREDIENTES ( ID_INGREDIENTE INTEGER NOT NULL, DESCRICAO VARCHAR(100) NOT NULL, UNIDADE CHAR(3) NOT NULL ); Para fazer a demonstração será utilizada uma janela de manutenção semelhante à figura abaixo com o devido tratamento dos eventos que possam ocorrer objetivando um código simples que possa funcionar para qualquer tela de cadastro com características semelhantes. De forma a organizar o código um componente ActionList será utilizado com a criação e implementação de 7 ações (actNovo, actLocalizar, actSalvar, actCancelar, actExcluir, actSair, actLocalizarPorDescricao). Edit DBEdit (ParentColor = true, ReadOnly = true) DBLookupComboBox KeyField = CampoChaveDaTabelaRelacionada (Sigla) ListSource = DataSourceDaTabelaRelacionada ListField = CampoDescricaoDaTabelaRelacionada (Nome_Unidade) Durante o desenvolvimento dos códigos algumas variáveis são utilizadas e precisarão ter seu conteúdo substituído de acordo com a tabela na qual a manutenção será feita. A seguir o detalhamento do significado de tais variáveis: Tabela: Componente do tipo SQLQuery (guia SQLdb) que contém em sua propriedade Fields, todos os campos físicos da tabela que terão manutenção; CampoChave: Nome do campo chave da tabela não pode ser duplicado e que identifica um registro unicamente (o tipo de tal campo deve obrigatoriamente ser numérico inteiro). CampoDescricao: Campo descritivo, que serve para buscas parciais (geralmente um nome ou uma descrição do conteúdo do registro). Caderno Didático – Lazarus IDE Página 70 71 Universidade Federal de Santa Maria Abertura e Fechamento de Consultas A manutenção de uma ou mais tabelas envolve a ativação de uma consulta (inicialização de uma transação e busca dos dados) e o seu encerramento (liberação dos recursos e encerramento da transação). Um código para fazer tais procedimentos é muito comum em uma aplicação de manutenção de dados. Neste sentido, a seguir são apresentadas duas sub-rotinas que serão futuramente referenciadas em diversas outras partes do código. O primeiro procedimento “AbrirConsultas()” responde pela ativação da transação (se a mesma não estiver ativa) e pela abertura das consultas, iniciando pelas tabelas relacionadas e por último a tabela principal. O segundo procedimento “FecharConsultas” inicialmente encerra a tabela principal e as tabelas relacionadas e por fim encerra a transação. Uma próxima chamada ao procedimento de “AbrirConsultas()” fará a inicialização de uma nova transação, podendo dessa forma visualizar dados inseridos por outros usuários. procedure AbrirConsultas(); begin //Se a transação não está ativa então inicia ela if not SQLTransaction.Active then SQLTransaction.StartTransaction; if not if not if not //se a if not end; TabelaRelacionada1.Active then TabelaRelacionada1.Open; //Se as tabelas TabelaRelacionada2.Active then TabelaRelacionada2.Open; //relacionadas não estão TabelaRelacionadaN.Active then TabelaRelacionadaN.Open; //ativas então ative-as tabela não está ativa então a ative Tabela.Active then Tabela.Open; procedure FecharConsultas(); begin //se as tabelas relacionadas estão ativas então as mesmas devem ser fechadas if TabelaRelacionada1.Active then TabelaRelacionada1.Close; if TabelaRelacionada2.Active then TabelaRelacionada2.Close; if TabelaRelacionadaN.Active then TabelaRelacionadaN.Close; //se a tabela está ativa então ela deve ser encerrada if Tabela.Active then Tabela.Close; //se a transação estiver ativa a mesma deve ser encerrada if SQLTransaction.Active then SQLTransaction.EndTransaction; end; Abertura e Encerramento da Janela de Cadastro Os eventos de abertura (OnShow) e fechamento (OnClose e OnCloseQuery) de um formulário também merecem atenção. No primeiro caso o estado das tabelas envolvidas na aplicação precisa ser ajustado (as tabelas devem ser iniciadas fechadas, aguardando uma ação do usuário). No caso do fechamento devemos verificar se existem alterações pendentes que não foram salvas assim como encerrar os DataSets utilizados. Evento OnShow FecharConsultas(); HabilitarDesabilitarControles; Evento OnCloseQuery if TestaSeDesejaSalvar() <> mrCancel then CanClose:=true else CanClose:=false; Evento OnClose Fechamento da Aplicação (ação actSair) A ação de saída deve simplesmente fechar o formulário (método close). As demais verificações ficam a cargo dos eventos descritos anteriormente: OnClose (disparado antes do fechamento) e OnCloseQuery (disparado antes do fechamento para confirmar se a aplicação será ou não fechada). Close; FecharConsultas(); Caderno Didático – Lazarus IDE Página 71 72 Universidade Federal de Santa Maria Concorrência e Auto-incremento de um campo chave numérico Em geral, um campo chave inteiro não deve estar disponível nem para o usuário informar, muito menos alterar. Neste sentido, faz-se necessário uma rotina que produza uma numeração sequencial. Os SGBDs modernos oferecem mecanismos de geração automática destes valores (campos conhecidos como Autoincremento ou Autonumeração), mas o uso destes recursos em algumas situações impede que o programador tenha o controle sobre a numeração gerada. A proposta do código que será sugerido é de produzir a partir dos próprios dados, a cada nova inclusão, um valor para o campo chave. Um detalhe importante que precisa ser observado antes da construção deste código: a possibilidade do banco de dados e/ou a tabela em questão estar sendo compartilhada e mais de um usuário realizar acesso simultâneo aos dados. Neste caso faz-se necessário tomar o cuidado de nunca gerar dois códigos iguais para registros diferentes, situação essa que produziria uma duplicação de chave primária ocasionando um erro e impedindo o salvamento do registro. A seguir serão analisados dois códigos, o primeiro para tabelas locais, onde não existe acesso simultâneo, ou seja, somente um usuário/aplicação realiza o acesso de cada vez. Em ambos os caso será utilizada uma função para produzir e retornar o último valor disponível para ser utilizado em um novo registro. function BuscaUltimoCodigo(): integer; var retorno : integer; //variável auxiliar para guardar o código novo index_old : string; //variável auxiliar para guardar a indexação atual begin //guarda o campo que indexa a tabela index_old:=Tabela.IndexFieldNames; //troca a indexação da tabela para o campo chave Tabela.IndexFieldNames:=CampoChave; AbrirConsultas(); //Inicia transação e abre as consultas //posiciona o cursor no último registro Tabela.Last; //guarda na variável retorno o valor do campo chave + 1 retorno := Tabela.FieldByName(CampoChave).asInteger + 1; //volta a indexação da tabela como a mesma estava originalmente Tabela.IndexFieldNames:=index_old; //se o retorno for 0 (zero - tabela vazia) então o valor a ser retornado é 1 (um) if retorno = 0 then retorno := 1; //A função devolve para o código que a chamou o valor de retorno BuscaUltimoCodigo := retorno; end; Havendo a necessidade de realizar acessos simultâneos em uma tabela, como por exemplo, em um supermercado, onde diversos computadores estão lendo e gravando alterações nas tabelas simultaneamente, uma alternativa interessante para o controle da geração de valores para o campo chave é a criação de uma tabela auxiliar que contém um campo para identificar o nome da tabela e outro para armazenar o próximo código a ser utilizado. Algo semelhante ao demonstrado na figura e no script abaixo: CREATE TABLE IDS_DISPONIVEIS ( NOME_TABELA VARCHAR(30) NOT NULL PRIMARY KEY, ULT_ID INTEGER NOT NULL ); Caderno Didático – Lazarus IDE Página 72 73 Universidade Federal de Santa Maria A seguir uma alternativa à função “BuscaUltimoCodigo” dessa vez considerando acessos simultâneos. Neste código faz-se a utilização de um componente SQLQuery e também do componente SQLTransaction (recomenda-se a utilização de um componente SQLTransaction adicional, e não aquele que eventualmente já existe na aplicação). Diferentemente da função “BuscaUltimoCodigo” demonstrada anteriormente o código a seguir é independente de tabela, ou seja, ele irá receber como parâmetro o nome da tabela do qual se deseja produzir o próximo identificador. Este código pode ficar disponível em uma unit de acesso geral (uma sugestão seria utilizar a unit do DataModule onde estão configuradas as conexões ao banco de dados). function BuscaUltimoCodigo(nome_tabela: string): integer; const MAX_TENTATIVAS = 5; //Numero de tentativas que serão feitas TEMPO_ESPERA = 100; //Tempo de espera em milissegundos após cada tentativa var ult_id : integer; //variável para guardar e retornar o último valor tentativas : integer; //variável para controlar o número de tentativas executadas begin tentativas:=0; ult_id:=0; //Enquanto o número de tentativas não alcançar o limite while tentativas <= MAX_TENTATIVAS do begin try try //Se a transação auxiliar estiver ativa então ela deve ser encerrada if SQLTransactionAux.Active then SQLTransactionAux.EndTransaction; SQLTransactionAux.StartTransaction; //Inicia uma nova transação //Se a query auxiliar estiver ativa então ela deve ser fechada if SQLQueryAux.Active then SQLQueryAux.Close; //Limpa e adiciona ao texto da query o comando update SQLQueryAux.SQL.Clear; SQLQueryAux.SQL.Add('update ids_disponiveis set ult_id = ult_id + 1 ' + 'where nome_tabela = ' + QuotedStr(nome_tabela)); SQLQueryAux.ExecSQL; //executa o comando update //Limpa e adiciona ao texto da query o comando select SQLQueryAux.SQL.Clear; SQLQueryAux.SQL.Add('select ult_id from ids_disponiveis ' + 'where nome_tabela = ' + QuotedStr(nome_tabela)); SQLQueryAux.Open; //executa o comando select //Atribui a variável ult_id o valor do campo retornado pelo select ult_id:=SQLQueryAux.FieldByName('ULT_ID').asInteger; SQLTransactionAux.Commit; //Encerra a transação gravando os dados break; //Encerra o laço de repetição except //Caso algum erro ocorra SQLTransactionAux.Rollback; //Cancela as alterações feitas no banco de dados tentativas:=tentativas+1; //Incrementa o número de tentativas sleep(TEMPO_ESPERA); //Aguarda o tempo configurado para tentar novamente end; finally //Após concluir o código (se existirem ou não erros de execução) //Testa se a transação não esta ativa e volta a ativa-la if not SQLTransactionAux.Active then SQLTransactionAux.StartTransaction; end; //Se o nº de tentativas alcançou o máximo então gera um erro para bloquear a aplicação if tentativas = MAX_TENTATIVAS then raise Exception.Create('Não foi possível obter um identificador único'); end; //A função devolve para o código que a chamou o valor de retorno BuscaUltimoCodigo := ult_id; end; Caderno Didático – Lazarus IDE Página 73 74 Universidade Federal de Santa Maria Verificando Alterações em um Registro Um DataSet (conjunto de dados) troca de estado em diversos momentos (hora está sendo consultada, hora está recebendo um registro, hora está editando um registro, etc.) e durante as mudanças de estado é necessário saber o que o usuário quer fazer com as informações que até então não foram salvas. Suponha, por exemplo, uma situação onde o usuário clica no botão que faz a inclusão e antes de salvar ele clica novamente sobre o botão da inclusão, o que aconteceria com os dados digitados que não foram salvos? Considerando situações como essas, faz-se necessário questionar o usuário sobre o que o mesmo deseja fazer com o registro atual: salvá-lo, cancelar as alterações feitas ou cancelar a ação que ele eventualmente tenha feito (no caso do exemplo anterior, o clique dado no botão novo). A seguir uma função que dependendo da situação do DataSet questiona o usuário acerca do que fazer diante de uma situação de perda de dados não salvos. A função retorna três possíveis valores: mrYes (os dados devem ser salvos), mrNo (os dados não devem ser salvos) e mrCancel (a ação disparada não deve ser executada). function TestaSeDesejaSalvar(): integer; var retorno : integer; //Variável auxiliar para armazenar a resposta begin retorno := mrNo; //Por padrão o retorno é não salvar //Se dados estiverem sendo incluídos ou editados if Tabela.State in [dsInsert, dsEdit] then begin //Alimenta a variável com a resposta do usuário retorno := MessageDlg('Confirmação', 'Deseja salvar as alterações?', mtInformation, [mbYes, mbNo, mbCancel], 0); if retorno = mrYes then //Se a resposta for sim actSalvar.Execute() //executa a ação de salvar else if retorno = mrNo then //Se a resposta for não begin Tabela.Cancel; //Cancela as alterações FecharConsultas(); //Fecha os DataSets e encerra a transação end; end; //A função devolve para o código que a chamou o valor de retorno TestaSeDesejaSalvar := retorno; end; Inicialização, Validação dos Dados e Autorização de Edição dos Controles Visuais Comumente algumas informações precisam ser inicializadas no ato da inclusão ou então validadas antes de um salvamento. A seguir são propostos três subrotinas com propósitos distintos, porém úteis nas demais ações que serão descritas posteriormente. Seu código vai depender de cada situação e neste material apenas são disponibilizados exemplos. Procedimento para Inicializar os Campos de um Formulário procedure InicializarCampos; begin //Código opcional para fazer algum tipo de inicialização. //Exemplo ... deixar um LookupComboBox sem nenhum item selecionado: DBLkpCmbBxUnidade.ItemIndex := -1; //Tornar padrão um determinado item de um DBRadioGroup DBRdGrpTipo.ItemIndex := 1; end; Caderno Didático – Lazarus IDE Página 74 75 Universidade Federal de Santa Maria Função para Validar os Campos de um Formulário function ValidarCampos(): boolean; var retorno : boolean; //variável auxiliar para guardar o retorno da função begin retorno := true; //por padrão não há erros //Se o tamanho do campo sem os espaços em branco for 0 if Length(Trim(DBEdtDescricao.Text)) = 0 then begin DBEdtDescricao.SetFocus; //Coloca o foco no componente com problema retorno := false; //Atribui false para a variável de retorno //Mostra uma mensagem de erro MessageDlg('A descrição é obrigatória e não foi informada.', mtError, [mbOK], 0); Abort; //Aborta a execução do programa end; //Se nenhum item do ComboBox foi selecionado if DBLkpCmbBxUnidade.ItemIndex = -1 then begin DBLkpCmbBxUnidade.SetFocus; //Coloca o foco no componente com problema retorno := false; //Atribui false para a variável de retorno //Mostra uma mensagem de erro MessageDlg('A unidade é obrigatória e não foi informada', mtError, [mbOK], 0); Abort; //Aborta a execução do programa end; //A função devolve para o código que a chamou o valor de retorno ValidarCampos := retorno; end; Procedimento para Habilitar ou Desabilitar o Acesso os Campos de um Formulário A propriedade Enabled dos componentes DataWare é atribuída em função do estado do DataSet ao qual estão conectados: procedure HabilitarDesabilitarControles; begin DBEdtCodigo.Enabled:=Tabela.Active; DBEdtDescricao.Enabled:=Tabela.Active; DBLkpCmbBxUnidade.Enabled:=Tabela.Active; DBEdtUnidade.Enabled:=Tabela.Active; end; Inclusão de um novo registro (ação actNovo) Para realizar a inclusão de um novo registro a única exigência é a geração de um valor para o campo chave primária. No código abaixo este valor é armazenado em uma variável e logo em seguida a tabela é colocada em modo de inserção. var codigo : integer; //variável para guardar o valor da chave primária begin if TestaSeDesejaSalvar() <> mrCancel then //Caso a ação não tenha sido cancelada begin AbrirConsultas();//Abre as consultas e inicia a transação codigo := BuscaUltimoCodigo(); //Variável código é alimentada com um novo valor // ou codigo := BuscaUltimoCodigo('NOME_DA_TABELA'); Tabela.Insert; Caderno Didático – Lazarus IDE //Coloca o DataSet em modo de inserção Página 75 76 Universidade Federal de Santa Maria //Atribui ao campo chave da tabela o código gerado Tabela.FieldByName(CampoChave).asInteger := codigo; InicializarCampos; //Realiza eventuais inicializações HabilitarDesabilitarControles end; end; Persistência (gravação) do registro atual (ação actSalvar) A efetivação dos dados na base de dados deve ser feita se o estado da tabela for compatível e se não houver erros nos dados informados (tipos incompatíveis, valores obrigatórios, etc.). O comando Post grava os dados no DataSet e o comando ApplyUpdates envia o DataSet para ser persistido na base de dados. Havendo sucesso nessas duas etapas os dados podem ser efetivados (commit) e em não havendo sucesso as alterações devem ser canceladas (rollback). var chave: integer; //variável auxiliar para guardar o identificador do registro begin //Verifica se a o DataSet está em Inserção ou Edição de dados if Tabela.State in [dsInsert, dsEdit] then begin if ValidarCampos() then //Se os campos foram corretamente validados begin try //Guarda o identificador do registro chave := Tabela.FieldByName(CampoChave).Value; Tabela.Post; //Grava os dados no DataSet Tabela.ApplyUpdates; //Envia os dados para o Servidor de BD SQLTransaction.Commit; //Realiza um Commit e encerra a transação ativa AbrirConsultas(); //Inicia uma nova transação e abre as consultas //Posiciona o DataSet no registro em que se encontrava antes do salvamento Tabela.Locate(CampoChave, chave, []); except //Ocorrendo um erro do tipo Exception on E : Exception do begin //Exibe a mensagem do erro ShowMessage('Não foi possível salvar as informações. ' + #13 + 'Ocorreram os seguintes erros ' + #13 + E.Message); //Desfaz as alterações feitas na base de dados SQLTransactionPrincipal.Rollback; end; end; end; HabilitarDesabilitarControles; end; end; Cancelamento das alterações do registro atual (ação actCancelar) O cancelamento das alterações em um DataSet é distinto durante uma Inserção (onde os dados não existiam) e em uma Edição (onde existiam valores que foram alterados). No caso do cancelamento de uma edição os dados anteriores devem retornar. No caso do cancelamento de uma inserção, o Caderno Didático – Lazarus IDE Página 76 77 Universidade Federal de Santa Maria DataSet deve ser fechado para que a ação de inclusão seja novamente acionada. Em ambos os casos o cancelamento é feito nos dados contidos no DataSet sem envolver a base de dados diretamente. //Código da ação actCancelar var sit : TDataSetState; //Variável auxiliar para guardar o estado do DataSet begin sit := Tabela.State; //Captura o estado do DataSet antes do cancelamento //Se o DataSet está em Inserção ou Edição if sit in [dsInsert, dsEdit] then begin //Confirma se os dados serão de fato cancelados if MessageDlg('Confirmação', 'Tem certeza que deseja cancelar as alterações ?', mtInformation, [mbYes, mbNo], 0) = mrYes then begin //Se sim, cancela a edição no DataSet Tabela.Cancel; //Se o estado inicial era inserção então os DataSets e a transação serão fechados if sit = dsInsert then FecharConsultas(); end; HabilitarDesabilitarControles; end; end; Exclusão do registro atual (ação actExcluir) A exclusão de um registro pressupõe que o usuário esteja posicionado sobre o registro ao qual deseja excluir. É aconselhável solicitar uma confirmação antes da exclusão. if Tabela.Active then //Testa se a tabela está ativa begin //Confirma se a excluão deve ser feita if MessageDlg('Confirmação','Tem certeza que deseja excluir o registro ' + Tabela.FieldByName(CampoDescricao).asString, mtConfirmation, [mbYes, mbNo], 0) = mrYes then begin try Tabela.Delete; //Deleta o registro no DataSet Tabela.ApplyUpdates; //Envia as alterações para o BD //Efetiva as alterações no BD SQLTransaction.Commit; except on E : Exception do begin SQLTransaction.Rollback; //Cancela as alterações no BD //Exibe uma mensagem indicando que não foi possível fazer a exclusão MessageDlg('Não foi possível excluir o registro ' + #13 + 'Possivelmente existem dados em outras tabelas ' + 'que estão relacionados a este registro.', mtError, [mbOk], 0); end; end; FecharConsultas(); //Encerra a transação e fecha os DataSets end; HabilitarDesabilitarControles; end; Caderno Didático – Lazarus IDE Página 77 78 Universidade Federal de Santa Maria Localização de Registros A localização de um registro normalmente é feita de duas maneiras. Na primeira, o usuário, conhecendo o valor do campo chave da tabela (um código, por exemplo), o informa e o programa tenta fazer uma busca exata. Na segunda maneira, o usuário informa parte de uma informação descritiva acerca da tabela e a aplicação tenta fazer uma busca aproximada com os dados informados. Neste último caso, o código é utilizado normalmente no evento OnChange (ao mudar o conteúdo) de uma Edit normal. Busca por chave primária (actLocalizar) var codigo : string; //Variável auxiliar para guardar o código informado begin //Se a ação não for cancelada if TestaSeDesejaSalvar() <> mrCancel then begin try FecharConsultas(); //Encerra a transação e os DataSets //Solicita ao usuário que informe um código if InputQuery('Código', 'Informe o código que você deseja localizar', codigo) then begin StrToInt(codigo); //Tenta fazer uma conversão para evitar erros //Inicia a transação e abre os DataSets AbrirConsultas(); //Se uma busca exata não encontrar nenhum registro if not Tabela.Locate(CampoChave, Codigo, []) then begin FecharConsultas(); //Mostra mensagem que o registro não foi encontrado MessageDlg('Código não encontrado!', mtWarning, [mbOk], 0); end; //Caso registro tenha sido encontrado o DataSet o terá posicionado end; except FecharConsultas(); //Se houver algum erro, fecha o DataSet end; HabilitarDesabilitarControles; end; end; Busca por campo descritivo (actLocalizarPorDescricao) //Se há algo informado na edit (o tamanho sem os espaços em branco é maior que zero) if Length(Trim(EdtDescricao.Text)) > 0 then begin if TestaSeDesejaSalvar() <> mrCancel then //Se a ação não for cancelada begin //Encera e na sequência inicia a transação e os datasets FecharConsultas(); AbrirConsultas(); //Realiza uma busca parcial e sem distinção de maiúsculas/minúsculas Tabela.Locate(CampoDescricao,EdtDescricao.Text,[loCaseInsensitive, loPartialKey]); end; HabilitarDesabilitarControles; end; Caderno Didático – Lazarus IDE Página 78