使用SQLDirect组件时，TClientDataset.ApplyUpdates失败，并显示“不支持SQL" [英] TClientDataset.ApplyUpdates fails with 'SQL not supported' when using SQLDirect components

问题描述

从DelphiXE2更新到Delphi Seattle 10 Update 1后，当使用

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

我做了一个小型测试应用程序.
组件: TDBGrid->TDataSource->TClientDataSet->TDataSetProvider->TSDQuery->TSDDatabase
查询是 select * from tt_plan_task ，提供程序具有 UpdateMode = upWhereAll ，ClientDataset具有 IndexFieldName = tt_plan_task_id .
我们为 tt_plan_task.tt_prj 字段修改一个值.

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.

在执行 ApplyUpdates(0)时，该代码会跟踪到 DataSnap.Provider 中的 TSQLResolver.InternalDoUpdate (对于 UpdateKind = ukModify).
在那里，生成的SQL和参数看起来像预期的那样.
然后，代码跳至

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);

此语句崩溃，错误为不支持SQL (Data.DBConsts中为SProviderSQLNotSupported)

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?

其他信息:

• 通过TSDQuery组件执行SELECT和(参数化的)UPDATE查询工作正常.
• 使用具有所有FireDac组件( TDBGrid-> TDataSource-> TClientDataSet-> TDataSetProvider-> TFDQuery-> TDFConnection )的clientdataset可以正常工作
• 这是具有数据库方言3的FireBird数据库
• FireBird版本为2.5.3.26778
• Delphi 10安装时没有Interbase组件
• 所有这些都在Win7 VM中，在其中删除了Delphi XE2(清除了所有的"Borland/CodeGear/Embarcadero")，并安装了Seattle.我的同事在干净的Win10 VM中进行干净的Seattle安装时也遇到类似的问题.
• 应用程序和FireBird是Win32，操作系统是Win64

• 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

[注意:请自行回答此问题，因为我花了相当长的时间才能弄清楚这一点.可能会对别人有帮助.]

推荐答案

Delphi XE3在DOA上> SProviderSQLNotSupported ，其中用户在使用Oracle Direct Access时也遇到类似的问题.
建议:

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)以性能为代价设置 TDataSetProvider.ResolveToDataSet = true .在测试应用中有效.

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

2) PSExecuteStatement 可能未在第三方软件中实现(覆盖)，其基本过程来自 data.db :

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;

第二种情况是发生了什么.SQLDirect代码确实具有对TSDDataSet方法的覆盖

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;

在Delphi Seattle下，该文件解析/编译以覆盖:

which under Delphi Seattle resolves/compiles to override:

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

但没有

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

解决方案是添加一个:

在SDEngine.pas中TSDDateSet的受保护方法中，更新如下:

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;

具有实现:

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;

这篇关于使用SQLDirect组件时，TClientDataset.ApplyUpdates失败，并显示“不支持SQL"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！