Problems with data paged in FirebirdSQL

[Ezequiel Juliano Müller]

I'm using FirebirdSQL database. When I try to run a test with paginated data for example:

Result := criteria.Page(1, 10).Items;

Firebird returns me the following error:

'[FireDAC][Phys][FB]Invalid parameter to SKIP. Only integers >= 0 are allowed.'.

The problem seems to be in the following code snippet:

unit Spring.Persistence.Core.Base;

function TPager.BuildSQL(const sql: string): string;
var
  offset: Integer;
begin
  offset := 0;
  if fPageIndex > 1 then
    offset := (fPageIndex - 1) * fPageSize;
  Result := fGenerator.GeneratePagedQuery(sql, fPageSize, offset);
end;

In this case, the Index is 1 and the offset is a value of 0 and that causes an error in Firebird.

[Stefan Glienke]

Please make sure it is using the correct generator class (that would be TFirebirdSQLGenerator). AFAIK it uses this syntax instead of the old FIRST SKIP syntax: http://www.firebirdsql.org/refdocs/langrefupd20-select.html#langrefupd20-select-rows

Another cause could be that FD is translating something under the hood.

[Ezequiel Juliano Müller]

I'm with the configured generator class:

procedure TDALConnection.DataModuleCreate(Sender: TObject);
begin
  inherited;
  fAdapter := TFireDACConnectionAdapter.Create(FDConnection);
  fAdapter.AutoFreeConnection := False;
  fAdapter.QueryLanguage := qlFirebird;
  fAdapter.Connect;
end;

If I do that way it works:

Result := criteria.Page(2,10).Items;

What I'm not getting is to bring only one record.

[Stefan Glienke]

Please check that it runs through TFirebirdSQLGenerator.GeneratePagedQuery because that generates a different statement that what you posted causes the error. and according to the QueryLanguage you selected it should use that sql generator.

[Ezequiel Juliano Müller]

Yes it is going through TFirebirdSQLGenerator.GeneratePagedQuery.

The process is this so realized...

I'm calling the following method:

When called the criteria it comes to:

Here he comes:

[Stefan Glienke]

From looking through the Firebird docs it looks like we have an off-by-one error here and it should say ROWS 1 to 10:

ROWS <m> [TO <n>]

Points to note when using two arguments:

• If m > the total number of rows in the dataset, an empty set is returned.
• If m lies within the set but n doesn't, the rows from m to the end of the set are returned.
• If m < 1 or n < 1, an error is raised.
• If n = m-1, an empty set is returned.
• If n < m-1, an error is raised.

[Stefan Glienke]

fixed #152

→ <<cset fad135e1c600>>