How to get TFDQuery to recognise when new fields are added to a TFDMemTable it is querying via TFDLocalSQL

Question

After adding a field to an existing TFDMemTable, any TFDQuery that queries that table through a TFDLocalSQL does not recognise the new field.

The code below illustrates this: a TFDMemTable with a single field added to it in code and a TFDQuery that does a select on that table. (Assume the TFDMemTable is added to the DataSets property of the TFDLocalSQL and the TFDQuery points to the TFDConnection) The process for adding the field to a live TFDMemTable comes from an example project and the use of Unprepare, which doesn't seem to work in this case, comes from this question.

procedure TForm2.FormCreate(Sender: TObject);
begin

  with FDMemTable1.FieldDefs.AddFieldDef do begin
    Name := 'col1';
    DataType := ftString;
    Size := 10;
  end;
  FDMemTable1.CreateDataSet;
  FDMemTable1.Active := true;

  FDQuery1.SQL.Text := 'SELECT * FROM FDMemTable1';

  FDConnection1.Connected := true;
  FDLocalSQL1.Active := true;
  FDQuery1.Active := true;
end;

procedure TForm2.Button1Click(Sender: TObject);
var
  tempMT: TFDMemTable;
begin
  tempMT := TFDMemTable.Create(nil);

  try
    tempMT.Data := FDMemTable1.Data;
    FDMemTable1.Close;
    with FDMemTable1.FieldDefs.AddFieldDef do begin
      Name := 'col99';
      DataType := ftString;
      Size := 10;
    end;
    FDMemTable1.Open;
    FDMemTable1.MergeDataSet(tempMT, dmDataSet, mmNone);
    if not FDMemTable1.FieldDefs.Updated then FDMemTable1.FieldDefs.Update;
  finally
    tempMT.Free;

    FDQuery1.Active := false;
    FDQuery1.Unprepare;              // this is meant to uncache the fields
    FDQuery1.Active := true;         // Does not include 'col99' in result set
  end;
end;

The field is definitely added to the TFDMemTable and works fine. Any tips on how I get the TFDQuery to recognise the new column.

Answer 1

I think the reason you are getting this behaviour is that your code is missing a step.

In the finally clause of your click-handler, you need to close the FDConnection, like so

  finally
    tempMT.Free;

    FDConnection1.Connected := False;
    FDQuery1.Active := false;
    FDQuery1.Unprepare;              // this is meant to uncache the fields
    FDQuery1.Active := true;         // Does include 'col99' in result set
    Caption := IntToStr(FDQuery1.FieldCount);
  end;
  PageControl1.ActivePage := TabSheet3;  // has FDQuery grid

Once you have made the above change, you should find that you get the added column in FDQuery1. Btw, you can take out FDQuery.Unprepare, because it makes no difference. Prepare is usually used to compile the SQL code for the query inside the back-end DB server. In this case, the back-end server isn't involved because the FDLocalSQL1 component generates the data for FDQuery1 from the mem table. Unprepare usually tells the back-end server that it can release the compiled query resources.

I think the reason your version didn't work is because of the call to GetActualActive in

procedure TFDCustomLocalSQL.CheckActivate;
var
  i: Integer;
begin
  if GetActualActive and not FActivated then begin
    for i := 0 to DataSets.Count - 1 do
      DataSets.CheckUnique(DataSets[i]);
    FActivated := True;
    InternalAttachToSQL;
    for i := 0 to DataSets.Count - 1 do
      if DataSets[i].IsValid then
        InternalDataSetAdded(DataSets[i]);
  end;
end;

GetActualActive is defined as

function TFDCustomLocalSQL.GetActualActive: Boolean;
begin
  Result := Active and (Connection <> nil) and Connection.Connected;
end;

so the rest of CheckActivate is skipped if FDLocalSQL1 was previously active, which it was, and the FDConnection is already connected, which it is. So, disconnecting the FDConnection allows the innards of CheckActivate to execute, so FDLocalSQL1 "notices" the change to the structure of FDMemTable1.

Btw, rather that code it from scratch, I based my project on this one:

D:\D10\Samples\Object Pascal\Database\FireDAC\Samples\Comp Layer\TFDLocalSQL\InMemDB

which avoided a certain amount of guesswork in how your project is set up.