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…

Accepted Answer

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.

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

Solution

Problem