Why are you using absolute values for java.sql.Types instead of java.sql.TIMESTAMP.
Timestamp is a bit strange as we tend to let the backend intuit which type it is due to the fact that before java 9 there was no TIMESTAMP_WITH_TIMEZONE type in java
setNull(pos, DATE) works because we don't have that issue.
pstmt = con.prepareStatement("SELECT * FROM TEST_TABLE WHERE ? IS NULL OR ? = C_TIMESTAMP"); pstmt.setNull(1,Types.TIMESTAMP, "timestamp"); pstmt.setNull(2,Types.OTHER, "timestamp");
Now, I'm not able to set a null value for a timestamp.
My table:
CREATE TABLE TEST_TABLE (C_TIMESTAMP TIMESTAMP)
My query:
SELECT * FROM TEST_TABLE WHERE ? IS NULL OR ? = C_TIMESTAMP
Data type and type name for my column C_TIMESTAMP are respectively 93 and "timestamp" (checked with the metadata provided by the driver).
Using PreparedStatement.setNull(pos, 93) or PreparedStatement.setNull(pos, 93, "timestamp") for pos = 1, 2 does not work (could not determine data type of parameter).
Using PreparedStatement.setNull(pos, 91) does work!
Is it expected?
Rémi
Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg@fastcrypt.com> a écrit :
But this workaround is not really useful for me, because my application uses generated (dynamic) queries and targets multiple database types (not only PosgreSQL). So, when I know the targeted database, I do not know my parameter types anymore.
I'd probably perform the null test in Java and pass the true/false boolean result along to the query:
SELECT * FROM my_table WHERE ?::bool;
ps.setBoolean(1, tsvar = null); //my Java is rusty but you get the idea.
