4

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

UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
  • two questions 1) wich database are you using? 2) are you using BDE? – RRUZ Sep 02 '10 at 09:01
  • No I am using SQL SERVER with DevArt components, I asked for TQUery because it is more globally understood – UnDiUdin Sep 02 '10 at 09:18
  • You must provide such information in your question, is very important. I edited your tags. – RRUZ Sep 02 '10 at 09:22
  • Yes you are right, I noticed that DevArt components are derived directly from TObject, so they have a different implementation than BDE even for most common properties. – UnDiUdin Sep 02 '10 at 12:25

2 Answers2

4

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.

Ondrej Kelle
  • 36,941
  • 2
  • 65
  • 128
  • I didn't know that, anyway I started suspecting this while looking at sql server profiler. I thought that the parameters were just a way of doing text search & replace, while according to what you say it means there is something deeper... Which is the advantage of passing parameters in a spearate call? which is the difference if I just search and repalce text before executing the query? – UnDiUdin Sep 02 '10 at 12:24
  • 1
    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. – mjn Sep 02 '10 at 12:51
  • 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 Sep 03 '10 at 06:30
3

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
  • 2,905
  • 3
  • 26
  • 43
  • 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 兓⁌硅捥瑵㩥猠汥捥⁴‪牦浯丠呇䅟偐䥌䅃䥚乏൉圊䕈䕒㨠䑉䅟偐䥌䅃䥚乏⁅‾〱甀 – UnDiUdin Sep 02 '10 at 12:22