Lucee with PostgreSQL - there are more question marks in the SQL than params defined

Viewed 52

I'm experiencing an issue while using PostgreSQL with Lucee on the backend. One of the tables has column with jsonb datatype. I'm using ? operator to check existence of the value. While this works when I run it in DBeaver, I'm experiencing an issue while processing the file with lucee. Please see the code below.

public query function getItems( uid ) {
     var sqlText = "
        SELECT ar.role_group, ar.role_indv 
        FROM app.app_roles ar, app.app_user au
        WHERE au.user_roles ? ar.role_id::text AND ar.role_is_active = 'Y' AND au.user_id = :uid
     ";
     return queryExecute( sqlText, {uid: { value: arguments.uid, cfsqltype: "integer" }}, {datasource : "my_dev"});
        }
writeDump(getItems(24));

SQL code is working and seems correct. The issue is on this line: WHERE au.user_roles ? ar.role_id::text

I couldn't figure out why Lucee is having issues with this code. Do any of you know a workaround for this issue? Please see error below.

Message: string there are more question marks in the SQL than params defined

Thank you.

0 Answers
Related