de-parameterize query1.sql.text
lazarus / 16-Databases

----------------------------------------------------------------------
mirce.vladimirov · 2014-06-30 16:07

I've got this code :
Code: [Select]  query1.close;
  query1.sql.Clear;
  query1.sql.Add('select * from dokuments where field1=:p1 and
field2=:p2 and datum<=:dodatum');
  query1.ParamByName('p1').Value:=1;
  query1.ParamByName('p2').Value:=2;
  query1.ParamByName('dodatum').AsDate:=strtodate('30.06.2014');
  query1.open;
Everithing is good so far. Now, I wold like to log the SQL text, so if
I do :
Code: [Select]  memo1.Text:=query1.SQL.text  then i get exactly the
same text from the "query1.sql.add" from few lines above and that's
not what I need.
What I need is the SQL sentence but with parameter names replaced with
values, I need the exact SQL command that is executed by the SQL data
server. I want to save that text like this :

Code: [Select]select * from dokuments where field1=1 and field2=2 and
datum<='2014-06.30'

----------------------------------------------------------------------
taazz · 2014-06-30 16:19

That is impossible to get from the client side. Even if you do attach
your self to the connection and read the sql command after it has been
prepared from the the query,  the values from the parameters are not
in there they will be send to the server on a secondary command after
the sql has been prepared on the server.

----------------------------------------------------------------------
mirce.vladimirov · 2014-06-30 19:01

I've managed to do atleast something :

Code: [Select]  memo1.Clear;
  memo1.Append(query1.sql.text);
  memo1.append(query1.Params[0].Name + '=' +
query1.Params[0].AsString);
  memo1.append(query1.Params[1].Name + '=' +
query1.Params[1].AsString);
  memo1.append(query1.Params[2].Name + '=' +
query1.Params[2].AsString);
which fills the TMemo with contetnts like this :
Code: [Select]select * from dokuments where field1=:p1 and field2=:p2
and datum<=:dodatum

p1=1
p2=2
dodatum=30.06.2014
I've got nothing better, so I'll use it. I hope someone else will find
it usefull too.

----------------------------------------------------------------------
kpeters58 · 2014-06-30 20:25

Why so complicated?

Do (contrived example):

Code: [Select]const
   SQL_EXPR = 'select * from customers where ID = %u and birthdate =
%s';
....
Code: [Select]query.SQL.Text := Format(SQL_EXPR, [32445,
'1958-02-14']);
and then simply log query.SQL.Text

----------------------------------------------------------------------
LacaK · 2014-07-01 04:55

Which TSQLConnection do you use ?
There is logging possibility. See: http://www.freepascal.org/docs-
html/fcl/sqldb/tsqlconnection.onlog.html

----------------------------------------------------------------------
mirce.vladimirov · 2014-07-01 20:17

Quote from: LacaK on July 01, 2014, 06:55:48 amWhich TSQLConnection do
you use ?
There is logging possibility. See: http://www.freepascal.org/docs-
html/fcl/sqldb/tsqlconnection.onlog.html

I use TMySQL50Connection, I develope under Windows 7 but my exe runs
on WinXP, Vista, Win7 and Win8. I use Lazarus 1.0.2.