Delphi 10.2: Using Local SQL with Firedac Memory Tables

Question

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.

MartynA · Accepted Answer · 2019-11-13 10:06:20Z

3

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.

edited Nov 13, 2019 at 10:06

answered Nov 12, 2019 at 18:39

MartynA

30.8k5 gold badges36 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MichaSchumann Over a year ago

Thanks, that was exacly what I did with one difference: I loaded the memtables from a stream. They seem to loose their names when loaded from stream because if I added records manually it worked. So I just set the names again after loading from a stream everything works fine. You pointed me into the directoion adding records in code so I accept your answer!

MartynA Over a year ago

Hmm, that's a bit odd. I've updated my answer so that the FDMemtable is saved to a memory stream during the FormCreate and reloaded from it, but this works fine and does not affect the LocalSql operations.

Collectives™ on Stack Overflow

Delphi 10.2: Using Local SQL with Firedac Memory Tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related