DB2/AS400 JDBC Driver Returns Char Data in Binary Format
So I'm working on this project right now that requires me to connect to an old JD Edwards system running on a DB2/AS400 platform. For the past two days I've been ripping my hair out trying to figure out why certain tables/columns returned from the JDBC driver (called JT400) are in Binary format. Come to find out that a lot of older AS400 systems use a data format called EBCDIC, which is basically a alternate to ASCII that's built for easy compatibility with punchcards. Yes, you read that right - PUNCHCARDS.
Basically, everything I expected to be a varChar was coming back from the AS400 JDBC Driver as a Binary/ByteArray object in CFMX. I looked high and low for an explanation and finally found the JDBC configuration options for the JT400 Driver. Looks like there is a connect string option that tells the JDBC driver to convert the binary types into varChar!
Once I found this information, all I had to do was add "translate binary=true;" to the end of the JDBC URL and all was good! A couple of notes here though... 1) Yes, there is a space in there, and yes, you should leave it in there. 2) Case matters - lowercase the whole thing or it's not going to work.
There's actually quite a bit that can be tweaked with the JT400 driver, but finding it sometimes can be real interesting.
# Posted By Rob Brooks-Bilson | August 21, 2006 5:36 PM
Thank you for sharing it in your blog
# Posted By Webb | November 8, 2007 3:24 PM
# Posted By Pablo | May 27, 2008 10:34 PM
Thanks a lot!
# Posted By Erik | July 1, 2009 11:48 AM
# Posted By Cameron Childress | July 1, 2009 11:52 AM
The root cause for this problem is a configuration problem on your AS/400 (iSeries, i/5, System i). On an AS/400 every table have an CCSID (Character set id). This character id controls how data should be translated if the reading job is using a different character id than the data stored in the table. But for some reason there is a special CCSID 65535 (binary data). If you configure your table for this special CCSID no conversion occurs and you got your problem. So the use of the mentioned option in your post should be only a temporary workaround. It ist better to change the tables to the correct CCSID for your country.
# Posted By Ralf M Petter | November 25, 2010 8:37 AM