How to know the EXACT SQL (with substituted parameters)?

[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?

[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.

[brace]

ok, but why not adding a feature that does a simple text & replace just for debug reasons?

[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.

[brace]

Thanks (I'd suggest to add this as a new tab in the debug windows when DEBUG = True)

[wilton_rad]

in TMSQuery.SQL type text select * from customers


sqlcustomer.close;
sqlcustomer.fieltersql :='customer=4';
sqlcustomer.open;

[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.

[Ludek]

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.

[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?