How to catch SQL generated by Delphi TDataSetProvider?

Question

I have TClientDataSet, TDataSetProvider and TIBQuery chain and there is 'unprepared statement' error while executing CDS.Post, apparently, the update satement generated by TDataSetProvider is wrong. How to catch this update SQL? SQL Montioring is not working for my IBX components. I have tried to use code (inspired by TClientDataset.ApplyUpdates fails with 'SQL not supported' when using SQLDirect components):

procedure TSQLResolver.DoExecSQL(SQL: TWideStringList; Params: TParams);
var
  RowsAffected: Integer;
  TmpList: TStringList;
begin
  TmpList:=TStringList.Create;
  TmpList.LoadFromFile('C:\Test\test.txt');
  RowsAffected := (Provider.DataSet as IProviderSupport).PSExecuteStatement(SQL.Text, Params);
  if not (poAllowMultiRecordUpdates in Provider.Options) and (RowsAffected > 1) then
  begin
    TmpList.Add(SQL.Text);
    TmpList.SaveToFile('C:\Test\test.txt');
    TmpList.Free;
    (Provider.DataSet as IProviderSupport).PSEndTransaction(False);
    Provider.TransactionStarted := False;
    DatabaseError(STooManyRecordsModified);
  end;
  if RowsAffected < 1 then
    DatabaseError(SRecordChanged);
end;

But nothing is written in the Test/test.txt file. Where can I put such logging code?

Delphi 2010 but I guess that this applies to any version of Delphi.

Answer 1

The reason nothing gets written to your log file is that the way you've added your logging, it would only execute if the update affects more than one row. Imo, your code should be

procedure TSQLResolver.DoExecSQL(SQL: TStringList; Params: TParams);
var
  RowsAffected: Integer;
  TmpList: TStringList;
begin
  TmpList:=TStringList.Create;
  try
    TmpList.LoadFromFile('C:\Test\test.txt');
    RowsAffected := (Provider.DataSet as IProviderSupport).PSExecuteStatement(SQL.Text, Params);
    TmpList.Add(SQL.Text);
    TmpList.SaveToFile('C:\Test\test.txt');
    if not (poAllowMultiRecordUpdates in Provider.Options) and (RowsAffected > 1) then
    begin
      (Provider.DataSet as IProviderSupport).PSEndTransaction(False);
      Provider.TransactionStarted := False;
      DatabaseError(STooManyRecordsModified);
    end;
    if RowsAffected < 1 then
      DatabaseError(SRecordChanged);
  finally
    TmpList.Free;
  end;
end;

which should work regardless of the number of rows updated. It also fixes the TmpList leak that you've set up by freeing TmpList in code that only conditionally executes. Btw, as written, you'll find that this code does not log the values fed in by the values in Params, but it's easy enough to add these to the log or even merge them into the logged SQL.Text.

Hopefully, that should fix your immediate problem, but frankly your time would be better spent boiling your code down to an mre. I say that for several reasons, firstly that I've never had the 'unprepared statement' error using IBX + DSP/CDS, secondly logging using the IBX components usually works fine and thirdly that preparing an mre often reveals the cause of the problem. So I suspect there is an error in code you've not shown us.

If you set up the mre to work with one of the sample IB databases and post it here as a new q (not as an edit to this one, please), you're far more likely to find readers prepared to try it themselves.