How do I output the value of a parameter in SQL after it has been inserted?

Question

Is it possible to retrieve the SQL statement with the values of parameters after it has been set and inserted into the SQL component?

SQL Statement:

SELECT * FROM SomeTable
WHERE SomeColumn = :pSomeValue;

Code:

procedure LoadParams(aValue: string);
begin
  Qry.Close;
  Qry.ParamByName('pSomeValue').AsString := aValue;
  MessageDlg(Qry.SQL.Text, mtInformation, [mbOK], 0); // this does not show the value but the parameter name.     
  Qry.Open;
end;

I want to be able to see the statement before it is opened but when I try this now I get the param name instead of the value.

Which database engine and delphi connection library do u use ? http://en.wikipedia.org/wiki/Insert_sql#Retrieving_the_key — Arioch 'The, Apr 17 '13 at 09:20
you may choose to use AutoInc field: http://www.nexusdb.com/support/index.php?q=howautoincworks2.htm mmm you told you use [tag:nexusdb] as well, don't you? — Arioch 'The, Apr 17 '13 at 09:44

score 4 · Accepted Answer · answered Apr 17 '13 at 08:54

4

Query parameters are normally substituted in the DBMS, i.e. the values are sent over the connection separately, and not as part of the SQL statement. If you wish to see the SQL and the values together, the DB's logs might help you with that.

answered Apr 17 '13 at 08:54

Levente Pánczél

1,895
2
14
16

3

+1 as an example for `select * from Invoice where CustomerID=:ID` this can be seen in SQL-Profile `exec sp_executesql N'select * from Invoice where CustomerID=@P1',N'@P1 int',12` – bummi Apr 17 '13 at 08:59

Pieter B · Answer 2 · 2013-04-17T13:03:39.810

One thing you can do is duplicate the parameter and put it in the SELECT:

SELECT :pSomeValueDuplicate AS paraminput, * FROM SomeTable
WHERE SomeColumn = :pSomeValue;

Qry.ParamByName('pSomeValue').AsString := aValue;
Qry.ParamByName('pSomeValueDuplicate').Value := Qry.ParamByName('pSomeValue').Value;

Then you can check the field: paraminput for what you used as input. Somehow I can't get Delphi to use the same parameter twice in 1 query.

SELECT 'SELECT * FROM SomeTable WHERE SomeColumn ='+ :pSomeValueduplicate AS thesqlinput,* FROM SomeTable WHERE SomeColumn = :pSomeValue;

Actually you can do it with just the one parameter:

SELECT 'SELECT * FROM SomeTable WHERE SomeColumn ='+ SomeColumn AS thesqlinput,* FROM SomeTable WHERE SomeColumn = :pSomeValue;

How do I output the value of a parameter in SQL after it has been inserted?

2 Answers2