How to translate CCSID 65535 in SQuirrel from a DB2 on an iseries
I am new to SQuirrel SQL. I need some help to translating CCSID 65535 into ASCII, UNICODE (or anything human readable)
I am using the JDBC driver per the following guide.
According to IBM's website:
What character conversion issues must my program deal with? The IBM i database uses EBCDIC to store text. Java uses Unicode. The JDBC driver handles all conversion between character sets, so your program should not have to worry about it.
but I think they refer to CCSID 37 and not 65535(Hex).
I have got the following info, from my DB2 DB
Doing DSPFD gives me:
Coded character set identifier . . . . . . : CCSID 65535
Doing DSPFFD gives me:
TXT CHAR 3 3 41 Both Text
Field text . . . . . . . . . . . . . . . : Text Coded Character
Set Identifier . . . . . : 65535
But the SQuirrel query result for the TXT field is:
5c c1 c4 c4 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 c1 40 7e 40 c2 40 4e 40 c3 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40
Which should be translated to something like:
*ADD A = B + C
Answer
From the JDBC FAQ:
Why is the Toolbox JDBC returning EBCDIC characters to my Java program?
Normally, the Toolbox JDBC driver will translate EBCDIC characters to Unicode Strings automatically. If it appears to be returning untranslated EBCDIC characters instead, then it is likely that the field in the IBM i database is tagged with CCSID 65535. The Toolbox JDBC driver recognizes this CCSID as a field that should not be translated. To avoid this behavior, tag fields on the IBM i that you want to be translated, with a valid CCSID. Alternately, you can set the "translate binary" connection property to "true", which instructs the JDBC driver to translate all fields, including those tagged with CCSID 65535. The easiest way to do this is to add
";translate binary=true"
to the end of the URL used when connecting to the database.