SQL Return Codes

An application program containing executable SQL statements must provide at least one of the following:

• A structure named SQLCA
• A stand-alone integer variable named SQLCODE
• A stand-alone CHAR(5) (CHAR(6) in C) variable named SQLSTATE

Both a stand-alone SQLCODE and SQLSTATE may be provided. If an SQLCA is provided, neither a stand-alone SQLCODE or SQLSTATE can be provided. A stand-alone SQLCODE or SQLSTATE must not be declared in a host structure.

An SQLCA is provided automatically in REXX and RPG. In other languages, an SQLCA can be obtained by using the INCLUDE SQLCA statement. INCLUDE SQLCA must not be used if a stand-alone SQLCODE or SQLSTATE is provided. The SQLCA includes an integer variable named SQLCODE (SQLCOD in RPG) and a character-string variable named SQLSTATE (SQLSTT in RPG).

The option of providing a stand-alone SQLSTATE instead of an SQLCA allows for conformance with the ISO/ANSI SQL standard. The option of providing a stand-alone SQLCODE instead of a stand-alone SQLSTATE is a deprecated feature in the ISO/ANSI SQL standard. If conformance with the ISO/ANSI SQL standard is desired, the stand-alone SQLSTATE should be used.

SQLCODE

Regardless of whether the application program provides an SQLCA or a stand-alone variable, SQLCODE is set by the database manager after each SQL statement is executed. DB2 UDB for AS/400 conforms 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 with a warning.
• If SQLCODE < 0, execution was not successful.

A complete listing of DB2 UDB for AS/400 SQLCODEs and their corresponding SQLSTATEs is provided in Appendix B of the SQL Programming Concepts book.

SQLSTATE

Regardless of whether the application program provides an SQLCA or a standalone variable, SQLSTATE is also set by the database manager after execution of each SQL statement. 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 proposed ISO/ANSI standard. A complete list of SQLSTATE classes and SQLSTATEs associated with each SQLCODE is supplied in Appendix B of the SQL Programming Concepts book.