In "JDBC from RPG Enhancements" (October 21, 2007, article ID 55530) I explained how your RPG programs can use Java database drivers to run SQL statements, including stored procedures, running on third-party databases such as SQL Server and Oracle.

Unfortunately, at the time of that article, my utility for calling JDBC from RPG did not support retrieving result sets from stored procedures -- a problem that needed to be solved!

This article remedies that problem by adding support for reading result sets from a stored procedure, and explains how to use that support in your RPG programs.

When you use JDBC, you typically decide between two types of SQL statements. Those that return result sets, such as SELECT statements, and those that do not, such as Update, Insert, Create Table, and so forth. In standard JDBC, you call the "execute query" routine to run SQL statements that return a result set, or the "execute update" routine to run a statement that does not return a result set, but instead returns a count of the number of rows that have been updated.

Stored procedures are different than other SQL statements, however. Stored procedures can do both. They can return a result set, or they can return an update count, or they can return both a result set and an update count. Even more interesting is the fact that they have the potential to return multiple result sets in response to a single procedure call. Cool, eh?

JDBCR4, My Tool

As I discussed in previous articles (you'll find links to them at the end of this one) I've written an RPG service program called JDBCR4 that attempts to make it easy to use JDBC drivers from RPG.

Preparing Callable Statements

Java represents SQL statements as Statement objects. In addition to the basic Statement object, it also provides a PreparedStatement object, which is used for SQL statements that you might want to execute multiple times (perhaps with different parameter values), and a CallableStatement object that you use when calling a stored procedure.

Using my JDBCR4 tool, you create a CallableStatement by writing RPG code like the following:

    D stmt            s                   like(CallableStatement)
       
      . . . first, establish a connection . . .

         stmt = JDBC_PrepCall( conn
                             : 'call union_employees('UN1')');

The JDBC_PrepCall() subprocedure takes the SQL string (the call statement in the 2nd parameter) and analyzes it to make sure it's valid SQL code, then creates a callable statement object that's set up to run that statement. The output of JDBC_PrepCall() is a CallableStatement Java object that will call the stored procedure -- when you execute the statement.

Running a Callable Statement

To execute the statement you run the JDBC_ExecCall subprocedure, as follows:

     D rs              s                   like(ResultSet)
     D rsmd            s                   like(ResultSetMetaData)
     D IsResultSet     s              1n
         .
         .
         IsResultSet = JDBC_execCall( stmt );

The JDBC_execCall() subprocedure accepts only one parameter -- the statement to execute. That's the statement you created when you called JDBC_PrepCall. So, JDBC_execCall() runs that statement, then it returns an indicator. The indicator will be set to *ON if the first thing returned by the stored procedure was a result set, or *OFF if the first thing returned is an update count.

Retrieving Result Sets

Routines named JDBC_getResultSet() and JDBC_getUpdateCount() have been added to JDBCR4 to let you retrieve the result set or update count.

         IsResultSet = JDBC_execCall( stmt );
         if (IsResultSet);
             rs = JDBC_getResultSet(stmt);
         else;
             count = JDBC_getUpdateCount(stmt);
         endif;

The result set (named "rs" in the preceding code snippet) is very much like the cursors that we use in embedded SQL. It provides a means of reading all of the columns and rows returned by the SQL statement. This result set is no different from the one returned by a SELECT SQL statement that you might've executed with the JDBC_ExecQry() routine.

To read a result set, you call JDBC_nextRow() to advance to the next row in the result set, then you call JDBC_getCol() to retrieve each column. If the stored procedure, above, returns a result set containing an employee number, a first name, and a last name, you might read that result set as follows:

      dow JDBC_nextRow(rs);
          emplno = %int( JDBC_getColByName(rs: 'EMEMPL'));
          first  = JDBC_getColByName(rs: 'EMFNAM');
          last   = JDBC_getColByName(rs: 'EMLNAM');
         ... do whatever you like with this record ...
      enddo;

As I mentioned above, a stored procedure can potentially return more than one result. It can return many result sets as well as an update count. To let you loop through the different results that the stored procedure has returned, I've provided the JDBC_getMoreResults() routine. Like JDBC_ExecCall(), JDBC_getMoreResults() will return *ON if the next result is a result set, or *OFF if the next result is an update count.

         stmt = JDBC_PrepCall( conn
                             : '{call union_employees('UN1')}':);

         IsResultSet = JDBC_execCall( stmt );
         count = 0;
         dou count = -1;
             if ( IsResultSet = *OFF );
                  count = JDBC_getUpdateCount(rs);
             else;
                rs = JDBC_getResultSet( stmt );

                dow JDBC_nextRow(rs);
                   emplno = %int( JDBC_getColByName(rs: 'EMEMPL'));
                   first  = JDBC_getColByName(rs: 'EMFNAM');
                   last   = JDBC_getColByName(rs: 'EMLNAM');
                   ... do whatever you like with this record ...
                enddo;
             endif;

             IsResultSet = JDBC_getMoreResults( stmt );
         enddo;

The JDBC_getMoreResults() subprocedure will close the existing result set and advance to the next available result, and will return *ON if the next result is a result set, or *OFF if there's an update count instead. If the update count is -1, it means that there are no more results.

Since JDBC_getMoreResults() will close the currently open result set before returning a new one (or update count) there's no reason to add extra calls to JDBC_FreeResult() -- the memory used by the result set will be freed up by JDBC_getMoreResults().

Code Download

You can download the updated copy of my JDBCR4 utility from the following link. This link also includes sample programs that demonstrate how to use the various features of the JDBCR4 utility:
http://www.pentontech.com/IBMContent/Documents/article/56356_471_RpgAndJdbc.zip [2]

Previous Articles

To learn more about JDBC from RPG, please check out the following articles:

• JDBC from RPG Enhancements [3] (Contains general info about JDBC from RPG, and provides links to all of the past articles)
  
• JDBC from RPG by Column Name [4] explains how to read fields from a result set by column name instead of column number.