From: | Tazhkenov Denis <imaskar(at)live(dot)ru> |
---|---|
To: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | JDBC driver setNull(_,Types.OTHER) bug |
Date: | 2015-09-16 10:54:50 |
Message-ID: | DUB128-W530EEFFF5B2450BD29CD9EAB5B0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
tl/dr:
select 'ok' where ?=? or (? is null)
stp.setObject(1, UUID.randomUUID(), Types.OTHER);
stp.setNull(2, Types.OTHER); // works
stp.setNull(3, Types.OTHER); // doesn't work
tl/dr end
Suppose, you use uuid columns in your table and need to compare a cloumn value with a parameter and alose to compare this parameter with null. In this case you have to use Types.OTHER to compare parameter with your column, but you can't use Types.OTHER whith (? is null) clause, because it issues error "can't detect parameter datatype for parameter$_" (not actual message, mine is localized).
Tested with versions:
driver
postgresql-9.4-1202.jdbc42
postgresql-9.4-1202.jdbc41
postgresql-9.3-1101.jdbc4db
postgres (PostgreSQL) 9.3.5
Test case:
http://ideone.com/W3sfhI
It does have some workarounds:
*if you know that parameter will be of uuid type, add cast to your clause;
-inconvinient, if your statements are auto-generated or if you can't change them (responsibility scope!);
-may slow down queries if used too broad;
*if at runtime you are setting uuid parameter to null, use Types.VARCHAR;
-didn't test, if it would correctly work when comparing with actual column, or writing there.
Maybe there is some pg-specific typeId for the uuid type?
Regards,
Denis Tazhkenov
From | Date | Subject | |
---|---|---|---|
Next Message | Userob | 2015-09-16 11:11:02 | Re: BUG #13621: Program paths are registered incorrectly. |
Previous Message | 林俊彥 | 2015-09-16 10:27:46 | [Bug / Question ] " authentication_timeout " is invalid. |