CODEWITHSUNDEEP
javaspringjdbcerror-handlingdb2
Juha Syrjälä
Juha Syrjälä

Reputation: 34271

How to make JDBC SQLExceptions for DB2 more descriptive?

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.

Upvotes: 15

Views: 10225

Answers (3)

andy
andy

Reputation: 1105

Set the JDBC driver property retrieveMessagesFromServerOnGetMessage to true. Example connection url:

jdbc:db2://host:50128/MYDB:retrieveMessagesFromServerOnGetMessage=true;

See also DB2 11.1 Documentation

Upvotes: 24

skaffman
skaffman

Reputation: 403501

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.)

Upvotes: 3

Aaron Digulla
Aaron Digulla

Reputation: 328624

  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.

Upvotes: 11

Related Questions