Zeos with sqlite print sql text
lazarus / 16-Databases

----------------------------------------------------------------------
superc · 2021-04-19 08:22

Hello,

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

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  qry :
TZQuery;begin  try    qry := TZQuery.Create(self);    qry.Connection
:= ZConnection1;     qry.SQL.Clear;    qry.SQL.Add('SELECT * FROM
x');    qry.SQL.Add('WHERE x = :x');    qry.SQL.Add('  AND y = :y'); 
 qry.ParamByName('x').AsString := 'blablabla';   
qry.ParamByName('y').AsString := '1';     qry.Open;  finally   
qry.Free;  end; end;   
If I try to print sql text with
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
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.

----------------------------------------------------------------------
WooBean · 2021-04-19 08:34

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?

----------------------------------------------------------------------
dseligo · 2021-04-19 08:54

Try something like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  qry :
TZQuery;  i: Integer;begin  try    qry := TZQuery.Create(self);   
qry.Connection := ZConnection1;     qry.SQL.Clear;   
qry.SQL.Add('SELECT * FROM x');    qry.SQL.Add('WHERE x = :x');   
qry.SQL.Add('  AND y = :y');    qry.ParamByName('x').AsString :=
'blablabla';    qry.ParamByName('y').AsString := '1';     For i:=0 to
qry.Params.Count-1 do      ShowMessage(qry.Params[i].DisplayName+':
'+qry.Params[i].AsString);     qry.Open;  finally    qry.Free; 
end; end;

----------------------------------------------------------------------
Zvoni · 2021-04-19 08:57

Found something: https://www.sqlite.org/c3ref/expanded_sql.html
QuoteFor 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

----------------------------------------------------------------------
superc · 2021-04-19 09:16

alternatively, a monitor for sqlite would also be fine to view the
queries launched by the program, but I don't know any

----------------------------------------------------------------------
dseligo · 2021-04-19 09:25

Quote from: superc on April 19, 2021, 11:16:08 amalternatively, 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 · 2021-04-19 09:28

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 · 2021-04-19 09:51

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? :-)