Exception caused by TDataSetProvider poUseQuoteChar and lowercase table names does not surface

Question

The default TDataSetProvider.Options.poUseQuoteChar is true.

I was (again) bitten by this when my SQL statement used a lower case table name and my TClientDataSet.ApplyUpdates(0) did not do any updates without raising an exception.

In DataSnap.Provider the code in function TCustomResolver.InternalUpdateRecord(Tree: TUpdateTree): Boolean; traps the exception:

except
  if ExceptObject is Exception then
  begin
    E := Exception(AcquireExceptionObject);
    PrevErr.Free;
    PrevErr := Err;
    Err := (Tree.Source as IProviderSupportNG).PSGetUpdateException(E, PrevErr);
    if HandleUpdateError(Tree, Err, FMaxErrors, FErrorCount) then
    begin
      Tree.Delta.UseCurValues := True;
      Continue;
    end else
      break;
  end else
    raise;
end;

and I see that E.Message is

[FireDAC][Phys][FB]Dynamic SQL Error'#$D#$A'SQL error code = -204'#$D#$A'Table unknown'#$D#$A'tt_calendar'#$D#$A'At line 1, column 8

I have no ReconcileErrorHandler, and in the above code HandleUpdateError returns false, but for some reason the exception does not surface.

My setup is:

New events created in DevExpress TcxSchedulerStorage, connected to TDataSource -> TClientDataSet -> TDataSetProvider -> TFDQuery -> TFDConnection, in this case to a Firebird database. Everything default settings, simple select * from tablename in TFDQuery.SQL.Text,
using Delphi Tokyo 10.2.3.

Is there a single setting that I can change that would force the exception visible and that solves this once and for all (for any database type)?
I'm even willing to patch a Delphi file.

I have now 'solved' this with runtime code:

procedure TDMTT.DataModuleCreate(Sender: TObject);
var i: integer;
begin
  for i := 0 to ComponentCount-1 do
    if Components[i] is TDataSetProvider then
       (Components[i] as TDataSetProvider).Options := (Components[i] as TDataSetProvider).Options - [poUseQuoteChar];
end;

but would prefer not having to think of this every time.

Well, you can modify TDataSetProvider constructor to exclude the option. — Sertac Akyuz, Apr 06 '18 at 16:39
Code a TDatasetprovider.BeforeUpdateRecord event handler that handles all your update requirements, so that the Datasetprovider never codes or executes any SQL for you. — Freddie Bell, Apr 07 '18 at 14:01
@SertacAkyuz Simple, I overlooked that. Care to make it an answer, so that it stays available for others? — Jan Doggen, Apr 09 '18 at 14:59
@Jan - You go ahead if you like please. I won't be very comfortable with that answer - I know nothing about the casing problem and I'm not sure if it's the best approach. — Sertac Akyuz, Apr 09 '18 at 15:05

score 1 · Answer 1 · answered Apr 16 '18 at 11:15

As Sertac Akyuz suggested, modifying the TDataSetProvider constructor is one option. The drawback is that you are patching Delphi code, but I'm fine with that.

In Datasnap.Provider, add the indicated line:

constructor TDataSetProvider.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FResolveToDataSet := False;
  UpdateMode := upWhereAll;
  FDSWriter := nil;
  FConstraints := True;
  FRecordsSent := 0;
  Options := Options - [poUseQuoteChar];  // Line added
end;

Exception caused by TDataSetProvider poUseQuoteChar and lowercase table names does not surface

1 Answers1