I have an app using Hibernate attaching to a DB2/400 (V5R2) database on an iSeries. All is working fine with my regular HQL stuff. Recently I had a need to do a native SQL query. I set up my query and it ran fine, with one issue. The native query return the data in HEX format, not readable at all. Any ideas what might be causing this?

Thanks

More info...

Using Hibernate 2.1.7

My cfg file..

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="dialect">net.sf.hibernate.dialect.DB2400V5R2Dialect</property>
<property name="connection.driver_class">com.ibm.as400.access.AS400JDBCDriver</property>
<property name="connection.url">jdbc:as400://myas400</property>
<property name="connection.username">myuserid</property>
<property name="connection.password">mypassword</property>
<property name="show_sql">false</property>
<property name="connection.naming">system</property>
<property name="connection.transaction isolation">none</property>
<property name="connection.provider_class">net.sf.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">46000</property> <!-- seconds -->
<mapping
My Mappings
</session-factory>
</hibernate-configuration>

I found my fix on an IBM web forum. Here is what I found..

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 OS/400 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 server 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.

I made the change to my URL and that fixed it.

Four years after...

I'm having a variation of this problem. When I don't append "translate binary=true" the IBM JDBC driver returns byte[]. When I do append, it returns Character. I want an ASCII String. The byte[] is EBDCIC which I now translate to ASCII, an incredible pain in the neck. I found an IBM forum for the JT400 toolkit which has the DB2 JDBC driver, but no help.