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 https://jdbc.postgresql.org/download.html
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.
@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.
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
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.
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.
