Como utilizar parâmetros em dbExpress (DBX) Framework
Em reposta a dúvida do Rafael e Carlos Gonzaga sobre como criar consultadas parametrizadas com dbExpress Framework resolvi escrever sobre o tema.
Para começar, existe uma pequena diferença ao utilizar parâmetros em SQL com DBX Framework e VCL. Enquanto a VCL utiliza “:PARAMETRO” para definir parâmetros em uma query, em DBX Framework parâmetro é representado pelo sinal de “?” e não inclui nome.
Como estamos acostumados com a VCL e certamente é muito mais legível codificar utilizando o nome dos parâmetros, você espera trabalhar da mesma forma com DBX Framework e isso é possível criando uma pequena extensão ao DBX Framework.
Para entender melhor, caso você utilize puramente o framework, seu código seria assim:
program DBX4Example;
{$APPTYPE CONSOLE}
uses
SysUtils,
DBXDynalink,
DBXCommon,
DBXInterbase;
var
aDBXConn: TDBXConnection;
aDBXTrans : TDBXTransaction;
aCmnd: TDBXCommand;
aReader: TDBXReader;
begin
aDBXConn := TDBXConnectionFactory.GetConnectionFactory.GetConnection('employee ib','sysdba','masterkey');
if aDBXConn <> nil then
begin
aCmnd := aDBXConn.CreateCommand;
// Start transaction
aDBXTrans:= aDBXConn.BeginTransaction(TDBXIsolations.ReadCommitted);
// Prepare and execute the SQL Statement
aCmnd.Text := 'SELECT * FROM Country Where Country = ?';
aCmnd.Prepare;
aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');
aReader := aCmnd.ExecuteQuery;
while aReader.Next do
begin
Writeln( aReader.Value['Country'].GetAnsiString );
end;
// Commit transaction
aDBXConn.CommitFreeAndNil(aDBXTrans);
Readln;
aReader.Free;
aCmnd.Free;
aDbxConn.Free;
end;
end.
Veja que a query utiliza interrogação para definir o parâmetro e após o Prepare a longa linha para definir o valor do parâmetro com posição zero.
Esta primeira parte responderia a pergunta do Rafael e do Carlos, mas podemos tornar esse código mais amigável utilizando Class Helpers, recurso este que foi implementado no Delphi 2006 e nos ajuda a extender classes sem utilizar herança.
Assim sendo vou estender a classe TDBXParameterList, o qual irá permitir que eu nomeie os parâmetros e define seus respectivos valores por nome.
O código abaixo estende a classe utilizando a sintaxe class helper, veja bem isso não é herança. Esta extensão define os seguintes métodos:
SetParamatersName: este método irá receber uma lista de nomes, o qual será usado para nomear os parâmetros, você deve passar a quantidade de nomes igual a quantidade de parâmetros;
ByName: método que nos permite acessar os parâmetros através do nome, o DBX Framework traz o método GetOrdinal que permite acessar os parâmetros por nome, o ByName irá simplificar este acesso;
unit DBXParameterListHelper;
interface
uses
DBXCommon, SysUtils;
type
TDBXParameterListHelper = class helper for TDBXParameterList
public
procedure SetParamatersName(aNames: Array of String);
function ByName( Name : String ) : TDBXWritableValue;
end;
implementation
{ TDBXParameterListHelper }
function TDBXParameterListHelper.ByName(Name: String): TDBXWritableValue;
begin
Result := Self.Parameter[Self.GetOrdinal(Name)].Value;
end;
procedure TDBXParameterListHelper.SetParamatersName(aNames: array of String);
var
i: Integer;
begin
if Length(aNames) <> Self.Count then
raise Exception.Create('aNames should have the same number of parameters');
for i := 0 to Self.Count - 1 do
begin
Self[i].Name := aNames[i];
end;
end;
end.
Com está extensão esta parte do código:
// Prepare and execute the SQL Statement
aCmnd.Text := 'SELECT * FROM Country Where Country = ?';
aCmnd.Prepare;
aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');
Ficaria assim:
// Prepare and execute the SQL Statement
aCmnd.Text := 'SELECT * FROM Country Where Country = ?';
aCmnd.Prepare;
aCmnd.Parameters.SetParamatersName(['COUNTRYNAME']);
aCmnd.Parameters.ByName('COUNTRYNAME').SetAnsiString('USA');
Caso a query tenha vários parâmetros, ficaria assim:
// Prepare and execute the SQL Statement
aCmnd.Text := 'SELECT * FROM Country Where Country = ? and Currency = ?';
aCmnd.Prepare;
aCmnd.Parameters.SetParamatersName(['COUNTRYNAME', 'CURRENCY']);
aCmnd.Parameters.ByName('COUNTRYNAME').SetAnsiString('USA');
aCmnd.Parameters.ByName('CURRENCY').SetAnsiString('Dolar');
Ao final vimos como utilizar consultas parametrizadas com DBX Framework e como utilizar class helper para estender classes.
Download do código fonte aqui.
[...] This post was mentioned on Twitter by Embarcadero Tech, Andreano Lanusse. Andreano Lanusse said: Blog post: Como utilizar parâmetros em dbExpress (DBX) Framework http://bit.ly/bDVkdk [...]
Um bom exercício, mas acho que a utilidade pode não ser tão boa, em comparação com a VCL.
Eu notei, especialmente, dois problemas:
- Não posso colocar os parâmetros na query, lá ainda temos o “?”
- Não posso usar um mesmo parâmetro em dois pontos na query (dois parâmetros com o mesmo nome)
Oi Leandro,
Na forma que implementei não é possível ter parâmetros com o mesmo nome, mas como os parâmetros são objetos, temos o caminho para fazer isso. Sobre não poder colocar o parâmetro na query, não entendi, visto que “?” é o parâmetro.
A idéia aqui não é tornar o framework a VCL e sim tornar o desenvolvimento mais amigável com o framework. Além disso a performance do framework é muito maior que a da VCL para operações onde não temos data aware.
Oi Andreano
Bom exemplo
Não sei se estou implementando errado, mas seguindo o exemplo padrão (não implementando as extensões), dá um erro “Invalid Ordinal : 0″
Nota: Uso sql server express 2005 sp3 com windows 7 utimate/windows xp sp3
Verifiquei tbm q depois do “Prepare” a propriedade “IsPrepared” fica false
Ótimo post , os sesus post estão sendo de grande valia.
Continue assim !!
Ordinal é o nome da coluna, parece que você está passando 0 como nome de coluna.
Achei o post uma maravilha, mas gostaria de saber como faço para passar outros tipos de valores para os parâmetros como por exemplo do tipo TDBXDate.
Valeu!
É só passar um TDateTime
Mas seguindo o exemplo como lhe disse, o “0″ é a ordem do parametro:
…
con := CDataBase(‘SQL2005CONN’);
cmd :=con.CreateCommand ;
cmd.Text :=’select *from cadpro00 where pro00_codsvc = ?’;
cmd.Parameters.Parameter[0].Value.SetInt16(107);
// if cmd.IsPrepared then
reader :=cmd.ExecuteQuery ;
…
Ou seja o “0″ q estou passando é justamente a posição do primeiro “?” não é isso?
E outra pq a propriedade “IsPrepared” do TDBXCommand não fica “True” !!!
É isso mesmo.
No caso da propriedade isPrepared, como é a primeira execução a query ainda não foi preparada mesmo.
Sim é justamente ai q da o erro: “Invalid Ordinal : 0″ quando executo a query.
O q pode estar acontecendo? será q não funciona com o driver do sql server?
O erro “Invalid Ordinal” que os comentários acima relataram é porque o código apresentado pelo Andreano não cria o parâmetro antes de atribuí-lo o valor ‘USA’.
Segue código corrigido:
program DBX4Example;
{$APPTYPE CONSOLE}
uses
SysUtils,
DBXDynalink,
DBXCommon,
DBXInterbase;
var
aDBXConn : TDBXConnection;
aDBXTrans : TDBXTransaction;
aCmnd : TDBXCommand;
aReader : TDBXReader;
aParam : TDBXParameter;
begin
aDBXConn := TDBXConnectionFactory.GetConnectionFactory.GetConnection('employee ib','sysdba','masterkey');
if aDBXConn nil then begin
aCmnd := aDBXConn.CreateCommand;
// Start transaction
aDBXTrans:= aDBXConn.BeginTransaction(TDBXIsolations.ReadCommitted);
// Prepare and execute the SQL Statement
aCmnd.Text := 'SELECT * FROM Country Where Country = ?';
aParam := aCmnd.CreateParameter;
aParam.DataType := TDBXDataTypes.WideStringType;
aParam.Name := 'COUNTRY';
aCmnd.Prepare;
aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');
aReader := aCmnd.ExecuteQuery;
while aReader.Next do
Writeln( aReader.Value['Country'].GetAnsiString );
// Commit transaction
aDBXConn.CommitFreeAndNil(aDBXTrans);
Readln;
aReader.Free;
aCmnd.Free;
aDbxConn.Free;
end;
end.
Percebam que a possibilidade de nomear um parâmetro pela propriedade TDBXParameter.Name abre caminho para que haja controle por nome dos parâmetros sem necessidade de se criar class helpers.
Errata:
Faltou o comando:
aCmnd.Parameters.AddParameter(aParam);
antes de:
aCmnd.Prepare;
Oi Rafael, realmente é isso mesmo, deu certo agora.
Tanto acessando pelo nome como tbm pelo index.
Valeu mesmo