ASP - Utilizando os procedimentos armazenados (Consultas Parametrizadas)

Os procedimentos armazenados (stored procedures), utilizados em servidores de banco de dados tais como o SQL Server , aumentam o desempenho e modularizam o código permitindo uma manutenção mais fácil . Como estamos trabalhando com banco de dados access o termo mais correto a ser utilizado é o de consultas parametrizadas, o comportamento , no entanto é basicamente o mesmo.

Consultas parametrizadas são consultas criadas e armazenadas no banco de dados que são utilizadas passando-se um parâmetro durante a execução da consulta. Isto torna as consultas parametrizadas muito flexíveis e úteis para retornar informações em um banco de dados relacional.

O banco de dados Microsoft Access nos dá a possibilidade de criar e armazenar consultas dentro do arquivo de banco de dados. A vantagem em armazenar as consultas no banco de dados é que para executar uma instrução SQL o JET(o mecanismo do banco de dados) verifica erros de sintaxe, depois ele tenta otimizar a instrução e a seguir executa a instrução, tudo isto leva tempo e a coisa piora se você estiver executando as mesmas instruções SQL diversas vezes, pois o JET terá que refazer tudo novamente.

Ao criar e armazenar definições de consultas através de instruções SQL o Access analisa a instrução e a otimiza só então ele armazena a instrução original e a otimizada, quando for executar a instrução o trabalho estará feito e o tempo de execução será diminuído.

Vamos mostrar como criar e usar consultas parametrizadas em uma base de dados Access usando o script ASP.

Usando consultas armazenadas parametrizadas

Vamos usar o banco de dados Biblio.mdb e a tabela Authors em nosso exemplo. A estrutura da tabela Authors é a seguinte:

Antes de iniciar eu sugiro que você faça uma copia da tabela Authors . A seguir deixe na tabela somente uns dez registros (A tabela original possui uns 3000 registros) , excluindo os demais registros. Após a exclusão dos registros a tabela deverá ter mais ou menos os seguintes registros (isto pode variar para cada caso).

Agora vamos criar e armazenar quatro consultas no banco de dados Biblio.mdb, são elas:

Obs: Uma maneira de criar as consultas no banco de dados biblio.mdb é usar o Microsoft Access. Abra o banco de dados biblio.mdb usando o Microsoft Access e na guia Consulta clique no botão novo ; selecione o modo estrutura e na janela Mostrar tabela clique no botão fechar. A seguir copie a consulta desejada e selecione no menu Exibir Modo SQL , colando a seguir a consulta e salvando-a como o nome desejado.

1-) Uma consulta seleção que irá selecionar os registros da tabela Authors. A consulta será armazenada com o nome de Seleciona_Autor , e, é exibida abaixo:

SELECT Authors.Author, Authors.[Year Born], Authors.Au_ID

FROM Authors

WHERE (((Authors.Au_ID)>=[:param1] And (Authors.Au_ID)<=[:param2]));

a-) SELECT Authors.Author, Authors.[Year Born], Authors.Au_ID

irá selecionar o nome do autor , o ano de nascimento e o código de identificação

b-) FROM Authors

da tabela Authors

c-) WHERE (((Authors.Au_ID)>=[:param1] And (Authors.Au_ID)<=[:param2]));

Quando o código do autor for maior ou igual a um parâmetro que iremos passar em tempo de execução e menor ou igual a outro parâmetro fornecido também em tempo de execução.

2-) Uma consulta Inclusão que irá incluir registros na tabela Authors. A consulta será armazenada com o nome de Insere_Autor , e, é exibida abaixo:

INSERT INTO Authors ( Author, [Year Born] )

SELECT [:param1] AS Expr1, [:param2] AS Expr2;

a-) INSERT INTO Authors ( Author, [Year Born] )

irá inserir na tabela Authors o nome e o ano de nascimento

b-) SELECT [:param1] AS Expr1, [:param2] AS Expr2;

param1 e param2 são os parêmetros passados em tempo de execução da consulta e referem-se aos campos : Authors e Year Born.

3-) Uma consulta Exclusão que irá excluir registros da tabela Authors. A consulta será armazenada com o nome de Exclui_Autor , e, é exibida abaixo:

DELETE Authors.Au_ID

FROM Authors

WHERE (((Authors.Au_ID)=[:param1]));

a-) DELETE Authors.Au_ID

irá excluir o registro referente ao codigo do autor

b-) FROM Authors

da tabela Authors

c-) WHERE (((Authors.Au_ID)=[:param1]));

onde o código do autor for igual ao parâmetro passado em tempo de execução

4- Uma consulta Atualização que irá atualizar registros da tabela Authors. A consulta será armazenada com o nome de Atualizai_Autor , e, é exibida abaixo:

UPDATE Authors SET Authors.Author = [:param1], Authors.[Year Born] = [:param2]

WHERE (((Authors.Au_ID)=[:param3]));

a-) UPDATE Authors SET Authors.Author = [:param1], Authors.[Year Born] = [:param2]

irá atualizar na tabela Authors os campos nome do autor e ano de nascimento usando os parâmetros passados em tempo de execução

b-) WHERE (((Authors.Au_ID)=[:param3]));

onde o código do autor for igual ao parâmetro passado em tempo de execução.

O que torna a utilização das consultas armazenadas parametrizadas dinâmicas é justamente a passagem dos parâmetros em tempo de execução ; isto permite a criação de um código de alto desempenho.

Ao final devemos Ter quatro consultas armazenadas no banco de dados. Se você abrir o banco de dados no Access e visualizar as consultas terá o seguinte:

Consultas armazenadas no banco de dados biblio.mdb

Criando Parâmetros em tempo de execução

Estamos prontos para usar as consultas criadas no banco de dados. Os parâmetros deverão ser criados e passados à consulta em tempo de execução Para isto iremos usar o objeto Parameter e o método CreateParameter da ADO.

O objeto Parameter representa um parâmetro ou argumento associado ao objeto Command baseado em uma consulta parametrizada ou um procedimento armazenado

O método CreateParameter cria um novo objeto Parameter com as propriedades especificadas e aplica-se ao objeto Command. Sua sintaxe é a seguinte:

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

Name – representa o nome do parâmetro definido na consulta armazenada

Type – representa o tipo de dados usado do objeto parameter. Os tipos mais usados são:

Direction – representa o tipo do parâmetro objeto usado. Pode Ter os seguintes valores:

Size – representa o tamanho máximo do parâmetro usado (em bytes ou caracteres)

Value – representa o valor o objeto Parameter ; é do tipo Variant.

Ao criar um parâmetro com o método CreateParameter ele não anexa automaticamente o objeto Parameter criado na coleção Parameters. Para anexar o parâmetro criado usamos o método Append com a seguinte sintaxe:

collection.Append object

No nosso exemplo usaremos a seguinte sintaxe:

commandObj.Parameters.Append commandObj. CreateParameter (Name, Type, Direction, Size, Value

O que estamos fazendo aqui é apenas criando o parâmetro via método CreateParameter e anexando-o a coleção Parameters do objeto Command via método Append em uma única instrução.

Usando as consultas armazenadas com passagem de parâmetros

Nosso exemplo usará dois arquivos de script ASP:

1-) principal.asp – este arquivo exibirá os dados da tabela Authors e indicará as opções que irão chamar o arquivo query.asp

2-) query.asp – este arquivo contém o script que irá criar os parâmetros que serão passando-os as consultas armazenadas e que irá executar as consultas gerando o ação requerida.

Ao iniciar , o arquivo principal.asp irá gerar a seguinte tela:

O código do arquivo principal.asp é o seguinte:

<html><head>
<TITLE>mostrar.asp</TITLE>
</head>
<body bgcolor="#00FFFF">
<% 
'Para acesso sem um DSN fariamos
set conntemp=server.createobject("adodb.connection")
conntemp.open "DBQ=C:\teste\biblio.mdb;DRIVER={Microsoft Access Driver (*.mdb)}"

'Vamos selecionar todos os registros da tabela Authors 
aspSQL="select * from authors"
set rstemp=conntemp.execute(aspSQL)
contador=0

' verifica se há dados no recordset
If rstemp.eof then
  response.write "Não há dados a exibir<br>"
  response.write aspSQL & "<br>Não retorna informação alguma..."
  connection.close
  set connection=nothing
  response.end
end if

' Vamos usar código HTML para criar uma tabela e exibir nossos dados
%>
<p><font color="#004080" size="5"><strong>Usando Consultas Armazenadas
 com Parametros - tabela:Authors </strong></font>
<hr>
<table border=2 bgcolor="#FFFFFF">
<%
' Vamos dar nome aos cabeçalhos dos campos
response.write "<tr>"

'Percorremos os campos
for each item in rstemp.fields
  response.write "<td><B>" & item.name & "</B></TD>"
next

response.write "</tr>"
' Agora iremos exibir os dados do Recordset gerado pela consulta SQL
' Vamos percorrer o recordset até chegar no final do arquivo
DO UNTIL rstemp.eof
  'atribuindo os valores dos campos as variáveis
  Codigo=rstemp("Au_ID")
  Nome=rstemp("Author")
  Nascimento=rstemp("Year born")
  'escrevendo os campos em uma tabela para exibição via um Browser
  response.write "<tr>"
  response.write "<td>" & Codigo & "</td>"
  response.write "<td>" & Nome & "</td>"
  response.write "<td>" & Nascimento & "</td>" 
  response.write "</tr>"
  'move-se para próximo registro do recordset
  rstemp.movenext
  contador=contador+1 
LOOP
%>
</table>
<%

' Fechamos o recordset e liberamos a memória usada

rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
%>

Esta tabela possui <% =contador %> registros
<table border="0" width="100%" bgcolor="white">
<tr>

<td><a href="http://macorati/query.asp?operacao=0"><strong>Selecionar
registros</strong></a></td>
<td><a href="http://macorati/query.asp?operacao=1"><strong>Inserir
registros</a></td>
<td><a href="http://macorati/query.asp?operacao=2"><strong>Excluir
Registros</strong></a></td>
<td><a href="http://macorati/query.asp?operacao=3"><strong>Atualizar
Registros</strong></a></td>
</tr>

</table>
</body>
</html>

O código deste arquivo já foi analisado anteriormente, ele exibe os dados da tabela Authors na tela. Mostra também as opções : Selecionar, Inserir, Excluir e Atualizar registros.

Cada opção exibida na tela pelo arquivo principal.asp , ao ser selecionada, irá chamar o arquivo query.asp passando como parâmetro a operação a ser a realizada , assim temos:

O arquivo query.asp irá verificar o parâmetro recebido e executar a operação pertinente. O seu código é exibido a seguir:

1-)Definimos um tempo máximo para execução de uma consulta em 60 segundos.

Timeout=60

Server.ScriptTimeout= Timeout

2-) Criamos uma conexão e abrimos a conexão com o banco de dados biblio.mdb em c:\teste

set connObj=server.createobject("adodb.connection")

connObj.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\biblio.mdb;"

3-) Criamos um objeto Command relacionado com a conexão aberta ; definimos o tempo máximo de execução de um comando antes de gerar um erro , e avaliamos o texto do comando como um procedimento armazenado (consulta parametrizada).

Set commandObj=Server.CreateObject("ADODB.Command")

commandObj.ActiveConnection=connObj

commandObj.CommandTimeout=Timeout

commandObj.CommandType=adCmdStoredProc

4-) Através de um instrução Select Case avaliamos o parâmetro passado para saber qual operação vamos executar:

Select Case request("operacao")

5-) Para seleção de registros , query.asp?operacao=0 , atribuímos o nome da consulta armazenada

commandObj.CommandText="Seleciona_Autor"

6-) Iremos selecionar os registros cujos valores do campo Au_Id forem maiores ou igual a dois e menores ou igual a 7.

Criamos dois parâmetros e o anexamos a coleção Parameters do objeto Command.

O primeiro será o referente a param1 e terá o valor 2

commandObj.Parameters.Append commandObj.CreateParameter("param1", _

adInteger,adParamInput,10,2)

O segundo refere-se a param2 e terá o valor 7.

commandObj.Parameters.Append commandObj.CreateParameter("param2", _

adInteger,adParamInput,10,7)

7-)Criamos um objeto Recordset do tipo somente leitura (ForwardOnly)

Set rsObj=Server.CreateObject("ADODB.Recordset")

rsObj.CursorType=1

8-) Executamos a consulta para gerar o recordset com os registros selecionados.

rsObj.Open commandObj

9-) Após exibir os registros selecionados , fechamos o recordset e excluimos os parâmetros criados

Set rsObj=Nothing

commandObj.Parameters.Delete "param2"

commandObj.Parameters.Delete "param1"

10-) Oferecemos a opção de retorno a página principal , após a exibição dos registros selecionados:

<a href="principal.asp"><strong>Voltar a pagina principal</strong></a><br>

O processo será repetido para cada operação , sendo que somente o tipo de consulta e a quantidade parâmetros será diferente. Assim temos para as consultas restantes:

a-) Consulta Inclui_Autor: query.asp?operacao=1

commandObj.CommandText="Insere_Autor"

Criamos dois parâmetros :

O primeiro parâmetro : param1 é do tipo adVarChar e refere-se ao campo Author. Será usado para receber o nome do autor que desejamos incluir.

commandObj.Parameters.Append commandObj.CreateParameter(":param1", _

adVarchar,adParamInput,50,"1")

O segundo parâmetro: param2 é do tipo inteiro e refere-se ao compo Year_Born . Irá receber o ano do nascimento do autor

commandObj.Parameters.Append commandObj.CreateParameter(":param2", _

adInteger,adParamInput,10,2)

Atribuimos valores aos parâmetros criados:

commandObj(":param1")="Jose Carlos Macoratti * "

commandObj(":param2")= 1976

Executamos a consulta para inserir efetivamente os valores na tabela Authors.

commandObj.Execute

Excluimos parâmetros criados

commandObj.Parameters.Delete ":param2"

commandObj.Parameters.Delete ":param1"

b-) Consulta Exclui_Autor: query.asp?operacao=2

commandObj.CommandText="Exclui_Autor"

Criamos um parâmetro, param1 , que irá receber um valor inteiro referente ao código do Autor a ser excluido.

commandObj.Parameters.Append commandObj.CreateParameter(":param1", _

adInteger,adParamInput,10,1)

Atribui o valor ao parâmetro e executa a consulta exclusão , removendo o registro da tabela Authors.

commandObj(":param1")= 15

commandObj.Execute

Exclui o parâmetro criado

commandObj.Parameters.Delete ":param1"

c-) Consulta Atualiza_Autor ; query.asp?operacao=3

commandObj.CommandText="Atualiza_Autor"

Criamos três parâmetros:

O parâmetro param1 , do tipo caractere, irá receber o valor referente ao nome do autor.

commandObj.Parameters.Append commandObj.CreateParameter(":param1", _

adVarchar,adParamInput, 50,"Z")

O parâmetro param2 , do tipo inteiro, irá receber o valor referente ao ano do nascimento do Autor.

commandObj.Parameters.Append commandObj.CreateParameter(":param2", _

adInteger,adParamInput,10,0)

O parâmetro param3 , do tipo inteiro, irá receber o valor referente ao código do Autor que desejamo atualizar.

commandObj.Parameters.Append commandObj.CreateParameter(":param3", _

adInteger,adParamInput,10,0)

Atribuímos valores aos parâmetros, e executamos a consulta atualização

commandObj(":param1")="Registro Atualizado * "

commandObj(":param2")=1965

commandObj(":param3")=10

commandObj.Execute

Exclui o parâmetro criado

commandObj.Parameters.Delete ":param3"

commandObj.Parameters.Delete ":param2"

commandObj.Parameters.Delete ":param1"

Ao final do processamento fechamos o objeto command e a conexão criada.

Set commandObj=Nothing

connObj.Close

Set connObj=Nothing

E Redirecionamos para o arquivo principal.asp caso as operações executadas forem : 1, 2 ou 3.

Select Case request("operacao")

case "1" , "2" , "3"

response.redirect "/principal.asp"

end select

Embora tenhamos atribuído os valores aos parâmetros embutidos no código , nada impede , e, certamente seria mais útil e elegante que permitíssemos ao usuário informar os valores que seriam atribuídos aos parâmetros.

Poderíamos usar formulários para coletar estas informações. Para facilitar a explicação do código preferimos deixar isto como um exercício para você implementar.

Até mais ver ...

Copyright (c) 2000 - José Carlos Macoratti