Consutas Parametrizadas/ Consultas

Trabalhando com Consultas e Consultas Parametrizadas

Executando uma Consulta não Parametrizada

Uma consulta pode ser armazenada no banco de dados ( SQL Server , Oracle e até no Access ) , isto agiliza o processamento. Uma consulta não parametrizada é uma instrução SQL que foi salva no banco de dados e que não precisa de nenhum argumento adicional para ser executada. Na DAO elas tem o nome de QueryDefs e na ADO de Views. Vejamos como faziamos e como devemos fazer na ADO.

DAO

    Dim db As DAO.Database

    Dim rst As DAO.Recordset

    Dim fld As DAO.Field

    'Abre a base de dados

    Set db = DBEngine.OpenDatabase("C:\teste\nwind.mdb")

    'Abre o Recordset

    Set rst = db.OpenRecordset("Consulta teste",dbOpenForwardOnly, dbReadOnly)

    'Mostra os dados na janela de depuraçao

    While Not rst.EOF

        For Each fld In rst.Fields Debug.Print fld.Value & ";";

        Next

        Debug.Print

        rst.MoveNext

    Wend

    'Fecha o recordset

   rst.Close

ADO

    Dim cnn As New ADODB.Connection

    Dim rst As New ADODB.Recordset

    Dim fld As ADODB.Field

    'Abre a conexão

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\nwind.mdb;"

    'Abre o Recordset

    rst.Open "[Consulta teste]", cnn, & _

        adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

    'Mostra os dados na janela de depuração

    While Not rst.EOF

        For Each fld In rst.Fields Debug.Print fld.Value & ";";

        Next

        Debug.Print

        rst.MoveNext

    Wend

    'Fecha o Recordset

    rst.Close

No exemplo acima temos a base de dados Nwind.mdb no diretorio c:\teste com a consulta chamada Consulta Teste armazenada no banco de dados. O código para a ADO é quase idêntico ao usado na DAO . Observe que como o nome da consulta contém espaços usamos colchetes [ ] na ADO para inserir o nome da consulta.

Executando uma Consulta Parametrizada

Uma consulta parametrizada é uma instrução SQL armazenada no banco de dados que requer argumentos adicionais para que possa ser executada. Na ADO pertecem a coleção QueryDefs e na ADO ficam na coleção de Procedures. Os argumentos serão passados para a consulta em tempo de execução. Para você entender o exemplo , image um formulário onde é solicitado a data inicial e a data final das vendas realizada em um periodo. A consulta Vendas por Ano esta armazenada no banco de dados Nwind.mdb no diretorio c:\teste. Após preencher as caixas de texto ( text1 e text2 ) com as respectivas datas , você deverá clicar no botão que dispara a consulta e gera os dados das vendas para o período informado.(Veja o artigo Criando uma Consulta Parametrizada )

DAO

    Dim db As DAO.Database

    Dim qdf As DAO.QueryDef

    Dim rst As DAO.Recordset

    Dim fld As DAO.Field

    'Abre a base de dados

    Set db = DBEngine.OpenDatabase("C:\teste\nwind.mdb")

    'Determina a consulta da coleção QueryDefs

    Set qdf = db.QueryDefs("Sales by Year")

    'Especifica os valores dos argumentos

    qdf.Parameters("("Forms!Sales by Year Dialog!BeginningDate") _

        = #8/1/1993#

    qdf.Parameters("Forms!Sales by Year Dialog!EndingDate") = #8/31/1993#

    'Abre o Recordset

    Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)

    'Mostra os dados na janela de depuração

    While Not rst.EOF

        For Each fld In rst.Fields Debug.Print fld.Value & ";";

        Next

        Debug.Print

        rst.MoveNext

    Wend

    'Fecha o recordset

    rst.Close

Obs: No exemplo acima informamos diretamente os valores para a os parâmetros (observe o formato mm/dd/yyyy) mas em um caso real você deverá informar "#" & format(text1,"mm/dd/yyyy") & "#" para o inicio e "#" & format(text2,"mm/dd/yyyy") & "#" para o fim. Os parâmetros da consulta são:

Forms!Sales by Year Dialog!BeginningDate e Forms!Sales by Year Dialog!EndingDate

A consulta SQL armazenada na base de dados é a seguinte:

PARAMETERS Forms![Sales by Year Dialog]!BeginningDate DateTime, Forms![Sales by Year Dialog]!EndingDate DateTime;
SELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, Format([ShippedDate],"yyyy") AS Year
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between [Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year Dialog]![EndingDate]));

Note a a primeira linha definindo os parâmetros (PARAMETERS) e o tipo de dados.(DateTime).

A grande vantagem de usar consultas armazenadas no Banco de dados é a rapidez com que elas são executadas , pois já estão pré-compiladas e não precisam ser analisadas durante a execução.

ADO

    Dim cnn As New ADODB.Connection

    Dim cmd As New ADODB.Command

    Dim rst As New ADODB.Recordset

    Dim fld As ADODB.Field

    'Abre o Recordset

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\nwind.mdb;"

    'Cria um comando

    Set cmd.ActiveConnection = cnn

    cmd.CommandText = "[Sales by Year]"

    'Executa o comando passando os valores aos parâmetros

    Set rst = cmd.Execute(, Array(#8/1/93#, #8/31/93#), adCmdStoredProc)

    'Mostra os dasdos na janela de depuracao

    While Not rst.EOF

        For Each fld In rst.Fields Debug.Print fld.Value & ";";

        Next

        Debug.Print

        rst.MoveNext

    Wend

    'Fecha o Recordset

    rst.Close

Consultas Ação

No exemplo anterior vimos a utilização do método Execute do objeto Command da ADO para retornar uma quantidade de linhas (registros). Podemos também usar o método Execute para realizar tarefas que não retornam linhas mas realizam ações tais como : atualizar registros e excluir registros .

Vejamos exemplos na DAO e na ADO: usaremos o banco de dados Nwind.mdb no diretório c:\teste e neste exemplo iremos atualizar (Update) todos os clientes ( Customers ) cujo país ( Country ) for igual a USA para United States. O comando usado para promover a atualização é o comando Update

DAO

    Dim db As DAO.Database

    'Abre a base de dados

    Set db = DBEngine.OpenDatabase("C:\teste\nwind.mdb")

    'Executa a consulta

    db.Execute "Update Customers Set Country = 'United States' "WHERE Country = 'USA'"

    Debug.Print "Records Affected = " & db.RecordsAffected

    'Fecha a base de dados

    db.Close

ADO

Dim cnn As New ADODB.Connection

    Dim iAffected As Integer

    'Abre a conexão

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\nwind.mdb;"

    'Executa a consulta

    cnn.Execute "Update Customers Set Country = 'United States' " & _

        "WHERE Country = 'USA'", iAffected, adExecuteNoRecords

    Debug.Print "Records Affected = " & iAffected

    'Fecha a conexão

    cnn.Close

O código ADO é mais rápido para realizar a mesma tarefa devido ao parâmetro adExecuteNoRecords que faz com que o objeto Connection não precise criar um chache para retornar os registros.

A DAO possue dois métodos para executar instruções SQL : OpenRecordset e Execute . A ADO possui somente o método Execute que possui a seguinte sintaxe:

Para retornar linhas(registros):

Set recordset = command.Execute( RecordsAffected, Parameters, Options )

Para consultas Ação:

command.Execute RecordsAffected, Parameters, Options

Os parâmetros são:

RecordsAffected Opcional. Uma variável do tipo Long que retorna o número de registros afetados pela operação.

Parameters Opcional. Um vetor do tipo Variant de valores passados para a consulta SQL.

Options Opcional. Um valor do tipo Long que indica como o provedor irá avaliar a propriedade CommandText (contém um texto de um comando que atuará no provedor) do objeto Command.Os valores possíveis são:

Constante	Descrição
adCmdText	Indica que CommandText será avaliado pelo Provedor como uma definição textual de um comando.(Uma instrução SQL)
adCmdTable	Indica que a ADO irá gerar uma consulta SQL para retornar todas as linhas da tabela indica em CommandText.
adCmdTableDirect	Indica que o Provedor irá gerar uma consulta SQL para retornar todas as linhas da tabela indica em CommandText.
adCmdStoredProc	Indica que o Provedor irá avaliar CommandText como uma stored procedure.
adCmdUnknown	Indica que o tipo de comando em CommandText é desconhecido.
adExecuteAsync	Indica que o comando será executado assincronamente.
adFetchAsync	Indica que as linhas restantes apos uma quantidade inicial especificada na propriedade irá atuar assincronamente