Guru: Error Handling in SQL PL, Part 1

June 12, 2017 Ted Holt

I once fancied myself a logical thinker. I changed my mind when I started programming computers. I quickly realized that I was incapable of writing an error-free program. Chalk up another valuable lesson to experience. More experience taught me to program for both expected and unexpected conditions, and now I apply that concept to all languages that I use, including SQL PL.

SQL PL has excellent exception-handling methods, and they’re not hard to use. In this article and Part 2 to follow, we look at how DB2 informs you that your SQL request worked correctly or not. Next, we’ll take a look at conditions, the various statuses a procedure might assume. After conditions, we’ll talk about condition handlers, which give you a way to make a routine behave the way you want it to when something goes awry. Last, we’ll talk about ways that you can force conditions in order to achieve your purposes.

Notification

DB2 is like Napoleon Bonaparte, who said, “When your enemy is doing something wrong, do not interrupt him.” If an operation does not complete successfully, DB2 will not interrupt program execution. What it will do is load two variables – the SQL state and the SQL code – with values that you can test.

SQL PL does not define these variables for you. If you want to use them, you have to declare them:

declare SQLState      char(5);
declare SQLCode       integer;

While you can test either variable, SQL state is the more useful of the two, as you can refer to SQL state, but not SQL code, in named conditions and condition handlers. If you have been using SQL code in other settings, such as RPG programs, I recommend you make the switch to SQL state.

The first two characters of SQL state are known as the SQL state class. Their value indicates a general category into which SQL state ID’s fall.

• 00: Successful completion
• 01: Successful completion with warning(s)
• 02: No data
• Other: Error

Conditions

I couldn’t find a definition of a condition, so here’s my attempt: a condition is a state that exists when DB2 has issued a warning or exception in response to a database operation. Conditions are of two types: general and specific.

A general condition covers a category of database responses. They are three in number:

• SQLEXCEPTION: a major error occurred. The SQL state class is other than ‘00’, ‘01’, or ‘02’.
• SQLWARNING: DB2 detected an irregularity, but nothing that warranted canceling the operation. The SQL state class is ‘01’.
• NOT FOUND: DB2 found no data that matched a search. The SQL state class is ‘02’.

A specific condition applies to one SQL state value only. For example, DB2 sets the SQL state variable to 42710 when CREATE TABLE tries to create a table that already exists.

Condition Names

SQL PL allows you to assign descriptive names to specific conditions. The following statement, in three equivalent forms, gives the name CreateFailed to SQL state 42710.

declare CreateFailed condition for sqlstate value '42710';
declare CreateFailed condition for sqlstate '42710';
declare CreateFailed condition for '42710';

Condition Handlers

A condition handler is a routine that the system executes in response to a warning or exception condition. You can assign one handler to more than one condition. The handler executes one statement, which may be a compound statement.

There are three types of handlers, distinguished by what happens after the handler runs.

• A Continue handler returns to the statement following the one that caused the condition.
• Exit handlers leave the compound statement.
• Undo handlers are like exit handlers, but differ in that they rollback uncommitted database changes before leaving the compound statement. Undo handlers are only permitted in atomic compound statements.

Here’s an example.

create or replace procedure CreatePlants                
   set option dbgview = *source,                        
              commit  = *none                           
                                                        
begin                                                   
   declare v_Error       char(1)      default '0';      
   declare v_ErrorCount  integer      default 0;        
                                                        
   declare Table_Exists  condition for sqlstate '42710';
                                                        
   declare continue handler for Table_Exists            
      begin                                             
         set v_Error = '1';                             
         set v_ErrorCount = v_ErrorCount + 1;           
      end;                                              
                                                        
   create table plants                                  
   ( ID dec(3),  Location  varchar(16),                 
   primary key (ID));     
   label on table plants is 'Plant master';
                                        
   . . .  more code . . .

end

If table PLANTS already exists, the CREATE TABLE command fails with a SQL state of 42710. The continue handler assigns the value ‘1’ to v_Error and increments v_ErrorCount. Since this is a continue handler, control passes to the LABEL statement following CREATE TABLE.

But what if the existence of PLANTS means something’s wrong? In that case, you might use an exit handler.

create or replace procedure CreatePlants
   set option dbgview = *source,
              commit  = *none

begin
   declare Table_Exists  condition for sqlstate '42710';

   declare exit handler for Table_Exists
      resignal;

   create table plants
   ( ID dec(3),  Location  varchar(16),
   primary key (ID));

   label on table plants is 'Plant master';

      . . .  more code . . .

end

If the PLANTS table already exists, DB2 responds with SQL code 42710, which has the condition name Table_Exists. There is an exit handler for that condition, which causes control to leave the compound statement, which is the main statement of the stored procedure, which means that control leaves the stored procedure and returns to the caller. The exit handler has only one statement – RESIGNAL. What’s that? That’s a topic for part 2.

RELATED STORY

Determine The State Of The Error