[jboss-user] [EJB 3.0] - JPQL, Postgres and nulls

[ghinkle]

I am wondering if anyone has been able to effectively use null jpql parameter bindings with postgres version 8+.

The story is this. I have columns that are nullable. I want to search by them finding nulls if i pass in a null parameter or where they match if i pass in a non-null parameter. Now JPQL by spec has gone with SQL-92 style null comparisons so null != null, but null is null. This means to do above I either a) need two queries (one for a null param, one for non-null) and or b) I need to put them together.

a) Need to use an if statement to choose the query if param is null
SELECT a from Apple a WHERE a.col is null
SELECT a from Apple a WHERE a.col = :parameter

b) One query, but fails on pg8+
SELECT a from Apple a 
WHERE (:parameter is null and a.col is null) or a = :parameter


Now here's where the postgres problem comes in. With PG 8+ they prepare the statements on the server side for real and bind each parameter. Prior to pg8 they just stuffed string versions of bind parameters into the query string, leaving the server to figure out the parameter type.

The problem is of course that even if PreparedStatement.setNull is being called it wouldn't know what type to pass in for my query. Postgres blows up with a "operator does not exist: integer = bytea".

Finally, I can get this working with protocolVersion=2, but that is inefficient and screws up other things that I need to work. Option a) is also not a good option when you have multiple nullable columns in a query (you'd need all combinations). Anybody have any ideas?

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4005610#4005610

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4005610