Page 1 of 1
How to know the EXACT SQL (with substituted parameters)?
Posted: Thu 02 Sep 2010 09:05
by brace
I have a query with parameters inside a TMSQuery.SQL. Something like
Code: Select all
select * from customers
where customer = :CustomerID
in code I do
Code: Select all
// Delphi
sqlcustomer.ParamByName('CustomerID').asinteger := 4;
sqlcustomer.open;
I want to debug and see the exact sql command sent to the server, if I try to use TMSQuery.sql I just see
Code: Select all
select * from customers
where customer = :CustomerID
while I would like to have this:
Code: Select all
select * from customers
where customer = 4
is there a quick way to have this without reading all the parameters and reconstructing manyally the query?
If I use TMSQuery.Debug := True I still see the parameters instead of the EXACT QUERY PASSED TO THE SERVER.
May you help me?
Posted: Thu 02 Sep 2010 12:47
by Dimon
The TMSQuery.Debug property displays executing query and all its parameters' values. SDAC doesn't replace parameters values in the query, but passes them using OLEDB interface. Therefore we can't display query with replaced parameters.
Posted: Thu 02 Sep 2010 13:19
by brace
ok, but why not adding a feature that does a simple text & replace just for debug reasons?
Posted: Thu 02 Sep 2010 13:47
by Dimon
We will investigate the possibility of adding this functionality in the future. As soon as we solve this question we will let you know.
Posted: Fri 03 Sep 2010 12:32
by brace
Thanks (I'd suggest to add this as a new tab in the debug windows when DEBUG = True)
solution
Posted: Fri 10 Sep 2010 18:13
by wilton_rad
in TMSQuery.SQL type text select * from customers
sqlcustomer.close;
sqlcustomer.fieltersql :='customer=4';
sqlcustomer.open;
Posted: Mon 13 Sep 2010 07:22
by brace
Thanks for the reply, but my goal is to have a quick (and dirty) way of debugging complex queries.
I have many queries (I didn't write!) with something like 200 parameters in total.
To try to profile them or to refactor I need to make some tests and now I am forced to write a small app just to change the parameters, it would be easier to have the query text with substituted parameters "in clipboard", paste it in amangement studio/ db monitor / db optimizer...
Of course this tool becomes usefule when the number of parameters is high.
Posted: Mon 13 Sep 2010 11:14
by Ludek
brace wrote:To try to profile them or to refactor I need to make some tests and now I am forced to write a small app just to change the parameters
I'm using sql server profiler application (from ms sql server installation), there you can see something like
exec sp_executesql N'.....', N' ', ....
it works perfectly in sql management studio, showing real plans and anything you want.
Posted: Mon 13 Sep 2010 12:59
by brace
it works perfectly in sql management studio, showing real plans and anything you want.
Are you suggesting me to use the queries from profiler (so they are already with substituted parameters)?
This is an idea, anyway having a Delphi side debug tool would speed up things anyway. Do you agree?