CODEWITHSUNDEEP
sqldelphi
Freddie Bell
Freddie Bell

Reputation: 2287

How to Log SQL.text after parameters have been assigned, but before Open

I am attempting to document the SQL calls in a large project without making extensive changes to the code. To start this process, we decided to try to log the DBX queries that are executed.

So I have:

procedure TRService.GetContactInfo(sessionid: String; msrno: Integer);
var
  SQL: String;
  qryContact: TSQLQuery;
  username: String;
begin
  qryContact := TSQLQuery.Create(nil);
  qryContact.SQLConnection := TSQLConnection1;
  qryContact.SQL.Add('select name, address');
  qryContact.SQL.Add('from contact');
  qryContact.SQL.Add('where (msrno = :msrno)');
  qryContact.ParamByName('msrno').AsInteger := msrno;
  {$ifdef LOGSQL}
  SQL := qryContact.???
  LogSQL(SQL);
  // CallNewServer(SQL);
  {$else}
  qryContact.Open;
  {$endif}
end;

end;

When the LogSQL function is called, I want it to log

'Select * from contact where (msrno = 12345)'

Which property '.???' of the TSQLQuery can I use to give me the SQL string with the parameters already replaced with values? Ideally I want to do this before the qryContact.open, because in a future version, the qryContact.open will be replaced by a call to a different server.

Upvotes: 4

Views: 1569

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

It's not that simple. The parameters are not simply substituted in the SQL. The SQL is sent to the database separately, along with a list of parameter values. The database will do the substitution one way or another.

Also, actually substituting the value isn't even true to what's happening in most cases. Most databases will be way smarter in preparing a query with parameters than with a constant value in the query. If you run the same query twice but with different parameter values, it will recognize that it's the same query. The database can then decide to skip all kinds of preparations (like parsing the SQL and finding an execution plan) and reuse the cached work it did for the previous execution.

Anyway, long story short: If you want to log it like that, you'll either have to substitute the values yourself (hard to do right), or just log the parameterized query and log the list of parameters separately.

I would probably choose the latter, not just because it's easier, but also because the log will show more clearly which values are constant and which values are parameters, so it's more true to what actually happened.

Upvotes: 6

Related Questions