SQLSTATEs for ODBC error reporting

DB2® ODBC provides a standard set of codes called SQLSTATEs because different database servers often have different diagnostic message codes. Certain guidelines apply to the use of SQLSTATEs within applications.

SQLSTATEs are defined by the X/Open SQL CAE specification. This allows consistent message handling across different database servers.

SQLSTATEs are alphanumeric strings of five characters (bytes) with a format of ccsss, where cc indicates class and sss indicates subclass. All SQLSTATEs use one of the following classes:
'01'
A warning.
'S1'
Generated by the DB2 ODBC driver for ODBC 2.0 applications.
'HY'
Which is generated by the DB2 ODBC driver for ODBC 3.0 applications.
Important: In ODBC 3.0, 'HY' classes map to 'S1' classes. 'HY' is a reserved X/Open class for ODBC/CLI implementations. This class replaces the 'S1' class in ODBC 3.0 to follow the X/Open and ISO CLI standard.

For some error conditions, DB2 ODBC returns SQLSTATEs that differ from those states listed in Microsoft open database connectivity (ODBC). This inconsistency is a result of DB2 ODBC following the X/Open SQL CAE and SQL92 specifications.

DB2 ODBC SQLSTATEs include both additional IBM®-defined SQLSTATEs that are returned by the database server, and DB2 ODBC-defined SQLSTATEs for conditions that are not defined in the X/Open specification. This allows for the maximum amount of diagnostic information to be returned.

Follow these guidelines for using SQLSTATEs within your application:
  • Always check the function return code before calling SQLGetDiagRec() to determine if diagnostic information is available.
  • Use the SQLSTATEs rather than the native error code.
  • To increase your application's portability, only build dependencies on the subset of DB2 ODBC SQLSTATEs that are defined by the X/Open specification, and return the additional ones as information only. (Dependencies refer to the application that makes logic flow decisions based on specific SQLSTATEs.)
    Tip: Consider building dependencies on the class (the first two characters) of the SQLSTATEs.
  • For maximum diagnostic information, return the text message along with the SQLSTATE (if applicable, the text message also includes the IBM-defined SQLSTATE). It is also useful for the application to print out the name of the function that returned the error.