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.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
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