Select Menu



por Alessandro Trovato

» » » » » » » » VBA - Artigo 027 - Aprimorando a conexão com o banco de dados
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Progredindo em VBA no Microsoft Excel

Aprimorando a conexão com o banco de dados

No artigo anterior expliquei como conectar a um banco de dados usando como referência o exemplo mais disseminado na internet. Neste artigo veremos outras formas de acessar o banco de dados que podem ser mais apropriadas, dependendo do SGBD em uso. Se você não leu o artigo anterior, leia-o antes de prosseguir.

O objeto ADODB.Recordset possui métodos Open e Close, que permitem abrir e fechar a conexão com o banco de dados. Vejamos os parâmetros do método Open:
- Source: É a fonte dos dados, podendo ser uma sentença SQL, um procedimento armazenado (ou stored procedure, em inglês), um nome de tabela, um caminho para um arquivo (como um arquivo do Access), um objeto ADODB.Command ou uma URL;
- ActiveConnection: Pode ser um objeto ADODB.Connection ou mesmo uma string de conexão (desta maneira o objeto ADODB.Connection nem precisa ser criado);
- CursorType: Define o tipo de cursor (ou ponteiro) que será usado, devendo ser um valor da enumeração CursorTypeEnum, que será explicada mais adiante;
- LockType: Define a forma de bloqueio aos registros, devendo ser um valor da enumeração LockTypeEnum, que também será explicada mais adiante;
- Options: Define como interpretar o parâmetro Source, podendo usar um ou mais valores das enumerações CommandTypeEnum  e ExecuteOptionEnum.

Todos esses parâmetros são opcionais. O objeto ADODB.Recordset possui propriedades com os mesmos nomes dos parâmetros (exceto Options). Desta forma você pode definir as propriedades e depois usar o método Open sem especificar nenhum parâmetro.

A enumeração CursorTypeEnum pode ter os seguintes valores:
- adOpenForwardOnly (0): É o valor padrão e o cursor só pode ir adiante. Fornece o melhor desempenho;
- adOpenDynamic (2): Abre um cursor dinâmico, onde alterações, adições e exclusões feitas por outros usuários serão visíveis. Como deve-se imaginar, esta facilidade reduz bastante o desempenho;
- adOpenKeyset (1): Abre um cursor dinâmico, mas nesta opção apenas as alterações de outros usuários serão visíveis. Adições não serão visíveis e exclusões tornarão registros inacessíveis. Este cursor é mais propício para ambientes onde não serão feitas adições ou exclusões;
- adOpenStatic (3): Abre um cursor estático, onde uma cópia dos valores do momento serão visualizadas. Eventuais alterações, adições ou exclusões por outros usuários não serão visíveis;
- adOpenUnspecified (-1): Abre um cursor sem tipo especificado, usando o formato padrão do SGBD.

O cursor padrão (adOpenForwardOnly) só permite usar o método MoveNext, enquanto os cursores dinâmicos permitem os métodos MoveFirst, MoveLast e MovePrevious, dando mais flexibilidade no acesso aos dados.

O cursor adOpenStatic permite o acesso ao valor da propriedade RecordCount, que contém a quantidade de registros (os outros cursores devolvem o valor -1).  Desta forma é possível utilizar uma estrutura de repetição For... Next no lugar de Do... Until EOF visto no artigo anterior. O cursor adOpenStatic é o ideal caso precise saber a quantidade de registros e sem necessidade de editar os dados.

Já a enumeração LockTypeEnum pode ter os seguintes valores:
- adLockReadOnly (1): Valor padrão, permite apenas leitura dos registros;
- adLockPessimistic (2): Bloqueio "pessimista", bloqueia o registro no banco de dados logo após a edição do mesmo;
- adLockOptimistic (3): Bloqueio "otimista", bloqueia apenas quando for efetuada a atualização do registro;
- adLockBatchOptimistic (4): Idem anterior, mas para atualizações em modo batch;
- adLockUnspecified (-1): Valor não especificado, usa a forma padrão do SGBD.

Após muita teoria, vamos ver códigos. O exemplo abaixo acessa um banco de dados SQL Server sem necessidade de criar um objeto ADODB.Connection e usa o cursor adOpenStatic, que permite acessar a quantidade de registros:

Sub TesteProdutos()

    Dim Registros   As New ADODB.Recordset
    Dim Chave       As String
    Dim Consulta    As String
    Dim Setor       As String
    Dim Categoria   As String
    Dim Iteracao    As Integer

    Setor = "Feira"
    Categoria = "Fruta"

    Chave = "Provider=SQLOLEDB;Data Source=SQLSERVEREXPRESS\DATABASE;" & _
        "Initial Catalog=SUPERMERCADO;Integrated Security=SSPI;"
    Consulta = "SELECT NOME FROM PRODUTOS WHERE SETOR = '" & _
        Setor & "' AND CATEGORIA = '" & Categoria & "' ORDER BY NOME;"
   
    Registros.Open Consulta, Chave, adOpenStatic, adLockReadOnly

    For Iteracao = 1 To Registros.RecordCount
        Debug.Print Iteracao & ": " & Registros('NOME')
        Registros.MoveNext
    Next

    Registros.Close

End Sub

Alternativamente, pode-se definir as propriedades e depois executar o método Open sem parâmetros:

Sub TesteProdutos()

    Dim Registros   As New ADODB.Recordset
    Dim Setor       As String
    Dim Categoria   As String
    Dim Iteracao    As Integer

    Setor = "Feira"
    Categoria = "Fruta"

    Registros.ActiveConnection = "Provider=SQLOLEDB;" & _
        "Data Source=SQLSERVEREXPRESS\DATABASE;Initial Catalog=SUPERMERCADO;" & _
        "Integrated Security=SSPI;"
    Registros.Source = "SELECT NOME FROM PRODUTOS WHERE SETOR = '" & _
        Setor & "' AND CATEGORIA = '" & Categoria & "' ORDER BY NOME;"
    Registros.CursorType = adOpenStatic
    Registros.LockType = adLockReadOnly
    Registros.Open

    For Iteracao = 1 To Registros.RecordCount
        Debug.Print Iteracao & ": " & Registros('NOME')
        Registros.MoveNext
    Next

    Registros.Close

End Sub

Você pode também definir algumas propriedades e colocar apenas alguns dos parâmetros. Particularmente, prefiro o primeiro exemplo, que é mais autodocumentado.

Se você possui alguma tabela para fazer um teste similar, não esqueça de alterar os parâmetros Data Source e Initial Catalog na variável Chave, além da própria sentença SQL em Consulta.

Apenas para realçar, os exemplos acima são acessos em modo estático e somente para leitura dos dados. Caso você precise de alterar, apagar ou acrescentar registros, você pode acessar os dados de forma dinâmica. Lembre-se que em modo dinâmico você não tem acesso à propriedade RecordCount, não sendo possível utilizar a estrutura For... Next. Vejamos o exemplo adaptado para acesso dinâmico:

Sub TesteProdutos()

    Dim Registros   As New ADODB.Recordset
    Dim Chave       As String
    Dim Consulta    As String
    Dim Setor       As String
    Dim Categoria   As String
    Dim Contador    As Integer

    Setor = "Feira"
    Categoria = "Fruta"

    Chave = "Provider=SQLOLEDB;Data Source=SQLSERVEREXPRESS\DATABASE;" & _
        "Initial Catalog=SUPERMERCADO;Integrated Security=SSPI;"
    Consulta = "SELECT NOME FROM PRODUTOS WHERE SETOR = '" & _
        Setor & "' AND CATEGORIA = '" & Categoria & "' ORDER BY NOME;"
   
    Registros.Open Consulta, Chave, adOpenDynamic, adLockOptimistic

    Contador = 1
     
      Do Until Registros.EOF
        Debug.Print Contador & ": " & Registros('NOME')
        Registros.MoveNext
            Contador = Contador + 1
    Loop

    Registros.Close

End Sub

Perceba que além de alterar o acesso de estático para dinâmico, também foi alterado o modo de bloqueio de somente leitura para otimista, tornando possível editar os dados.

O código acima faz praticamente a mesma coisa que os anteriores, mas ele permite edições. O cursor é um ponteiro para o registro, que vai se movendo com o método MoveNext. O registro que está sendo apontado pode ser modificado ou mesmo excluído. Suponha que você está debugando o código e vê uma fruta escrita de forma errada. Você pode editar o campo correspondente diretamente na área de verificação imediata:

Registros('NOME') = "Maçã"

Até aqui você editou o dado dentro do objeto ADODB.Recordset, mas não foi feita a atualização no banco de dados, o que pode ser feito com o método Update:

Registros.Update

Se for preciso excluir algum registro, use o método Delete, que irá excluir apenas o registro o qual o cursor está apontando, sem necessidade de usar o Update:

Registros.Delete

Para incluir registros, é preciso um pouco mais de atenção, pois o ADODB.Recordset só permite incluir os campos que foram trazidos na seleção. Se houver campos que não permitem valores nulos, é preciso que eles estejam na consulta. Há uma maneira muito mais prática de lidar com isso. Lembra que no começo do artigo foi dito que o parâmetro Source pode ser o nome de uma tabela? Altere a linha da variável consulta para o nome da tabela e veja o resultado:

Consulta = "PRODUTOS"

Desta maneira está trazendo a tabela inteira para o objeto ADODB.Recordset. Se sua tabela for pequena não deve ter impacto, mas se a tabela contiver muitos dados o computador pode ficar sobrecarregado. Neste caso é melhor fazer uma seleção especificando todos os campos e filtrando os dados desejados.

Para adicionar dados, é preciso usar o método AddNew em conjunto com o Update. Também é preciso definir os dados. A maneira mais prática é o código a seguir:

With Registros
    .AddNew
    .Fields("CODIGO") = "35"
    .Fields("NOME") = "Morango"
    .Fields("CATEGORIA") = "Frutas"
    .Fields("SETOR") = "Feira"
    .Update
End With

Outro método útil é o Filter, que, como o próprio nome indica, filtra os registros usando um critério. Se quiser filtrar a tabela de produtos para exibir apenas os produtos que comecem com a letra P, use o seguinte código:

Registros.Filter = "NOME LIKE 'P%'"

Esse método aceita mais de uma condição:

Registros.Filter = "NOME LIKE 'P%' OR NOME LIKE 'B%'"

Após o uso do filtro, o cursor irá para o primeiro registro entre os filtrados. Para limpar o filtro, use o parâmetro adFilterNone, que irá redefinir os registros, passando a exibir todos os que foram trazidos na consulta SQL. O cursor retornará à primeira posição.

Por fim, há o método Find, para localizar um registro específico e que pode receber até quatro parâmetros:
- Criteria: É o critério de busca. Aqui não é possível pesquisar por mais de uma coluna (não é possível usar AND ou OR);.
- Skiprows: Define a partir de quantos registros a partir da posição atual do cursor irá fazer a pesquisa. O valor padrão é 0;
- Direction: Define a direção da busca, podendo ser adSearchForward (para frente) ou adSearchBackward (para trás);
- Start: A posição inicial da busca.

Apenas o parâmetro criteria é obrigatório, os demais são opcionais. Vejamos um exemplo de código:

Registros.Find = "NOME = 'BANANA'"

O cursor irá apontar para o primeiro registro encontrado, caso haja mais de um.

Perceba que usando o acesso com cursor dinâmico não há tanta necessidade de códigos SQL. Entretanto, quanto mais conhecer de SQL, mais dá para obter dos recursos disponíveis do SGBD. Caso esteja usando acesso estático e houver necessidade de adicionar, editar ou apagar algum registro, o melhor é ter uma rotina apropriada para isso, usando de preferência o objeto ADODB.Connection e usando o método Execute, com sentenças SQL. É preciso ponderar a necessidade de usar o acesso dinâmico, visto que ele demanda mais recursos da máquina.

Neste artigo há muita informação disponível, mas também há muita coisa que ficou de fora. Tenha uma base para efetuar testes e aprender na prática. Use o modo de depuração (tecla F8) para avançar o código um pouco de cada vez e veja o conteúdo que aparece nos campos do registro apontado pelo cursor. Teste e pratique bastante (junto com uma dose de paciência) e você dominará conexões com banco de dados.

Até o próximo artigo!

Pedro Martins


Pós-graduando em Business Intelligence e Big Data pela Faculdade Impacta de Tecnologia. Formado em Tecnologia em Eletrônica Digital com Ênfase em Microprocessadores

Autor Pedro Martins

Esta é uma breve descrição no bloco de autor sobre o autor. Você edita-lo, no html
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Nenhum comentário

Comentarios