MS Sql pagination error- incorrect syntax

[sbecks]

Thanks for reporting an issue! Please spend 60 seconds reading this...

======== TEMPLATE BUG FORM ========

Wappler Version: 3.4.2
Operating System: Windows 10

Expected behavior

What do you think should happen?

Paginated server query should return paginated results

Actual behavior

What actually happens?

Server query returns an error:

message: "SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '75'.

How to reproduce

Only seems to impact running a paginated query using MS Sql, I'm using an Azure SQL database. Issue persists with hardcoded values in the server query so doesn't relate to the GET variables being passed. Running the query with an offset value of 0 works fine. The incorrect syntax is always 'near' the end of the offset range (so offset of 50 will have 'incorrect syntax near 75' error).

Looks to be the same issue here:

Pagination Issues with SQL Server

I am having a problem with getting pagination to work. If I look at the results for the original query, it works fine. When I click on one of the pages, I get an error. I added “?offset=5” to the query and I see the following error: {“code”:“42000”,“file”:“C:\Websites\GraphicsXchange\dmxConnectLib\lib\db\Connection.php”,“line”:108,“message”:“SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘AS’.”,“trace”:"#0 C:\Websites\GraphicsXchange\dmx…

and here:

Pagination help.. Offset getting sent but nothing

ok, finally getting back to this. Seems to still be an issue and cannot think of why. Here is the error {“code”:“42000”,“file”:“D:\home\site\wwwroot\dmxConnectLib\lib\db\Connection.php”,“line”:88,“message”:“SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘AS’.”,“trace”:"#0 Sorting and all other functions work great across entire site but anything with pagination has an error and does not work.

[sbecks]

Just wondered if you were able to confirm this as a bug?..not desperate for a fix but if I can’t use pagination I need to rethink the page layout/data structure quite substantially. I have to use SQL Server so not an option to shift to MySQL.

[patrick]

It certainly is a bug, do you also use PHP?

[sbecks]

Hi @patrick yes I’m using PHP. Thanks,

[patrick]

Unzip the following file in dmxConnectLib/lib/db/server.

mssql.zip (856 Bytes)

[sbecks]

That’s great seems to work perfectly…Thank you!

[patrick]

Fixed in Wappler 3.5.1