Adding a calculated field to a Query at run time

Question

I'm getting data using a query in Delphi, and would like to add a calculated field to the query before it runs. The calculated field is using values in code as well as the query so I can't just calculate it in SQL.

I know I can attach an OnCalcFields Event to actually make the calculation, but the problem is after adding the calculated field there are no other fields in the query...

I did some digging and found that all of the field defs are created but the actual fields are only created

if DefaultFields then
    CreateFields

Default Fields is specified

procedure TDataSet.DoInternalOpen;
begin
    FDefaultFields := FieldCount = 0;
    ...
end;

Which would indicate that if you add fields you only get the fields you added.

I would like all the fields in the query AS WELL AS the ones I Add.

Is this possible or do I have to add all the fields I'm using as well?

Lauzon

I don't understand why you can't use the values from the code in your SQL....I build dynamic SQL statements regularly that use values from the code in the SQL.... – Platitudinous

see my answer for a new way to do this since delphi Berlin – Goltz

did you try to PREPARE the query ? It could have created fielddefs (not field objects yet though) – Deason

Answer 1

Delphi now has the option to combine automatic generated fields and calculated fields : Data.DB.TFieldOptions.AutoCreateMode an enumeration of type TFieldsAutoCreationMode. This way you can add your calculated fields at runtime. Francois wrote in his answer how to add a field at runtime.

Different modes of TFieldsAutoCreationMode :

• acExclusive

  When there are no persistent fields at all, then automatic fields are created. This is the default mode.

• acCombineComputed

  The automatic fields are created when the dataset has no persistent fields or there are only calculated persistent fields. This is a convenient way to create the persistent calculated fields at design time and let the dataset create automatic data fields.

• acCombineAlways

  Automatic fields for the database fields will be created when there are no persistent fields.

Goltz

It looks like this allows combining design time calculated fields and run-time data fields. Did you test/succeed what you claim in the answer? – Platino

This isn't math homework where you have to 'proof' everything. The code to create a field at runtime is described in another answer – Goltz

Well, the documentation does not say what you claim. – Platino

Tested in C++ Builder XE8, everything work OK. This is simpler solution than that of Francois, because you dont have to add additional code to your project. – Tarpon

Answer 2

Nothing prevents you from creating all the fields first in your code,
then add your calculated fields.

You can either use a "hacked type" to use the protected CreateFields:

type
  THackQuery = class(TADOQuery)
  end;
[...]
  MyQuery.FieldDefs.Update;
  THackQuery(MyQuery).CreateFields;

or borrowing some code from CreateFields:

  MyQuery.FieldDefs.Update;
  // create all defaults fields
  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]);

then create your calculated fields:

  MyQueryMyField := TStringField.Create(MyQuery);
  with MyQueryMyField do
  begin
    Name := 'MyQueryMyField';
    FieldKind := fkCalculated;
    FieldName := 'MyField';
    Size := 10;
    DataSet := MyQuery;
  end;

Chirp

In the case where you are subclassing a TQuery or another TDataset type, you don't need to "hack" to access protected fields. This answer is the correct way to go when you are (a) adding a calculated field in code, and (b) the entire thing is in code such as you are writing a custom query or component entirely in code. – Amidst

Answer 3

You need to add all fields in addition to your calculated field.

Once you add a field, you have to add all of the fields that you want in the data set.

Delphi calls this persistent fields versus dynamic fields. All fields are either persistent or dynamic. Unfortunately, you can't have a mixture of both.

Another thing to note, from the documentation is

Persistent fields component lists are stored in your application, and do not change even if the structure of a database underlying a dataset is changed.

So, be careful, if you later add additional fields to a table, you will need to add the new fields to the component. Same thing with deleting fields.

If you really don't want persistent fields, there is another solution. On any grid or control that should show the calculated field, you can custom draw it. For example, many grid controls have a OnCustomDraw event. You can do your calculation there.

Curling

Answer 4

If you have know your to be calculated fields names at runtime, you can use something like that.

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;

Consternation

Answer 5

    //assume we have an fdquery named  FDQuery1
    //we write this code in AfterOpen section

note: This method have a problem The calculated field is placed in place of the last field To solve this problem, it is enough to add an additional field in query, for example, consider the value zero as the final field like this :

select * , 0 as myfield from mytable


procedure Tform1.FDQuery1AfterOpen(DataSet: TDataSet);
var
 i : Integer;
 aField : TField;
begin
    FDQuery1.Close;
    for i := 0 to FDQuery1.FieldDefs.Count - 1 do begin
     aField := FDQuery1.FieldDefs [ i ].CreateField ( fdQuery1 );
    end;
    aField.DataSet := fdQuery1;
    with aField do
    begin
      Name := 'myField';
      FieldKind := fkCalculated;
      FieldName := 'myField';
      Size := 10;
      DataSet := FDQuery1;
    end;
    fdQuery1.FieldDefs.Update;
    fdQuery1.AfterOpen:=nil;
    fdQuery1.Open;
    ShowMessage(FDQuery1.FieldByname('myField').AsString);
end;

now you can write onClacField method for myField

Mellman