Incorrect parameter preparation with lists
Closed this issue · 4 comments
When using a list parameter with any parameters following it, it gets translated into an incorrect parameter list that throws an exception under PostgreSQL.
For example:
session.update( "UPDATE sometable SET array_column = ARRAY [:arrayValues] WHERE id = :id", mapOf("arrayValues" to listOf(1), "id" to "2") )
This generates a length 9 parameter list. The first parameter will be "1", the second parameter will be "2", the 9th parameter will be null and thus trigger PostgreSQL to throw org.postgresql.util.PSQLException: No value specified for parameter 9. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257).
(Also, why are parameter slots for collections rounded up to a power of 2?)
Hi,
If you're passing an array in a prepared statement you have to pass java.sql.Array as the parameter, not an arbitrary collection.
e.g. session.connection.createArrayOf(JDBCType.INTEGER, *ids)
And depending on your JDBC driver, you might need to take care to call free() on the created array.
This kind of complication is why there's no implicit support in the core module.
(Also, why are parameter slots for collections rounded up to a power of 2?)
This attempts to prevent the PreparedStatement cache from being filled with a bunch of queries that only vary in the number of parameters passed.
The better solution is the pass an array if your database supports it (like you're trying to do). Then there's only one query in the cache regardless of the number of parameters passed.
Cheer,
Andrew
Fair enough. I'm using proper arrays now and don't run into this issue, although note it shouldn't be specific to using arrays. For example, with in-clauses:
sql.select("SELECT * FROM users WHERE uid IN (:uids) AND enabled = :enabled", mapOf("uids" to listOf(1,2,3), "enabled" to false), mapper = {})
Exception in thread "main" org.postgresql.util.PSQLException: No value specified for parameter 9. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:284) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
If you switch around the parameter order so "enabled = :enabled" comes before the in-clause, it works. I'll keep this issue open in case you want to fix that, but feel free to close if it doesn't concern you.
That certainly looks like a bug - I'll look into it
Fixed in 0.17