Delphi - Change field to calculated field at runtime. Is this a good practice?

6.3k views Asked by At

As in the question's title, I argue with a colleague about how calculated fields should be used. From my knowledge, calculated fields are created at runtime as in the François's answer on the question Adding a calculated field to a Query at run time. On the same question there is another answer, from sabri.arslan which suggest to change an existing field to a calculated one(code bellow)

var
 initing:boolean;

procedure TSampleForm.dsSampleAfterOpen(
  DataSet: TDataSet);
var
 i:integer;
 dmp:tfield;
begin
if not initing then
 try
  initing:=true;
  dataset.active:=false;
  dataset.FieldDefs.Update;
  for i:=0 to dataset.FieldDefs.Count-1 do
  begin
   dmp:=DataSet.FieldDefs.Items[i].FieldClass.Create(self);
   dmp.FieldName:=DataSet.FieldDefs.Items[i].DisplayName;
   dmp.DataSet:=dataset;
   if (dmp.fieldname='txtState') or (dmp.FieldName='txtOldState') then
   begin
     dmp.Calculated:=true;
     dmp.DisplayWidth:=255;
     dmp.size:=255;
   end;
  end;
  dataset.active:=true;
 finally
  initing:=false;
 end;
end;

procedure TSampleForm.dsSampleAfterClose(
  DataSet: TDataSet);
var
 i:integer;
 dmp:TField;
begin
if not initing then
begin
 for i:=DataSet.FieldCount-1 downto 0 do
 begin
  dmp:=pointer(DataSet.Fields.Fields[i]);
  DataSet.Fields.Fields[i].DataSet:=nil;
  freeandnil(dmp);
 end;
 DataSet.FieldDefs.Clear;
end;
end;

procedure TSampleForm.dsSampleCalcFields(
  DataSet: TDataSet);
var
 tmpdurum,tmpOldDurum:integer;
begin
  if not initing then
    begin
      tmpDurum := dataset.FieldByName( 'state' ).AsInteger;
      tmpOldDurum:= dataset.FieldByName( 'oldstate' ).AsInteger;
      dataset.FieldByName( 'txtState' ).AsString := State2Text(tmpDurum);
      dataset.FieldByName( 'txtOldState' ).AsString := State2Text(tmpOldDurum);
    end;
end;

procedure TSampleForm.btnOpenClick(Sender: TObject);
begin
 if dsSample.Active then
   dsSample.Close;
 dsSample.SQL.text:='select id,state,oldstate,"" as txtState,"" as txtOldState from states where active=1';
 dsSample.Open;
end;

I believe that this change leads to an unknown behavior of the specified TField. It is safe to change a dataset field to a calculated one on the runtime? What kind of issues can this generate?

LE: This is a question. Its purpose it is to demonstrate good practice on adding a calculated field on a dataset at runtime. And, yes adding a calculated field at runtime is bad design.

LE2: This is only an example for 'do not this in this way'. As an argument I asked what is the behavior of the field in discussion after doing this. How that field will act?

3

There are 3 answers

1
AlexSC On BEST ANSWER

No, it´s not a good practice. The simple fact that the code is complex suggests that such a practice should be avoided. Someone already referenced the KISS principle and I agree with that.

Particularly, the simple fact that the dataset has to be opened twice is enough to make me to dislike this practice.

In addition, changing the field´s nature from data to calculated will change the way the dataset organizes the fields in its internal record representation (what the dataset calls a record buffer). Such a representation may be very different from one dataset implementation to another. Since the question didn´t identify a particular dataset, the changes in behavior are (in general):

  1. A data field will stored its value in a structure belonging to the underlying database client; a calculated field will stored its value in a not persistent buffer;
  2. During the dataset opening, there is a process named the field binding that consists to bind the data fields to the database client corresponding structure; when this binding fails, the dataset usually raises an exception; the calculated fields do not take part of this process because they use an internal field buffer to stored their value;
  3. The field, after becoming a calculated one, will accept values during the execution of OnCalcFields event in the way we are used to; it may not be used for filtering purposes, depending on the dataset implementation.

However, a certain dataset implementation my present some other consequences, depending on its purpose and features.

1
Andy_D On

A TField either maps to a database column or it doesn't and is derived by a calculation. This should be set at design-time. Any attempt to change this at runtime smacks of bad design IMO, and you are setting yourself up for a lot of potential headaches.

5
House of Dexter On

Did anyone notice this bit of code?

procedure TSampleForm.btnOpenClick(Sender: TObject);
begin
 if dsSample.Active then
   dsSample.Close;
 dsSample.SQL.text:='select id,state,oldstate,"" as txtState,"" as txtOldState from states where active=1';
 dsSample.Open;
end;

He's not changing a Database Field to a Calculated Field...He's changing a Non Existent Field to a Calculated Field. He knows the field type...it's going to be a string...So is it a big deal...No...Is it a hack...Yes...You can do the same thing in SQL with Cast...Matter of fact I've never really seen a reason to use Calculated Fields...I can usually do the same thing easier in the SQL.

I added more information after a bit more digging on why not to do either...

sabri.arslan code...to create the Fields...from the FieldList...also has problems as missing setting up keys and handling heirchy fields.

dmp:=DataSet.FieldDefs.Items[i].FieldClass.Create(self);

Then we have Francois...

if you call the none hacked code you get this...
  for I := 0 to MyQuery.FieldDefList.Count - 1 do
    with MyQuery.FieldDefList[I] do
      if (DataType <> ftUnknown) and not (DataType in ObjectFieldTypes) and
        not ((faHiddenCol in Attributes) and not MyQuery.FIeldDefs.HiddenFields) then
        CreateField(Self, nil, MyQuery.FieldDefList.Strings[I]);

umm...missing SetKeyFields will this cause unforseen behavior? Also if your ObjectView property is set to True...your Dataset will not behave properly for hierarchiacally fields...It looks to be safer to just call the Hack to CreateFields than to use his code...other than you must be positive that your dataset component never calls this code...

CreateField calls CreateFieldComponent and you get Result := FieldClassType.Create(Owner) for your TField

Taken from the Borland help of TFieldDef "A field definition has a corresponding TField object, but not all TField objects have a corresponding field definition. For example, calculated fields do not have field definition objects."

So I ask you...are you positive your not introducing unknown behavior by creating the CalculatedField on the fly? Are you positive that the Fields haven't been created yet or won't be created later? (There's a bug in sabri.arslan code because he does the open/after open...He's overwritting the original TFields..., I don't see why we need to recreate the TField's for the already opened dataset)

So what happens when CreateFields is called by the dataset(The BDE and ADO do this on InternalOpen and check to make sure that their are no values in Fields...do all the Dataset components do it this way? They don't have to). What happens to the Fields that you have already created...are they overwritten? I didn't see any code in the TDataset or TFieldDef that checked if the TField had already been created for the corresponding TFieldDef other than a check to DefaultFields(if Fields has a value).