question on how to retrieve the text of a SQL query with params in readable way

[pjtuloup]

Hi All

I now do my SQL queries with ParamByName, which gives for example

DataModule1.SQLQuery1.SQL.Add('INSERT INTO <table> (Field1...) VALUES (:Value1, ...)
DataModule1.SQLQuery1.ParamByName('Value1').AsInteger := Numvalue;

I would like to control the generated query and therefore display it. But if I try to display DataModule1.SQLQuery1.SQL.Text, I get 'INSERT INTO <table> (Field1...) VALUES (:Value1, ...)

How can I get the real query with the real values ​​to insert?

Thanks in advance !

[Leledumbo]

How can I get the real query with the real values ​​to insert?

Typically: you cannot. AFAIK so far no client libraries for any DBMS provide such a functionality, presumably for security purpose. Your best bet is to enable server side logging and see the incoming query from there.

[MarkMLl]

Typically: you cannot. AFAIK so far no client libraries for any DBMS provide such a functionality, presumably for security purpose. Your best bet is to enable server side logging and see the incoming query from there.

Ideally you need both, because you can never be sure whether e.g. some intermediate layer has messed up the character set.

I know that a number of people have looked at exposing local expansion, most recently Zvoni. In my case when I last looked at it (which wasn't recent) I did all the substitutions myself so I could see what was going on.

MarkMLl

[Leledumbo]

In my case when I last looked at it (which wasn't recent) I did all the substitutions myself so I could see what was going on.

This is a better (not best) bet, because despite your expansion might be correct, it might not be the one the client library is sending to the server due to escaping and such.

[pjtuloup]

Thank you gentlemen, it is terribly unfortunate, because I am in a case where I suspect the constitution of the query is not done correctly (it is refused by MYSQL as having a syntax error while the parameters entered in SQL.Text are correct)

[MarkMLl]

Thank you gentlemen, it is terribly unfortunate, because I am in a case where I suspect the constitution of the query is not done correctly (it is refused by MYSQL as having a syntax error while the parameters entered in SQL.Text are correct)

If substitution of a parameter has caused a syntax error, it suggests a quoting problem.

MarkMLl

[rvk]

Otherwise show the SQL, the table definition and the relevant code filling the parameters.
I'm sure someone here can spot the problems.

[pjtuloup]

I'm not in my office right now and I don't have the code on hand; but I'll post the elements in about ten hours... and I think it's going to be a real puzzle because of course I've already checked quite a bit and found nothing abnormal.

[rvk]

Thank you gentlemen, it is terribly unfortunate, because I am in a case where I suspect the constitution of the query is not done correctly (it is refused by MYSQL as having a syntax error while the parameters entered in SQL.Text are correct)

If substitution of a parameter has caused a syntax error, it suggests a quoting problem.

Does the TMySQLConnection do substitution locally for MySQL (while MySQL itself supports parameters at server side)?

If there is no substitution done and the parameter is sent to the server, then no quoting is required (because the server will know what type is used).

@pjtuloup When posting the SQL, also mention exactly what server and components you are using.

[Sieben]

TSQLConnection provides means to log the SQL it is sending to the server, have a look at property LogEvents and event OnLog. If you activate detParamValue this will include the parameter values, you might also want to try detActualSQL. In event OnLog you can for example add the Msg parameter to a TStringList and save it before closing your app. Or you can append them to a TMemo for instant debugging.

[pjtuloup]

TSQLConnection provides means to log the SQL it is sending to the server, have a look at property LogEvents and event OnLog. If you activate detParamValue this will include the parameter values, you might also want to try detActualSQL. In event OnLog you can for example add the Msg parameter to a TStringFile and save it before closing your app. Or you can append them to a TMemo for instant debugging.

Very interesting! Thank you!

[MarkMLl]

TSQLConnection provides means to log the SQL it is sending to the server, have a look at property LogEvents and event OnLog. If you activate detParamValue this will include the parameter values, you might also want to try detActualSQL. In event OnLog you can for example add the Msg parameter to a TStringFile and save it before closing your app. Or you can append them to a TMemo for instant debugging.

I've just checked that with a test project of mine which talks to a PostgreSQL server, and while it certainly does log I think the FCL might be inserting the wrong placeholder (i.e. $1 etc. rather than %s as expected by that dialect of SQL). I'll try to come back to that one...

Which in any event illustrates the importance of having more than one arrow in ones quiver.

As of FPC 3.2.2 (circa 2024) there's a commented-out call to PQprepare() because parameters are unsupported before PostgreSQL v8 (circa 2005).

Because FPC/Lazarus makes it so easy to move from one database backend to another, it's probably worth coding ones own parameter substitution in all cases: otherwise what's expected to be a couple of hours work could easily turn into a day or so.

MarkMLl

[pjtuloup]

Hi

Here are somme elements

Yes, I use SQL_db components
Lazarus  3.4
FPC 3.2.2
Os Client Windows 11 10.0.22631
Os Server Linux Debian 12.4
Mysql 8.0.36

and now the code which causes a "MySQL syntax error" error message:

Code: Pascal  [Select][+][-]

1. DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt,IdItem,Date, Heure, Domaine, User, Profil, Table) VALUES (:Chp1i, :Chp2i, :Chp3d, :Chp4t, :Chp5s, :Chp6s, :Chp7s, :Chp8s); ');
2.  
3.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
4.    DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := NoNewOp;
5.    DataModule1.SQLQuery1.ParamByName('Chp3d').AsDate := Date;
6.    DataModule1.SQLQuery1.ParamByName('Chp4t').AsString := FormatDateTime('hh:nn:ss', Time);
7.    DataModule1.SQLQuery1.ParamByName('Chp5s').AsString := 'DGFIP';
8.    DataModule1.SQLQuery1.ParamByName('Chp6s').AsString := UserEnCours;
9.    DataModule1.SQLQuery1.ParamByName('Chp7s').AsString := '0';
10.    DataModule1.SQLQuery1.ParamByName('Chp8s').AsString := 'operations';
11.  

I only show this part of the code because this is where the error occurs, on the ParamByName('Chp8s').AsString
The proof is that the following code works without error:

Code: Pascal  [Select][+][-]

1. DataModule1.SQLQuery1.SQL.Add('INSERT INTO ehistocreaop (IdEvt,IdItem,Date, Heure, Domaine, User, Profil) VALUES (:Chp1i, :Chp2i, :Chp3d, :Chp4t, :Chp5s, :Chp6s, :Chp7s); ');
2.  
3.    DataModule1.SQLQuery1.ParamByName('Chp1i').AsInteger := NoNewHistOp;
4.    DataModule1.SQLQuery1.ParamByName('Chp2i').AsInteger := NoNewOp;
5.    DataModule1.SQLQuery1.ParamByName('Chp3d').AsDate := Date;
6.    DataModule1.SQLQuery1.ParamByName('Chp4t').AsString := FormatDateTime('hh:nn:ss', Time);
7.    DataModule1.SQLQuery1.ParamByName('Chp5s').AsString := 'DGFIP';
8.    DataModule1.SQLQuery1.ParamByName('Chp6s').AsString := UserEnCours;
9.    DataModule1.SQLQuery1.ParamByName('Chp7s').AsString := '0';
10.    //DataModule1.SQLQuery1.ParamByName('Chp8s').AsString := 'operations';
11.  

You see ? The others ParamByName('Chpxx').AsString pass without problem while the syntax used is the same for ParamByName('Chp8s').AsString.

Of course I checked the table: Fields Profile and Table are both Varchar(15); other parameters (Null authorized, etc...) are the same for both, too.

It's 20h30 in France, I don't have time to try the suggested solutions and won't be able to do so until Wednesday. I'll keep you up-to-date !

[rvk]

Yikes.
INSERT INTO ehistocreaop (IdEvt,IdItem,Date, Heure, Domaine, User, Profil, Table)

Do you have "Table" as a fieldname.
Isn't Table a reserved word in MySQL ???
(I'm almost sure it is !)

You might want to rename that field or use quotes around "Table" in the field definitions.
But I would definitely avoid reserved words in this case !!

Edit: Yep. TABLE is a reserved word ! (See the R after it in this list)
https://dev.mysql.com/doc/refman/8.4/en/keywords.html

[MarkMLl]

Do you have "Table" as a fieldname.

...and it's the 8th parameter :-)

MarkMLl