Error with PostgreSQL and JSONB field

We are experiencing some issues querying our Postgres database (version 13.1, driver: 9.4.1212) since it gives us the error shown on the following image:

The error message, translated from Italian, says: column index, 1, is higher than the number of index 0 columns. This happens when we use the cfqueryparam tag in the where condition (as shown in the image below).
On the other hand, if we specify the param value (e.g. genreId = 2), all goes smoothly.
It could be helpful to know that such error turns out only when cfqueryparam is used inside a jsonb function (such as jsonb_path_exists). When used outside, it works properly.

<cfquery name="local.q" datasource="test">
  SELECT data as book_field
  FROM books
  WHERE json_path_exists(data, '$.genres.name ? (@[] == <cfqueryparam value="#arguments.genreId#" sqltype="varchar"> )');
</cfquery>

Assuming it could depend on the fact that we used an archived driver and deciding to update it, we downloaded from PostgreSQL JDBC Download
and positioned it in ‘.\lucee-5.3.7.47\WEB-INF\lucee-server\bundles’. Nonetheless, although we can see the driver is present in the directory it doesn’t get loaded as it can be seen in the image below.

Version 42.2.19 is the driver we want our program to use, but it gets overwritten by the previous one (version 9.4.1212). We tried to remove the older version, but it gets imported anyway.
We tried to specify the exact version in our Application.cfm and yet the problem still stands. Image below shows our datasource and our attempt to do as described above.

We don’t know if updating the driver is going to solve our main problem, still we would like to do it in order to keep our application as updated as possible.

We thank you in advance for all the help you will be able to give us.

OS: Windows
Lucee Version: 5.3.7.47

1 Like

@Roberto_Marzialetti I checked this, Yes the issue happens when use cfqueryparam in where condition inside jsonb function But the same error happens with ACF too. So I think this is a Postgres issue.

@cfmitrah, many thanks .

Concerning the driver, could you tell me how I can update it?
If you need more info, let me know

Thanks

I think it’s a problem due to Lucee not handling semantic versioning, causing it to see 9.4.1212 as more recent than 42.2.19 [LDEV-3150] false update notification for PostgreSQL in admin - Lucee

this can all be solved by beautiful human sorting [LDEV-1784] Add human sorting option - Lucee

1 Like

Did you try removing the Postgresql 9.4.1212 extension first?

Hello everyone, I am a colleague of Roberto and we’re working on the same problem.
I’m writing to inform you that we managed to update the driver but unfortunately the problem still remains.

On the other hand, the error has changed as you can see below

As before, the error appears only when we use cfqueryparam. Otherwise, it works well.

1 Like

Solved it in a different way (easier than we thought it would be). Yet the main problem still stands, we can’t use cfqueryparams in a jsonb function :frowning:

We solved the issue regarding the driver but we’re still struggling to solve the impossibility of using cfqueryparams in jsonb functions :frowning:

If I think about it, from your queryparam I can’t see any sql type definition in the error, thus your cfqueryparam is using type “char” as a default. My question: Why are you setting this cfqueryparam in double quotes? I always thought this is something the cfqueryparam does for you, or am I missing something? Besides, I wonder if the cfqueryparam sql type would even work correctly, because the data types are set inside json. I wonder if cfqueryparam would just sanitize correctly for these values.

What I would also do is, try logging those queries from your postgresql view, and see how its logging this select query. Then you’ll see in your sql error log how cfqueryparam is escaping your query.

Not sure if it’s related, but I have always used CF_SQL_OTHER as my sqltype with json fields and it worked just fine. You may give that a try.

1 Like

Unfortunately, the problem still persists even specifying the type you’ve suggested.

We actually used the variable both in double quotes and without. Doesn’t seem to change a thing anyway.

We’ll try logging the queries!

Do you have to use Postgresql Json Path to query your data? Sounds like indexing is a bit more difficult,
here’s a similar query in that works with a a queryparam but it’s not pretty.

gparams= [{value= url.id, sqltype= ‘varchar’}];

qry =
"SELECT *
FROM books
WHERE data->‘genres’ @> cast(quote_ident(cast(? as text))as jsonb) ";

dbo = queryExecute(qry, gparams);

dump (dbo);