Error when use parameters in custom queries using node.js LIMIT and OFFSET clause

[detinfo]

Wappler Version :5.0
Operating System :Windows
Server Model: NodeJS Local web server
Database Type:MariaDB 10.3.34
Hosting Type:

Expected behavior

What do you think should happen?

execute the SQL statement without adding the single colons (’) on LIMIT as they cause an error because they are not a literal.

Actual behavior

when execute custom query with LIMIT and OFFSET Wappler adds a single colon in parameter. It provoques a SQL error.
example:
SELECT * FROM users LIMIT :P1
or
SELECT * FROM users LIMIT :P1 OFFSET :P2

This adds single colons ’ arount the values of :P1 and :P2, which causes a SQL error when hiting th run button. sample:
SELECT * FROM users LIMIT ‘10’ OFFSET ‘2’

How to reproduce

1.- Create a custom query with Limit or Offset.
2.- include LIMIT with parameter :P1
3.- hit de run button

if LIMIT is hardcoded it works OK.

Bug reported previously here

Problem with Parameters in custom queries using node.js

Wappler Version: 3.6.1 Operating System: Windows, using node.js in Docker Expected behavior In a Database custom query I try to use parameters in the LIMIT part of the select, sample: SELECT * FROM users LIMIT :P1,2 or SELECT * FROM users LIMIT 1 OFFSET 2 This adds single colons ’ arount the values of 1 and :P2, which causes a SQL error when hiting th run button. sample: SELECT * FROM users LIMIT ‘10’ OFFSET ‘2’ The problem only comes in node.je environment. PHP environment wo…

[Alexander_Paladines]

Wappler 5.2.0
Windows 11, NodeJS, docker

Hi,
I get same error:

When LIMIT is static value (LIMIT 5), the query run without problems, but, when trying to add param to get something like Paged Records with {{$_GET.limit}} using param (LIMIT :P2) I get “ER_PARSE_ERROR”.

Please, how I can get limit and offset to build a custom pagination in UI like Paged Records query type doing.
Using JOIN tranformation not works form me because the lack of condicions in the whole JOIN transformation:

Join Results of 2 Data Collections

Hi, Is possible to apply condition in the JOIN Transformation?, I mean, if apply a WHERE condition in query of Collection 2 it doesn’t affect the generate table in UI because the condition only apply to the query of one collection and not to the entire JOIN with both collections.

@detinfo

How to reproduce

1.- Create a custom query with Limit or Offset.
2.- include LIMIT with parameter :P1
3.- hit de run button

[Alexander_Paladines]

bump!

[patrick]

Not all databases allow the LIMIT statement to be parameterized. Tested with SQLite where is works fine, MySQL gave an error.

[Alexander_Paladines]

Oh I see.
I’m working on Mariadb… I guess just like mysql the error is the same, maybe an alternative way to do that?

[mebeingken]

You might have to go with a stored procedure.

[Alexander_Paladines]

Thanks, I will investigate it, but looks a bit complicate it for me.
My workaround was to change the way in how I use the relational fields and directly paste in a new column the value, maybe with with time the DB growth a bit more. But now I can use native Paged Records inside wappler wihout the need to use custom query to solve this:

Join Results of 2 Data Collections

Hi, Is possible to apply condition in the JOIN Transformation?, I mean, if apply a WHERE condition in query of Collection 2 it doesn’t affect the generate table in UI because the condition only apply to the query of one collection and not to the entire JOIN with both collections.

[George]

@patrick

Not all databases allow the LIMIT statement to be parameterized. Tested with SQLite where is works fine, MySQL gave an error.

Marking as not bug as this is database related issue