How to use the RecsSkip and RecsMax property of TFDQuery at runtime

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

Ong

Cannot reproduce. Just, before changing the fetch options on a prepared query object call Disconnect first. Which doesn't seem to be your case though, or is that query object prepared when you change the fetch options in your real code? Oh, and in any case, tuples will be fetched in unpredictable order so long you won't sort them on the DBMS side (by using ORDER BY clause). – Fayola

A Added the Disconnect but doesn't work – Ong

Is it Tokyo 10.2.3? – Fayola

yes, I use 10.2.3 – Ong

also add the 'Order BY' . Doesn't skip at runtime – Ong

Thanks! Oh, and I forgot. Sorry for emitting so many comments.. And DBMS that you use? Do you use index fields? Well, in the end might be easier for you to post the whole module (*.dfm and *.pas) :) – Fayola

I use firebird as database, and don't use the index field. – Ong

Still cannot reproduce. Delphi Tokyo 10.2.3, Firebird 3.0.2.32703. We need to go deeper ;-) – Fayola

@Fayola I found the problem. It was only the space before the select ' select '. – Ong

Wait. I'll investigate.. It should not happen. I haven't used leading space in my command. – Fayola

Nice catch! Sorry for not following your exact example.. – Fayola

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.

Fayola

I'll update the post with issue number after deeper risk analysis. – Fayola

You're welcome! And thanks for finding this issue! I'm about to start the analysis and report back (just need some coffee now :) – Fayola

@Victoria: Well done, +1. Tbh, what depresses me about this problem is that even high-quality libraries like FD rely on hacks like if ...CompareText(Copy(FCommandText, 1, 6), 'SELECT') = 0 rather than use a proper SQL parser to do its analysis. After all, the 'S' in SQL stands for 'Structured' so an analyser ought to be able to deal with nested clauses in the query as well as the top-level construct such as SELECT ... – Hospital

@MartynA, it would be nice to have one! Ravaut123, reported as RSP-20403. The proposed hotfix should be harmless (I hope :) – Fayola

Voted on RSP-20403 – Ong