Improve classification of DB2 and Sybase exceptions by adding new error-state classifications [SPR-4873]

[spring-projects-issues]

Abhishek Gupta opened SPR-4873 and commented

Sub: Additional sql-state classification required for Sybase and DB2

We currently classify SQLExceptions as Transient or Non-Transient based on SQL-States we provide in a configuration file.
Following are the SQL-States for DB2 and Sybase that we use:

SQLStates for DB2,DB2UDB:

• 08001 Transient Exception No Connection (application requester could not connect to the target.)
• 40003 Transient Exception No Connection
• S1000 Transient Exception Communication Failure
• 57011 Transient Exception Out of virtual memory
• 57019 Transient Exception DB currently in use
• 40001 Transient Exception Deadlock detected
• 23505 Non-Transient Exception Duplicate Key

SQLStates for Sybase:

• JZ006 Transient Exception Unexpected I/O error
• JW0I0 Transient Exception Internal error with a timed I/O stream
• JZ0I1 Transient Exception I/O timeout
• JZ0I2 Transient Exception I/O timeout
• JZ0C0 Transient Exception Connecton already closed
• JZ0P1 Transient Exception Unexptected result type
• JZ0EM Transient Exception End of Data
• JZ0P4 Transient Exception Protocol Error
• 40001 Transient Exception Deadlock detected
• 23000 Non-Transient Data Integrity Violated

The descriptions for the above sybase sql-states can be found here: http://manuals.sybase.com/onlinebooks/group-jcarc/jcg0400e/jcrg/`@Generic__BookTextView`/7402

Not all of the above given error-states are supported in spring's SQLExceptionTranslator. It would be nice to have these available out of the box in spring, so we can completely replace our sql-state based error-handling with that of spring's.

---

Affects: 2.5.2, 2.5.3, 2.5.4

Issue Links:

• Adding hook into SQL State based exception translation [SPR-4899] #9575 Adding hook into SQL State based exception translation

[spring-projects-issues]

Abhishek Gupta commented

Hi,
The original Fix Version for this jira was 2.5.6.
I would be glad to help fix this issue and submit the code with the above error states if this can be done 2.5.6. This would really help us comepletly replace our existing sql-state based error-handling with the one provided by spring.

Thanks,
Abhi

[spring-projects-issues]

Juergen Hoeller commented

I've revised SQLStateSQLExceptionTranslator to return TransientDataAccessResourceException for those well-known vendor-specific SQL states. However, SQLStateSQLExceptionTranslator is still following its original design: classifying based on the SQL state "class" (the first two digits) in a Java-coded fashion. Also note that we are still returning DataAccessResourceFailureException for states that are arguably non-transient (such as 08*, 57* and also 58* on DB2).

We are considering extensible SQL state value matching through sql-error-codes.xml in Spring 3.0. There is already the option to switch "useSqlStateForTranslation" to "true" for a given database's SQLErrorCodes (see PostgreSQL and Derby). This will then match the specified codes against the SQL state instead of the SQLException vendor error code. However, this will then match *all *of the specified codes for that database against the SQL state. There is no way to match against error codes as well as SQL state values for the same database at present. This gap is what I'm keeping #9575 open for, provided that we find an actual need for such super-flexible two-level matching.

Juergen