06
Sun, Oct
2 New Articles

Writing Stored Procedures in RPG

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

Stored procedures provide an easy way to reuse existing RPG logic or to make new RPG code available to other platforms or environments.

 

Stored procedures are programs that are called from SQL. These programs can be written using the SQL procedure language, but they may also be written using RPG. Since stored procedures are  called from SQL, creating one is often a simple way to make application functions available from non-RPG application environments, such as a Java application or an application running on a different system platform. 

 

If you know SQL procedure language and think the application function is well-suited for SQL, then perhaps you would write the stored procedure using SQL. On the other hand, you may choose to write the stored procedure in RPG, perhaps because you and your fellow developers are more familiar with writing and maintaining RPG logic or because RPG is better-suited for the specific task (e.g., maybe you need to use some functions that are easier to do in RPG than SQL, such as %EDITC or %EDITW). An even better reason to use RPG is that you may very well already have a program or procedure written in RPG that can now be reused from another environment.

 

In this article, I will focus on some specific details of writing  RPG external stored procedures, with particular focus on some areas that are not well-documented in other sources.

 

So how do you turn an RPG program into a stored procedure? It can be very simple; you need to enter the "Create Procedure" SQL statement, which specifies the name of the program to be called and the parameter list it requires. The RPG program itself does not need to use embedded SQL, although it may do so. When a stored procedure is written in a language other than SQL, such as RPG, it is referred to as an "external stored procedure." The program name is supplied using the "External Name" parameter, and the parameters are described immediately after the stored procedure name. The statement below creates a simple stored procedure from an RPG program named PRODINFO.

 

CREATE PROCEDURE   GetProdInfo     

  (IN ProdID CHAR (5), OUT Name CHAR (20),

   OUT Category CHAR(15),  OUT Price DEC(5,2))

   EXTERNAL NAME  MyLib/ProdInfo               

   LANGUAGE  RPGLE                       

   PARAMETER STYLE GENERAL

 

Note that the stored procedure name may or may not be the same as the program name. The parameters are listed as either input or output (INOUT is the option for both input and output). Of course, RPG technically treats all parameters to programs as both input and output, but logically, most parameters are typically either providing input to the program from the caller or sending output back to the caller. The Create Procedure statement must be entered using an SQL interface, such as interactive SQL or Navigator.

 

It's a good idea to specify the language of the program (or procedure) that is being designated as a stored procedure, although in most cases, if the program already exists, the system will find it and use the language of the program object.

 

You may be interested to know that procedures in service programs may also be used as stored procedures but only if they do not have a return value-in other words, if they are called using CALLP rather than called from an expression. The syntax for the External Name parameter in that case would look like this: 

 

EXTERNAL NAME MyLib/MySrvPgm(ProdInfo) 

 

The Parameter Style keyword is an important one and is the primary focus of this article. In this example, we used "General," which is the simplest form of passing parameters. With the General style, the parameters listed on the Create Procedure statement can be matched exactly one to one with the entry parameters in the program. While this may seem obvious, we'll look at some other parameter styles in which the relationship between the two parameter lists is not so obvious.

Parameter Style Differences

Based on the parameter list and the "General" parameter style specified in the statement above, the PRODINFO program's procedure interface (PI) might look like the following. For those of you who don't use PIs for programs, this is the equivalent to the *Entry PList. While technically your RPG program is not required to use a PI to be a stored procedure, most modern RPGers are switching to replacing their *Entry PLists with PIs for all new RPG programs, so I'll use them in my examples here.

 

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

 

Once you have written the PRODINFO RPG program (if it doesn't already exist) and entered the Create Procedure SQL statement for it, it is ready to be used as a stored procedure. It can be that simple! The procedure can be called from an SQL statement that might look something like the following. I've used RPG syntax with embedded SQL here since most of you are likely familiar with that interface. The syntax for the call from another platform and/or language may be different.

 

    Call GetProdInfo( :ProdNo, :ProdName, :Category, :Price)

 

There are many other Create Procedure parameters and options that can be used, but since most of them are rarely used, I'll leave them for you to study on your own using the documentation. However, the impact of the parameter style on the requirements for your RPG program may not be obvious from the documentation, so I will take a closer look at that here.

 

Depending on the environment calling the stored procedure, it may be necessary to use a parameter style other than the "General" style used here. The RPG program code must change to match the parameter style needed. For example, many SQL environments require the use of null indicators, so your stored procedure would need to be able to support those as part of the parameter list. Parameter style General does not support null indicators, but the other styles do, so let's take a look at how to code the parameter list to handle some other commonly used styles.

 

The simplest of the parameter styles that support null indicators is called "General with Nulls" (which is sometimes called "Simple, allow null values"). In this case, the parameters as listed on the Create Procedure statement do not change. However, the parameter list (or procedure interface, PI) in the program must change. To create a stored procedure using this parameter style, the Create Procedure statement would look like the following:

 

CREATE PROCEDURE   GetProdInfo     

  (IN ProdID CHAR (5), OUT Name CHAR (20),

   OUT Category CHAR(15),  OUT Price DEC(5,2))

   EXTERNAL NAME  MyLib/ProdInfo               

   LANGUAGE  RPGLE                       

   PARAMETER STYLE GENERAL WITH NULLS

 

However, the program's PI would look like this:

 

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

     D  NullInds                             5I 0 Dim(4)

 

Note that even though the Create Procedure statement still specifies four parameters, this program now receives five parameters. The additional parameter is an array that contains the same number of elements as there are parameters, not counting itself (four in our example). Note that even though they are referred to as "null indicators," they are not indicator data types. They are 2-byte integer values, which are best coded in RPG IV as a 5-digit integer data type (5i 0). It is important to notice that this fifth parameter is not included in the list of parameters at the beginning of the Create Procedure statement. It is an implicit parameter generated because of the parameter style.

 

Each element of the NullInds array will contain a value to indicate whether its corresponding parameter should be considered null. In this example, the first element on NullInds corresponds to ProdID, the second element corresponds to ProdName, etc. If the null indicator value for a parameter (i.e., the array element corresponding to a parameter) has a value of -1, the related parameter is null and any value that may appear in that field should be ignored. If the value of the field is not null, the null indicator has a value of 0. Note that for parameters that are either Input or InOut, the program should check the corresponding null indicator value passed before using the value in each parameter. Likewise, if the program wants to indicate that one of the Out or InOut values passed back to the caller is null, it should place a value of -1 in the appropriate null indicator. In this example, you may decide to write the stored procedure program to return nulls for all the output fields if no product was found that matched the product ID supplied in the input parameter. In that case, you should set NullInds(2), NullInds(3), and NullInds(4) to a value of -1 before returning to the caller.

 

If you prefer, you could also define that fifth implicit parameter as a data structure with four subfields. In this case, the following D specs may be used:

 

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

     D  NullInds                                  LikeDS(NullDS)

     D NullDS           DS

     D  ProdID_NI                     5I 0

     D  ProdName_NI                   5I 0

     D  ProdCat_NI                    5I 0

     D  ProdPrc_NI                    5I 0

 

With this alternative code in place, you now have specific names for each null indicator value, so to make the output parameters null, you would place a value of -1 in fields named NullInds.ProdName_NI, NullInds.ProdCat_NI, and NullInds.ProdPrc_NI. Keep in mind that the use of the LikeDS keyword means the null indicator field names are implicitly qualified. 

 

Calling this version of the stored procedure with the null indicators might look like the following code.

 

  Call GetProdInfo( :ProdNo, :ProdName :Name_NI, :Category :Cat_NI, :Price :Price_NI)

 

Note that null indicator fields immediately follow the parameter field with no comma in between. If the calling program knows a field should never be null, the call statement need not include a null indicator for that field. In this example, the caller knows it will never pass in a null product ID value, so it can leave out the indicator for that field. If it seems odd to you that the call statement should list the fields and indicators in this sequence when the RPG program accepts them in a completely different sequence and format, join the club! I can't explain why the parameters are reformatted, but remember that the call to your program is actually coming via the database (that's where all SQL statements are processed). So the program call to your RPG program is generated on the System i, and that's how it's possible for the transformation of format and sequence of parameters to happen.

 

The parameter lists get even stranger when using other parameter styles. There is a parameter style known as SQL, where not only are null indicators passed as individual parameters, but some other information is also passed. The Create Procedure statement for creating an SQL parameter style stored procedure would look like this:

 

CREATE PROCEDURE   GetProdInfo     

  (IN ProdID CHAR (5), OUT Name CHAR (20),

   OUT Category CHAR(15),  OUT Price DEC(5,2))

   EXTERNAL NAME  MyLib/ProdInfo               

   LANGUAGE  RPGLE                       

   PARAMETER STYLE SQL

 

Note that the same four fields are specified as parameters as was true in the first two versions of this procedure. However, the PI for this version of the program would look like the following:

 

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

     D  ProdID_NI                     5I 0 

     D  ProdName_NI                   5I 0 

     D  ProdCat_NI                    5I 0 

     D  ProdPrc_NI                    5I 0 

     D  RtrnSQLStt                    5A

     D  ProcName                    517A   Varying

     D  SpecName                    128A   Varying

     D  MsgTxt                       70A   Varying

 

 

This example has two significant differences from the "General with Nulls" one. Most notably, you can see that some extra pieces of information are implicitly passed at the end of the list. Look closely and you'll notice something else unusual. As with the "General with Nulls" style, null indicator variables are defined in the parameter list for each "real" parameter passed, and they are each defined as a 5-digit integer as before. But this time, each null indicator is passed individually, whereas last time, all the null indicators were passed in a single structure or array. This is a significant detail that is not always obvious from the documentation or the examples of RPG stored procedures provided.

 

The additional parameters passed at the end of the SQL style list include some extra information and also some support for error or special condition handling. The field I've named RtrnSQLStt is an output parameter that you can use to return a valid "SQL State" value to the caller. For those of you unfamiliar with SQL State, it's a return code field that can be used with embedded SQL to reflect the status of the last SQL statement. It serves a similar function to resulting indicators or built-in functions such as %EOF, %Found, and %Error. Those of you who use embedded SQL are probably familiar with the SQL Code return code. SQL State provides the same function as SQL Code and is the more modern way of dealing with conditions since the values are standard across all SQL platforms.

 

A value of all zeroes in the SQL State field (named RtrnSQLStt in our example) indicates that the stored procedure was run with no errors or special conditions to report back to the caller. It will be filled with zeroes when your program is called. There is a special range of values that you can use to report errors to your caller, so if you plan to use the SQL parameter style, make sure you study the possible values to use. There is also a companion message text field at the end of the parameter list if you need to include more information about the condition.

 

The first of the other two extra fields simply contains the name of the procedure that was called (GetProdInfo in our example). The second is known as the "specific name." Remember that with an external stored procedure, it would be possible to use the same program object to create stored procedures with different names. If it is important to the program logic to know which procedure name was used to call it, this will provide that information.

 

The bottom line is that stored procedures provide a very easy way to reuse existing RPG logic or to make new RPG code available to other platforms or environments. As you can see, the primary challenge that exists is simply figuring out what changes, if any, will be needed in the RPG program's parameter list to make it work as a stored procedure. I have found that the details about these parameter list differences are hard to come by-at least in terms that make sense to RPGers. Most of the examples in IBM documentation and Redbooks are not written in RPG. Even non-IBM examples that I found in Internet searches tend to focus on one parameter style, so finding details about the differences can be difficult. My goal was to share the results of my studies on this topic, and I hope it will be of help to those of you struggling to find information about how to make RPG stored procedures work.

Susan Gantner

Susan Gantner's career has spanned over 30 years in the field of application development. She began as a programmer, developing applications for corporations in Atlanta, Georgia, and working with a variety of hardware and software platforms. She joined IBM in 1985 and quickly developed a close association with the Rochester laboratory during the development of the AS/400 system. She worked in Rochester, Minnesota, for five years in the AS/400 Technical Support Center. She later moved to the IBM Toronto Software Laboratory to provide technical support for programming languages and AD tools on the AS/400 and iSeries.

Susan left IBM in 1999 to devote more time to teaching and consulting. She co-authored one of the most popular System i Redbooks ever, Who Knew You Could Do That with RPG IV? She and partner Jon Paris make up Partner400, a consulting company focused on education and mentoring services related to application modernization. Susan is also part of System i Developer, a consortium of top educators on System i technology who produce the RPG & DB2 Summit events. Its members include Jon Paris, Paul Tuohy, Skip Marchesani, and Susan Gantner.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • 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

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • 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: