I need to avoid attempting to update non-physical fields in a Delphi TClientDataset connected to a TSQLQuery

Question

Precis: My code is attempting to update non-physical fields in a Delphi XE TClientDataset, (connected to a TSQLQuery with its SQL property set) that were created as result of a runtime Open command.

I have a TClientDataset connected to a TDatasetProvider connected to a TSQLQuery connected to a TSQLConnection. The first 3 of these objects are encapsulated within a couple of classes in a library that I use in many places on several projects. These classes create these 3 objects at runtime and eliminate a significant amount of repetitious code, necessary as I have many, many of these triplets.

Quite typically I will load the TClientDataset from a database by specifying some SQL in the SQL property of the TSQLQuery and calling Open on the TClientDataSet. The Fields in the TClientDataset are created via this call to Open ie. they don't exist prior to Open.

I have run into a problem in a situation where three of the fields generated into the TClientDataset are non-physical; that is, the SQL does calculations to generate them. Unfortunately, in the TClientDataset, these 3 fields do not get created any differently to the physical fields; their FieldKind is fkData (ideally it would be fkInternalCalc), Calculated property is False (ideally it would be True) and their ProviderFlags include pfInUpdate (which ideally it should not). Not surprisingly, when it comes time to do an ApplyUpdates on the TClientDataset an exception is thrown...

Project XXX.exe raised exception class TDBXError with message
SQL State: 42S22, SQL Error Code: 207 Invalid column name 'Received'.
SQL State: 42S22, SQL Error Code: 207 Invalid column name 'Issued'.
SQL State: 42S22, SQL Error Code: 207 Invalid column name 'DisplayTime'.

I can avoid this error by clearing these field's pfInUpdate flags in the TDatasetProvider's OnUpdateData event handler. However this solution requires that the specific field names be known to this function which sits in the generic classes mentioned above, thus breaking the code's generality.

What I am looking for is a generic means of signalling the calculated nature of these fields to the event handler function.

I cannot change their FieldKind or Calculated properties (to fkInternalCalc and True respectively) after the Open call as this generates a WorkCDS: Cannot perform this operation on an open dataset exception message. And, I cannot change these properties before the Open call since the Fields do not exist yet.

I can remove the pfInUpdate flag from these Field's ProviderFlags properties after Open but this does not get passed onto the "Delta" TClientDatset that arrives at the OnUpdateData event handler. I also tried setting the field's FieldDefs.InternalCalcField properties; again this does not get passed to the Delta dataset.

So, all the signalling ideas that I have tried have not worked. I would be grateful for any new ideas or an alternate approach.

All of the internet search results that I have encountered - including Cary Jensen's excellent articles - deal with design-time or non-SQL generated setups that do not apply to my situation.

Answer 1

You can create a mechanism in your class to pre-configure the ProviderFlags for the individual fields you want to ignore in your Update process.

As per the comments on your question, I'm suggesting you to create a new method in the class to open the inner ClientDataSet, all the magic will take place inside this method.

First, one simple mechanism is to include a new TStringList property which lists all the fields you want to ignore, that you will match by name. Feel free to adopt this or create a new better mechanism, the important thing is you're able to identify which fields you want to configure that way.

type
  TMyClass = class
    // all your current class here
  private
    FUpdateIgnoredFields: TStringList;
  public
    property UpdateIgnoredFields: TStringList read FUpdateIgnoredFields write SetUpdateIgnoredFields;
    //don't forget to create this in your constructor, free it in the destructor
    //and Assign any new value in the SetUpdateIgnoreFields method, as usual.
    procedure OpenInnerCDS; //the magic goes here
  end;

procedure TMyClass.OpenInnerCDS;
var
  FieldName: string;
  AFieldToIgnore: TField;
begin
  //opens the inner cds, but before that, configures the update-ignored  
  //fields in the underlying dataset
  //Let's call it InnerBaseDataSet;
  FInnerBaseDataSet.Open; //this opens the DataSet and creates all the fields for it.
  try
    for FieldName in FUpdateIgnoredFields do
    begin
      AFieldToIgnore := FInnerBaseDataSet.FindField(FieldName);
      if Assigned(AFieldToIgnore) then
        AFieldToIgnore.ProviderFlags := AFieldToIgnore.ProviderFlags - [pfInUpdate, pfInWhere];
    end;
    //now, let's open the ClientDataSet;
    FInnerClientDataSet.Open;
  finally
    //I suggest no matter what happens, always close the inner data set
    //but it depends on how the CDS->Provider->DataSet interaction is configured
    FInnerBaseDataSet.Close;
  end;
end;

//the way you use this is to replace the current ClientDataSetOpen with something like:

var
  MyInsance: TMyClass;
begin
  MyInstance := TMyInstance.Create();  //params
  try
    //configuration code here
    //MyInstance.InnerCDS.Open;  <-- not directly now
    MyInstance.UpdateIgnoreFields.Add('CALCULATED_SALARY');
    MyInstance.OpenInnerCDS;
    //use the CDS here.
    MyInstance.InnerCDS.ApplyUpdates(-1); //safely apply updates now.
  finally
    MyInstance.Free;
  end;
end;

Take it as a idea.

I wrote all the code here, maybe the syntax is wrong, but it shows the whole idea.

Answer 2

you can pass ProviderFlags (as well as few other properties) from client to provider (delta) side by setting corresponding optional parameters on CDS. do not forget to set IncludeInDelta param