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.