TechTip: Testing Stored Procedures

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

If you've braved the intimidating unknowns of creating stored procedures, you've probably learned that support for them in IBM's STRSQL command is limited. You can call a stored procedure only if it does not return a parameter or a result set.

For those of you who haven't tried creating stored procedures, they are simply programs called from within SQL. On the iSeries, we can write stored procedures in RPG, SQL, CL, and more. Here is an SQL command that creates a simple stored procedure.

create procedure yourlib/testproc               
(in file char(21))                                    
 language sql                                         
 dynamic result sets 1                                
 begin                                                
 declare stmt char(50);                               
 declare c1 cursor for s1;                            
 set stmt = 'SELECT * FROM ' || file || ' ORDER BY 1';
 prepare s1 from stmt;                                
 open c1;                                             
 return;                                              
 end                                                  

This procedure accepts a 21-byte input parameter and returns one result set. The input parameter is the file name used in the SELECT statement. If you attempt to call this procedure from within STRSQL, you receive an error. However, if you use the Run SQL Scripts tool within iSeries Navigator, you can call the stored procedure, as shown in Figure 1.

http://www.mcpressonline.com/articles/images/2002/TT%20for%20Testing%20Stored%20ProcsV300.jpg

Figure 1: Use the Run SQL Scripts tool in iSeries Navigator. (Click images to enlarge.)

Once the stored procedure is called, the stored procedure on the System i builds the SELECT statement, which reads data from the 'CUST' file. The result set is returned to iSeries Navigator, which displays the results, as shown in Figure 2.

http://www.mcpressonline.com/articles/images/2002/TT%20for%20Testing%20Stored%20ProcsV301.jpg

Figure 2: The stored procedure results are shown.

If you're developing stored procedures for your application and you want an easy mechanism for testing them, try using iSeries Navigator's Run SQL Scripts.

Kevin Forsythe has worked on IBM midrange systems for more than 20 years. With many years of experience in programming, analysis, consulting, and instruction, he is uniquely skilled at making difficult material more easily understood. As the lead instructor for DMC Consulting's AS/Credentials training classes, Kevin is responsible for developing up-to-date courseware as well as providing instruction on a wide range of topics. This comprehensive background includes practical application, education, and research and provides a perspective that allows him to help others steer their way through the technical maze that surrounds them.

Kevin speaks regularly at conferences and user group meetings, volunteers as a Subject Matter Expert for COMMON, has written numerous articles on a variety of iSeries topics, and authored the bestselling book SQL for eServer i5 and iSeries.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  •  

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: