DB2/AS400 JDBC Driver Returns Char Data in Binary Format
Related Categories: ColdFusion
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.
Thank you for sharing it in your blog