CODEWITHSUNDEEP
delphifiredac
zac
zac

Reputation: 4908

How to limit number of records in TFDMemTable?

I have a TFDMemTable filled with thousands of records. Is there a way to limit result records for only the first 50 ?

I've tried to use:

FDMemTable.FetchOptions.RecsSkip := 0;
FDMemTable.FetchOptions.RecsMax := 50;
FDMemTable.Open;

But it did not work, data remained unchanged.

Upvotes: 1

Views: 1727

Answers (1)

MartynA
MartynA

Reputation: 30715

I expect @Victoria will be able to show you a better and more general way, but there are at least two ways to do this:

  • Use FD's FDLocalSQL feature to copy the first X rows of the FDMemTable into, say, an FDQuery and then copy them back into your FDMemTable.

  • Apply a filter to the FDMemTable to filter out the other records, use an FDBatchMove to copy the X records into a second FDMemTable and then copy them back into the original FDMemTable.

To implement the first of these, add the following components to your form/datamodule:

FDLocalSQL1: TFDLocalSQL;
FDConnection1: TFDConnection;
FDQuery1: TFDQuery;
FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink;

and then execute code like this:

procedure TForm3.CopyData1;
begin
  FDConnection1.DriverName := 'SQLite';
  FDConnection1.Connected := True;

  FDLocalSQL1.Connection := FDConnection1;
  FDLocalSQL1.DataSets.Add(FDMemTable1);  // this is the source dataset

  FDLocalSQL1.Active := True;

  FDQuery1.SQL.Text := 'select * from FDMemTable1 order by ID limit 5';  //  ID being an Integer field of the FDMemTable
  FDQuery1.Active := True;

  FDMemTable1.Close;
  FDMemTable1.Data := FDQuery1.Data;  // Re-opens FDMemTable 1, which now contains only the first X records
end;

FD's LocalSQL uses Sqlite to do its stuff. The functional equivalent in Sqlite's SQL to "Select Top X ..." is its limit clause.

An advantage of using LocalSQL for your task, of course, is that because LocalSQL supports order by, you can it to determine which (top) X records are retained.

The batchmove method requires a bit less code but requires you to have a way of identifying the first X records using a filter expression. An example using an ID field might be

procedure TForm3.CopyData2;
begin
  FDMemTable1.Filter := 'ID <=50';
  FDMemTable1.Filtered := True;
  FDBatchMove1.Execute;  //  move data from FDMemTable1 to FDMemTable2;
  FDMemTable1.Close;
  FDMemTable1.Data := FDMemTable2.Data;  // Re-opens FDMemTable 1, which now contains only the first X records
end;

Btw, you say

I have a TFDMemTable filled with thousands of records. I

I think the problem with the method you've tried is probably that by the time you have the records in the FDMemTable, it's too late to try and limit the number of them in the way you're attempting. *)

Upvotes: 3

Related Questions