In the April 20, 2006, issue of this newsletter, I demonstrated how to access a MySQL database from an RPG program. I did that by writing a service program that contains routines for running SQL statements on the MySQL server. Under the covers, the service program uses Java's JDBC support to connect to the MySQL database.

Since writing that article, I've made changes that let my service program use any JDBC driver, instead of just the MySQL ones. That way, you can use the same service program to connect to MySQL, iSeries, db2, Oracle, SQL Server, MS Access, and more. Any database that you can get a JDBC driver for is accessible through the updated service program.

The reason that I wrote this service program is that most database manufacturers don't write drivers for the iSeries. If I were writing a Windows program, for example, I'd have no trouble finding drivers that I could use to connect to a third-party database. But these drivers aren't always available for the iSeries. Fortunately, almost everyone makes JDBC drivers that allow database access from Java. Because Java programs can be run on any platform, I can use the JDBC drivers from the iSeries. Because RPG has support for calling Java methods directly, I can use those JDBC drivers from RPG, too!

I demonstrated this technique by connecting to a MySQL database in the article titled "MySQL Database Access from RPG" (article ID 52433 at iSeriesNetwork.com). You can read it at the following link:
http://www.iseriesnetwork.com/article.cfm?id=52433

The problem with the technique that I used in that article is that the Java class name of the MySQL driver has to be hard-coded in the prototype. Because of that, if you want to use a different driver, you have to change the code and recompile it.

In the new version, instead of using RPG's support for Java methods to load the driver, I call the Java Native Interface (JNI) APIs directly. The advantage to doing so is that the class name doesn't have to be hard-coded into the service program. Instead, you can pass the class name of the JDBC driver as a parameter, and it loads that class and uses it for the JDBC connection.

In the previous article, you loaded the MySQL driver and connected to the database as follows:

         conn = MySql_Connect( 'text.example.com'
                             : 'myDatabaseName'
                             : %trim(userid)
                             : %trim(passwrd) );

This code calls a subprocedure in my JDBC service program. That subprocedure accepts parameters for the server to connect to, the name of the database on that server, and the user ID and password to sign in with. It uses those parameters to establish a JDBC connection. To maintain backward compatibility, I kept this MySQL_Connect() subprocedure the same in the new version of the JDBC service program.

I added a new subprocedure that does the same thing as MySQL_Connect(), except that it can connect with any JDBC driver that you've installed. For example:

         conn = JDBC_Connect( 'com.mysql.jdbc.Driver'
                            : 'jdbc:mysql://test.example.com/myDatabaseName'
                            : %trim(userid)
                            : %trim(passwrd) );

This produces a database connection to a MySQL server just as the MySQL_Connect() routine does. Except that in this case, I provide the name of the Java class for the JDBC driver in the first parameter, and a JDBC URL in the second. If I want to use the Java Toolbox driver for accessing an iSeries database, I can code the following:

         conn = JDBC_Connect( 'com.ibm.as400.access.AS400JDBCDriver'
                            : 'jdbc:as400://as400.example.com
                            : %trim(userid)
                            : %trim(passwrd) );

Simply by changing the class name in the first parameter and the JDBC URL in the second parameter, I can use a different JDBC driver for a different database. Now I can run SQL statements on the DB2/400 server instead of a MySQL server. After this connection is established, I can use the same procedure for running SQL statements that I used with the MySQL database. Take a look at the April 20 article for a demonstration of running SQL statements over a JDBC connection.

The MySQL_Connect() and JDBC_Connect() subprocedures can pass two properties to the JDBC server, the user ID and the password. Some database servers support additional properties that let you control other aspects of the connection. I created a JDBC_ConnProp() subprocedure that lets you pass a Java properties object instead of passing only a user ID and password. That way, you can set any properties that you want.

To do that, you need to call the JDBC_Properties() subprocedure to create a Java properties object. Then you can call the JDBC_SetProp() subprocedure to set the value of each property that you want to pass.

When you call JDBC_ConnProp(), you pass the Java properties object, and all the properties are passed on to the server. When you're done connecting, you call JDBC_FreeProp() to free the memory used by the properties.

Here's an example of connecting to an iSeries database and passing some additional properties:

         prop = JDBC_Properties();
         JDBC_setProp(prop: 'prompt'  : 'false');
         JDBC_setProp(prop: 'user'    : %trim(userid));
         JDBC_setProp(prop: 'password': %trim(password));
         JDBC_setProp(prop: 'errors'  : 'full');
         JDBC_setProp(prop: 'naming'  : 'system');

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

The preceding example sends the prompt, errors, and naming properties to the server in addition to the user and password properties available to JDBC_Connect(). After the connection is established, you can run SQL statements over the connection, just as you would've done with JDBC_Connect() or MySQL_Connect().

Here's an example that sets properties for a MySQL connection:

         prop = JDBC_Properties();
         JDBC_setProp(prop: 'user'          : %trim(userid));
         JDBC_setProp(prop: 'password'      : %trim(password));
         JDBC_setProp(prop: 'useSSL'        : 'true');
         JDBC_setProp(prop: 'useCompression': 'true');
         JDBC_setProp(prop: 'connectTimeout': '30');

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

Different JDBC drivers support different properties. Refer to the documentation for the JDBC driver to find out which properties it supports.

Although the samples that I've demonstrated use MySQL and iSeries databases, you can use any pure Java JDBC driver. Refer to the documentation for the JDBC driver to find the appropriate class name, URL, and properties.

You can download the updated JDBC service program, and programs that demonstrate calling it, from the following link:
http://www.pentontech.com/IBMContent/Documents/article/52577_138_RpgAndJdbc.zip