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

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

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

Post by brace » Thu 02 Sep 2010 09:05

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 02 Sep 2010 12:47

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
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Thu 02 Sep 2010 13:19

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

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 02 Sep 2010 13:47

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
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Fri 03 Sep 2010 12:32

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

wilton_rad
Posts: 4
Joined: Fri 10 Sep 2010 14:37

solution

Post by wilton_rad » Fri 10 Sep 2010 18:13

in TMSQuery.SQL type text select * from customers


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

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 13 Sep 2010 07:22

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
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Mon 13 Sep 2010 11:14

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.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 13 Sep 2010 12:59

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?

Post Reply