Chapter 21
Handling Errors
In This Chapter
Flagging error conditions
Branching to error-handling code
Determining the exact nature of an error
Finding out which DBMS generated an error condition
Wouldn't it be great if every application you wrote worked perfectly every time? Yeah, and it would also be really cool to win $314.9 million playing Powerball. 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.
For example, the WHENEVER directive enables you to take a predetermined action whenever a specified condition (if SQLSTATE has a non-zero value, for example) is met. 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.
SQLSTATE
SQLSTATE specifies a large number of anomalous conditions. SQLSTATE is a five-character string in which only the uppercase letters A through Z and the numerals 0 through 9 are valid characters. The five-character string is divided into two groups: a two-character class code and a three-character subclass code. The class code holds a status after the completion of an SQL statement. That status could indicate successful completion of the statement, or one of a number of major types of error conditions. The subclass code provides additional detail about this particular execution of the statement. Figure 21-1 illustrates the SQLSTATE layout.
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 implementors (the people who build database management systems) to define because the SQL specification can’t anticipate every condition that may come up in every implementation. However, implementors should use these nonstandard class codes as little as possible to avoid migration problems from one DBMS to another. Ideally, implementors should use the standard codes most of the time and the nonstandard codes only under the most unusual circumstances.
Figure 21-1: SQLSTATE of 00000 indicates successful completion of an SQL:statement.
I introduce SQLSTATE in Chapter 20, but here's a recap. A class code of 00 indicates successful completion. Class code 01 means that the statement executed successfully but produced a warning. Class code 02 indicates a no data condition. Any SQLSTATE class code other than 00, 01, or 02 indicates that the statement did not execute successfully.
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 determine which of several possible actions you should take.
To use SQLSTATE in a module language program (which I describe in Chapter 16), include a reference to it in your procedure definitions, as the following example shows:
PROCEDURE NUTRIENT
(SQLSTATE, :foodname CHAR (20), :calories SMALLINT,
:protein DECIMAL (5,1), :fat DECIMAL (5,1),
:carbo DECIMAL (5,1))
INSERT INTO FOODS
(FoodName, Calories, Protein, Fat, Carbohydrate)
VALUES
(:foodname, :calories, :protein, :fat, :carbo) ;
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 it looks something like this:
foodname = "Okra, boiled" ;
calories = 29 ;
protein = 2.0 ;
fat = 0.3 ;
carbo = 6.0 ;
NUTRIENT(state, foodname, calories, protein, fat, carbo) ;
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.
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 if 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.
The WHENEVER directive is actually a declaration and is therefore 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 some 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 GOTOerror_trap ;
The CONTINUE option is essentially a NO-OP that says "ignore this."
Diagnostics Areas
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. That is, information on the most recent error can be found at the top of the stack, with info on older errors farther down in the list. 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 (hopefully all) errors.
The diagnostics area is a DBMS-managed data structure that has two components:
Header: The header contains general information about the most recent SQL statement that was executed.
Detail area: The detail area contains information about each code (error, warning, or success) that the statement generated.
Diagnostics header area
In the SET TRANSACTION statement (described in Chapter 15), you can specify DIAGNOSTICS SIZE. The SIZE that you specify is the number of detail areas allocated for status information. If you don't include a DIAGNOSTICS SIZE clause in your SET TRANSACTION statement, your DBMS assigns its default number of detail areas, whatever that happens to be.
The header area contains several items, as listed in Table 21-1.
Table 21-1 Diagnostics Header Area
|
Fields |
Data Type |
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
|
|
|
|
|
Exact numeric with no fractional part |
|
|
|
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
The following list describes these items in more detail:
The NUMBER field is the number of detail areas that have been filled with diagnostic information about the current exception.
The ROW_COUNT field holds the number of rows affected if the previous SQL statement was an INSERT, UPDATE, or DELETE.
The COMMAND_FUNCTION field describes the SQL statement that was just executed.
The COMMAND_FUNCTION_CODE field gives the code number for the SQL statement that was just executed. Every command function has an associated numeric code.
The DYNAMIC_FUNCTION field contains the dynamic SQL statement.
The DYNAMIC_FUNCTION_CODE field contains a numeric code corresponding to the dynamic SQL statement.
The MORE field may be either a 'Y' or an 'N'. 'Y' indicates that there are more status records than the detail area can hold. 'N' indicates that all the status records generated are present in the detail area. Depending on your implementation, you may be able to expand the number of records you can handle by using the SET TRANSACTION statement.
The TRANSACTIONS_COMMITTED field holds the number of transactions that have been committed.
The TRANSACTIONS_ROLLED_BACK field holds the number of transactions that have been rolled back.
The TRANSACTION_ACTIVE field holds a '1' if a transaction is currently active and a '0' otherwise. A transaction is deemed to be active if a cursor is open or if the DBMS is waiting for a deferred parameter.
Diagnostics detail area
The detail areas contain data on each individual error, warning, or success condition. Each detail area contains 28 items, as Table 21-2 shows.
Table 21-2 Diagnostics Detail Area
|
Fields |
Data Type |
|
|
Exact numeric with no fractional part |
|
|
|
|
|
|
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Exact numeric with no fractional part |
|
|
Exact numeric with no fractional part |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CONDITION_NUMBER holds the sequence number of the detail area. If a statement generates five status items that fill up five detail areas, the CONDITION_NUMBER for the fifth detail area is 5. To retrieve a specific detail area for examination, use a GET DIAGNOSTICS statement (described later in this chapter in the "Interpreting the information returned by SQLSTATE" section) with the desired CONDITION_NUMBER. RETURNED_SQLSTATE holds the SQLSTATE value that caused this detail area to be filled.
CLASS_ORIGIN tells you the source of the class code value returned in SQLSTATE. If the SQL standard defines the value, the CLASS_ORIGIN is 'ISO 9075'. If your DBMS implementation defines the value, CLASS_ORIGIN holds a string identifying the source of your DBMS. SUBCLASS_ORIGIN tells you the source of the subclass code value returned in SQLSTATE.
CLASS_ORIGIN is important. If you get an SQLSTATE of '22012', for example, the values indicate that it is in the range of standard SQLSTATEs, so you know that it means the same thing in all SQL implementations. However, if the SQLSTATE is '22500', the first two characters are in the standard range and indicate a data exception, but the last three characters are in the implementation-defined range. And if SQLSTATE is '90001', it's completely in the implementation-defined range. SQLSTATE values in the implementation-defined range can mean different things in different implementations, even though the code itself may be the same.
So how do you find out the detailed meaning of '22500' or the meaning of '90001'? You must look in the implementor's documentation. Which implementor? If you're using CONNECT, you may be connecting to various products. To determine which one produced the error condition, look at CLASS_ORIGIN and SUBCLASS_ORIGIN: They have values that identify each implementation. You can test the CLASS_ORIGIN and SUBCLASS_ORIGIN to see whether they identify implementors for which you have the SQLSTATE listings. The actual values placed in CLASS_ORIGIN and SUBCLASS_ORIGIN are implementor-defined, but they also are expected to be self-explanatory company names.
If the error reported is a constraint violation, the CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and CONSTRAINT_NAME identify the constraint being violated.
Constraint violation example
The constraint violation information is probably the most important information that GET DIAGNOSTICS provides. 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) ;
And 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),
...) ;
Now 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 discover that this means that the statement is committing an "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 is wrong with that INSERT. But you can find out what you need to know by using GET DIAGNOSTICS as follows:
DECLARE ConstNameVarCHAR(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 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 in the case where ALTER TABLE is used to add constraints that didn't exist when you wrote the program:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT SalLimitCHECK(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 $200,000. You can program your INSERT statement so that, if you get an SQLSTATE of '23000' and you 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 the information returned by SQLSTATE
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 explicitly define them. 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, use the following syntax:
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 21-2. 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 in one of the following ways:
Return control to the parent procedure that called the subprocedure that raised the exception.
Use a WHENEVER clause (as described earlier in this chapter) to branch to an exception-handling routine or perform some other action.
Handle the exception on the spot with a compound SQL statement (as described in Chapter 20). A compound SQL statement consists of one or more simple SQL statements, sandwiched between BEGIN and END keywords.
The following is an example of a compound-statement exception handler:
BEGIN
DECLARE ValueOutOfRange EXCEPTION FOR SQLSTATE'73003' ;
INSERT INTO FOODS
(Calories)
VALUES
(:cal) ;
SIGNAL ValueOutOfRange ;
MESSAGE 'Process a new calorie value.'
EXCEPTION
WHEN ValueOutOfRange THEN
MESSAGE 'Handling the calorie 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 might cause an exception to occur. If the value of :cal exceeds the maximum value for a SMALLINT data item, SQLSTATE is set to "73003". 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 a new calorie value' statement is executed. However, if an exception has occurred, that series of statements is skipped, and the EXCEPTION statement is executed.
If the exception was a ValueOutOfRange exception, then a series of statements represented by the MESSAGE 'Handling the calorie range error' statement is executed. The RESIGNAL statement is executed if the exception isn't a ValueOutOfRange exception.
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 value-out-of-range error.