Q: I'm using your JDBCR4 tool to access a database on another server. It works well as long as I want my data returned as single-byte EBCDIC text, but if there are any double-byte characters, I have problems. How can I handle double-byte data in JDBCR4?

A: This article provides additional routines for JDBCR4, and these routines provide Unicode support.

Over the last two years, I've published a series of articles that discuss how to access data on an external database server (e.g., MySQL, Microsoft SQL Server, Oracle) from an RPG program. By interfacing RPG with Java, I can use Java's JDBC drivers to connect to almost any type of database. I've provided an RPG service program called JDBCR4 that you call from your ILE RPG programs to access these external databases. In this article, I add Unicode support to JDBCR4, and I also provide links to previous articles about using JDBC from RPG.

How Does Unicode Work in RPG?

RPG has its own built-in support for UCS-2 Unicode. Instead of declaring your variable as A=Alphanumeric, you declare it as C=UCS-2 Character, and then you use it exactly the same way you'd use any other character string. The %char() function will convert a UCS-2 field to an alphanumeric (EBCDIC) field, and the %ucs2() function will convert the other direction, from EBCDIC to UCS-2 Unicode.

For example, if I wanted to declare an RPG field that's 100 characters long, I can declare it as 100C if it want Unicode, or 100A if I want regular EBCDIC data.

     D myUnicode       s            100C  
     D myEbcdic        s            100a  
      /free                               
         myUnicode = %ucs2('Hello World');
         myEBCDIC = %char(myUnicode);     

The advantage of the Unicode field is that it supports all the characters supported by Unicode, which means almost every character used in every language. The same Unicode variable can store both English text and Korean text without doing anything fancy, because Unicode supports all the characters from both languages.

How Do You Use It in JDBCR4?

Java uses Unicode for everything internally, so it was very easy to ask RPG to pass Unicode data to Java instead of EBCDIC data. The only tricky part is that my routines need to know whether you're passing EBCDIC to them or whether you're passing Unicode to them, so I needed to use separate subprocedures for Unicode vs. EBCDIC. Here are the new subprocedures I added to JDBCR4:

With these new routines, working with the data as Unicode is just as easy as working with the data in EBCDIC. Just declare your variables as Unicode and call the Unicode procedure instead of the EBCDIC one. No other changes needed!

Code Download

You can download the new JDBCR4 with Unicode support from the following link:
http://systeminetwork.com/files/RpgAndJdbc.zip

Previous Articles

Here are links to the previous articles I've written about using JDBC from RPG. NOTE: The code from this article (above) contains all sample programs from the following articles, but also contains bug fixes. Please use this article's code.

• MySQL Database Access from RPG
• JDBC from RPG
• Handle Null Values in JDBC from RPG
• Access MS SQL Server Database from an RPG Program
• JDBC from RPG Enhancements
• JDBC from RPG by Column Name
• JDBC from RPG with Multiple Result Sets