How to show SQL that uses parameters?

[kneighbour]

With all of my apps, I have the option to log certain key functions to a text log file. This helps to debug problems.

Usually in the area of UniQuery I have a line someting like

log(qryDoSomething.sql.text);

This works just fine unless the query uses parameters - all you see if the SQL with the parameter name in it. ie 'SELECT * FROM TABLE WHERE FIELD = :somthing'

What I would like to do is show the SQL with all the values of the parameters included.

Is there some way to do this?

[AlexP]

Hello,

For logging, you can use TUniSQLMonitor, and in the onSQL event save the Text parameter of the method, in which SQL and its parameters are.

Code: Select all

procedure TForm1.UniSQLMonitor1SQL(Sender: TObject; Text: string;
  Flag: TDATraceFlag);
begin
 log(Text);
end;

[kneighbour]

what a good idea! Never even knew that component existed.

[AlexP]

If you have any further questions, feel free to contact us.

[kneighbour]

Ok - the SQL monitor option is ok - the problem with using that is two fold.

1. it does not generate a complete SQL statement. One reason I log SQL statements is that I can then try and optimize/fix the SQL. So I usually just cut and paste the SQL into my Firebird frontend.

2. It records every SQL transaction. Not just the one SQL query I am looking at - but EVERYTHING. This is definitely not what I want. I try to put SQLMonitor.active before and after the call I want to watch, but that does not cut out all the other calls.

It would be nice if the UniQuery component actually presented the final SQL as a property.

[FredS]

Ok -
It would be nice if the UniQuery component actually presented the final SQL as a property.

I saw this in the docs: http://www.devart.com/unidac/docs/index ... nalsql.htm

Not in a position to test right now..

[AlexP]

If you want to retrieve the query text with parameter values being already pasted, you need to parse the query text in the OnSQL property and paste parameter values instead of their names, that can be retrieved with a loop through all parameters

Code: Select all

  for i := 0 to TCustomDADataSet(Sender).Params.Count - 1 do begin
    TCustomDADataSet(Sender).FindParam('').AsString
  end;