Determine The State Of The Error

The SQL Procedures Language (SQL PL) has an effective error-handling mechanism–condition handlers. When a statement returns a certain SQL state or a certain type of exception, the condition handler takes control. However, determining which SQL state to test for can be problematic. Here are two ways.

Let’s start with a simple stored procedure.

Create or replace procedure CreatePlants
begin
   create table plants
   ( ID dec(3),  Location  varchar(16),
   primary key (ID));

   label on table plants is 'Plant master';

   insert into plants values
   (  1,  'Lost Angeles'),
   (  2,  'New Yolk'),
   (  3,  'Last Vegas');
end

What could go wrong? Well, the plants table might already exist. If so, the job log has message SQL0601 (PLANTS in MYLIB type *FILE already exists.).

Let’s say that if the plants table already exists, you’ll ignore the error and continue with the next statement. However, to do so requires you to know the SQL state. Here’s one way to determine which SQL state to trap:

create or replace procedure CreatePlants
begin
   declare sqlstate   char(5)       default '00000';
   declare ErrorMsg   varchar(96);

   declare exit handler for sqlexception
      begin
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;
      end;

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

   label on table plants is 'Plant master';

   insert into plants values
   (  1,  'Lost Angeles'),
   (  2,  'New Yolk'),
   (  3,  'Last Vegas');
end

The exit handler traps any fatal error, builds a message that includes the SQL state, and cancels the stored procedure with SQL state 99001. The job log has message SQL0438. (Message SQLSTATE=42710 returned from SIGNAL, RESIGNAL, or RAISE_ERROR.)

I am indebted to Scott Forstie of IBM for sharing another method with me. A few months ago, he pointed out that the Run SQL Scripts tool, which I run as part of IBM i Access Client Solutions, displays the SQL state when a command ends in error. You can run the entire procedure or just the statement in question, as appropriate. This screen shot shows what I’m talking about.

Now that you know that the failed CREATE TABLE raises SQL state 42710, you can create a continue handler.

create or replace procedure CreatePlants
begin

   declare Table_Exists condition for sqlstate '42710';

   declare continue handler for Table_Exists
      begin end;

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

   label on table plants is 'Plant master';

   insert into plants values
   (  1,  'Lost Angeles'),
   (  2,  'New Yolk'),
   (  3,  'Last Vegas');
end

The continue handler receives control when CREATE TABLE fails, does nothing about the error, and execution continues with the LABEL ON statement. (I have written about this technique before.)

I’m far from a wizard with SQL PL, but the more I use it, the better I get. If you’re wondering which language to learn next, you may want to consider SQL PL.

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.