JDBC from RPG Enhancements

By:

RPG's built-in support for calling Java methods makes it possible to use JDBC database drivers from an RPG program. In previous newsletters, I showed you how to run SQL statements on MySQL, IBM DB2, Microsoft SQL Server, and Oracle databases from your RPG programs. In this article, I discuss some enhancements to my original JDBC tool. This new version supports stored procedure calls, commitment control, and meta-information about result sets.

The Basic Idea

Because this series of articles has evolved quite a bit from the original article, I think it's a good idea to review the basics of what this code is, why it exists, and how it works.

If you were a Windows programmer writing software for Windows, you'd have no problem accessing databases by any manufacturer. That's because every database manufacturer creates an ODBC driver for Windows. When that driver is installed, you can call the standard ODBC routines to access any database. That's great news for Windows programmers, and it's not so good for anyone else. If you're an RPG programmer on i5/OS, how are you to access anything aside from what IBM provides with the operating system?

As I write this, I know that MySQL AB is working towards better i5/OS integration, but that software hasn't been released yet. Other major database software vendors, such as Oracle and Microsoft, do not provide drivers for i5/OS or RPG programs -- but they do provide them for Java! Java code, including the JDBC drivers, can be run on any computer without recompiling it. Because RPG can call Java routines, you can use Java's database drivers (which are called JDBC drivers) from an RPG program.

You have to watch out for database drivers that aren't "pure Java," however. Some JDBC drivers designed for Windows optimize the database connection by calling some routines written in C. Unfortunately, these drivers won't work on i5/OS, because Windows C code won't run under i5/OS. To make sure you're getting a driver that will work on any platform, look for a driver written in 100 percent pure Java. This is often referred to as a "type 4" JDBC driver.

Some Installation Required

To install a JDBC driver, follow the manufacturer's instructions. Generally, you have to follow these steps:

Download the JDBC driver from the database manufacturer.
The JDBC driver will be in the form of one or more JAR files. These need to be uploaded to the IFS on your i5/OS system.
To make the JAR files available to your program, you must either place them in the /QIBM/UserData/Java400/ext directory of the IFS or add them to your CLASSPATH. (Don't do both!)

Java experts recommend that you use the CLASSPATH, because putting too many things in the ext directory adds overhead to Java applications, and having them in the CLASSPATH makes it easier to test different versions.

For example, you might want to have driver version 1.0 in your production applications, while driver version 2.0 is used in a test environment. If you use the ext directory, this setup would be impossible -- but with a CLASSPATH, all you need to do is specify a different CLASSPATH for a different environment.
Make sure you meet the minimum system requirements of the JDBC drivers. Most JDBC drivers that I've used require Java version 1.4 or higher to run, so you need version 1.4 of Java installed on your System i. When you have multiple versions of Java installed, you can use the QIBM_RPG_JAVA_PROPERTIES environment variable to tell RPG which version of Java to run. To set this variable, run the following CL command:
```
ADDENVVAR ENVVAR(QIBM_RPG_JAVA_PROPERTIES)
          VALUE('-Djava.version=1.4;')
```
Like the CLASSPATH, the preceding command is case sensitive and must be run before any other Java has been run in your current job. After the Java Virtual Machine (JVM) has been loaded, these properties are no longer checked for.

Also, the QIBM_RPG_JAVA_PROPERTIES variable was not included in i5/OS until V5R3. However, if you're a V5R1 or V5R2 user, you can install a PTF to get support for this variable. The PTF number for V5R1 is SI10069, and for V5R2 it's SI10101.

Personally, I prefer to have a CL program that runs when I sign on and sets my preferences for the Java properties and the classpath. Here's the program that I use:

PGM

         ADDENVVAR ENVVAR(CLASSPATH) +
            VALUE('/java/jfreechart/jfreechart-1.0.2.jar:+
                   /java/jfreechart/jcommon-1.0.5.jar:+
                   /java/poi/poi-3.0.1-FINAL-20070705.jar:+
                   /java/poi/poi-contrib-3.0.1-FINAL-20070705.jar:+
                   /java/poi/poi-scratchpad-3.0.1-FINAL-20070705.jar:+
                   /java/poi/xlparse.jar:+
                   /java/jdbc/mysql-connector-java-3.1.12-bin.jar:+
                   /java/jdbc/ojdbc14.jar:+
                   /java/jdbc/sqljdbc.jar:+
                   .')

          ADDENVVAR ENVVAR(QIBM_RPG_JAVA_PROPERTIES) +
                    VALUE('-Djava.version=1.4;+
                           -Djava.awt.headless=true;+
                           -Dos400.awt.native=true;')

ENDPGM

For the preceding example, you need to include only the JAR files required for your environment. In the preceding CLASSPATH, I have:

the jfreechart and jcommon JAR files, for working with the JFreeChart software to generate graphs from RPG
the poi, poi-contrib, poi-scratchpad and xlparse JAR files, for reading and writing Microsoft Excel files from RPG
the mysql-connector JAR file, for working with MySQL databases from RPG
the ojdbc14 JAR file, for working with Oracle databases from RPG
the sqljdbc JAR file, for working with Microsoft SQL Server databases from RPG

When you create your own CLASSPATH variable, you should include only the JAR files that you've downloaded and installed on your own system. You should also change the IFS path names to match where you placed the files on your system.

Then, I use the following command to make sure this program runs automatically when I sign on:

CHGUSRPRF USRPRF(my-userid-here) INLPGM(my-lib/my-cl-pgm)

Using the JDBC Drivers from RPG

The code download for this article contains a service program named JDBCR4. In that service program are my RPG routines that are intended to make it relatively easy to use JDBC drivers from RPG.

The first step in using a JDBC driver is to set the properties used when connecting to the database. The available property names are different with each database manufacturer. At a minimum, you typically need to set properties for user and password. Often, you also use properties to set the database name and other aspects of the connection. Here's how you'd do that in RPG:

      /copy JDBC_H
     D user            s              32a 
     D passwrd         s              32a
     D prop            s                   like(Properties)
         .
         .
         user = 'klemscot';
         passwrd = 'bigboy';
         .
         .
         prop = JDBC_Properties();
         JDBC_setProp(prop: 'user'    : %trim(userid));
         JDBC_setProp(prop: 'password': %trim(passwrd));

The same user and password properties also work for MySQL and Oracle, but Microsoft spells them a bit differently for SQL Server, so you'd do the following instead:

         JDBC_setProp(prop: 'userName': %trim(userid));
         JDBC_setProp(prop: 'password': %trim(passwrd));

There are many other properties you can set, but they differ dramatically from database to database. Check out the documentation for your particular JDBC driver for more details.

The next step is to tell Java which JDBC driver you want to use and create a connection to the database. This is done by calling the JDBC_ConnProp() procedure from my JDBCR4 service program.

Here's an example of connecting to a SQL Server database:

         conn = JDBC_ConnProp('com.microsoft.sqlserver.jdbc.SQLServerDriver'
                             :'jdbc:sqlserver://myserver.example.com:1433'
                             : prop );
         if (conn = *NULL);
             return;
         endif;

The first parameter to JDBC_ConnProp is the Java class name of the JDBC driver. The second parameter is a connection string that tells Java which database server to connect to, and which port it's running on. The last parameter is the property list (containing the user ID, password, and other things) that should be set in the connection.

If the connection fails, JDBC_ConnProp() returns the *NULL special value. In the preceding code, the program is terminated when the connection can't be established.

The driver name and connection string vary with every JDBC driver you use. Here are the ones that I know of:

SQL Server:

         JDBC_setProp(prop: 'databaseName': 'myDataBase');

         conn = JDBC_ConnProp('com.microsoft.sqlserver.jdbc.SQLServerDriver'
                             :'jdbc:sqlserver://myserver.example.com:1433'
                             : prop );

Oracle:

         conn = JDBC_ConnProp('oracle.jdbc.OracleDriver'
                          :'jdbc:oracle:thin:@123.123.123.123:1521:myDataBase'
                          : prop );

MySQL:

         conn = JDBC_ConnProp('com.mysql.jdbc.Driver'
                          :'jdbc:mysql://myserver.example.com/myDataBase'
                          : prop );

IBM DB2 for i5/OS

         conn = JDBC_ConnProp( 'com.ibm.as400.access.AS400JDBCDriver'
                             : 'jdbc:as400://myserver.example.com'
                             : prop );

Running SQL Statements

Once connected to your database, you can run SQL statements against it. You do have to call the JDBC routines to run these statements (using ordinary embedded SQL from RPG won't work). The JDBCR4 service program contains several routines for running SQL statements:

JDBC_ExecUpd(): Runs an SQL statement that does not return any rows or columns as a result. For example, the Update, Insert, Create Table, and Drop Table SQL statements do not return a result set, because they do not return any rows or columns.
JDBC_ExecQry(): Runs an SQL statement that does return rows and columns -- for example, the SQL Select statement to retrieve records from a database.
JDBC_PrepStmt(): Prepares an SQL statement to be executed later. Prepared statements can use parameter markers (question marks) to indicate a spot where data will be inserted from variables. After a prepared statement has been created, you can use JDBC_SetString(), JDBC_setInt(), JDBC_SetDecimal(), JDBC_SetDate(), and so forth, to set the values of the parameter markers from variables in your program. You can use the JDBC_ExecPrepUpd() and JDBC_ExecPrepQry() procedures to run prepared statements.
JDBC_PrepCall(): Prepares a callable statement, in much the same manner as JDBC_PrepStmt() creates a prepared statement. The difference is that a callable statement is intended to call a stored procedure instead of running a standard SQL statement such as Select or Update. You set parameter values for a callable statement the same way you do with the prepared statement described earlier, except that there's one additional routine named JDBC_RegisterOutParameter() that tells Java when a procedure's parameter is used for output. After you prepare your stored procedure call and set the parameter values, you can use JDBC_ExecCall() to run the stored procedure. Routines named JDBC_getString(), JDBC_getInteger(), and so forth, are used to retrieve the value of output parameters after the stored procedure has been run.

This article's code download includes sample programs that demonstrate all these means of running statements.

It's worth noting that the SQL statements that you issue have to be in the correct syntax for the database that you're connected to. Usually, the syntax of the basic statements, such as Update, Insert, and Select, are very similar amongst the different database vendors, but they're often slightly different. Read the documentation for your database if you don't know the correct syntax of the SQL statements.

The routines related to callable statements are new in this version of the JDBCR4 service program and were contributed by John Carroll. The jdbctest4 sample program included in the source code download demonstrates calling a stored procedure. Thanks, John!

Result Set Meta-Data

In addition to his routines for callable statements, John Carroll also provided JDBC_getMetaData(), JDBC_GetColName(), JDBC_GetColCount(), JDBC_GetColDspSize(), and JDBC_GetColTypName() to assist with reading SQL result sets.

These routines are useful when you don't know at compile time which fields will be returned and how big those fields are. In that case, you can retrieve the meta data for a result set by calling the JDBC_getMetaData() procedure.

The JDBC_getMetaData() procedure returns a Java meta data object that can be used with the other APIs listed herein to get the column names, the number of columns, the size of each column, and the data type of the column, respectively.

There's an example of this functionality in the jdbctest3 sample program included in this article's code download.

Commitment Control

Last, but not least, John Carroll's contribution also includes routines named JDBC_Commit() and JDBC_Rollback(), which let you commit or roll back your transactions, as you'd typically do with commitment control. These routines work only with databases that support commitment control, of course. The jdbctest4 sample program demonstrates how to use these commitment control routines.

Links to Previous Articles

I've written the following previous articles about using JDBC from RPG. (The code download for this article contains all of the sample programs discussed in the previous articles as well, so feel free to read the articles at the following links, but download the code from this article, instead.)

Access Oracle Databases from an RPG Program:
http://www.systeminetwork.com/article.cfm?id=55181 [2]>

Access MS SQL Server Database from an RPG Program:
http://www.systeminetwork.com/article.cfm?id=54759 [3]

Handle Null Values in JDBC from RPG:
http://www.systeminetwork.com/article.cfm?id=52741 [4]

JDBC from RPG:
http://www.systeminetwork.com/article.cfm?id=52577 [5]

MySQL Database Access from RPG:
http://www.systeminetwork.com/article.cfm?id=52433 [6]

Code Download

You can download the updated JDBCR4 service program, as well as all the sample programs described in this article and the previous JDBC-related articles, from the following link:
http://www.pentontech.com/IBMContent/Documents/article/55530_330_RpgAndJdbc.zip [7]