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.