Pagination not working for MSSQL, But work fine for Mysql and Sqlite

Wappler General Bugs
1

I created a simple table with 3 fields, id, code, name and inserted with 100 records for page query testing.

I tested on Mysql and Sqlite, the pagination work fine.

But for MSSQL 2019 & 2012 which I have used for testing, the pagination has no response and via debug screen, below is the error message

offset @p0 rows fetch next @p1 rows only - invalid usage of the option next in the fetch statement

Is this a Bug of wappler or MSSQL? Anyone can help?

4

Do you use Node or PHP?

5

I only use node

6

I did a google search on the error message, it seems that MSSQL required the ORDER BY clause to be on the query when using OFFSET and FETCH.

OFFSET FETCH Clause (SQL Server Compact) | Microsoft Learn

Limitations in Using OFFSET-FETCH

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

Does it work when you add an order by field to the query. You can simply use the id column in the sorting to add it.

1 Like
7

Thanks. It works…