IN THIS CHAPTER

Identifying error conditions

Discovering SQLSTATE

Dealing with whatever comes your way

Using the WHENEVER clause

Checking the diagnostics areas

Seeing an example of a constraint violation

Putting more constraints on an existing table

Interpreting SQLSTATE information

Handling exceptions

Wouldn’t it be great if every application you wrote worked perfectly every time? Yeah, and it would also be really cool to win $210 million in the Powerball lottery. Unfortunately, both possibilities are equally unlikely to happen. Error conditions of one sort or another are inevitable, so it’s helpful to know what causes them.

SQL’s mechanism for returning error information to you is the status parameter (or host variable) SQLSTATE. Based on the contents of SQLSTATE, you can take different actions to remedy the error condition. The WHENEVER directive, for example, enables you to take a predetermined action whenever a specified condition is met — if SQLSTATE has a nonzero value, to take one example. You can also find detailed status information about the SQL statement that you just executed in the diagnostics area.

In this chapter, I explain these helpful error-handling facilities and how to use them. First, however, I show you the conditions that may cause those error-handling facilities to be invoked.

Identifying Error Conditions

When people say that a person has a condition, they usually mean that something is wrong with that person; he’s sick or injured. People usually don’t bother to mention that a person is in good condition; rather, we talk about people who are in serious condition or, even worse, in critical condition. This idea is similar to the way that programmers talk about the condition of an SQL statement. The execution of an SQL statement can lead to a successful result, to a questionable result, or to an outright erroneous result. Each of these possible results corresponds to a condition.

Getting to Know SQLSTATE

Every time an SQL statement executes, the database server places a value in the status parameter SQLSTATE. SQLSTATE is a 5-character field, accepting the 26 uppercase letters and the numerals 0 through 9. The value placed in SQLSTATE indicates whether the preceding SQL statement executed successfully. If it didn’t execute successfully, the value of SQLSTATE provides some information about the error.

The first two of the five characters of SQLSTATE (the class value) give you the major news about whether the preceding SQL statement executed successfully, returned a result that may or may not have been successful, or produced an error. Table 4-1 shows the four possible results.

The following list further explains the class values:

• 00: Indicates that the preceding SQL statement executed successfully. This is a very welcome result — most of the time.
• 01: Indicates a warning, meaning that something unusual happened during the execution of the SQL statement. This occurrence may or may not be an error; the database management system (DBMS) can’t tell. The warning is a heads-up to the developer, suggesting that perhaps she should check the preceding SQL statement carefully to ensure that it’s operating correctly.
• 02: Indicates that no data was returned as a result of the execution of the preceding SQL statement. This result may or may not be good news, depending on what the developer was trying to do with the statement. Sometimes an empty result set is exactly what the developer wanted the SQL statement to return.
• Any class code other than 00, 01, or 02: Indicates an error condition. An indication of the nature of the error appears in the three characters that hold the subclass value. The two characters of the class code, plus the three characters of the subclass code, together comprise the five characters of SQLSTATE.

The SQL standard defines any class code that starts with the letters A through H or the numerals 0 through 4; therefore, these class codes mean the same thing in any implementation. Class codes that start with the letters I through Z or the numerals 5 through 9 are left open for implementers (the people who build DBMSs) to define because the SQL specification can’t anticipate every condition that may occur in every implementation. Implementers should use these nonstandard class codes as little as possible, however, to prevent migration problems from one DBMS to another. Ideally, implementers should use the standard codes most of the time and the nonstandard codes only under the most unusual circumstances.

Because SQLSTATE updates after every SQL operation, you can check it after every statement executes. If SQLSTATE contains 00000 (successful completion), you can proceed with the next operation. If it contains anything else, you may want to branch out of the main line of your code to handle the situation. The specific class code and subclass code that an SQLSTATE contains determines which of several possible actions you should take.

To use SQLSTATE in a module language program, in which SQL statements are called from a module by a host program written in a procedural language such as C, include a reference to it in your procedure definitions, as in the following example:

PROCEDURE POWERPLANT

(SQLSTATE, :enginename CHAR (20), :displacement SMALLINT,

:hp INTEGER, :cylinders INTEGER, :valves INTEGER

INSERT INTO ENGINES

(EngineName, Displacement, Horsepower, Cylinders, Valves)

VALUES

(:enginename, :displacement, :hp, :cylinders, :valves) ;

At the appropriate spot in your procedural language program, you can make values available for the parameters (perhaps by soliciting them from the user) and then call up the procedure. The syntax of this operation varies from one language to another but looks something like this:

enginename = "289HP" ;

displacement = 289 ;

hp = 271 ;

cylinders = 8 ;

valves = 16 ;

POWERPLANT(state, enginename, displacement, hp, cylinders, valves);

The state of SQLSTATE is returned in the variable state. Your program can examine this variable and then take the appropriate action based on the variable’s contents.

Handling Conditions

You can have your program look at SQLSTATE after the execution of every SQL statement. The question then is what to do with the knowledge that you gain. Depending on the contents of SQLSTATE, you may want your program to branch to a procedure that handles the existing situation. Examples here would be along the lines of the following:

• If you find a class code of 00, you probably don’t want to do anything. You want execution to proceed as you originally planned.
• If you find a class code of 01 or 02, you may or may not want to take special action. If you expected a “warning” or “not found” indication, you probably want to let execution proceed normally. If you didn’t expect either of these class codes, you probably want to have execution branch to a procedure specifically designed to handle the unexpected, but not totally unanticipated, warning or not-found result.
• If you receive any other class code, something is wrong. You should branch to an exception-handling procedure. The specific procedure that you choose to branch to depends on the contents of the three subclass characters, as well as the two class characters of SQLSTATE. If multiple different exceptions are possible, there should be an exception-handling procedure for each one because different exceptions often require different responses. Some errors may be correctable, or you may find a workaround. Other errors may be fatal, calling for termination of the application.

Dealing with Execution Exceptions: The WHENEVER Clause

What’s the point of knowing that an SQL operation didn’t execute successfully if you can’t do anything about it? If an error occurs, you don’t want your application to continue executing as though everything is fine. You need to be able to acknowledge the error and do something to correct it. If you can’t correct the error, at the very least you want to inform the user of the problem and bring the application to a graceful termination.

The WHENEVER directive is the SQL mechanism for dealing with execution exceptions. WHENEVER is actually a declaration and, therefore, is located in your application’s SQL declaration section, before the executable SQL code. The syntax is as follows:

WHENEVER <condition> <action> ;

The condition may be either SQLERROR or NOT FOUND. The action may be either CONTINUE or GOTO address. SQLERROR is TRUE if SQLSTATE has a class code other than 00, 01, or 02. NOT FOUND is TRUE if SQLSTATE is 02000.

If the action is CONTINUE, nothing special happens, and the execution continues normally. If the action is GOTO address (or GO TO address), execution branches to the designated address in the program. At the branch address, you can put a conditional statement that examines SQLSTATE and takes different actions based on what it finds. Here are two examples of this scenario:

WHENEVER SQLERROR GO TO error_trap ;

or

WHENEVER NOT FOUND CONTINUE ;

The GO TO option is simply a macro. The implementation (that is, the embedded language precompiler) inserts the following test after every EXEC SQL statement:

IF SQLSTATE <> '00000'

AND SQLSTATE <> '00001'

AND SQLSTATE <> '00002'

THEN GOTO error_trap;

The CONTINUE option is essentially a NO-OP that says “ignore this.”

Getting More Information: The Diagnostics Area

Although SQLSTATE can give you some information about why a particular statement failed, the information is pretty brief, so SQL provides for the capture and retention of additional status information in diagnostics areas. Multiple diagnostics areas are maintained in the form of a last-in-first-out (LIFO) stack. Information on the most recent error appears at the top of the stack. The additional status information in a diagnostics area can be particularly helpful in cases in which the execution of a single SQL statement generates multiple warnings followed by an error. SQLSTATE reports the occurrence of only one error, but the diagnostics area has the capacity to report on multiple errors — ideally, all errors.

The diagnostics area is a DBMS-managed data structure that has two components:

• Header: The header contains general information about the last SQL statement executed.
• Detail area: The detail area contains information about each code (error, warning, or success) that the statement generated.

Examining an Example Constraint Violation

The constraint violation information is probably the most important information that GET DIAGNOSTICS provides. I discuss GET DIAGNOSTICS in “Interpreting SQLSTATE Information,” later in this chapter.

Consider the following EMPLOYEE table:

CREATE TABLE EMPLOYEE (

ID CHAR(5) CONSTRAINT EmpPK PRIMARY KEY,

Salary DEC(8,2) CONSTRAINT EmpSal CHECK Salary > 0,

Dept CHAR(5) CONSTRAINT EmpDept,

REFERENCES DEPARTMENT) ;

Now consider this DEPARTMENT table:

CREATE TABLE DEPARTMENT (

DeptNo CHAR(5),

Budget DEC(12,2) CONSTRAINT DeptBudget

CHECK(Budget >= SELECT SUM(Salary) FROM EMPLOYEE,

WHERE EMPLOYEE.Dept=DEPARTMENT.DeptNo),

…);

Consider an INSERT as follows:

INSERT INTO EMPLOYEE VALUES(:ID_VAR, :SAL_VAR, :DEPT_VAR);

Suppose that you get an SQLSTATE of 23000. You look it up in your SQL documentation, and it says “integrity constraint violation.” Now what? That SQLSTATE value means that one of the following situations is true:

• The value in ID_VAR is a duplicate of an existing ID value: You have violated the PRIMARY KEY constraint.
• The value in SAL_VAR is negative: You have violated the CHECK constraint on Salary.
• The value in DEPT_VAR isn’t a valid key value for any existing row of DEPARTMENT: You have violated the REFERENCES constraint on Dept.
• The value in SAL_VAR is large enough that the sum of the employees’ salaries in this department exceeds the budget: You have violated the CHECK constraint in the Budget column of DEPARTMENT. (Recall that if you change the database, all constraints that may be affected are checked, not just those defined in the immediate table.)

Under normal circumstances, you would need to do a great deal of testing to figure out what’s wrong with that INSERT, but you can find out what you need to know by using GET DIAGNOSTICS as follows:

DECLARE ConstNameVar CHAR(18) ;

GET DIAGNOSTICS EXCEPTION 1

ConstNameVar = CONSTRAINT_NAME ;

Assuming that SQLSTATE is 23000, this GET DIAGNOSTICS sets ConstNameVar to EmpPK, EmpSal, EmpDept, or DeptBudget. Notice that in practice, you may also want to obtain the CONSTRAINT_SCHEMA and CONSTRAINT_CATALOG to uniquely identify the constraint given by CONSTRAINT_NAME.

Adding Constraints to an Existing Table

This use of GET DIAGNOSTICS — determining which of several constraints has been violated — is particularly important when ALTER TABLE is used to add constraints that didn’t exist when you wrote the program, as in this example:

ALTER TABLE EMPLOYEE

ADD CONSTRAINT SalLimit CHECK(Salary < 200000) ;

Now if you insert data into EMPLOYEE or update the Salary column of EMPLOYEE, you get an SQLSTATE of 23000 if Salary exceeds 200000. You can program your INSERT statement so that if you get an SQLSTATE of 23000 and don’t recognize the particular constraint name that GET DIAGNOSTICS returns, you can display a helpful message, such as Invalid INSERT: Violated constraint SalLimit.

Interpreting SQLSTATE Information

CONNECTION_NAME and ENVIRONMENT_NAME identify the connection and environment to which you are connected at the time the SQL statement is executed.

If the report deals with a table operation, CATALOG_NAME, SCHEMA_NAME, and TABLE_NAME identify the table. COLUMN_NAME identifies the column within the table that caused the report to be made. If the situation involves a cursor, CURSOR_NAME gives its name.

Sometimes, a DBMS produces a string of natural-language text to explain a condition. The MESSAGE_TEXT item is for this kind of information. The contents of this item depend on the implementation; the SQL standard doesn’t define them explicitly. If you do have something in MESSAGE_TEXT, its length in characters is recorded in MESSAGE_LENGTH, and its length (in octets) is recorded in MESSAGE_OCTET_LENGTH. If the message is in normal ASCII characters, MESSAGE_LENGTH equals MESSAGE_OCTET_LENGTH. If, on the other hand, the message is in Kanji or some other language whose characters require more than an octet to express, MESSAGE_LENGTH differs from MESSAGE_OCTET_LENGTH.

To retrieve diagnostic information from a diagnostics area header, use the following:

GET DIAGNOSTICS status1 = item1 [, status2 = item2]… ;

statusn is a host variable or parameter; itemn can be any of the keywords NUMBER, MORE, COMMAND_FUNCTION, DYNAMIC_FUNCTION, or ROW_COUNT.

To retrieve diagnostic information from a diagnostics detail area, the syntax is as follows:

GET DIAGNOSTICS EXCEPTION <condition number>

status1 = item1 [, status2 = item2]… ;

Again, statusn is a host variable or parameter, and itemn is any of the 28 keywords for the detail items listed in Table 4-4 (refer to “The diagnostics detail area,” earlier in this chapter). The condition number is — surprise! — the detail area’s CONDITION_NUMBER item.

Handling Exceptions

When SQLSTATE indicates an exception condition by holding a value other than 00000, 00001, or 00002, you may want to handle the situation by taking one of the following actions:

• Returning control to the parent procedure that called the subprocedure that raised the exception.
• Using a WHENEVER clause to branch to an exception-handling routine or perform some other action.
• Handling the exception on the spot with a compound SQL statement. A compound SQL statement consists of one or more simple SQL statements, sandwiched between BEGIN and END keywords.

Following is an example of a compound-statement exception handler:

BEGIN

DECLARE ValueOutOfRange EXCEPTION FOR SQLSTATE '74001' ;

INSERT INTO ENGINES

(Displacement)

VALUES

(:displacement) ;

SIGNAL ValueOutOfRange ;

MESSAGE 'Process the next displacement value.'

EXCEPTION

WHEN ValueOutOfRange THEN

MESSAGE 'Handling the displacement range error' ;

WHEN OTHERS THEN

RESIGNAL ;

END

With one or more DECLARE statements, you can give names to specific SQLSTATE values that you suspect may arise. The INSERT statement is the one that may cause an exception to occur. If the value of :displacement exceeds the maximum value for a SMALLINT data item, SQLSTATE is set to 74001. The SIGNAL statement signals an exception condition. It clears the top diagnostics area. It sets the RETURNED_SQLSTATE field of the diagnostics area to the SQLSTATE for the named exception. If no exception has occurred, the series of statements represented by the MESSAGE 'Process the next displacement value' statement is executed. If an exception has occurred, however, that series of statements is skipped, and the EXCEPTION statement is executed.

If the exception was a ValueOutOfRange exception, the series of statements represented by the MESSAGE 'Handling the displacement range error' statement is executed. If any other exception occurred, the RESIGNAL statement is executed. RESIGNAL merely passes control of execution to the calling parent procedure. That procedure may have additional error-handling code to deal with exceptions other than the expected ValueOutOfRange error.