Testing our IBM i SQL Stored Procedure
Stored procedures often encapsulate core business rules, like calculating discounts, validating transactions, or retrieving sensitive data. A bug here can ripple through multiple systems. Test, TEST and TEST AGAIN!!!
To test an IBM i (AS/400) SQL stored procedure that returns two values, you need to understand how those values are returned:
- Are they OUT or INOUT parameters?
- Are they returned via a result set?
- Or are they RETURN values (e.g. scalar return from a function, not a procedure)?
In our case this is a stored procedure (not a function) that receives one IN parameter and returns two OUT parameters, here's how to test it.
Using IBM i Access Client Solutions (ACS) – Run SQL Scripts
The stored procedure will:
Accept an employee ID as input
Return two values:
EMP_NAME_OUT(employee name) andCITY_OUT(employee's city)
The easiest way is to write an SQL script like this:
declare global temporary table SESSION.RESULTS (
NAME varchar(50),
CITY varchar(30)
)
with REPLACE;
-- Declare host variables (ACS supports SET or VALUES INTO)
begin
declare V_NAME varchar(50);
declare V_CITY varchar(30);
-- Call the procedure NOTE I AM SELECTING EMPLOYEE(1)
call NICKLITTEN.GET_EMPLOYEES(
1,
V_NAME,
V_CITY
);
-- Insert results into temp table to view them
insert into SESSION.RESULTS
values
(
V_NAME,
V_CITY
);
end;
-- Now view the results
select *
from SESSION.RESULTS;
Run this script and we will hopefully see the employee with id=1 in the result set:
Try changing the id number for other values and see what results you get?
It all works very nicely for our sample employees but returns <NULL> when we send in an invalid employee number! That stinks right?
So, lets tweak out stored proc to add a little error handling:
Basic Stored Procedure - with Error Handling
in EMP_ID_IN int,
out EMP_NAME_OUT varchar(50),
out CITY_OUT varchar(30)
)
language SQL
reads sql data
begin
-- Declare variables for error handling
declare SQLCODE int default 0;
declare ROW_COUNT int default 0;
-- Handler for no rows found (SQLSTATE '02000')
declare continue handler for not found
begin
set EMP_NAME_OUT = 'NO EMPLOYEE FOUND';
set CITY_OUT = 'N/A';
end;
-- Handler for multiple rows returned (SQLSTATE '21000')
declare continue handler for sqlstate '21000'
begin
set EMP_NAME_OUT = 'MULTIPLE EMPLOYEES FOUND';
set CITY_OUT = 'N/A';
end;
-- General handler for unexpected SQL errors
declare continue handler for SQLEXCEPTION
begin
set EMP_NAME_OUT = 'ERROR: SQLCODE ' ||
cast(SQLCODE as varchar(10));
set CITY_OUT = 'N/A';
end;
-- Perform the SELECT INTO query
select EMP_NAME,
CITY
into EMP_NAME_OUT,
CITY_OUT
from NICKLITTEN.EMPLOYEE_MASTER
where EMP_ID = EMP_ID_IN;
end;
This SQL Stored Procedure now has some best practice IBM i SQL standards to handle no row found and also multiple rows found.
Now let's test this with a deliberately bad employee number:
declare global temporary table SESSION.RESULTS (
NAME varchar(50),
CITY varchar(30)
)
with REPLACE;
-- Declare host variables (ACS supports SET or VALUES INTO)
begin
declare V_NAME varchar(50);
declare V_CITY varchar(30);
-- Call the procedure NOTE I AM SELECTING EMPLOYEE(1)
call NICKLITTEN.GET_EMPLOYEES(
3,
V_NAME,
V_CITY
);
-- Insert results into temp table to view them
insert into SESSION.RESULTS
values
(
V_NAME,
V_CITY
);
end;
-- Now view the results
select *
from SESSION.RESULTS;
and what do we get?
YES - it worked:
Now let's test this with a deliberately bad employee number. Ask for employee 999 who does not exist:
It's time for a video workshop next...