JDBC Driver - how to pass null datetime2 value as parameter to native query?

Question

After updating from JTDS to MSSQL JDBC driver (version 10.2.0.jre8 - hibernate 4.3.11.Final) I'm encountering the following error when trying to pass a null value within a datetime2 parameter used within a native query:

Root Exception stack trace: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

In our app we're using a hell of a lot of native queries (lots of which invoke functions with parameters) and we use the following way of providing values for the parameters (example query):

String myQueryString = "SELECT * FROM dbo.MY_STORED_FUNC( :str1, :date1, :date2)";
Query query = getEntityManager().createNativeQuery(myQueryString);

query.setParameter("str1", "stringValue")
     .setParameter("date1", date1)
     .setParameter("date2", date2)
     .getResultList();

The problem now is that when date1 and/or date2 are null, the driver can't seem to handle this (which is quite suprising seeing an older driver handled this without issues :/ ). It seems to be unable to infer the type of the parameter since the value is null, and somehow treats this as varbinary. The db allows null's for the parameters - we can invoke the function with passing null and all works fine - as long as we don't do it with the JDBC driver :)

I've been searching through this and found some potential solutions:

Do the nullchecking before passing the parameter - in case of null pass empty string

This seems quite 'ugly', but in general it works:

.setParameter("date1", date1 == null ? "" : date1)

The problem I have with this solution is that it provides a fix for that specific query invocation - but our app is riddled with this type of queries with potential null datetime2 parameters and it's almost impossible to go over all of them. Same goes for switching to JPA repo's - I need to find a generic solution that will work globally for the app without the need to adjust queries or specific setParameter() invocations.

I also found a suggestion to use the TemporalType within the setParameter() method to tell the driver exactly which type should be used - this however didn't work at all. When using the following code I keep getting the following error - meaning it didn't do anything for the null handling: (Doesn't matter which temporalType we use - it's the same for all)

.setParameter("to", to, TemporalType.DATE)

Unexpected type [null] passed with TemporalType; expecting Date or Calendar

Using a typed parameter directly I also found this solution but again - results in the same error :/

Parameter<Date> paramDate1 = query.getParameter("date1", Date.class);
Parameter<Date> paramDate2 = query.getParameter("date2", Date.class);

...

.setParameter(paramFrom, from)
.setParameter(paramTo, to)

Error:

Root Exception stack trace: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

Since we needed to use a custom dialect anyways to fix the NVARCHAR (-9) JDBC mapping, I was hoping I could maybe do it through here - but hardcore things like even forcing a VARBINARY to STRING/CHARACTER mapping didn't work and I'm out of ideas...

public class MSSQLDialect extends SQLServer2012Dialect {

    public MSSQLDialect() {
        super();
        registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 1, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 255, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());
        registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName());
        registerHibernateType(Types.VARBINARY, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NULL, StandardBasicTypes.STRING.getName());
    }
}

How is this not working in newer drivers but is handled well in older JTDS?

JDBC Driver - how to pass null datetime2 value as parameter to native query?

0 Answers0