In the May 18, 2006, issue of this newsletter, I demonstrated how RPG's support for calling Java methods makes using JDBC possible. This support lets you access databases designed for Oracle, Microsoft, IBM, and MySQL systems, to name a few.

Since I published that article, I've received numerous requests for information about how to set and retrieve null values using my RPG code. In this article, I discuss how to do that and also provide an updated copy of my JDBC wrapper service program, which makes working with nulls easier.

If you tried my original code, you might have encountered the situation in which programs with the Java Virtual Machine (JVM) exited when the program tried to read a null field. That's because the code wasn't written to understand null values. Consider the following code snippet from the JDBC_getCol() subprocedure from the original article:

    monitor;                              
       jdbc_begin_object_group(5);        
       result = r(getColString(rs: col)); 
       jdbc_end_object_group();           
       return result;                     
    on-error;                             
       return ';                         
    endmon;

The problem is that when a column contains a null value, getColString() returns the special value of *NULL. However, this code doesn't check for that value! Instead, it feeds the result into r() — a shortcut for the toBytes() method of the String class. This results in an error because toBytes() dislikes receiving a *NULL!

To fix that problem, I restructured the code a bit so that it now reads as follows:

     jdbc_begin_object_group(5);
     str = getColString(rs: col);
     if (str = *NULL);
        result = ';
        null = *ON;
     else;
        result = r(str);
     endif;
     jdbc_end_object_group();

As you can see, the code checks immediately whether the value returned from getColString() is *NULL. If it is, the code sets the return value to an empty string and never tries to convert it to an RPG string. This way, no error is triggered in the JVM.

The code also handles an optional indicator value. If you pass an extra parameter to JDBC_getCol(), it sets that parameter to *ON if the value is null, and *OFF otherwise. That way, it works a little more like the null indicator support that we're used to using in RPG.

For example, the following is an SQL statement that gets a list of item numbers and descriptions from the ItmMast table in my company's MySQL database and prints the list to a report. Because the description can be null, the code checks the null indicator and prints **Undefined if the description is null:

         rs = jdbc_ExecQry( conn : 'Select ItemNo,Description'
                                 + '  from ItmMast'
                                 );
         dow (jdbc_nextRow(rs));
             ItemNo = %int(jdbc_getCol(rs: 1));
             Desc   = jdbc_getCol(rs: 2: is_null);
             if (is_null);
                Desc = '**Undefined';
             endif;
             except;
         enddo;

         jdbc_freeResult(rs);

Because the null indicator is an optional parameter, existing code can continue to call JDBC_getCol() without changes. In fact, you should be able to install my new JDBC wrappers and use them in new programs without even needing to recompile or rebind the existing programs. The new service program is completely backward compatible!

I also updated the routines for setting column values in prepared statements so that they accept a null indicator. Once again, I used an optional parameter so that you need to pass the null indicator only if it's needed.

Consider the following code:

             stmt = JDBC_PrepStmt(conn : 'Insert Into ItmTest '
                                     + '(ItemNo, Count, LastChg, LastSold, '
                                     + 'TimeSold, Price, Description)'
                                     + ' values (?,?,?,?,?,?,?)' );
             if (stmt = *NULL);
                ErrMsg = 'Prepare Statement failed!';
                return;
             endif;

             JDBC_setDecimal  (stmt: 1: 10004 );
             JDBC_setInt      (stmt: 2: 0           : *ON );
             JDBC_setTimeStamp(stmt: 3: %timestamp(): *OFF);
             JDBC_setDate     (stmt: 4: %date()     : *OFF);
             JDBC_setTime     (stmt: 5: %time()     : *OFF);
             JDBC_setDouble   (stmt: 6: 0           : *ON );
             JDBC_setString   (stmt: 7: Desc        : is_null );

             rc = JDBC_ExecPrepUpd( stmt );
             if (rc < 0);
                ErrMsg = 'Execute Prepared Failed!';
                return;
             endif;

             JDBC_FreePrepStmt( stmt );

This code prepares an SQL statement to insert a new record into the ItmMast table. The first column is set to 10004. It doesn't pass a null indicator, so the field is never null. The next column sets an integer field in the database, but it does pass a null indicator, and this one is *ON, so the field is set to null. The next three columns contain a timestamp, date, and time, and I've set them to the current system date, time, and timestamp. I do pass a null indicator, but it's set off, so the fields aren't set to null. The sixth column is set to null, again by passing *ON for the null indicator. The last column is the description that I read in the previous SQL statement. I pass a variable for the null indicator — in fact, the same variable that I retrieved in the previous Select statement. That way, if the description field is null, a null value is written here. If it isn't null, the value from the Select statement is written.

You can download JDBCR4, my updated service program that has null support, from the following link:
http://www.pentontech.com/IBMContent/Documents/article/52741_77_RpgAndJdbc.zip

Read my previous articles about this subject at the following links:

JDBC from RPG
http://www.iseriesnetwork.com/article.cfm?id=52577

MySQL Database Access from RPG
http://www.iseriesnetwork.com/article.cfm?id=52433