Um problema a ser evitado é achar que parâmetros dentro de uma query são como macro-substituições, uma especie de Search/Replace dentro de querys, pois não são. Parâmetros são formas de identificar para o servidor SGBD os valores e seus tipos de forma organizada e não sujeita a erros e além disso, a possibilidade de repetição de uma mesma sentença SQL para ser mais performática. Na sequencia descrevo como isso é possível.
Usando querys parametrizadas
Boa parte dos programadores gostam de usar querys literais, isto é, concatenando valores para produzir uma query que mais tarde você vai ler e entender o que foi solicitado ao servidor e comparar com o que retornou. Veja o exemplo de uma query literal:
if Query1.Active then Query1.Close; Query1.SQL.Text:= 'UPDATE CLIENTES SET '+ ' RAZAO_SOCIAL='''+Trim(edtRazaoSocial.Text)+'''' ' WHERE ID_CLIENTE='+edtID_CLIENTE.Text; Query1.ExecSQL;
Qual o problema com a query acima? Imagine que a razão social é INTELIG S/A e o ID_CLIENTE seja 666 então a query com toda a concatenação seria a seguinte:
UPDATE CLIENTES SET RAZAO_SOCIAL='INTELIG S/A' WHERE ID_CLIENTE=666
Se você chegou até aqui, não deve ter visto nada de mais. Mas vamos dizer agora que a razão social digitada foi ”–INTELIG S/A(duas aspas simples proposital), como então seria a sentença SQL? Vejamos:
UPDATE CLIENTES SET RAZAO_SOCIAL=''--INTELIG S/A' WHERE ID_CLIENTE=666Se você for bom de SQL, notou que eu transformei o que há a partir dos dois traços para frente em comentário e agora os nomes de todos os clientes serão apagados na base. Então o principal problema em escrever querys literais é lidar com a injeção de código. Alguns podem usar QuotedStr() e essa função resolverá a questão de aspas, mas é anti-performática especialmente quando estamos lidando longos textos, imagine um código HTML, RTF ou longos Memos ficar contando as posição das aspas e duplicando-as. Além disso, QuotedStr não resolve questões envolvendo códigos unicode, caracteres de escape e outros métodos que podem sabotar sua query. Então qual é a solução? Usar parâmetros e sinalizar o final da query, veja o exemplo:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('UPDATE CLIENTES SET ');
Query1.SQL.Add(' RAZAO_SOCIAL=:P_RAZAO_SOCIAL');
Query1.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE');
Query1.SQL.Add(';'); // sinalizando o fim da query
Query1.ParamByName('P_RAZAO_SOCIAL').AsString:=edtRazaoSocial.Text;
Query1.ParamByName('P_ID_CLIENTE').AsInteger:=StrToInt(edtID_CLIENTE.Text);
Query1.ExecSQL;Em nosso exemplo, sob nenhuma hipótese nossa query poderá ser sabotada pelo tipo de dados que informamos num formulário. Talvez se pergunte porque eu não usei SQL.Text e concatenei tudo, se você viu o artigo anterior a respeito de produtividade na IDE Delphi e Lazarus deve ter notado que faço uso do MultiPaste ou Column Mode. Sim, usando estes recursos, escrevo a query dentro do editor SQL(IBExpert, Flamerobin,…), testo a query, e depois de funcionando então vou colá-lo na nossa IDE de programação, daí usando usando MultiPaste ou Column Mode gero o código de forma rápida como visto acima, mão na roda!
Mas ainda não terminamos, querys parametrizadas tem ainda outra vantagem, caso elas sejam muito repetitivas em nossa aplicação poderemos prepará-las uma única vez e usá-las muitas vezes otimizando-a para uso em rede.
Usando querys parametrizadas com preparação
Aprendemos até aqui a orientar programadores a não usarem querys literais, mas usar querys parametrizadas, mas há outro cenário que elas também nos beneficiam, vamos imaginar um sistema onde em várias partes é preciso sempre repetir a mesma busca:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT status, razao_social, id_cliente FROM CLIENTES');
Query1.SQL.Add('WHERE CNPJ=:P_CNPJ');
Query1.ParamByName('P_CNPJ').AsString:=Trim(Pesquisa.Text);
Query1.Open();Querys como a acima serão executadas no servidor SGBD sempre da mesma forma, (1) fazendo a analise sintatica da query, (2) otimizando a query, (3) calculando custo de operação com índices, (4) compilando para o formato BLR e finalmente (5) executar e se necessário então retornar o resultado. De todas essas etapas a mais custosa é o PLANO(plan) para escolher o índice adequado porque quanto mais índices existirem, mais tempo levará a analise, isso contradiz um pouco a ideia de que quanto mais indices existirem é melhor, não? Estou me desviando do tópico, retomando…imagine a query acima dentro de um sistema de atendimento com mais de 400 pessoas realizando a mesma consulta, apenas com clientes diferentes (WHERE CNPJ=:P_CNPJ), todas serão executadas inúmeras vezes e passando pelo mesmo processo a cada consulta.
Há uma forma de otimizar isso usando parâmetros, veja:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT status, razao_social, cnpj FROM CLIENTES');
Query1.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE');
if not Query1.Prepared then
Query1.Prepare;Notou o Query1.Prepare? O que ele faz? Ele submete ao SGBD a uma “preparação”, isto é, nossa query vai forçar o SGBD a ir até a última etapa, mas não executa, mas manterá o BLR e o PLANO e assim nas próximas execuções repetitivas não será necesspario passar por todo o processo novamente.
E se eu não usar o método Prepare? Ferramentas de programação como o Delphi e Lazarus fazem inferencia, isto é, acabam preparando do mesmo jeito só que isso pode acontecer num momento incoveniente do programa e mais de uma vez , então é bom que você faça a preparação num momento bem antes a execução a querie para que o tempo de preparação não se some ao tempo da execução.
Como o SGBD saberá que uma query preparada já existe? O servidor manterá na memória todas as querys já preparadas em forma de assinatura:
SELECT status, razao_social, cnpj FROM CLIENTES WHERE ID_CLIENTE=?Esta é a assinatura, em alguns SGBDs os nomes dos campos são irrelevantes. Qualquer query similar a assinatura será reconhecida imediatamente como uma query preparada.
Não importa para o SGBD de qual estação, programa ou parte do programa venha a query, o que importa é a assinatura, se ela for reconhecida então usará o que já foi preparado. Por isso podemos dizer que querys preparadas não é feita para beneficiar apenas 1 conexão/programa, mas muitas conexões e muitos programas diferentes onde estatisticamente a mesma consulta acontece.
Notou que eu faço um teste antes de prepará-las? Olha só:
if not Query1.Prepared then Query1.Prepare;
Porque faço isso? Parece óbvio, mas se prepararmos todas as vezes não obteremos a performance que desejamos. A preparação deve ser feita uma única vez, e nunca mais repetida. Mesmo que eu feche o programa e abrir de novo, a preparação deve estar lá em alguma parte da memória do SGBD.
O SGBD irá eliminar da memória os recursos não utilizados e isso inclui preparações “obsoletas” ou em “desuso”, mas também quando explicitamente o programador chama um método conhecido como “UnPrepare” – falaremos dele mais tarde.
A “preparação” também é excelente em transferencias de dados em lote, veja o exemplo:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT status, razao_social, cnpj FROM CLIENTES');
Query1.SQL.Add('WHERE ULTIMO_PEDIDO<dateadd (-5 year to current_date)');
Query1.Open()
if Query2.Active then
Query2.Close;
Query2.SQL.Clear;
Query2.SQL.Add('UPDATE CLIENTES SET STATUS=''C'' ');
Query2.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE');
if not Query2.Prepared then
Query2.Prepare;
while not Query1.eof do
begin
(...)
if isPrecisaCancelar then
begin
Query2.ParamByName('P_ID_CLIENTE').AsInteger:=Query1.FieldByName('P_ID_CLIENTE').AsInteger;
Query2.ExecSQL;
end;
Query1.Next;
end;
if Query2.Prepared then
Query2.UnPrepare;No exemplo acima, a repetição da mesma query seria óbvia então realizamos a “preparação” e economizados tempo. Outra coisa interessante e que não mencionei anteriormente foi que ao termino do processo, quando não vou mais usar a query preparada então executar:
if Query2.Prepared then Query2.UnPrepare;
O UnPrepare acima fará com que a assinatura de nossa “preparação” no SGBD seja eliminada, isso é útil porque elimina da memória do SGBD todos os recursos alocados.
Devo fazer sempre o UnPrepare no final? Quase sempre a resposta é “nunca”. Geralmente só usamos o “UnPrepare” nas situações de transferencia de lote de dados, um cenário onde não há outros na rede que poderiam se beneficiar de uma query preparada. Um “UnPrepare” quando outros na erde estão se beneficiando de queries repetitivas mataria a performance. Então a regra é simples: quando a chance é zero de outros repetirem o mesmo procedimento depois que o mesmo já foi executado então podemos com tranquilidade chamar o método “UnPrepare”, nos outros cenários, você nunca irá usá-lo.
No vídeo a seguir, eu demonstro a diferença entre uma query preparada e outra não preparada:
Quando não usar a preparação
Sentenças SQL são muito flexíveis e cada SGBD tem suas peculiaridades e não é possível descrever todas as situações, mas quando uma sentença é ambigua para tomada de decisão você deve evitar uma preparação. Com exemplo podemos citar o like, quando usamos:
SELECT * FROM CLIENTES WHERE RAZAO_SOCIAL LIKE 'INDUSTRIA%'
No exemplo acima, está bem claro que se houver um índice associado a razão social, o SGBD deverá usá-lo, mas e se a query fosse:
SELECT * FROM CLIENTES WHERE RAZAO_SOCIAL LIKE '%INDUSTRIA%'
Neste novo exemplo, o SGBD irá ignorar qualquer índice, pois não faria sentido usá-los já que todos os registros precisarão ser lidos de qualquer forma. Então imaginaria o que aconteceria se preparasse uma query assim:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM CLIENTES');
Query1.SQL.Add('WHERE RAZAO_SOCIAL LIKE :PESQUISA');
if not Query1.Prepared then
Query1.Prepare;Pausa dramática. Como você não consegue determinar o que a pessoa digitará na barra de pesquisa, vamos inventar que a pessoa tenha pesquisado por:
'INDUSTRIA%'Como ela é a primeira pessoa a fazer a preparação, nosso PLANO seria usar um índice por Razão Social, certo? Sim, isto estaria certo, mas note que a mesma já foi preparada e há no SGBD a assinatura que já indicará o PLANO a ser executado. Daí vem a segunda pessoa e no campo de pesquisa faz a seguinte busca:
'%INDUSTRIA%' Como a query já foi preparada, as pesquisas seguintes usarão o mesmo PLANO, ou seja, você forçou o uso de um índice imprestável ao LIKE que só o fará perder tempo.
Alguns SGBD podem detectar a situação acima através de probabilidades e estatísticas e agir de forma preditiva, mas é muito melhor quando programadores evitam situações assim. Hoje um programador pode usar um banco e amanhã outro completamente diferente que não seja tão preditivo e espere que o desenvolvedor seja mais esperto e não submeta armadilhas ao próprio SGBD.
Respondendo logo a questão, em situações ambíguas evite usar parâmetro e prefira o QuotedStr() em seu lugar:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM CLIENTES');
Query1.SQL.Add('WHERE RAZAO_SOCIAL LIKE '+QuotedStr(edtPesquisa.Text));Conclusão
Tanto os parâmetros como a preparação de uma query não depende da linguagem de programação, pois este recurso está em todas elas. Estes recursos servem a um propósito bem definido: melhorar e agilizar pesquisas. Se notar que programadores de sua equipe não estão usando a tais, enviem o link para este artigo.
Cada SGBD é diferente, alguns deles subvertem querys mal feitas, outros usam algoritmos para melhorar uma query pouco performática, mas não se engane, se programadores forem eficientes na origem da query o banco será muito mais performático, do contrário, o banco vai ser uma tartaruga obesa com 3 patas.