How to convert IBM DB2 hexadecimal data in proper format (CCSID 37) at connection level without using CAST function

Question

In my application I am having IBM DB2 database as storage and my data service layer has been implemented using Node.js. I have established JDBC connection to IBM DB2 iSeries database by DataDirect approach given by Progress using db2.jar. When I execute any select query the result returned from DB is a hexadecimal value not a proper what I want. To solve this problem I have an option to use CAST function at query level with each column, but this is not so efficient as I have to apply this CAST in each column so I am trying to have a generic solution at connection level so that I do not have to apply this cast in each column just like "translate binary =true" in JTOpen.

Below are the result with select query -

Without CAST function :
Query =  SELECT poMast.ORDNO from AMFLIBL.POMAST AS poMast WHERE poMast.ORDNO = 'P544901'
Result in Hex format = D7F5F4F4F9F0F1


With CAST function :
Query = SELECT CAST(poMast.ORDNO CHAR(7) CCSID 37) AS ORDNO from AMFLIBL.POMAST AS poMast WHERE poMast.ORDNO IS NOT NULL
Result in proper format = P544901

Connection URL = "jdbc:datadirect:db2://hostname:port;DatabaseName=dbname;"

Any help will be appreciated.

Answer 1

may be try to modify your connexionstring like this

Connection URL = "jdbc:datadirect:db2://hostname:port;DatabaseName=dbname;translate binary=true;ccsid=37"

or like this

Connection URL = "jdbc:db2://hostname:port;DatabaseName=dbname;translate binary=true;ccsid=37"