Handling SQL error return codes using the SQLCA
When an SQL statement is processed in your program, SQL places a return code in the SQLCODE and SQLSTATE fields. The return codes indicate the success or failure of the running of your statement.
If SQL encounters an error while processing the statement,
the SQLCODE is a negative number and SUBSTR(SQLSTATE,1,2) is not '00',
'01', or '02'. If SQL encounters an exception but valid condition
while processing your statement, the SQLCODE is a positive number
and SUBSTR(SQLSTATE,1,2) is '01' or '02'. If your SQL statement is
processed without encountering an error or warning condition, the
SQLCODE is zero and the SQLSTATE is '00000'.
Note: There are situations
when a zero SQLCODE is returned to your program and the result might
not be satisfactory. For example, if a value was truncated as a result
of running your program, the SQLCODE returned to your program is zero.
However, one of the SQL warning flags (SQLWARN1) indicates truncation.
In this case, the SQLSTATE is not '00000'.
Note: If you do not test for negative SQLCODEs
or specify a WHENEVER SQLERROR statement, your program will continue
to the next statement. Continuing to run after an error can produce
unpredictable results.
The main purpose for SQLSTATE is to provide common return codes for common return conditions among the different IBM® relational database systems. SQLSTATEs are particularly useful when handling problems with distributed database operations.
Because the SQLCA is a valuable problem-diagnosis tool, it is a good idea to include in your application programs the instructions necessary to display some of the information contained in the SQLCA. Especially important are the following SQLCA fields:
- SQLCODE
- Return code.
- SQLSTATE
- Return code.
- SQLERRD(3)
- The number of rows updated, inserted, or deleted by SQL.
- SQLWARN0
- If set to W, at least one of the SQL warning flags (SQLWARN1 through SQLWARNA) is set.