[SOLVED] Delphi 10.2: Using Local SQL with Firedac Memory Tables

Delphi 10.2: Using Local SQL with Firedac Memory Tables

How can I use Firedac LocalSQL with FDMemtable? Is there any working example available?

According to the Embarcadero DocWiki I set up a local connection (using SQLite driver), a LocalSQL component and connected some Firedac memory tables to it. Then I connected a FDQuery and try to query the memory tables. But the query always returns "table xyz not known" even if I set an explicit dataset name for the memory table in the localSQL dataset collection.

I suspect that I miss something fundamental that is not contained in the Embarcadero docs. If anyone has ever got this up and running I would be grateful for some tips.

Solution

Here is some code I wrote for an answer here a while ago, which is a self-contained example of using LocalSQL, tested in D10.2 (Seattle). It should suffice to get you going. Istr that the key to getting it working was a comment somewhere in the EMBA docs that FD's LocalSQL is based on Sqlite, as you've noted.

procedure TForm3.CopyData2;
begin
  DataSource2.DataSet := FDQuery1;

  FDConnection1.DriverName := 'SQLite';
  FDConnection1.Connected := True;

  FDLocalSQL1.Connection := FDConnection1;
  FDLocalSQL1.DataSets.Add(FDMemTable1);

  FDLocalSQL1.Active := True;

  FDQuery1.SQL.Text := 'select * from FDMemTable1 order by ID limit 5';
  FDQuery1.Active := True;

  FDMemTable1.Close;
  FDMemTable1.Data := FDQuery1.Data;
end;

procedure TForm3.FormCreate(Sender: TObject);
var
  i : integer;
  MS : TMemoryStream;
begin
  FDMemTable1.CreateDataSet;
  for i := 1 to 10 do
    FDMemTable1.InsertRecord([i, 'Row:' + IntToStr(i), 10000 - i]);
  FDMemTable1.First;

  //  Following is to try to reproduce problem loading from stream
  //  noted by the OP, but works fine
  MS := TMemoryStream.Create;
  try
    FDMemTable1.SaveToStream(MS, sfBinary);
    MS.Position := 0;
    FDMemTable1.LoadFromStream(MS, sfBinary);
  finally
    MS.Free;
  end;
end;

As you can see, you can refer in the SQL to an existing FireDAC dataset simply by using its component name.