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.
