6

I am connected to IBM DB2 database with java but data is stored as binary format in database so when I fetch any value it comes as binary or hexdecimal format. How can I convert this in binary data in utf-8 at query level.

Sample code to fetch data -

String sql = "SELECT poMast.ORDNO from AMFLIBL.POMAST AS poMast "; Class.forName("com.ddtek.jdbc.db2.DB2Driver"); String url = "jdbc:datadirect:db2://hostname:port;DatabaseName=dbName;"; Connection con = DriverManager.getConnection(url, "username","password"); PreparedStatement preparedStatement = con.prepareStatement(sql); ResultSet rs = preparedStatement.executeQuery(); System.out.println("ResultSet : \n"); System.out.println(" VNDNO"); while (rs.next()) { System.out.println(rs.getString("ORDNO")); }
Improve this question
0

3 Answers 3

Reset to default
1

You probably need to use the CAST expression:

SELECT CAST(poMast.ORDNO as VARCHAR(50)) from AMFLIBL.POMAST AS poMast

Adjust the VARCHAR length to your needs. The string is in the database codepage (often UTF-8 these days) and converted to the client/application codepage when fetched.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

you can "cast" the result from your select to utf8 like below.

String sql = "SELECT poMast.ORDNO, CAST(poMast.ORDNO AS VARCHAR(255) CCSID UNICODE) FROM AMFLIBL.POMAST AS poMast ";

src: cast db2

Improve this answer

1 Comment

@MilanSoni i have updated my answer, please check if this works.
0

In my case, somehow bad UTF-8 data had gotten into varchars in a 1208/UTF-8 DB. Prior to conversion, when querying such data via the JDBC driver, the DB returned -4220 via the JDBC driver. This is fixable at the JDBC driver level by adding this property: java -Ddb2.jcc.charsetDecoderEncoder=3 MyApp see: https://www.ibm.com/support/pages/sqlexception-message-caught-javaiocharconversionexception-and-errorcode-4220

The Db2 LUW Command Line Processor fixed it long ago as an APAR, so this error is only seen via the JDBC driver when the above property is not set.

But, if you want to fix the data in the db, this works:

update <table_name> set <bad_data_col> = cast(cast( <bad_data_col> as vargraphic) as varchar);

1st db2 treats (casts) the bad data as a binary where "anything goes" and then converts (casts) it back to valid UTF-8. After the casts, the JDBC driver shows the same result with or without the special property set and returns no errors.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.