4

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.

Freddie Bell
  • 2,186
  • 24
  • 43
  • 1
    Not sure this is possible, see also [this](https://stackoverflow.com/questions/3625536/delphi-how-to-get-the-query-that-is-passed-to-the-server-when-parameters-are-us) – GuidoG Dec 03 '18 at 09:42
  • For `inserts/updates/deletes` I suggest using triggers on your database. – GuidoG Dec 03 '18 at 09:44
  • Some of the third party data access components provide SQL monitors that would accomplish what you want - they show components, SQL executed with parameter values and show fetch amounts (ex get 100 records). One such is Devarts's various Delphi Access Components (https://www.devart.com/dac.html) with the dbMonitor utility they provide . – Brian Dec 03 '18 at 12:41
  • Link to the dbMonitor information (it only works with Devart's data access components): https://www.devart.com/dbmonitor/ – Brian Dec 03 '18 at 13:22

1 Answers1

6

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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210