CODEWITHSUNDEEP
delphicalculated-field
James
James

Reputation: 9985

Adding a calculated field to a Query at run time

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?

Upvotes: 14

Views: 25448

Answers (5)

r_j
r_j

Reputation: 1368

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.

Upvotes: 4

jahanbini01
jahanbini01

Reputation: 1

    //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

Upvotes: 0

sabri.arslan
sabri.arslan

Reputation: 558

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;

Upvotes: 2

Francesca
Francesca

Reputation: 21640

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;

Upvotes: 16

Marcus Adams
Marcus Adams

Reputation: 53850

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.

Upvotes: 4

Related Questions