Null and JPA2 Native Query in MSSQL

This post involves a slightly edge case scenario that I encountered a couple of months ago, so hopefully I get all the details right the first time.

Essentially, I had a JPA2 project using Hibernate 3.6.10 as the ORM. This project had a requirement of some native SQL being used for dynamic table creation, so to achieve this I would call Query q = em.createNativeQuery(sql); and the proceed to call q.setParameter(...). This worked fine for both setting columns to a value, and setting them to null, at least on H2 and MySQL. However, if you tried to set to null when using SQLServer you’d get the following:

java.sql.SQLException: Operand type clash: varbinary is incompatible with float
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
	at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
	at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:396)
	at org.hibernate.ejb.QueryImpl.internalExecuteUpdate(QueryImpl.java:188)
	at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:99)

It was very curious as to where the varbinary was coming from (float is the correct type for the column). What happens is this. In JPA2 there is just one call to set a parameter, q.setParameter(...), unlike in pure JDBC which has a specific setNull(position, type) method that allows you to specify the underlying column type. To get around this the JPA2 provider has to either know what the column types are, which is fine if you are using JPA mapped entities, or use a generic type. In the case of Hibernate it uses setParameter( position, val, Hibernate.SERIALIZABLE ). Serializable maps to varbinary and, even though the value is null, this can not be coerced to a float by SQLServer. See the conversions chart 1/3 way down this page.

For me, the solution was to unwarp the Hibernate Session object from the entity manager, and use Hibernate’s native query interface that allows you to specify the underlying column types.

SQLQuery q = em.unwrap(Session.class).createSQLQuery(sql);
q.setParameter(1, null, Hibernate.DOUBLE);

Unless there is some way to supply column type mappings to the native JPA2 provider that I haven’t found then I see this as a significant shortcoming with JPA2’s native query interface.