tl/dr:
select 'ok' where ?=3D? or (? is null)
stp.setObject(1=2C UUID.randomUUID()=2C Types.OTHER)=3B
stp.setNull(2=2C Types.OTHER)=3B // works
stp.setNull(3=2C Types.OTHER)=3B // doesn't work
tl/dr end
Suppose=2C 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 th=
is case you have to use Types.OTHER to compare parameter with your column=
=2C but you can't use Types.OTHER whith (? is null) clause=2C because it is=
sues error "can't detect parameter datatype for parameter$_" (not actual me=
ssage=2C mine is localized).
Tested with versions:
=0A=
=0A=
driver
postgresql-9.4-1202.jdbc42=0A=
=0A=
postgresql-9.4-1202.jdbc41=0A=
=0A=
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=2C add cast to your clause=
=3B
-inconvinient=2C if your statements are auto-generated or if you can't cha=
nge them (responsibility scope!)=3B
-may slow down queries if used too broad=3B
*if at runtime you are setting uuid parameter to null=2C use Types.VARCHAR=
=3B
-didn't test=2C if it would correctly work when comparing with actual colu=
mn=2C or writing there.
Maybe there is some pg-specific typeId for the uuid type?
Regards=2C
Denis Tazhkenov
=
