11.28.2006

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.


4 comments:

Carl Pritchett said...

You can force the driver to do type conversion for you by setting the deferPerpares (a driver custom property) to false (Its true by default). The caveat is that this comes at a performance cost. See http://www.redbooks.ibm.com/redbooks/SG247068/wwhelp/wwhimpl/java/html/wwhelp.htm for more details. Not sure if this will work with Date, but it does with other types.

Anonymous said...

Thanks for your post. It helped
-Arijit

Anonymous said...

Nice info
Regards,
Jack
http://db2examples.googlepages.com/

Anonymous said...
This comment has been removed by a blog administrator.

AdSense Links