Recent

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

superc

  • New Member
  • *
  • Posts: 46
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: 114
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?
Win7/64, Lazarus 2.0.8 win64-win64, FPC 3.0.4

dseligo

  • Sr. Member
  • ****
  • Posts: 276
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: 596
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

superc

  • New Member
  • *
  • Posts: 46
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

  • Sr. Member
  • ****
  • Posts: 276
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

  • Sr. Member
  • ****
  • Posts: 276
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: 596
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

 

TinyPortal © 2005-2018