• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Determine The State Of The Error

    September 13, 2016 Ted Holt

    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.

     

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    System i Developer:  RPG & DB2 Summit - October 4-6 2016 in Chicago. Register now! BCD:  Webinar: What's Possible with PHP on IBM i. Tues., Sept. 27 at 1pm ET. Sign up now! Manta Technologies Inc.:  The Leader in IBM i Education! Download catalog and take sample sessions!

    Howdy, Partner. Welcome To The IBM i Modernization Ranch New OpenPower Servers Present Interesting IBM i Possibilities

    Leave a Reply Cancel reply

Volume 16, Number 20 -- September 13, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
UCG Technologies

Table of Contents

  • How Do You Do That With RDi? Part 1: Copy A Source Member
  • Determine The State Of The Error
  • Generate SELECT For All Columns

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle