0

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:

  • 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.]

Jan Doggen
  • 8,799
  • 13
  • 70
  • 144

2 Answers2

1

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;
Jan Doggen
  • 8,799
  • 13
  • 70
  • 144
0

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.

Marisco
  • 125
  • 1
  • 3
  • 16