views:

129

answers:

2

I have a query with parameters inside a Devart TMSQuery.SQL. Something like

select * from customers
where customer = :CustomerID

in code I do

// 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 have the :CustomerID, while I would like to have this:

select * from customers
where customer = 4

is there a quick way to have this without reading all the parameters and reconstructing manyally the query?

Of course I have a very big query with more than 100 parameters, this is why I need this

+4  A: 

The actual SQL statement of a parameterized query which is sent to the server never contains textual representation of the parameter values. Instead, it uses placeholder characters, e.g. question marks. In your example, this would be select * from customers where customer = ? This is prepared on the server and parameter values are then sent by the client in a separate call.

TOndrej
The server will prepare the query (allocate resources, build a query plan) only once when it receives the SQL with the placeholders, and reuse the query with the actual parameters. This can dramatically improve performance for repeated executions of the query.
mjustin
The server will prepare and reuse the same prepared query if you instruct the engine to do so, tipically with a call to .Prepare method or setting to true the Prepared property. It will dramatically improve the efficiency for example in loops.On the other hand, using parameters your applications are safe from SQL Injection attacks.Finally, in some databases (oracle, for example) the engine reuse other's session's plan if the exact same textual query comes from different clients, thus, using parametrized queries is a way to reduce execution times and server load.
jachguate
+2  A: 

If you're using Devart components then they have a TMSSQLMonitor component which may help. If you're connecting via odbc you can monitor the sql by turning on tracing on the odbc tab.

If you're using some other combination please describe.

daven11
Hello, I tried TMSSQLMonitor, but in the log I just have chinese characters... I have Delphi 2009, may be this has some problems with unicode?, this is for example what I get in the log window: 14 14.21.25 兓⁌硅捥瑵㩥猠汥捥⁴‪牦浯丠呇䅟偐䥌䅃䥚乏൉圊䕈䕒㨠䑉䅟偐䥌䅃䥚乏⁅‾〱甀