Detecting and processing error and warning conditions in host language applications

Each host language provides a mechanism for handling diagnostic information:

  • In C, COBOL, and PL/I, an application program containing executable SQL statements must provide at least one of the following:
    • A structure named SQLCA.
    • A stand-alone CHAR(5) (CHAR(6) in C) variable named SQLSTATE.
    • A stand-alone integer variable named SQLCODE.
    A stand-alone SQLSTATE or SQLCODE must not be declared in a host structure. Both a stand-alone SQLSTATE and SQLCODE may be provided.

    An SQLCA can be obtained by using the INCLUDE SQLCA statement. If an SQLCA is provided, neither a stand-alone SQLSTATE or SQLCODE can be provided. The SQLCA includes a character-string variable named SQLSTATE and an integer variable named SQLCODE.

    A stand-alone SQLSTATE should be used to conform with the SQL 2003 Core standard.

  • In Java™, for error conditions, the getSQLState method can be used to get the SQLSTATE and the getErrorCode method can be used to get the SQLCODE.
  • In REXX and RPG, an SQLCA is provided automatically.

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.