Basic Error Checking: SQLCODE and SQLSTATE

A stored procedure can be written without any error checking at all. In this case, if an error is encountered, execution stops and the procedure terminates. In order to write SQL procedures responsibly, you need to learn how errors can be best handled within procedures.

SQLSTATE and SQLCODE are two values within DB2's communications area that are populated each time an SQL statement is executed. These values can be accessed within a stored procedure to determine the state, such as success or failure, of the previously executed statement. They are identifiers that can be used to get more detailed information about the state of the statement.

SQLSTATE is a five-digit numeric string that conforms to the ISO/ANSI SQL92 standard. This is a code that is common across the DB2 family of products. For example, if you were trying to drop a table that did not exist, you would get an SQLSTATE 42704, which would be the same SQLSTATE regardless of which product in the DB2 family the statement was issued against. The first two characters of SQLSTATE are known as the SQLSTATE class code. A class code of 00 means successful completion. Thus, an SQLSTATE beginning with 00 implies a successful completion of the previous statement. Similarly, a class code of 01 implies a warning, and a class code of 02 implies a "not found" condition. All other class codes are considered errors. Messages associated with SQLSTATES are general in nature. For example, the message associated with SQLSTATE 42704 is, 'An undefined object or constraint name was detected'.

SQLCODE is an integer status code. It is a database vendor-specific code. Some SQLCODES are common across database vendors, such as +100 (which means NOT FOUND), but the vast majority of codes do not overlap across the various database products. You can follow some simple rules when working with SQLCODES. A value of 0 for SQLCODE means that the statement executed successfully, a positive value implies successful completion but with a warning message, and a negative value indicates an error. Messages associated with an SQLCODE are specific in nature. By using the same example of dropping a table that does not exist, the SQLCODE returned is -204 and the associated message is '<name> is an undefined name', where <name> would be the name of the table.

So how are SQLSTATE and SQLCODE used in a DB2 SQL procedure? Each statement in an SQL procedure is an SQL statement. Therefore, after the execution of each statement, the value of SQLCODE and SQLSTATE are implicitly set. To access these values, you need to explicitly declare the variables SQLCODE and SQLSTATE.

Tip

SQLCODE and SQLSTATE are reserved variable names and can only be declared at the outermost scope of a procedure, meaning they can only be declared at the start of the procedure.

SQLCODE and SQLSTATE can only be accessed in the first statement after a statement has been executed, because they are set after every statement execution.

To make your code more portable, use SQLSTATE for exception handling instead of SQLCODE. In addition to being standard across the DB2 family of products, a large proportion of SQLSTATES are also standard across database vendors.

It's probably a good time to look at some code snippets to elaborate on what has been presented so far. Consider the example in Figure 6.1.

Figure 6.1. An incorrect example of simple error checking.

 CREATE PROCEDURE simple_error1 ( IN p_midinit CHAR                                 ,IN p_empno   CHAR(6) )     LANGUAGE SQL     SPECIFIC simple_error1                      -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                       -- applies to zSeries se: BEGIN    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    DECLARE SQLCODE INT DEFAULT 0;    UPDATE employee                                       -- (1)       SET midinit = p_midinit     WHERE empno = p_empno;    IF SUBSTR(SQLSTATE,1,2) NOT IN ('00','01','02') THEN  -- (2)        . . . ;    END IF; END se

The IF statement on Line (2) will evaluate to true if an error condition exists. Recall that SQLSTATES not beginning with 00, 01, or 02 are considered errors. We could have also checked for SQLCODE being less than zero.

At first glance, you may think that this is a reasonable example of exception handling but it will not perform as expected. If the UPDATE statement on Line (1) is successful, the procedure will run as expected. If the UPDATE statement is unsuccessful, however, it will never reach the IF on Line (2). Remember that whenever an error is encountered, the stored procedure terminates and control is transferred to the calling application.

So, is this a Catch-22? Not really. This is where we introduce the concept of condition handlersthe subject of the next section.