Recent

Author Topic: Zeos with sqlite print sql text  (Read 1235 times)

superc

  • Full Member
  • ***
  • Posts: 241
Zeos with sqlite print sql text
« 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.

« Last Edit: April 19, 2021, 10:27:04 am by superc »

WooBean

  • Full Member
  • ***
  • Posts: 229
Re: Zeos with sqlite print sql text
« Reply #1 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?
Platforms: Win7/64, Linux Mint Ulyssa/64

dseligo

  • Hero Member
  • *****
  • Posts: 1194
Re: Zeos with sqlite print sql text
« Reply #2 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.  

Zvoni

  • Hero Member
  • *****
  • Posts: 2315
Re: Zeos with sqlite print sql text
« Reply #3 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
« Last Edit: April 19, 2021, 11:19:48 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

superc

  • Full Member
  • ***
  • Posts: 241
Re: Zeos with sqlite print sql text
« Reply #4 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

dseligo

  • Hero Member
  • *****
  • Posts: 1194
Re: Zeos with sqlite print sql text
« Reply #5 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).

dseligo

  • Hero Member
  • *****
  • Posts: 1194
Re: Zeos with sqlite print sql text
« Reply #6 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.

Zvoni

  • Hero Member
  • *****
  • Posts: 2315
Re: Zeos with sqlite print sql text
« Reply #7 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? :-)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018