Page 1 of 1
How to show SQL that uses parameters?
Posted: Thu 11 Dec 2014 04:18
by 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?
Re: How to show SQL that uses parameters?
Posted: Thu 11 Dec 2014 05:52
by 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;
Re: How to show SQL that uses parameters?
Posted: Thu 11 Dec 2014 21:31
by kneighbour
what a good idea! Never even knew that component existed.
Re: How to show SQL that uses parameters?
Posted: Fri 12 Dec 2014 05:48
by AlexP
If you have any further questions, feel free to contact us.
Re: How to show SQL that uses parameters?
Posted: Thu 18 Dec 2014 00:00
by 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.
Re: How to show SQL that uses parameters?
Posted: Thu 18 Dec 2014 01:25
by FredS
kneighbour wrote: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..
Re: How to show SQL that uses parameters?
Posted: Thu 18 Dec 2014 08:24
by 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;