4

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?

Community
  • 1
  • 1
RBA
  • 12,337
  • 16
  • 79
  • 126
  • 1
    If there is no specific reason for the complex code as presented in your question, go for the KISS principle: **Design** your calculated fields, fill them runtime. Even in those cases where the fields are unknown beforehand, you can **add** calculated fields at run time which is less convoluted then trying to change them as you suggest. I pity the developer who comes after you and has to understand why the database fields suddenly have different values in the code than in the database. – Jan Doggen Aug 13 '13 at 12:19
  • 1
    As I specified in the LE, I do not work in this way. This is a question about good practices when someone want to do this, even it is bad design. – RBA Aug 13 '13 at 12:30
  • @RBA: maybe you could suggest this someone to post a question about the reason that lead him/her to come up with that design, so the community could provide alternative solutions. – AlexSC Aug 13 '13 at 14:57
  • The reason was exposed here. So, this question is the reason the community can provide alternative solutions to change a field mapping the data to a calculated field. Beside the bad design. – RBA Aug 13 '13 at 16:52
  • 1
    Did anyone actually try to read the code? So he creates non existent field called txtState, txtOldState...In his example he's basically just pushing the values of state and oldstate as String Fields...is it better than Francois hack...Francois does the same thing...just uses a Hack to use the Protected methods to do create the fields instead...Now would I use either method...No it's easier and safer to use SQL to do it for your... – House of Dexter Aug 13 '13 at 17:42
  • @RBA: well, the problem is I don't know why someone would declare a field as data and change it to calculated right after opening the dataset. I believe it´s some kind of trick, but to accomplish what is precisely what I can't see. That's what I would like to know. – AlexSC Aug 13 '13 at 17:42

3 Answers3

3

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.

AlexSC
  • 1,823
  • 3
  • 28
  • 54
  • 1
    Thank you for your answer. But is already telling what I said. Can you describe the behavior of the field in discussion after this kind of change? I already admitted and said that this is a bad design, from start to stop. This is only an example of 'not to do'. – RBA Aug 13 '13 at 16:56
1

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).

BenMorel
  • 34,448
  • 50
  • 182
  • 322
House of Dexter
  • 386
  • 1
  • 7
  • technically, the field exists because the server will generate a column in the resultset and the dataset will not be able to see the difference from a real column. – AlexSC Aug 13 '13 at 19:36
  • returning a fabricated column from SQL is not the same as declaring a calculated field. First, by making the server to fabricate the column will lead it to work more and will generate more network trafic, specially if the resultset has a big number of rows. Second, the corresponding field will be a data field, that when changed, will change the dataset state. Calculated field do not do that, since they are calculated in the OnCalcFields event. – AlexSC Aug 13 '13 at 19:40
  • The amount of work and traffic is miniscule...about the same as rename a field to another name in SQL...and about the same amount of work...and that's done all the time...You now allow the Dataset to bind to the Field with the proper Datatype for your calculated field...You could argue that he's passing the work around...a bit for the Server a bit for the Client ;) No way am I arguing that this is a good idea...I just think Francois is just as much of a hack as this... – House of Dexter Aug 13 '13 at 21:16
  • Recopy( and edited) a statement I made on the question "Francois does the same thing...uses a Hack to use the Protected methods to create the fields instead" – House of Dexter Aug 13 '13 at 21:21
  • I don't use them...I create them on the server side...with SQL...That doesn't mean others might not need them...I've just never needed them in the 18 years of programming in Delphi... – House of Dexter Aug 14 '13 at 19:58
0

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.

Andy_D
  • 2,340
  • 16
  • 21
  • 2
    There's no substance in this answer any more than the question itself. - *"Is it safe to change a dataset field to a calculated one on the runtime?"* - *"I don't think so."* – Sertac Akyuz Aug 13 '13 at 11:17