• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Error Handling in SQL PL, Part 2

    October 16, 2017 Ted Holt

    In the previous episode of this exciting, action-packed series, I introduced you to the exception-handling methods that IBM has built into SQL PL. The cliff-hanger has lasted four months now, much too long. It’s high time I explained RESIGNAL, as I promised I would. I will explain SIGNAL while I’m at it.

    Before I start the syntax lesson, I need to explain a bit of philosophy. I use the hit-the-ball-drag-Harry method of exception-handling when I embed SQL in RPG and COBOL programs. That is, I execute a command, test the SQL state, execute a command, test the SQL state, execute a command . . . . Maybe you do too.

    SQL PL was designed with a different idea in mind. The idea is to set up condition handlers that take control if a certain condition or class of conditions arises. (If you’re not sure you understand condition handlers, it might be a good idea to re-read part 1 before continuing.) There is no need to check each statement for proper execution.

    That’s all well and good for errors that the database manager can detect, but what about errors that can only be tested with programming logic? This brings me to another idea that you may not be accustomed to. You can and should force conditions when the database manager cannot do so.

    I can illustrate this concept with a warehousing example. Consider a stored procedure (i.e. a program) that records the movement of inventory from one location to another. (If this code wraps in your browser, please copy it and paste it into a text editor. Even Notepad will do.)

    create or replace procedure MoveInventory
    
    /* =======================================================================
       Move Inventory
       SQL state values: 77001 - invalid item number
                         77002 - inventory is not in from-location
                         77003 - insufficient inventory in from-location
    ======================================================================= */
    
       ( in p_ItemNumber    char(6),
         in p_Quantity      dec (5),
         in p_FromLocation  char(6),
         in p_ToLocation    char(6) )
    
         
    begin atomic
       declare exit handler for sqlexception
          resignal;
          
       begin -- Be sure the item number is valid.
          declare  v_Message   varchar(48);
          if not exists (select ItemNumber from Items 
                          where ItemNumber = p_ItemNumber)
          then
             set v_Message = 'Invalid item number ' concat p_ItemNumber ;
             signal sqlstate '77001' set message_text = v_Message ;
          end if;
       end;
       
       -- Be sure the from-location exists and has sufficient inventory
       begin
          declare v_Quantity  dec(5);
          declare v_Message   varchar(64);
          declare exit handler for Not Found
             begin
                set v_Message = 'No inventory found for item ' concat p_ItemNumber concat
                                ' in location ' concat p_FromLocation; 
                signal sqlstate '77002' set message_text = v_Message;
             end;
          select Quantity into v_Quantity
            from ItemLocations
           where (ItemNumber, Location) = (p_ItemNumber, p_FromLocation);
          if v_Quantity < p_Quantity
          then
             set v_Message = 'Insufficient inventory found for item ' 
                             concat p_ItemNumber concat
                             ' in location ' concat p_FromLocation; 
             signal sqlstate '77003' set message_text = v_Message;
          end if;
       end;
       
       -- Subtract the quantity from the from-location
       update ItemLocations
          set Quantity = Quantity - p_Quantity
        where (ItemNumber, Location) = (p_ItemNumber, p_FromLocation);
       
       -- Add the quantity to the to-location
       merge into ItemLocations as tgt
          using (values (p_ItemNumber, p_Quantity, p_ToLocation))
                     as src (ItemNumber, Quantity, ToLocation)
             on (src.ItemNumber, src.ToLocation) = (tgt.ItemNumber, tgt.Location)
          when matched then
             update set tgt.Quantity = tgt.Quantity + src.Quantity
          when not matched then
             insert (ItemNumber, Quantity, Location)
                values (src.ItemNumber, src.Quantity, src.ToLocation);   
          
       -- Log the transaction
       insert into InventoryTransactionLog 
              (ItemNumber, Quantity, FromLocation, ToLocation)
          values (p_ItemNumber, p_Quantity, p_FromLocation, p_ToLocation);
          
    end;
    

    The caller sends in an item number, the quantity of that item to be moved, the location from which the item is being moved, and the location to which it is being moved.

       ( in p_ItemNumber    char(6),
         in p_Quantity      dec (5),
         in p_FromLocation  char(6),
         in p_ToLocation    char(6) )
    

    The body of the stored procedure is an atomic compound statement. This means that the entire procedure will be treated as one transaction. If any part fails, any changes that have been made to the database will be rolled back.

    The atomic compound statement includes one handler for all exceptions that may occur. A condition handler executes one statement, which in this example is RESIGNAL. RESIGNAL is used only within condition handlers to raise a condition that the caller will process. With no parameters, as in this example, RESIGNAL forwards the same condition (SQL state) that activated the condition handler. You may raise some other condition if you prefer.

       declare exit handler for sqlexception
          resignal;
    

    The first test is to verify that the item number is a valid identifier. It not, this procedure raises error 77001 and reports the invalid number in the message text and job log. When SIGNAL raises the condition, the exit handler that we just saw activates and forwards the condition to the caller.

       begin -- Be sure the item number is valid.
          declare  v_Message   varchar(48);
          if not exists (select ItemNumber from Items 
                          where ItemNumber = p_ItemNumber)
          then
             set v_Message = 'Invalid item number ' concat p_ItemNumber ;
             signal sqlstate '77001' set message_text = v_Message ;
          end if;
       end;
    

    The next test validates the inventory location from which the data is being moved and also verifies that there is sufficient inventory in that location to satisfy the demand. Depending on which error it finds, this procedure uses the SIGNAL statement to raise error condition 77002 or 77003.

    As before, the exit handler for the SQLEXCEPTION condition catches these error conditions and forwards them to the caller.

       -- Be sure the from-location exists and has sufficient inventory
       begin
          declare v_Quantity  dec(5);
          declare v_Message   varchar(64);
          declare exit handler for Not Found
             begin
                set v_Message = 'No inventory found for item ' concat p_ItemNumber concat
                                ' in location ' concat p_FromLocation; 
                signal sqlstate '77002' set message_text = v_Message;
             end;
          select Quantity into v_Quantity
            from ItemLocations
           where (ItemNumber, Location) = (p_ItemNumber, p_FromLocation);
          if v_Quantity < p_Quantity
          then
             set v_Message = 'Insufficient inventory found for item ' 
                             concat p_ItemNumber concat
                             ' in location ' concat p_FromLocation; 
             signal sqlstate '77003' set message_text = v_Message;
          end if;
       end;
    

    Subtract the quantity from the old location. If this update fails, the exit handler resignals the condition.

       -- Subtract the quantity from the from-location
       update ItemLocations
          set Quantity = Quantity - p_Quantity
        where (ItemNumber, Location) = (p_ItemNumber, p_FromLocation);
    

    Add the quantity to the new location. Here MERGE performs an “upsert”. If some quantity of this item is already in this location, the move quantity is added to the existing quantity, otherwise the item is added to the new location. If this operation fails, well, you know what will happen.

       -- Add the quantity to the to-location
       merge into ItemLocations as tgt
          using (values (p_ItemNumber, p_Quantity, p_ToLocation))
                     as src (ItemNumber, Quantity, ToLocation)
             on (src.ItemNumber, src.ToLocation) = (tgt.ItemNumber, tgt.Location)
          when matched then
             update set tgt.Quantity = tgt.Quantity + src.Quantity
          when not matched then
             insert (ItemNumber, Quantity, Location)
                values (src.ItemNumber, src.Quantity, src.ToLocation);   
    

    The last step is to log a record of the inventory movement.

    <

       -- Log the transaction
       insert into InventoryTransactionLog 
              (ItemNumber, Quantity, FromLocation, ToLocation)
          values (p_ItemNumber, p_Quantity, p_FromLocation, p_ToLocation);
    

    Before you continue, be sure you understand the role of the exit handler throughout this process. The SIGNAL statements do not send the condition (we could say the error) to the caller. They raise a condition within this routine, the exit handler catches the condition, and the RESIGNAL within the exit handler communicates the condition to the caller. What the caller does with the condition depends on how it was programmed.

    Using condition handlers to process unexpected and expected errors is not what I’m used to, but it makes perfect sense. If you’re determined to write robust applications that address the expected and anticipate the unexpected, you’ll find a strong ally in SQL PL.

    RELATED STORIES

    Error Handling in SQL PL, Part 1

    Harry’s Heart Attack

    The RESIGNAL Statement

    The SIGNAL Statement

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, COBOL, Four Hundred Guru, Guru, IBM i, RPG, SQL, SQL PL

    Sponsored by
    New Generation Software

    FREE Webinar:

    Creating Great Data for Enterprise AI

    Enterprise AI relies on many data sources and types, but every AI project needs a data quality, governance, and security plan.

    Wherever and however you want to analyze your data, adopting modern ETL and BI software like NGS-IQ is a great way to support your effort.

    Webinar: June 26, 2025

    RSVP today.

    www.ngsi.com – 800-824-1220

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Mad Dog 21/21: Big Blue’s Big Beast Boost IBM Unveils LTO-8 Tape Drives

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 67

This Issue Sponsored By

  • ProData Computer Services
  • HelpSystems
  • ASNA
  • HelpSystems
  • Manta Technologies

Table of Contents

  • Java On IBM i: A Developing Situation
  • IBM Unveils LTO-8 Tape Drives
  • Guru: Error Handling in SQL PL, Part 2
  • Mad Dog 21/21: Big Blue’s Big Beast Boost
  • Sundry October Power Systems Announcements

Content archive

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

Recent Posts

  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23
  • SEU’s Fate, An IBM i V8, And The Odds Of A Power13
  • Tandberg Bankruptcy Leaves A Hole In IBM Power Storage
  • RPG Code Generation And The Agentic Future Of IBM i
  • A Bunch Of IBM i-Power Systems Things To Be Aware Of
  • IBM i PTF Guide, Volume 27, Numbers 21 And 22

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