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:
TDBGrid -> TDataSource -> TClientDataSet -> TDataSetProvider -> TFDQuery -> TDFConnection
) (different testapp) works fine[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);
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;