Detecting and processing error and warning conditions in host language applications

An application program containing executable SQL statements can use either SQLCODE or SQLSTATE values to handle return codes from SQL statements.

There are two ways in which an application can get access to these values.
  • Include a structure named SQLCA. The SQLCA includes an integer variable named SQLCODE and a character string variable named SQLSTATE. In REXX, an SQLCA is provided automatically. In other languages, an SQLCA can be obtained by using the INCLUDE SQLCA statement.
  • If LANGLEVEL SQL92E is specified as a precompile option, a variable named SQLCODE or SQLSTATE can be declared in the SQL declare section of the program. If neither of these variables is declared in the SQL declare section, it is assumed that a variable named SQLCODE is declared elsewhere in the program. With LANGLEVEL SQL92E, the program should not have an INCLUDE SQLCA statement.
An SQLCODE is set by the database manager after each SQL statement executes. All database managers conform to the ISO/ANSI SQL standard, 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, but one or more warning indicators were set.
  • If SQLCODE < 0, execution was not successful.

The meaning of SQLCODE values other than 0 and 100 is product-specific.

An SQLSTATE is set by the database manager after each SQL statement executes. Application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE. SQLSTATE provides common codes for common error conditions. Application programs can test for specific errors or classes of errors. The coding scheme is the same for all IBM® database managers, and is based on the ISO/ANSI SQL92 standard.