Frequently, people use SQL stored procedures as a method of embedding business logic into their database servers. One of the advantages of stored procedures is that they can return a result set.

Programs that access the database through ODBC, JDBC, or OLE for Databases (OLE DB) typically use result sets in much the same way that we're used to using cursors. Because a stored procedure can return a result set, you can use tools based on these technologies to read these return values.

Unfortunately, RPG programs can't read result sets. Or can they? This article takes a look at how you can use SQL's Call Level Interface (CLI) to read a result set in an ILE RPG program.

The most common way to run SQL in RPG is by embedding it into your program. The reason that people believe that RPG programs can't read a result set is that embedded SQL doesn't support result sets.

Technologies such as JDBC, ODBC, and OLE DB work by calling the CLI APIs under the covers. An RPG program can process a result set by calling these CLI APIs directly. In fact, the CLI APIs solve many of the complaints that RPG programmers have had with the embedded SQL preprocessor:

1. The CLI APIs work nicely with free-format RPG. To do that with embedded SQL, you need V5R4 or later!

2. They don't conflict with other RPG language features, such as copy books and qualified data structure arrays.

3. They can read result sets, as I demonstrate in this article.

4. They're included with OS/400 and/or i5/OS. You don't have to pay extra for the SQL Developer's Kit (5722-ST1).

It's not all good news, though. The SQL CLI APIs are more complicated to use than embedded SQL, because they don't know anything about the variables that you define in your program. They're also a bit slower because none of the SQL code can be compiled at compile time.

To demonstrate this support, I start by creating an example of a stored procedure that returns a result set. In this example, I write an RPG program that calculates the next 10 work days for my company. For the sake of this example, my company works Monday through Friday and does not work on Saturday or Sunday. Here's the code to do that:

     H DFTACTGRP(*NO)

     D GetDOW          PR             9A   varying
     D   myDate                        D   const

     D count           s             10I 0
     D DayList         ds                  occurs(10)
     D   Date                        10A
     D   Name                         9A   varying

     D x               s             10I 0
     D dayname         s              9A   varying
     D NextDate        s               D   datfmt(*ISO)

      /free

          //  This gets the next 10 work days.
          //  (For this example, work days are
          //    Monday through Friday)

          Count = 0;
          NextDate = %date();

          for x = 1 to 10;

             dou  dayname<>'Saturday'
                 and dayname<>'Sunday';
                NextDate = NextDate + %days(1);
                dayname = GetDOW(NextDate);
             enddo;

             %occur(DayList) = x;
             Date = %char(NextDate:*ISO);
             Name = dayname;
             Count = Count + 1;

          endfor;

      /end-free

     C/exec sql Set Result Sets Array :DAYLIST for :COUNT Rows
     C/end-exec

     c                   eval      *inlr = *on
     c                   return
      *++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      * GetDOW(): Get the day of the week for a given date
      *++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     P GetDOW          B
     D GetDOW          PI             9A   varying
     D   myDate                        D   const
     D SUN             c                   const(d'2006-01-01')
     D days            s             10I 0
     D dowk            s             10I 0
      /free
          days = %diff(myDate: SUN: *DAYS);
          dowk = %rem(days: 7);

          select;
          when dowk = 0;
            return 'Sunday';
          when dowk = 1;
            return 'Monday';
          when dowk = 2;
            return 'Tuesday';
          when dowk = 3;
            return 'Wednesday';
          when dowk = 4;
            return 'Thursday';
          when dowk = 5;
            return 'Friday';
          when dowk = 6;
            return 'Saturday';
          endsl;

      /end-free
     P                 E

This code uses RPG's date math. It starts by calling the %date() built-in function (BIF) with no parameters, so that the BIF returns the current date. It then adds one day using the %days() BIF. It does this in a loop for the next 10 days. For each day, it finds out the day of the week, and if it's Saturday or Sunday, it adds more days until it gets to Monday.

Each date and day of week are added to a multiple-occurrence data structure (MODS) named DayList. If you run this program on May 11, 2006, DayList will contain the following values:

2006-05-12 Friday
2006-05-15 Monday
2006-05-16 Tuesday
2006-05-17 Wednesday
2006-05-18 Thursday
2006-05-19 Friday
2006-05-22 Monday
2006-05-23 Tuesday
2006-05-24 Wednesday
2006-05-25 Thursday

The advantage of making this a stored procedure is that I can use it from any application on my company's network. I can use the CLI APIs to call it from RPG programs on my iSeries. I can use JDBC to call it from Java programs anywhere. I can even use ODBC or OLE DB to call it from Visual Basic or .NET on Windows systems. If I want to change the logic for which days my company works, I have only one place to change it.

For example, the following Java program connects to my company's iSeries and gets the list of days:

import java.sql.*;

public class weeklist
{

    public static void main (String[] parameters)
    {
        Connection connection   = null;

        if (parameters.length != 2) {
            System.out.println("ERROR: must pass UserId and Password");
            return;
        }

        String userid = parameters[0];
        String passwd = parameters[1];

        String url = "jdbc:as400://iseries.example.com;naming=system";

        try {
            DriverManager.registerDriver(
               new com.ibm.as400.access.AS400JDBCDriver());

            connection = DriverManager.getConnection(url, userid, passwd);
            Statement stmt = connection.createStatement ();
            ResultSet rs = stmt.executeQuery ("CALL MYLIB/WORKDAYS");

            while (rs.next ()) {
                String weekDate = rs.getString(1);
                String dayName  = rs.getString(2);
                System.out.println (weekDate + " " + dayName);
            }
        }

        catch (Exception e) {
            System.out.println ();
            System.out.println ("ERROR: " + e.getMessage());
        }

        finally {
            try {
                if (connection != null)
                    connection.close ();
            }
            catch (SQLException e) {
                // Error is ignored.
            }
        }

        System.exit (0);
    }
}

This Java program connects to the server named iseries.example.com and calls the program named WORKDAYS in library MYLIB. (If you decide to try this program, you need to change those values.)

The program executes the called procedure in the same manner that it would run a query, except that instead of using the Select statement, it uses the Call statement.

To do this in RPG, you need to use the SQL CLI APIs. They are documented in the Information Center at the following link:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/apis/database2.htm

These APIs let you have several environments with different attributes all available to your program at the same time. In each environment, you can have more than one database connection, if you want. Each database connection can be used to run several SQL statements. This is done by allocating environments, connections, and statements as needed.

For example, the following code allocates a CLI environment handle. Then it uses that environment to allocate a connection handle:

     D env             s                   like(SQLHENV)
     D conn            s                   like(SQLHDBC)
       .
       .
      /free
           SQLAllocEnv(env);
           SQLAllocConnect(env: conn);

The SQLAllocEnv() API creates a new environment. That means that it reserves memory for the internal variables that the system uses to keep track of an SQL environment. It returns an environment handle (which I store in the env variable) so that you can tell subsequent APIs where the internal variables are.

The SQLAllocConnect() API needs to know about these internal variables, so I pass the environment handle as a parameter. From this environment, SQLAllocConnect() creates a new handle — for a database connection. Like SqlAllocEnv(), SqlAllocConnect() does no more than reserve memory for internal variables. I haven't yet connected to the database.

This process of creating handles might seem tedious and even a little complicated, but it's necessary in order to use the CLI APIs. Fortunately, just about every program that uses these APIs allocates handles in the exact same way, so if you do it once, you can simply copy the code from program to program.

Now that I have a space in memory for the connection variables, I can call APIs to manipulate them. For example, I can change the naming convention from SQL naming (the default) to system naming by calling the following API:

           SQLSetConnectAttrI( conn
                             : SQL_ATTR_DBC_SYS_NAMING
                             : SQL_TRUE
                             : %size(SQLINTEGER) );

In the memory for the connection handle, there's an internal variable for the system naming convention. All that this API call does is change the internal variable from its default value of FALSE to TRUE so that I can specify SQL statements that use the system naming convention.

When all the variables for the connection are set the way that I like them (to be honest, I rarely change anything besides the naming convention), I can connect to the database:

           SQLConnect( conn
                     : '*LOCAL'
                     : SQL_NTS
                     : *NULL
                     : SQL_NTS
                     : *NULL
                     : SQL_NTS );

I use the special value of *LOCAL to specify that I want to connect to the database on the local computer. If I want to connect to a different SQL database, I can specify a data source name, user name, and password on the call to the SQLConnect() API.

Once connected, I'm ready to run the stored procedure. To do that, I need to allocate space for an SQL statement handle and then provide the SQL code to be executed in that handle. For example:

     D stmt            s                   like(SQLHSTMT)
           .
           .
           SQLAllocStmt( conn : stmt );

           SQLExecDirect( stmt
                        : 'SELECT name,addr FROM custmas'
                        : SQL_NTS );

The SQLAllocStmt() API is another "allocate" API. All it does is create space for the SQL statement and the internal variables related to it. I also pass the connection handle so that these internal variables can be associated with the database connection. It responds by passing back the variable stmt, which is the handle for the SQL statement. I use that variable in the SQLExecDirect() API when I specify the SQL code to run.

In the preceding code snippet, I run an SQL Select statement to retrieve fields from a customer master file. I provide this example to show that any SQL statement can be run through the CLI APIs. Just as the SELECT API is used to run a query, the Call statement is used to run a stored procedure:

     D stmt            s                   like(SQLHSTMT)
           .
           .
           SQLAllocStmt( conn : stmt );

           SQLExecDirect( stmt
                        : 'CALL MYLIB/WORKDAYS'
                        : SQL_NTS );

The result set from this SQL statement is now saved in the internal memory for the statement. This is the memory that was reserved when I called the SQLAllocStmt() API.

To retrieve the results, I need to specify which of my local variables should be used for each SQL column returned. In this example, two columns are returned, the date and the name of the day. I use the SQLBindCol() API to tell SQL about the variables that I want them to be retrieved into:

     D date            s             10A
     D name            s              9A   varying
           .
           .
           SQLBindCol( stmt
                     : 1
                     : SQL_CHAR
                     : %addr(Date)
                     : %size(Date)
                     : *omit );

           SQLBindCol( stmt
                     : 2
                     : SQL_VARCHAR
                     : %addr(Name)
                     : %size(Name)-2
                     : *omit );

SQL already knows what the data types and sizes of the database fields are, so I don't have to tell it about those. However, unlike embedded SQL, the CLI knows nothing about my variables! The SQLBindCol() API tells SQL about the variables defined in my program that I want it to load results into.

In the preceding code snippet, I tell SQL that I want the first column from the given SQL statement to be loaded into a variable called Date. To do that, I pass the SQL_CHAR field to tell SQL that Date is a character variable. I also use RPG's %addr() BIF to tell SQL where Date is located in memory, and I use RPG's %size() BIF to tell SQL how big the Date field is. Now that SQL knows those things, it knows where to put the data from the first column of my database when I fetch each row.

After I tell SQL about the first column, I proceed to tell it about the second column. The second column should be loaded into a variable called Name, which is a VARYING string. In SQL, VARYING is called VARCHAR, so I tell it that the field is SQL_VARCHAR. I also tell SQL where this field is stored in memory, and how big the field is. In this case, I have to subtract two from the output of the %size() BIF, because SQL doesn't want to know about the extra two bytes that RPG reserves for the length of a VARYING field.

If the actual columns in the database or result set don't match the information that I provide about my variables, SQL does whatever conversions are necessary when it copies the data into them.

At long last, I can retrieve the rows from my result set. To do that, I call the SQLFetch() API as follows:

          dow SQLFetch( stmt ) = SQL_SUCCESS;
             except;
          enddo;

SQLFetch() reads the next row from the result set into the variables that I used the SQLBindCol() API to bind to the columns. If SQLFetch() is able to do so, it returns SQL_SUCCESS. If SQLFetch() reaches the end of the result set, it returns something else, and my loop ends.

For the sake of this example, I print the Name and Date variables (i.e., the two variables that I bound with SQLBindCol() earlier) to a program-described print file. Here's what my O-specs look like:

     OQSYSPRT   E
     O                       Date                10
     O                       Name                20

When I'm done, I disconnect from the database and return the memory used by the various handles (i.e., stmt, conn, and env) to the system so that it can be used by other programs. The following code does that:

          SQLFreeStmt( stmt : SQL_DROP );
          SQLDisconnect( conn );
          SQLFreeConnect( conn );
          SQLFreeEnv( env );

In this article, I present three sample programs:

You can download these sample programs, as well as the SQLCLI_H source member that contains all the prototypes and definitions used with the SQL CLI APIs, from the following link:
http://www.pentontech.com/IBMContent/Documents/article/52529_68_ResultSet.zip