Lazarus

Programming => Databases => Topic started by: superc on April 19, 2021, 10:22:15 am

Title: Zeos with sqlite print sql text
Post by: superc on April 19, 2021, 10:22:15 am
Hello,

I use a Zeos 7.2 component with Sqlite3 and I have a code like this:

Code: Pascal  [Select][+][-]
  1. var
  2.   qry : TZQuery;
  3. begin
  4.   try
  5.     qry := TZQuery.Create(self);
  6.     qry.Connection := ZConnection1;
  7.  
  8.     qry.SQL.Clear;
  9.     qry.SQL.Add('SELECT * FROM x');
  10.     qry.SQL.Add('WHERE x = :x');
  11.     qry.SQL.Add('  AND y = :y');
  12.     qry.ParamByName('x').AsString := 'blablabla';
  13.     qry.ParamByName('y').AsString := '1';
  14.  
  15.     qry.Open;
  16.   finally
  17.     qry.Free;
  18.   end;
  19.  
  20. end;    
  21.  

If I try to print sql text with
Code: Pascal  [Select][+][-]
  1.     ShowMessage(qry.SQL.Text);
i get the sql without the real parameters passed.

How can I get the real SQL with parameters that is executed?

Thanks in advance.

Title: Re: Zeos with sqlite print sql text
Post by: WooBean on April 19, 2021, 10:34:53 am
Line 9.  qry.SQL.Add('SELECT * FROM x');
tells that you are using table 'x' and then you are using column 'x' in that table 'x'.

Is it really intended?
Title: Re: Zeos with sqlite print sql text
Post by: dseligo on April 19, 2021, 10:54:55 am
Try something like this:

Code: Pascal  [Select][+][-]
  1. var
  2.   qry : TZQuery;
  3.   i: Integer;
  4. begin
  5.   try
  6.     qry := TZQuery.Create(self);
  7.     qry.Connection := ZConnection1;
  8.  
  9.     qry.SQL.Clear;
  10.     qry.SQL.Add('SELECT * FROM x');
  11.     qry.SQL.Add('WHERE x = :x');
  12.     qry.SQL.Add('  AND y = :y');
  13.     qry.ParamByName('x').AsString := 'blablabla';
  14.     qry.ParamByName('y').AsString := '1';
  15.  
  16.     For i:=0 to qry.Params.Count-1 do
  17.       ShowMessage(qry.Params[i].DisplayName+': '+qry.Params[i].AsString);
  18.  
  19.     qry.Open;
  20.   finally
  21.     qry.Free;
  22.   end;
  23.  
  24. end;    
  25.  
Title: Re: Zeos with sqlite print sql text
Post by: Zvoni on April 19, 2021, 10:57:50 am
Found something: https://www.sqlite.org/c3ref/expanded_sql.html
Quote
For example, if a prepared statement is created using the SQL text "SELECT $abc,:xyz" and if parameter $abc is bound to integer 2345 and parameter :xyz is unbound, then sqlite3_sql() will return the original string, "SELECT $abc,:xyz" but sqlite3_expanded_sql() will return "SELECT 2345,NULL".
No idea if it's implemented in Zeos (or FreePascal's own classes)

EDIT: I'd expect such a "feature" to be a property/function/whatever of the connection, not the query-object/class
Title: Re: Zeos with sqlite print sql text
Post by: superc on April 19, 2021, 11:16:08 am
alternatively, a monitor for sqlite would also be fine to view the queries launched by the program, but I don't know any
Title: Re: Zeos with sqlite print sql text
Post by: dseligo on April 19, 2021, 11:25:45 am
alternatively, a monitor for sqlite would also be fine to view the queries launched by the program, but I don't know any
I think queries are sent to database as is and parameters separately, so database has to support this (see Zvoni's answer).
Title: Re: Zeos with sqlite print sql text
Post by: dseligo on April 19, 2021, 11:28:58 am
You could parse SQL.Text and replace parameters (:x, :y, ...) with values. You should take type of parameter in account, i.e. you should enclose strings in quotes, format date and so on.
Title: Re: Zeos with sqlite print sql text
Post by: Zvoni on April 19, 2021, 11:51:29 am
Just looked into the source-files:
sqlite3.inc imports the function "sqlite3_expanded_sql" from the lib (a.k.a. retrieving the ProcedureAddress), but i couldn't find anywhere an implementation in the sqlite3connection-class

Anyone wants to write a class-helper? :-)
TinyPortal © 2005-2018