How to make JDBC SQLExceptions for DB2 more descriptive?

Question

How to make SQLExceptions thrown by DB2 JDBC driver more descriptive?

Currently I am getting this kind of exceptions. It is cumbersome to work with these cryptic SQLCODE and SQLSTATE numeric values. Is there a way where to make the SQL exception to contain code description.

Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -302, 
     SQLSTATE: 22001, SQLERRMC: null
      at com.ibm.db2.jcc.b.hh.c(hh.java:1662)
      at com.ibm.db2.jcc.b.hh.a(hh.java:1238)
      at com.ibm.db2.jcc.c.db.n(db.java:737) 
      ....

e.g. SQLSTATE 22001 has this description:

Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.

Edit: I am also using Spring and Hibernate frameworks.

Answer 1

Spring contains translators for SQLException which convert the database-specific codes and states into a description exception class hierarchy.

It's part of the larger Spring API, but there's nothing stopping you from using just that class.

---

For example, if you have a DAO which extends JdbcDaoSupport, then you can have code like this:

try {
   // ... some code that throws SQLException
} catch (SQLException ex) {
   throw getExceptionTranslator().translate(null, null, ex);
}

This translates and wraps the SQLException is Spring's own strongly typed exception hierarchy.

If you're not using JdbcDaoSupport, then you can use the getExceptionTranslator() method of JdbcTemplate (and if you're not using that, then look at the source to see how it works.)

Answer 2

1. Check that the exception implements com.ibm.db2.jcc.DB2Diagnosable

2. On a DB2Diagnosable, you can call getSqlca() to get a DB2Sqlca. It will return the SQL error code (getSqlCode()), the state (getSqlState()) and you can call getMessage() to get a properly formatted and readable error message.

There is probably a smart reason why IBM didn't map this to getMessage() of the exception. My guess is that they didn't because DB2Sqlca.getMessage() can throw nasty exceptions, so you must wrap it in try-catch.