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.
 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.
 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. |
Kevin Forsythe |
| About the Author: |
|
Kevin Forsythe has worked on IBM midrange systems for 25 years. With many years of experience in programming, analysis, consulting, and instruction, he is uniquely skilled at making difficult material more easily understood. His 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 is a member of COMMON's Speaker Hall of Fame, and he currently develops applications for DriveTime. Kevin has written numerous articles on a variety of AS/400/iSeries/System i/IBM i topics.
MC Press books written by Kevin Forsythe available now on the MC Press Bookstore.
| | Read More >> | |
