Detecting and processing error and warning conditions in host language applications

Each host language provides a mechanism for handling diagnostic information:

SQLSTATE

The database manager sets SQLSTATE after each SQL statement (other than GET DIAGNOSTICS or a compound statement) is executed. Thus, application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE.

SQLSTATE provides application programs with common codes for common error conditions. Furthermore, SQLSTATE is designed so that application programs can test for specific errors or classes of errors. The scheme is the same for all database managers and is based on the ISO/ANSI SQL 2003 Core standard. A complete list of SQLSTATE classes and SQLSTATEs associated with each SQLCODE is supplied in the SQL Messages and Codes topic collection.

SQLCODE

The database manager sets SQLCODE after each SQL statement (other than GET DIAGNOSTICS or a compound statement) is executed. SQLCODE is set as follows:
  • If SQLCODE = 0 and SQLWARN0 is blank, execution was successful.
  • If SQLCODE = 100, no data was found. For example, a FETCH statement returned no data, because the cursor was positioned after the last row of the result table.
  • If SQLCODE > 0 and not = 100, execution was successful with a warning.
  • If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful with a warning.
  • If SQLCODE < 0, execution was not successful.

A complete listing of DB2® for i SQLCODEs and their corresponding SQLSTATEs is provided in the SQL Messages and Codes topic collection.