DB2 JDBC Driver hate my Date!

It's been a long time since I worked on a project that uses DB2 for the database. I always preferred DB2 to Oracle because it consume less resources (and it's much easier to install) on a developer machine.

But today I hit a strange issue...

I need to create a query that has a condition on a DATE column in some database. So my Java method that needs to do the query, I receive a java.util.Date argument. This method uses Spring-Framework Hibernate templates to do the query. Basically my query looks like this :

getHibernateTemplate().find("from Stuff s where s.effectiveDate <= ?", today)

I run a unit test that uses HSQLDB and every thing is fine.

But when I ran the same query in the application running on top of DB2, I got a nice SQLException from the DB2 JDBC Driver. The cause of the exception is :

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 3

The specified SQLSTATE says that this has something to do with data conversion. Huh! I have a DATE column and I pass a Date object! That should be fine (actually it is fine with MySQL, Oracle, HSQLDB and probably many other).

After a googling a bit, I found out that DB2 (the JDBC Driver) expect to receive either a java.sql.Date or a java.sql.Timestamp (depending on the type of the column). So in my case, I had to pass a java.sql.Date.

The fix is simple, I just have to create a java.sql.Date when doing the request :

getHibernateTemplate().find("from Stuff s where s.effectiveDate <= ?", new java.sql.Date(today.getTime()))

In my opinion, this is insane! It is the JDBC driver job to convert the passed data-type to something the database can handle.