A problem to be avoided is thinking that parameters within a query are like macro-replacements, a kind of Search\/Replace within queries, as they are not. Parameters are ways to identify values and their types to the DBMS server in an organized and error-free manner and, in addition, the possibility of repeating the same SQL sentence to be more performant. Next, I describe how this is possible.<\/p>\n\n\n\n
Most programmers like to use literal queries, that is, concatenating values to produce a query that you will later read and understand what was requested from the server and compare with what was returned. Here's an example of a literal query:<\/p>\n\n\n\n
if Query1.Active then Query1.Close; Query1.SQL.Text:= 'UPDATE CLIENTS SET '+ ' RAZAO_SOCIAL='''+Trim(edtRazaoSocial.Text)+'''' ' WHERE ID_CLIENTE='+edtID_CLIENTE.Text; Query1.ExecSQL;<\/pre><\/div>\n\n\n\nWhat's the problem with the query above? Imagine that the corporate name is INTELIG S\/A<\/strong> and the CLIENT_ID is 666 then the query with all the concatenation would be as follows:<\/p>\n\n\n\n
UPDATE CLIENTS SET RAZAO_SOCIAL='INTELIG S\/A' WHERE ID_CLIENTE=666<\/pre><\/div>\n\n\n\nIf you've come this far, you probably haven't seen much of anything. But let's say now that the corporate name entered was \u201d\u2013INTELIG S\/A<\/strong>(two single quotes on purpose), what then would the SQL sentence look like? Let's see:<\/p>\n\n\n\n
UPDATE CLIENTS SET RAZAO_SOCIAL=''--INTELIG S\/A' WHERE CLIENT_ID=666<\/em><\/strong><\/code><\/pre>\n\n\n\nIf you're good at SQL, you've noticed that I've turned what's from the two forward dashes into a comment and now all the customer names will be deleted from the base. So the main problem in writing literal queries is dealing with code injection. Some can use QuotedStr() and this function will solve the issue of quotes, but it is anti-performative especially when we are dealing with long texts, imagine HTML code, RTF or long memos counting the position of the quotes and doubling them. Also, QuotedStr doesn't resolve issues involving unicode codes, escape characters, and other methods that can sabotage your query. So what's the solution? Use parameters and signal the end of the query, see the example:<\/p>\n\n\n\n
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('UPDATE CLIENTS SET '); Query1.SQL.Add(' RAZAO_SOCIAL=:P_RAZAO_SOCIAL'); Query1.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE'); Query1.SQL.Add(';'); \/\/ signaling the end of the query Query1.ParamByName('P_RAZAO_SOCIAL').AsString:=edtRazaoSocial.Text; Query1.ParamByName('P_ID_CLIENTE').AsInteger:=StrToInt(edtID_CLIENTE.Text); Query1.ExecSQL;<\/pre><\/div>\n\n\n\nEm nosso exemplo, sob nenhuma hip\u00f3tese nossa query poder\u00e1 ser sabotada pelo tipo de dados que informamos num formul\u00e1rio. Talvez se pergunte porque eu n\u00e3o usei SQL.Text e concatenei tudo, se voc\u00ea viu o artigo anterior a respeito de produtividade na IDE Delphi e Lazarus deve ter notado que fa\u00e7o uso do MultiPaste<\/a> ou Column Mode. Sim, usando estes recursos, escrevo a query dentro do editor SQL(IBExpert, Flamerobin,…), testo a query, e depois de funcionando ent\u00e3o vou col\u00e1-lo na nossa IDE de programa\u00e7\u00e3o, da\u00ed usando usando MultiPaste<\/a> ou Column Mode gero o c\u00f3digo de forma r\u00e1pida como visto acima, m\u00e3o na roda!<\/p>\n\n\n\n
But we're not done yet, parameterized queries have yet another advantage, if they are very repetitive in our application we can prepare them just once and use them many times, optimizing them for network use.<\/p>\n\n\n\n
Using parameterized queries with preparation<\/h2>\n\n\n\n
Aprendemos at\u00e9 aqui a orientar programadores a n\u00e3o usarem querys literais, mas usar querys parametrizadas, mas h\u00e1 outro cen\u00e1rio que elas tamb\u00e9m nos beneficiam, vamos imaginar um sistema onde em v\u00e1rias partes \u00e9 preciso sempre repetir a mesma busca:<\/p>\n\n\n\n
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT status, corporate_id, client_id FROM CLIENTS'); Query1.SQL.Add('WHERE CNPJ=:P_CNPJ'); Query1.ParamByName('P_CNPJ').AsString:=Trim(Search.Text); Query1.Open();<\/pre><\/div>\n\n\n\nQuerys como a acima ser\u00e3o executadas no servidor SGBD sempre da mesma forma, (1) fazendo a analise sintatica da query, (2) otimizando a query, (3) calculando custo de opera\u00e7\u00e3o com \u00edndices, (4) compilando para o formato BLR e finalmente (5) executar e se necess\u00e1rio ent\u00e3o retornar o resultado. De todas essas etapas a mais custosa \u00e9 o PLANO(plan) para escolher o \u00edndice adequado porque quanto mais \u00edndices existirem, mais tempo levar\u00e1 a analise, isso contradiz um pouco a ideia de que quanto mais indices existirem \u00e9 melhor, n\u00e3o? Estou me desviando do t\u00f3pico, 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<\/mark>), todas ser\u00e3o executadas in\u00fameras vezes e passando pelo mesmo processo a cada consulta. <\/p>\n\n\n\n
There is a way to optimize this using parameters, see:<\/p>\n\n\n\n
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT status, social_reason, cnpj FROM CLIENTS'); Query1.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE'); if not Query1.Prepared then Query1.Prepare;<\/pre><\/div>\n\n\n\nNotou o Query1.Prepare? O que ele faz? Ele submete ao SGBD a uma “prepara\u00e7\u00e3o”, isto \u00e9, nossa query vai for\u00e7ar o SGBD a ir at\u00e9 a \u00faltima etapa, mas n\u00e3o executa, mas manter\u00e1 o BLR e o PLANO e assim nas pr\u00f3ximas execu\u00e7\u00f5es repetitivas n\u00e3o ser\u00e1 necesspario passar por todo o processo novamente. <\/p>\n\n\n\n
E se eu n\u00e3o usar o m\u00e9todo Prepare<\/strong>? Ferramentas de programa\u00e7\u00e3o como o Delphi e Lazarus fazem inferencia, isto \u00e9, acabam preparando do mesmo jeito s\u00f3 que isso pode acontecer num momento incoveniente do programa e mais de uma vez<\/strong> , ent\u00e3o \u00e9 bom que voc\u00ea fa\u00e7a a prepara\u00e7\u00e3o num momento bem antes a execu\u00e7\u00e3o a querie para que o tempo de prepara\u00e7\u00e3o n\u00e3o se some ao tempo da execu\u00e7\u00e3o.<\/p>\n\n\n\n
Como o SGBD saber\u00e1 que uma query preparada j\u00e1 existe? O servidor manter\u00e1 na mem\u00f3ria todas as querys j\u00e1 preparadas em forma de assinatura:<\/p>\n\n\n\n
SELECT<\/strong> status, social_reason, cnpj FROM CLIENTS WHERE CLIENT_ID=?<\/strong><\/code><\/pre>\n\n\n\nEsta \u00e9 a assinatura, em alguns SGBDs os nomes dos campos s\u00e3o irrelevantes. Qualquer query similar a assinatura ser\u00e1 reconhecida imediatamente como uma query preparada.<\/p>\n\n\n\n
N\u00e3o importa para o SGBD de qual esta\u00e7\u00e3o, programa ou parte do programa venha a query, o que importa \u00e9 a assinatura, se ela for reconhecida ent\u00e3o usar\u00e1 o que j\u00e1 foi preparado. Por isso podemos dizer que querys preparadas n\u00e3o \u00e9 feita para beneficiar apenas 1 conex\u00e3o\/programa, mas muitas conex\u00f5es e muitos programas diferentes onde estatisticamente a mesma consulta acontece. <\/p>\n\n\n\n
Notou que eu fa\u00e7o um teste antes de prepar\u00e1-las? Olha s\u00f3:<\/p>\n\n\n\n
if not Query1.Prepared then Query1.Prepare;<\/pre><\/div>\n\n\n\nPorque fa\u00e7o isso? Parece \u00f3bvio, mas se prepararmos todas as vezes n\u00e3o obteremos a performance que desejamos. A prepara\u00e7\u00e3o deve ser feita uma \u00fanica vez, e nunca mais repetida. Mesmo que eu feche o programa e abrir de novo, a prepara\u00e7\u00e3o deve estar l\u00e1 em alguma parte da mem\u00f3ria do SGBD. <\/p>\n\n\n\n
O SGBD ir\u00e1 eliminar da mem\u00f3ria os recursos n\u00e3o utilizados e isso inclui prepara\u00e7\u00f5es “obsoletas” ou em “desuso”, mas tamb\u00e9m quando explicitamente o programador chama um m\u00e9todo conhecido como “UnPrepare” – falaremos dele mais tarde.<\/p>\n\n\n\n
A “prepara\u00e7\u00e3o” tamb\u00e9m \u00e9 excelente em transferencias de dados em lote, veja o exemplo:<\/p>\n\n\n\n
if Query1.Active then\n Query1.Close;\nQuery1.SQL.Clear;\nQuery1.SQL.Add('SELECT status, razao_social, cnpj FROM CLIENTES');\nQuery1.SQL.Add('WHERE ULTIMO_PEDIDO<dateadd (-5 year to current_date)');\nQuery1.Open()\n\nif Query2.Active then\n Query2.Close;\nQuery2.SQL.Clear;\nQuery2.SQL.Add('UPDATE CLIENTES SET STATUS=''C'' ');\nQuery2.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE');\nif not Query2.Prepared then\n Query2.Prepare;\n\nwhile not Query1.eof do\nbegin\n (...)\n if isPrecisaCancelar then\n begin\n Query2.ParamByName('P_ID_CLIENTE').AsInteger:=Query1.FieldByName('P_ID_CLIENTE').AsInteger;\n Query2.ExecSQL;\n end; \n Query1.Next;\nend;\n\nif Query2.Prepared then\n Query2.UnPrepare;<\/pre><\/div>\n\n\n\nIn the example above, the repetition of the same query would be obvious so we performed the \u201cpreparation\u201d and saved time. Another interesting thing that I didn't mention earlier was that at the end of the process, when I'm not going to use the prepared query anymore, then run:<\/p>\n\n\n\n
if Query2.Prepared then Query2.UnPrepare;<\/pre><\/div>\n\n\n\nO UnPrepare acima far\u00e1 com que a assinatura de nossa “prepara\u00e7\u00e3o” no SGBD seja eliminada, isso \u00e9 \u00fatil porque elimina da mem\u00f3ria do SGBD todos os recursos alocados.<\/p>\n\n\n\n
Devo fazer sempre o UnPrepare no final? Quase sempre a resposta \u00e9 “nunca”. Geralmente s\u00f3 usamos o “UnPrepare” nas situa\u00e7\u00f5es de transferencia de lote de dados, um cen\u00e1rio onde n\u00e3o h\u00e1 outros na rede que poderiam se beneficiar de uma query preparada. Um “UnPrepare” quando outros na erde est\u00e3o se beneficiando de queries repetitivas mataria a performance. Ent\u00e3o a regra \u00e9 simples: quando a chance \u00e9 zero de outros repetirem o mesmo procedimento depois que o mesmo j\u00e1 foi executado ent\u00e3o podemos com tranquilidade chamar o m\u00e9todo “UnPrepare”, nos outros cen\u00e1rios, voc\u00ea nunca ir\u00e1 us\u00e1-lo.<\/p>\n\n\n\n
No v\u00eddeo a seguir, eu demonstro a diferen\u00e7a entre uma query preparada e outra n\u00e3o preparada:<\/p>\n\n\n\n