Wednesday, May 22, 2013

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter

I was getting this error from Jasper server when leaving datetime input control blank:


Error Message

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3

Error Trace

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at 

Where clause in the query for java.sql.Timestamp column was like this:
and (time_col > $P{inputStartCreatedDate} or $P{inputStartCreatedDate} is null)

The solution is to add explicit type conversion like this:
and (time_col > $P{inputStartCreatedDate} or $P{inputStartCreatedDate}::timestamp is null)