Wednesday, June 12, 2013

Read Character Columns with CCSID 65535 in JDBC

In DB2 on the System i every Character Column have a CCSID which defines with which characterset the data of this column is encoded. For example a column with german characters should normally be encoded with 273 or 1141. When you access this columns from JDBC the data will be automatically converted from the characterset used in the column to Unicode. But when the CCSID of the column is 65535 (binary data) the jdbc driver will not convert and since all data on the System i is stored in EBCDIC you will only get garbage from this columns.

But there is an easy way to fix this problem. You need to set the translateBinary Option to true when you connect to your System i and every 65535 character field will be translated from EBCDIC to Unicode automatically.

  AS400JDBCDataSource dataSource = new AS400JDBCDataSource(systemName, userName, password);
  Connection conn = dataSource.getConnection();

  // Or when you prefer the old connection Method
  Connection conn2 = DriverManager.getConnection("jdbc:as400://" + systemName + ";translate binary=true",
    userName, password);

Then the result is human readable.

No comments:

Post a Comment