Error: "The conversion from UNKNOWN to UNKNOWN is unsupported." when using pQuery.setObject() with a java.util.Date

Question

I am trying to query or update a database using a parameterized query.  Below is a mocked up example

var todaysDate = new java.util.Date()

var pQuery = qie.getParameterizedQuery("SELECT givenDate FROM MediTable WHERE recordUpdated = :dateOfLastUpdate");

pQuery.setObject("dateOfLastUpdate", todaysDate);

pQuery.doQuery();

The above code is causing this error when the doQuery() is executed.

com.qvera.qie.exception.NoResultsException: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

I was hoping to use setObject in another query to dynamically set fields of various datatypes but it breaks when I pass in a date.  Is there a way to fix this?

 

 

Answer 1

The work around is to either use setDate() or use setObject() with a java.sql.Date instead.  The later would allow you to dynamically pass a date.  Note: you might need java.sql.Time or java.sql.Timestamp depending database fields type.  For example the above code could be modified like so to first convert the java.util.Date to a java.sql.Date:

var todaysDate = new java.sql.Date(new java.util.Date().getTime());

var pQuery = qie.getParameterizedQuery("SELECT givenDate FROM MediTable WHERE recordUpdated = :dateOfLastUpdate");

pQuery.setObject("dateOfLastUpdate", todaysDate);

pQuery.doQuery();

The error is specific to the JDBC driver that is used and the implementation setObject in that driver.  The javadoc for the prepared statement reads "The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database."  In the case of dates there are is some additional confusion because there is the java.util.Date and java.sql.Date.  The mssql driver maps the java.util.Date to UNKOWN and throws this error.