Over the past few weeks, I've received quite a few messages from people who want help accessing a Microsoft SQL Server database from their RPG programs.

We Have a Great Database, Why SQL Server?

In today's world, the ability to communicate from one system to another is critical. It's important that programs running on other computers can access the i5/OS database, and it's just as important that programs running on i5/OS can access other databases.

That's a bit of a problem, isn't it? All the major database manufacturers make drivers for Windows that let programs using ODBC, OLEDB, ADO, and so forth access their databases. By contrast, very few database manufacturers have created native drivers for i5/OS. IBM hasn't been much help, either. They give us the ability to communicate with DRDA-compliant databases, but that's about it. What's a poor RPG programmer to do?

Fortunately, every major database manufacturer makes drivers for Java. Because Java code can run on any platform (including i5/OS) without changes, we're able to use Java drivers to communicate with other databases. As long as a database driver is "pure Java", it will run just fine on i5/OS, even if the manufacturer had Linux, Mac, or Windows in mind when writing it.

ILE RPG programs are able to call Java methods, so RPG programmers can use Java database drivers. Presto! With a little bit of plumbing, we can use any Java database driver in RPG on an i5/OS system. I think that's very cool.

Some Background

I originally needed the ability to talk to a MySQL database. Where I work, we use PHP and MySQL to host our web server, but all our main business runs on i5/OS using RPG. To synchronize the two business applications, I used an RPG program to update and read from the MySQL database. I wrote an RPG service program that hides all of the complexity of the Java database logic. That way, I don't have to muck around with the Java code each time, I can just run an RPG subprocedure to execute an SQL statement. I discussed my MySQL support in a previous article, you can read it at the following link:
http://www.systeminetwork.com/article.cfm?id=52433

That article is very particular to MySQL, which worked well for me at the time, but I've discovered that a lot of people are interested in other databases. I thought "Why should my tool only work with MySQL? The same basic procedure should work with any database." So, I changed my tools around a little bit and made them general enough to let you load and work with any Java JDBC driver — as long as it's a "pure Java" (type 4) driver, that is.

The following articles discuss the more generalized JDBC interface that I've provided, and show examples of using it with both MySQL and our own DB2/400 databases:
http://www.systeminetwork.com/article.cfm?id=52577
http://www.systeminetwork.com/article.cfm?id=52741

SQL Server

I have to admit, I'm not a Microsoft guy. I don't use SQL Server. I knew that it should work the same way as any other JDBC database, but because I don't use SQL Server, I can't provide an example.

Fortunately, I discovered that Jon Juracich, a well-respected participant in the System iNetwork forums, had adapted my JDBC database tool to work with a Microsoft SQL Server database. He was kind enough to provide some sample code.

Since the JDBC driver is a Java tool, you need to install Java on i5/OS before you can use it. The Microsoft JDBC driver requires JDK version 1.4 or better.

Microsoft provides a type 4 JDBC driver for Microsoft SQL Server on their web site. "Type 4" means that the driver is written in pure Java, which is important because if the driver relies on any C or C++ code, it probably won't be compatible with i5/OS. You can download the JDBC driver from the following link:
http://msdn.microsoft.com/data/jdbc/default.aspx

Microsoft supplies Windows and Unix versions of the driver. As far as I can tell, the only difference between them is the way you unzip them. The Windows one is a self-extracting zip file, whereas the Unix one is a tar.gz file. If you use a Windows desktop, download the Windows version and run it to unzip it to a temporary location.

Most of what you'll find inside the downloaded file is documentation. The actual database driver is in the file named sqljdbc.jar. This JAR file is the one you'll have to upload to your System i. You can put it anywhere you like in the IFS. If you put it into the following directory, however, it'll be easy for Java to find it:

/QIBM/UserData/Java400/ext

If you put it into a different directory, you'll need to make certain that the JAR file is listed in your Java CLASSPATH before you try to run anything that uses the Java Virtual Machine (JVM) in each job that wants to use the driver.

The RPG Code

My RPG routines that interface with Java's JDBC engine are located in a service program named JDBCR4 (see the links at the end of the article for information about how to obtain it). Comments at the top of the JDBCR4 service program tell you how to compile it. These comments also describe how to create a binding directory so your RPG programs can find the service program when they need it.

Jon's sample code follows suit by providing the binding directory in the H-spec of the program. Because the binding directory is listed in the H-spec, you can easily compile the program with the CRTBNDRPG command, or option 14 from PDM. A copy book brings in the prototypes and custom code needed to call my service program — this way, it's easy to write lots of RPG programs that use it without duplicating any of the code.

     H DFTACTGRP(*NO)
     H OPTION(*NODEBUGIO:*SRCSTMT)
     H BNDDIR('JDBC')

      /copy jdbc_h

In Jon's example, he did not install the JDBC driver into the /QIBM/UserData/Java400/ext directory, so he needed to make sure that it was in his CLASSPATH. He elected to add code to the RPG program that checks for the JAR file and adds it if necessary. Here's how he did that:

     D getenv          PR              *   EXTPROC('Qp0zGetEnvNoCCSID')
     D  name                           *   VALUE OPTIONS(*STRING)

     D putenv          PR              *   EXTPROC('Qp0zPutEnvNoCCSID')
     D  name                           *   VALUE OPTIONS(*STRING)

     D EnvVal          S            256A   VARYING
     D EnvValChanged   S               N   INZ(*off)
         .
         .
         EnvVal = %str(getenv('CLASSPATH'));
         if %scan('.:':EnvVal) = *zeros;
           eval EnvVal = '.:' + EnvVal;
           eval EnvValChanged = *on;
         endif;
         if %scan('/sqljdbc.jar':EnvVal) = *zeros;
           eval EnvVal = EnvVal + ':/home/DUBMAPJJ01/sqljdbc.jar';
           eval EnvValChanged = *on;
         endif;
         if EnvValChanged;
           putenv('CLASSPATH=' + EnvVal);
         endif;

The preceding code uses the getenv() API to retrieve the current value of the CLASSPATH environment variable. If it doesn't find the JAR file, it adds it to the end of the CLASSPATH, and then uses the putenv() API to change it.

It's important to note that the preceding code only has an effect if this is the first time the JVM is used in the job. The CLASSPATH variable is checked the first time Java starts the JVM and is not updated thereafter.

Before you connect to SQL server, you have to provide some properties. These variables will be sent along with the connection request so you can control how the connection is set up. Here are the ones that Jon uses in his sample code:

     D prop            s                   like(Properties)
         .
         .
         prop = JDBC_Properties();
         JDBC_setProp(prop: 'userName'     : 'scottklement');
         JDBC_setProp(prop: 'password'     : 'bigboy');
         JDBC_setProp(prop: 'databaseName' : 'ClarifyProd');

The prop variable is just a property list that can store various properties (in this case, the userName, password, and databaseName properties). The databaseName property sets the default collection that will be accessed on the SQL server.

Now you can connect to the SQL server database. Here's how Jon does that:

     D conn            s                   like(Connection)
         .
         .
         conn = JDBC_ConnProp('com.microsoft.sqlserver.jdbc.SQLServerDriver'
                             :'jdbc:sqlserver://myserver.example.com:1433'
                             : prop );
         JDBC_freeProp(prop);

         if (conn = *NULL);
             return;
         endif;

In the preceding code, conn is a Java object that we use when we want to refer to this particular database connection.

The JDBC_ConnProp() subprocedure is a routine in JDBCR4 that connects to a database using a JDBC driver. The first parameter tells Java the fully qualified name of the Java class that implements the database driver. com.microsoft.sqlserver.jdbc.SQLServerDriver is the name of the JDBC driver for SQL Server. This is the Java class that the system will look for in the /QIBM/UserData/Java400/ext directory or in the CLASSPATH. If all is well, it will be found in the sqljdbc.jar file that you uploaded, and the driver will be loaded into memory.

If you want to interface with a different database (e.g., Oracle, Sybase, MySQL), you use a different Java class name in the first parameter. That's the main thing that needs to change if you use a different driver. See the documentation for your JDBC driver to find out the correct class name.

The second parameter is the connection string. In this case, the connection string contains jdbc: to tell you that it's using a JDBC driver, followed by sqlserver: to state that it's an SQL server driver. This is then followed by the TCP/IP host name and port number that you want to connect to. Make sure you change the host name to the correct one for your environment!

The last parameter to the JDBC_ConnProp() procedure is the list of properties you want to use when connecting. This is the prop object that contains the userName, password, and databaseName.

The JDBC_ConnProp() routine will return a *NULL if it fails, or a JDBC Connection object if successful.

Once connected, you can create an SQL statement to execute on the server. This statement needs to be one that the SQL server will understand, and it works a little bit differently from embedded SQL.

     D prepstm         s                   like(PreparedStatement)
          .
          .
         prepstm = JDBC_prepStmt(conn:
         'SELECT dbo.table_case.id_number ' +
           'FROM  dbo.table_site INNER JOIN ' +
           'dbo.table_case ON dbo.table_site.objid = ' +
           'dbo.table_case.case_reporter2site INNER JOIN ' +
           'dbo.table_x_cat_log ON dbo.table_case.objid = ' +
           'dbo.table_x_cat_log.x_cat_log2case INNER JOIN ' +
           'dbo.table_contact ON dbo.table_case.case_reporter2contact = ' +
           'dbo.table_contact.objid ' +
         'WHERE (dbo.table_site.site_id = ?) AND ' +
           '(dbo.table_contact.last_name = ?)  AND ' +
           '(dbo.table_contact.first_name = ?) AND ' +
           '(dbo.table_contact.phone = ?) AND ' +
           '(dbo.table_x_cat_log.x_case_title = ?) AND' +
           '(dbo.table_x_cat_log.x_c1 = ?) AND ' +
           '(dbo.table_x_cat_log.x_c2 = ?)'   );

         if (prepstm = *NULL);
             jdbc_close(conn);
             return;
         endif;

The preceding code calls the JDBC_PrepStmt() procedure to prepare an SQL statement to be run on the SQL server. The first parameter to JDBC_PrepStmt() is the reference to the JDBC connection. The second parameter is the SQL statement that is to be executed. As you can see, it's a typical SELECT statement that my SQL Server will understand.

However, you cannot embed RPG variables in the statement as you would with embedded SQL — at least not directly. Instead, the preceding code uses parameter markers. Did you notice the ? characters in the SELECT statement? Each one is to be replaced by an RPG variable. The question marks are numbered, so the first one in the preceding statement is #1, the second one is #2, and so on.

Jon's example sets the parameter values using the JDBC_setString() procedure from JDBCR4. Here's his code:

         JDBC_SetString(prepstm:1:'OH001');
         JDBC_SetString(prepstm:2:'SOX');
         JDBC_SetString(prepstm:3:'PATP');
         JDBC_SetString(prepstm:4:'888-888-2490');
         JDBC_SetString(prepstm:5:
           'Elevated Profile Usage: ' +
           'CAHDTK02_DBEDTKRC01_20060929_M456711_RUSH.' +
           'CABALQUINTO@CA');
         JDBC_SetString(prepstm:6:'AS400');
         JDBC_SetString(prepstm:7:'Distrack');

In this logic, the first ? will be replaced by OH001, the second ? by SOX, and so forth. That's how you insert RPG variables into the statement.

In addition to JDBC_setString(), there are also routines named JDBC_setInt(), JDBC_setDouble(), JDBC_setDecimal(), JDBC_setDate(), JDBC_setTime(), and JDBC_setTimestamp() that can be used with different RPG data types if your variable isn't a string. The JDBC_setDecimal() routine is the correct one for packed and zoned numbers.

Once you've filled in the parameters, you can execute the SQL statement. In JDBC calls, two routines execute SQL statements: executeQuery() and executeUpdate(). ExecuteQuery is for the SELECT statement or any other statement that returns a result set. ExecuteUpdate is for statements such as INSERT, UPDATE, CREATE TABLE, and so forth, that do not return a result set.

My JDBCR4 service program provides procedures named JDBC_ExecPrepQry() and JDBC_ExecPrepUpd() for executing a query, and an update, respectively.

Jon's example is a SELECT statement, so it uses JDBC_ExecPrepQry() as follows:

     D rs              s                   like(ResultSet)
     D CaseID          s             15A
         .
         .
         rs = jdbc_ExecPrepQry(prepstm);

         dow (jdbc_nextRow(rs));
             CaseID      = jdbc_getCol(rs: 1);
     ... add your own code here that works with the CaseID field ...
         enddo;

The JDBC_ExecPrepQry() procedure runs the SQL statement and returns the result set (which is similar to a "cursor" in embedded SQL). In this case, my result set is referenced by a variable named rs.

The JDBC_nextRow() procedure advances to the next available row (that's "record" in RPG parlance) in the result set. You can then read each column (or "field") in the result with the JDBC_getCol() procedure.

Columns are not referenced by field name. Instead, the first column output by the SELECT statement is called column #1, the second one is column #2, and so forth. In the preceding code, only one column is returned. Jon stores the value of this column into the CaseID variable. There's nothing special about CaseId; it's an ordinary RPG character variable that you can use in your business logic as needed.

When you're done with the SQL statement, you should tell JDBC to free up the result set and the prepared statement so that the memory can be freed up and returned to the system. This is done by calling the JDBC_FreeResult() and JDBC_FreePrepStmt() procedures, respectively. To disconnect from the database, you should also call the JDBC_Close() procedure.

         JDBC_FreeResult(rs);
         JDBC_FreePrepStmt(prepstm);
         JDBC_Close(conn);

Code Download

You can download my JDBCR4 service program and Jon Juracich's sample program from the following link:
http://www.pentontech.com/IBMContent/Documents/article/54759_301_RpgAndJdbc.zip