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.
(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.
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.
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:
LATEST COMMENTS
MC Press Online