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 occurred in the database (i.e. can this value tell me if it was a PK violation, or a unique constraint, or column value too 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?
Advertisement
Answer
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. )