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.

Socinian

Does your component derive from TClientDataSet or is a composition? – Ratal

Hopefully I have understood your question. The two classes I mentioned are not components in themselves, rather they contain the TClientDataSet, TDataSetProvider and TSQLQuery classes, none of which are derived ie. not subclassed. Of the two classes, one derives from the other but the base class of the two only derives from TObject. – Socinian

How do you open the inner ClientDataSet? I mean, do you call a method on your class or you directly call the inner ClientDataSet.Open method? – Ratal

The Open method is called directly on the inner TClientDataSet. The TClientDataSet is returned as a var parameter in the constructor of my dervied class because it is used extensively in code external to my class. – Socinian

The better approach that comes to my mind is to change that to call a Open (or whatever name) method on your class and that in turn Opens the inner ClientDataSet. The good thing is you don't have to change all your codebase, but only those places where you want to ignore some fields from the update in ApplyUpdates. – Ratal

Are you willing to make this change in your code? – Ratal

I have no concerns about changing my library code, although I obviously only do it with some care. I appreciate the efforts you have put into this. However, the exception does not occur when I Open the TClientDataSet but rather when the ApplyUpdates occurs. I have coded and successfully tested an alternate approach where I save a pointer to the previous OnUpdateData event handler (EH) and set a new local EH (whose first task is to call the previous EH). The local OnUpdateData EH contains some specific code that clears ProviderFlags for each non-physical field on the "Delta" dataset. – Socinian

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.

Ratal

The principle behind this idea is correct and answers my signalling needs. However, as I mentioned in my previous comment, the exception occurs during ApplyUpdates rather than when calling Open. To use the solution above I would utilise your UpdateIgnoredFields property within the TDataSetProvider's OnUpdateData event handler because the Open operates on the entire dataset while OnUpdateData operates on the "Delta" dataset. Many thanks for your help. Sorry I cannot vote you up, my "reputation" is insufficient :-) – Socinian

@Chris, the shown approach just prevents the exception at ApplyUpdates time. By removing the pfInUpdate flag. Now that I think about it a bit more you have to remove also the pfInWhere flag. Just try it and you'll see how this works. – Ratal

You are correct about the need to remove the pfInWhere flag as well. However, it does not work to clear the ProviderFlags on the main ClientDataSet - I tried this unsuccessfully early on in my testing - it only works on the "Delta" dataset. The reason is that although you can clear the flags on the main dataset this does not get signalled to the "Delta" dataset, which still has the flags set. I tried to trace through the VCL but couldn't find the "disconnect", still it definitely doesn't work. Hence the need to do this in the OnUpdateData event handler which deals only with the delta dataset. – Socinian

@Chris: I'm doing this before the fields get to exist in the ClientDataSet, so the changes must propagate when the ClientDataSet opens a couple of lines after in the code. Unless you create that at design time, it will work. – Ratal

It is not possible to do anything to the fields before the ClientDataSet is opened because it is the very act of opening that causes the SQL within the TSQLQuery to be executed and it is from this that all the fields in the ClientDataSet are created; in this arangement the fields simply do not exist prior to the dataset being opened. – Socinian

Using TSQLQuery to dynamically define the fields in the ClientDataSet is a very powerful feature but it means the fields don't exist until the dataset is opened; this was the cause of my initial problem. – Socinian

sorry, it looks like we are not talking the same language here. I don't know how to make you look or try what's already in my answer. – Ratal

I did read your code. It is clear (and well written). However the two datasets you mention don't represent my situation. In my setup there is a main ClientDataSet that exists continuously and a "Delta" dataset that is automatically created by the DataSetProvider as part of the ApplyUpdates call. The Delta dataset is a dynamic dataset that does not exist outside of the ApplyUpdates call and certainly does not exist at the point the main ClientDataSet is opened. The two datsets do not communicate. As I explained, I tried your ProviderFlags clearing approach previously and found it to not work. – Socinian

You said in your question: I have a TClientDataset connected to a TDatasetProvider connected to a TSQLQuery connected to a TSQLConnection. I'm calling that TSQLQuery the FInnerBaseDataSet and I'm modifying it's fields before opening the TClientDataSet. This doesn't represent your situation? – Ratal

Ah. I simply did not understand that your FInnerBaseDataSet was my TSQLQuery, rather, I thought it was an extra TClientDataset (the purpose of which was unclear to me!). So, I have implemented your solution as it is better than my one; my solution resulted in more decentralised code and more complex usage. The only change I made was to pass the fields as an open array so that I could do something like WorkQuery.SetFieldsExcludedFromUpdate(['DisplayTime', 'Issued', 'Received']) followed by WorkQuery.Open where WorkQuery is my class that encapsulates the chain of VCL classes. Thanks again. – Socinian

Nice to know it was good for you, glad I finally managed to explain what I meant. :) – Ratal

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

Hegarty