What are all the possible values for SQLException.getSQLState?

Question

SQLException.getSQLState retrieves the SQLState for the SQLException object. What are all the possible values that can be returned by this method? Can I use the value to identify specific errors that ocured in the database (i.e. can this value tell me if it was a PK violation, or a unique constraint, or column value to large etc)?

Also, the DatabaseMetaData.getSQLStateType() method is supposed to indicate whether the SQLSTATE returned by SQLException.getSQLState is X/Open (now known as Open Group) SQL CLI or SQL99. The only possible value for this should be DatabaseMetaData.sqlStateXOpen == 1 and DatabaseMetaData.sqlStateSQL99 == 2 but I am getting the value 0. Am I missing something?

Is there a way that I can determine the specific type of error that occurred in the DB using combinations from the above mentioned methods? Can I count on the values of SQLException.getSQLState? Are these values different from DB provider to DB provider?

Answer 1

This is to some degree JDBC driver-dependent. There do seem to be standard values, plus some proprietary values.

As a guide to what's possible, Spring's JDBC layer includes SQL error code and state translation. It provides a SQLState translator which gives rather vague exception translation, as well as a SQLErrorCode translator which is much more fine-grained, using known proprietary error codes.

If you can't use Spring, then download the source code, and extract the sql-error-codes.xml file, which contains the mapping from codes to exception types.

Answer 2

Official documents that include SQLStates can obviously be purchased, at a relatively high price, from ANSI and XOpen. But, the documentation for most databases have lists of SQLStates. Probably the most complete ( and accessible ) online listings are in the DB2 manuals. Check the DB2 Universal Messages manual, for instance. Oracle ( TechNet password required ) and Sybase, among others, also have online listings.

As to the second question, this is the intent of SQLState, however, the various databases have varying degrees of compliance. For example, some map multiple native error messages to the same SQLState. For generic use, one should probably concentrate on the major code ( the first two characters of SQLState, ) then determine if more specific info is available in the minor code ( beyond 000. )

http://www.jguru.com/faq/view.jsp?EID=46397