How to use the RecsSkip and RecsMax property of TF

Question

I was looking for a skip and take selection in the TFDQuery. The properties I found are .FetchOptions.RecsSkip and .FetchOptions.RecsMax. I use Tokyo 10.2.3 and database Firebird 3

I make the query at runtime and I want to get the start record at 5 and get the 8 next records.

I to something like:

Result does not skip the fist 5 records

var
  qryTest: TFDQuery;
begin
 qryTest:= TFDQuery.Create(self);
 qryTest.Connection := self.FDConnection;

 qryTest.sql.Text:= ' select * from salutationdescriptions order by ID';
 qryTest.Disconnect();
 qryTest.FetchOptions.RecsSkip:= 5;
 qryTest.FetchOptions.RecsMax:= 8;
 qryTest.Open();

But this give as result the first 8 records. The 5 first records are not skipped.

Ok, I to the same but now I set TFQQuery at designtime (component on the form) and add the selection 'select * from salutationdescriptions order by ID' in the component.

Run the code:

This skip the fist 5 records

  qryItem.Close;
  qryItem.Disconnect();
  qryItem.FetchOptions.RecsSkip:= 5;
  qryItem.FetchOptions.RecsMax:= 8;
  qryItem.Open();

The result I get is ok. This skip the first 5 records. When I add the qryItem.sql.text then it doesn't skip the first 5 records

This does not skip the fist 5 records

  qryItem.Close;
  qryItem.sql.Text:= ' select * from salutationdescriptions order by ID';
  qryItem.Disconnect();
  qryItem.FetchOptions.RecsSkip:= 5;
  qryItem.FetchOptions.RecsMax:= 8;
  qryItem.Open();

That doesn't skip the 5 first records.

Must I set something in the properties?

I want to use the RecsSkip and RecsMax at runtime. Any suggestions?

FOUND the problem

in the SQL.Text I must begin with 'Select ... no space between ' and select

Answer 1

As you've already found out, the problem is in a leading space in the SQL command. It is because the command generator does not take leading command spaces into account. There is a code like this (modified and extensively simplified for explanation by me):

function TFDPhysCommandGenerator.GenerateLimitSelect(ASkip, ARows: Integer;
  AOneMore: Boolean; var AOptions: TFDPhysLimitOptions): string;
begin
  if (True) and (CompareText(Copy(FCommandText, 1, 6), 'SELECT') = 0) then
    Result := GetLimitSelect(FCommandText, ASkip, ARows, AOptions)
  else
    Result := FCommandText;
end;

There you might see the problem. The command generator returns DBMS' native LIMIT command only when the FCommandText field, which is assigned from the query command text starts by the SELECT word. Which is not your case (due to the leading space), hence the generator returns the command as is.

This is a FireDAC bug I've reported as RSP-20403. Since there is more checks like this, I think the simplest fix for this would be trimming SQL command directly in its constructor like:

constructor TFDPhysCommandGenerator.Create(const ACommand: IFDPhysCommand);
begin
  ...
  FCommandText := Trim(ACommand.SQLText);
  ...
end;

So now it's upon EMBT how to fix this.