CODEWITHSUNDEEP
delphifiredac
Soon Santos
Soon Santos

Reputation: 2219

Open TFDQuery which already contains Fields

It is a practice in my code to populate the TFDQuery, TDataSource, TDBGrid only in runtime, so I can re-use these methods throughout the application.

For example: 

  with AFDQuery do
  begin
    Connection := dmDados.FDConnection;
    Active := False;
    SQL.Clear;

    SQL.Add('SELECT * FROM ' + VIEW_TXCONDOMINIO_VENCIDO);

    Open;
  end;
  ADataSource.DataSet := AFDQuery;
  ADBGrid.DataSource := ADataSource;

Let's say these code filled the TFDQuery with 10 fields. But I need 2 more fields that can only be calculated at run time. So I will have 12 fields.

Of course I found about Calculated and Aggregated Fields, but something is not fitting my needs yet.

There are some kind of Calculated and Aggregated fields as the documentation says.

What am I doing?

  1. Create my TFDQuery;
  2. Add the 2 (Calculated or Aggregate) fields;
  3. Run my code which will add the other 10 fields from FireDac.

If I use fkAggregate I can endup with 12 fields in the end, 10 of them of normal type and 2 of them as aggregate. The problem is that after all I can not set a value to these 2 aggregate fields, as I think they only allow expressions. SUM(), ... etc.

Then qryTaxasVencidas.AggFields.FieldByName('field_name').AsFloat := 10.0 does not work.

If I use fkCalculated or fkInternalCalc fields, they will for sure get the values and will be calculated in runtime in OnCalcFields.

The problem is that, I create 2 fields before opening the TFDQuery. When I open the TFDQuery it gives me an Argument out of range exception and the TDBGrid endup with only one field.

The code I am using (error happens in the end):

procedure TFrmGerAcordo.FormCreate(Sender: TObject);
begin
  CreateAggFieldsParaValoresTaxa;
  PopularTaxaCondominioPessoa(qryInadimplentes, dsInadimplentes, DBGridInadimplentes);
end;

procedure TFrmGerAcordo.CreateAggFieldsParaValoresTaxa;
var
  FieldName, Expressions: TStringList;
begin
  FieldName := TStringList.Create;
  FieldName.Add('valor_correcao');
  FieldName.Add('valor_honorarios');

  CreateCalcFields(qryTaxasVencidas, FieldName);
end;

procedure CreateCalcFields(AFDQuery: TFDQuery; FieldNames: TStringList);
var
  AField: TFloatField;
  I: Integer;
begin
  for I := 0 to FieldNames.Count - 1 do
  begin
    AField := TFloatField.Create(AFDQuery);
    AField.FieldName := FieldNames[I];
    AField.Calculated := True;
    AField.Visible := True;
    AField.FieldKind := fkInternalCalc;
    AField.DataSet := AFDQuery;
  end;
end;

procedure PopularTaxaCondominioVencido(var AFDQuery: TFDQuery;
var ADataSource: TDataSource; var ADBGrid: TDBGrid; Filtro: string);
var
  I: Integer;
begin
  with AFDQuery do
  begin
    Connection := dmDados.FDConnection;
    Active := False;
    SQL.Clear;
    SQL.Add('SELECT * FROM ' + VIEW_TXCONDOMINIO_VENCIDO + ' ');
    // Here I already have 2 fields in my query
    Open; // ERROR WILL HAPPEN HERE
  end;
  ADataSource.DataSet := AFDQuery;
  ADBGrid.DataSource := ADataSource;
end;

The DBGrid end up like this:

DBGrid with only one column

It seems like as I already have 2 fields in the TFDQuery, when FireDac populate the fields it messy up with the 2 fields is already in. What could I do?

Upvotes: 0

Views: 1870

Answers (1)

MartynA
MartynA

Reputation: 30715

What you ask is quite straightforward to do as long as you do it the right way, which is to use the FDQuery's FieldDefs to create its fields and then create the extra calculated fields you need.

The code below is what I use to add a calculated Boolean field with a FieldKind of fkInternalCalc to an FDQuery before opening it. It works entirely as expected. Prior to calling this code all I do is to the FDQuery's Sql.Text to the query to execute.

I'm sure you will be able to adapt this code to your situation.

procedure TForm2.Button1Click(Sender: TObject);
var
  i : Integer;
  AField : TField;
begin
  //  First, update the FDQuery's FieldDefs, then use them to create the fields
  //  based on the FDQuery's Sql.Text.  NOTE: the FDQuery must be closed at the start
  FDQuery1.Fields.Clear;
  FDQuery1.FieldDefs.Updated := False;
  FDQuery1.FieldDefs.Update;
  for i := 0 to FDQuery1.FieldDefs.Count - 1 do
    FDQuery1.FieldDefs[i].CreateField(Self);

 //  Next, add whatever calculated fields  are required
 AField := TBooleanField.Create(FDQuery1);
  AField.FieldName := 'Flag';
  AField.FieldKind := fkInternalCalc; // or fkCalculated
  AField.DataSet := FDQuery1;

  FDQuery1.Open;
  Caption := 'Opened';
end;

If you wanted to add calculated fields to an FDQuery which already has persistent TFields (created with the IDE Fields Editor), you could do that either by adding them to the persistent TFields with the Fields Editor, or use the code following the // Next comment in the code shown above.

Upvotes: 2

Related Questions