delphidelphi-10-seattletclientdataset

TClientDataset.ApplyUpdates fails with 'SQL not supported' when using SQLDirect components


After updating from DelphiXE2 to Delphi Seattle 10 Update 1 we have issues executing TClientDataSet ApplyUpdates calls when using the SQLDirect components version 6.4.5

I made a small test app.
Components: TDBGrid -> TDataSource -> TClientDataSet -> TDataSetProvider -> TSDQuery -> TSDDatabase
Query is select * from tt_plan_task, Provider has UpdateMode=upWhereAll, ClientDataset has IndexFieldName=tt_plan_task_id.
We modify one value for field tt_plan_task.tt_prj.

When executing ApplyUpdates(0), the code traces into TSQLResolver.InternalDoUpdate in DataSnap.Provider (for UpdateKind=ukModify).
There, the generated SQL and parameters look as expected.
The code then jumps to

procedure TSQLResolver.DoExecSQL(SQL: TStringList; Params: TParams);
var
  RowsAffected: Integer;
begin
  RowsAffected := (Provider.DataSet as IProviderSupportNG).PSExecuteStatement(SQL.Text, Params);

This statement crashes with error SQL not supported (SProviderSQLNotSupported in Data.DBConsts)

But since this an interface, I cannot trace further.
I don't know how to proceed from here in resolving the issue. Any suggestions how to, or what could be going on?

Additional info:

[Note: self-answering this question because it took me quite some time to figure this out. It may help others.]


Solution

  • When Googling the error message I stumbled upon this post SProviderSQLNotSupported on DOA with Delphi XE3 where a user had similar issues with Oracle Direct Access.
    It suggests either:

    1) Set TDataSetProvider.ResolveToDataSet=true at the cost of performance. This works in the test app.

    2) The PSExecuteStatement may not be implemented (overridden) in the 3rd party software, and its base procedure from data.db kicks in:

    function TDataSet.PSExecuteStatement(const ASQL: string; AParams: TParams): Integer;
    begin
      Result := 0;
      DatabaseError(SProviderSQLNotSupported, Self);
    end;
    

    That second situation is what happens. The SQLDirect code does have an override for the TSDDataSet method

    function PSExecuteStatement(const ASQL: string; AParams: TParams; {$IFDEF SD_CLR} var ResultSet: TObject {$ELSE} {$IFDEF SD_VCL17} var ResultSet: TDataSet {$ELSE} ResultSet: TSDPtr = nil {$ENDIF} {$ENDIF}): Integer; overload; override;
    

    which under Delphi Seattle resolves/compiles to override:

    function PSExecuteStatement(const ASQL: string; AParams: TParams; var ResultSet: TDataSet): Integer; overload; override;
    

    but there is none for

    function TDataSet.PSExecuteStatement(const ASQL: string; AParams: TParams): Integer;
    

    The solution is to add one:

    In the protected methods for TSDDateSet in SDEngine.pas, update as follows:

    function PSExecuteStatement(const ASQL: string; AParams: TParams): Integer; overload; override; // New override
    function PSExecuteStatement(const ASQL: string; AParams: TParams; {$IFDEF SD_CLR} var ResultSet: TObject {$ELSE} {$IFDEF SD_VCL17} var ResultSet: TDataSet {$ELSE} ResultSet: TSDPtr = nil {$ENDIF} {$ENDIF}): Integer; overload; override;
    

    with implementation:

    function TSDDataSet.PSExecuteStatement(const ASQL: string; AParams: TParams): Integer;  // JD 20-4-2016
    var
      ds: TDataSet;
    begin
      ds := nil;
      Result := InternalPSExecuteStatement( ASQL, AParams, false, ds );
    end;