Recycling RPG Programs as Stored Procedures, Part 2

Returning a result set using an SQL cursor; details on V7.1 support for RPG

In a previous EXTRA article, we created a simple stored procedure from an RPG program. In that case, we returned the results back using “ordinary” parameters. One of the nice features of stored procedures is that they may also return a result set—i.e., a sort of “subfile-like” list of items—to its caller. We’ll explore that option here and also cover the new V 7.1 support that allows RPG to receive and process result sets from a stored procedure.

In our earlier example, our stored procedure returned information about a specific customer, accepting a customer number as an input parameter and providing some details about that customer. But what if we needed to supply information for a list of all the customers in a state? In that case, since the stored procedure is called from an SQL statement, the best way to supply this information would be to return an SQL result set that the caller can process as if the database had returned that result set to them directly.

When you write the stored procedure in RPG, there are two ways to create and return a result set. You can create an SQL cursor and return that directly or you can build the result set in an RPG data structure (DS array or multiple-occurrence DS) and return that. We’ll take a look at both options.

No matter which way you choose to build the result set, you’ll need to use SQL in your RPG because the only way to return a result set from an RPG program is to use the “Set Result Sets …” SQL statement. So you’ll want to make your source member type SQLRPGLE (or SQLRPG, if you’re unfortunate enough to be required to use the outdated form of RPG for some reason) and use the CRTSQLRPGI (or CRTSQLRPG) compile commands to compile the program destined to become the stored procedure.

Returning a Result Set Using an SQL Cursor

If you decide to return an SQL cursor as your stored procedure result set, the task is very simple—assuming you know how to declare and use an SQL cursor. If you don’t, take a look at our earlier article on the subject.

A few significant differences exist between this stored procedure code and a normal RPG program that processes data using an SQL cursor. Most notably, your program will not process the data using the cursor at all. You’ll simply declare and open the cursor and then return the open cursor to your caller. In other words, you shouldn’t code any Fetch or Close cursor operations in your program because when you return the cursor to the caller, the caller will do the fetch and close operations.

The other requirement is the Set Result Sets statement mentioned previously. In the example that follows, you can see this illustrated at <A>. The fact that you specify “cursor” after the Result Sets is the indication that you’re returning an SQL cursor.

So a very simple RPG program acting as a stored procedure that receives a State value as an input parameter and needs to return an SQL cursor result set containing a list of all the customers in that State might look like this:

D CustRsCSR PI D State 2A /FREE EXEC SQL Declare CustCsr cursor for SELECT CustNo, Name, City, State FROM Customers WHERE State = :State ORDER BY Name; EXEC SQL Open CustCsr; <A> EXEC SQL <A> SET RESULT SETS Cursor CustCsr; *InLR = *On; Return; 

Obviously, there may need to be some additional logic in the program besides the SQL statements but as you can see, returning an SQL result set is very simple. Note that the Procedure Interface (or *Entry Plist) doesn’t declare anything about the result set being returned—just the more “normal” types of parameters.

Returning a Result Set Using an Array

Now let’s look at the other option for result sets in RPG. This may be a better alternative in situations when you need to use some additional logic outside the SQL cursor to further massage or edit the data to be returned. Or you may already have some RPG code that perhaps fills a subfile today using “native” I/O and you want to recycle this logic by making some relatively minor changes to the program to fill a DS array with the information instead of a subfile.

SQL refers to this as an array result set, but, of course, it’s actually a data structure—either a multiple occurrence DS or (our preferred method by far) a DS array. It doesn’t matter how you retrieve the data to fill the DS. You may even choose to use SQL to fill it or some portion of it, perhaps a multi-row fetch from an SQL cursor.

A very simple program that reads data from an F-spec declared table and populates a DS array to return a result set may look something like this one. We’ll highlight a few details following this code.

   FCustomers2IF   E           K DISK

   D CustRSCSR       PR                  ExtPgm('CUSTRSARR')
   D  StateIn                       2A

   D CustRSCSR       PI
   D  StateIn                       2A

   D  Counter        S              5P 0

<C>D CustomersDS     DS                  LikeRec(Custrec)

<D>D ReturnDta       DS                  Dim(99) Qualified
   D  CustNo                             Like(CustNo)
   D  Name                               Like(Name)
   D  City                               Like(City)
   D  State                              Like(State)

    /FREE

     Counter = 0;

     DoU ( Counter = 99 ) or %Eof(Customers);

<E>     Read Customers2 CustomersDS;

        If %Eof(Customers2);
           Leave;
        EndIf;

<F>     If CustomersDS.State = StateIn;
           Counter += 1;
<G>        Eval-Corr ReturnDta(Counter) = CustomersDS;
        EndIf;

     EndDo;

<H>  Exec SQL
<H>     SET RESULT SETS Array :ReturnDta FOR :Counter ROWS;

    *InLR = *On;

    Return;

In case you haven’t had much experience with some of the newer data structure definition and I/O features in RPG, we’ll cover a few of those details. We created a data structure using the LIKEREC keyword at <C>. It’s important to remember that structures created this way are always implicitly qualified. That’s why we referred to CustomersDS.State at <F>. We created that DS so that we have an appropriate DS to use with our READ operation <E>.

Note that using the DS name on the read means the data from the row (aka record) will go directly into the DS and not into the typical I-spec fields in the program. The reason we wanted to read into the DS is so that we could simplify our RPG logic by using the new Eval-Corr (Eval Corresponding) operation <G> to get the data from one DS to the other. This moves the contents for the same-named subfields from one DS to the other. The structure that will be returned as the result set is defined at <D>as a data structure array that requires the use of the Qualified keyword.

Last, at <H> we use the same Set Result Sets statement as in the previous example except that we used “array” instead of “cursor” to specify that we’re using a DS array. If we’d used a multiple occurrence DS instead, we’d have used the same statement.

Creating the Stored Procedure

Regardless of which method you use to fill and return the result set to your caller, after your RPG code is written, you’ll need to define your stored procedure to the database, just as we did last time with our simpler examples. We should note just a couple of significant differences.

First, you need to specify that the stored procedure returns a result set. Second, you’ll need to specify that it uses SQL. Any time the code for a stored procedure uses SQL, you must declare that when creating the procedure and you must declare the extent to which SQL is used. The options are:

• No SQL – This is what we used last time by default
• Contains SQL – This allows only very limited SQL statements to be used
• Reads SQL data – SELECT is allowed, but no INSERT, UPDATE or DELETE
• Modifies SQL data – Allows any combination of SQL statements

We didn’t mention this option in the last article, but if your RPG program used as a stored procedure contains SQL, you’re required to specify one of these options even if you aren’t returning a result set. But since returning a result set always uses SQL—if only to use the Set Result Sets statement—then one of these options must be specified.

To create a stored procedure from either of the programs shown here, we can use a statement in SQL that looks like this:

CREATE PROCEDURE CustomersByState
        ( IN State CHAR(2) )	
	DYNAMIC RESULT SETS 1 
	LANGUAGE RPGLE 
	READS SQL DATA 
	EXTERNAL NAME MYLIB/CUSTRSARR
        PARAMETER STYLE GENERAL ;

In these examples, we only have a single input parameter and no output parameters, but you can easily combine simple parameter passing (as shown in the earlier article) with returning the result set. You can also return multiple result sets, such as “Set Result Sets Cursor InvCsr, Cursor InvDetailsCsr; ”

Testing Your Stored Procedures

We mentioned in the previous article that you could test your stored procedures by calling them from a Navigator Run SQL Scripts window. This is still a great way to do at least the initial testing for your stored procedures. We also said that you could write an RPG program as a sort of test harness to call your stored procedures. However, when your stored procedure returns a result set, you won’t be able to test that from an RPG program unless/until you’re using V 7.1, as results could not be returned to an RPG program until then. We’ll show you what that new support in V 7.1 looks like in case you decide to test your procedures that way.

The ability to receive and process a result set from a stored procedure in an RPG program requires the use of some new RPG and SQL syntax. First, you’ll need to declare a D spec item called a RESULT_SET_LOCATOR as shown below at <I>. Then after calling the stored procedure (shown at <J>), you must associate that Locator with the stored procedure, as shown at <K>. Then allocate an SQL cursor in your program to the locator (<L>).

After doing all those steps, you fetch from and close the cursor as you normally would with an ordinary SQL cursor. These <I> through <I> steps take the place of declaring and opening your cursor. The rest of the code processes the SQL cursor as per normal. Note that you must use SQL to process the result set in an RPG program, which is probably not surprising since you also need to use SQL to call your stored procedure.

<I>D CustResultSet   S                   SQLType(RESULT_SET_LOCATOR)

   D CustData        DS
   D  CustNo                        5A
   D  Name                         30A
   D  City                         24A
   D  State                         2A

   D InputState      s              2a

    /free
     dsply 'Enter State:' ' ' InputState;

<J>  Exec SQL
<J>    Call CustomersByState( :InputState );

<K>  Exec SQL
<K>    Associate Result Set Locator (:CustResultSet)
<K>       with Procedure CustomersByState;

<L>  Exec SQL
<L>    Allocate C1 Cursor for Result Set :CustResultSet;

     Dow SQLCode <> 100;

       Exec SQL
         fetch next from C1 into :CustData;

       If SQLCode = 0;
         Counter += 1;

         Dsply ('Customer ' + %Char(Counter));
         Dsply (CustNo + ' ' + %Subst(Name:1:20) + City + State) ;

         EndIf;

     EndDo;

     Dsply ('Total for ' + InputState + ' ' + %Char(Counter));

     Exec SQL
       Close C1;

     *inLR = *on;

Return;                                       

We’ve described how you can test your stored procedure from Navigator and from an RPG program (assuming you’re at V 7.1 or later). Many of our clients are writing stored procedures that are destined to be called from a Java application—either running on IBM i or another platform. We’re not Java experts ourselves but after our previous article, we heard from our friend, Greg Helton, who is. He kindly offered a link to a blog post where he offers some code that will generate Java to call your IBM i stored procedures for testing. We haven’t had a chance to try it out, but feel free to give it a whirl if you want to see your stored procedures in action from Java.

More to Come

There you have our introduction to using RPG logic to create stored procedures. There are, of course, more details than we have had time for in these two parts. In a later article, we’ll cover some of those details, such as the different types of parameter styles and how and why you may want to use something other than the general style that we have used here. Until then, good luck with your stored procedure implementations!

       

---