Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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);
  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:
Executing SELECT and (parameterized) UPDATE queries through a TSDQuery component work fine.
Using a clientdataset with all FireDac components (TDBGrid -> TDataSource -> TClientDataSet -> TDataSetProvider -> TFDQuery -> TDFConnection) (different testapp) works fine
This is a FireBird database with database dialect 3
FireBird version is 2.5.3.26778
Delphi 10 was installed without the Interbase components
This is all in a Win7 VM where Delphi XE2 was removed from (everything 'Borland/CodeGear/Embarcadero' cleaned out), and Seattle installed. My colleague has similar issues with a clean Seattle installation in a clean Win10 VM.
Apps and FireBird are Win32, OSes are Win64
[Note: self-answering this question because it took me quite some time to figure this out. It may help others.]
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);
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
  ds: TDataSet;
begin
  ds := nil;
  Result := InternalPSExecuteStatement( ASQL, AParams, false, ds );
I had a similar problem migrading from Delphi 2009  to  Seattle 10, on TAsaDataSet + TDataSetProvider + TClientDataSet 
 we solved the problem  changing a few properties:
AsaDataSet.readyOnly := true;
DataSetProvider.ResolveToDataSet := True;
then at some point of our code we made sure the TAsaDataSet its able to its table.
TAsaDataSet .Close;
TAsaDataSet .Session  := DmConection.AsaConnection;
TAsaDataSet .SQL.Text := 'SELECT * FROM dba.table_name';
TAsaDataSet .Open;
We also notice from 2009 to Seatle the method signature OnGetTableName from:
(Sender: TObject; DataSet: TDataSet; var TableName: WideString); to:
(Sender: TObject; DataSet: TDataSet; var TableName: string);
Hope somehow it helps you.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.