How to Handle SQL Errors with SQLSTATE
SQLSTATE specifies a large number of anomalous conditions in SQL. 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.
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 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.
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, 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.