Insert/update Hex value to DB2 Z/OS PreparedStatement

Question

I need to insert in my database a Hex value with a PreparedStatement in Java.

I can do a select with Hex function for get my value:

String id = "02658947530232010038892587183C";
String request = "SELECT HEX(COLUMN) FROM TABLE WHERE HEX(COLUMN)=?";
Connection con = getConnectionDb2z();
ResultSet rs = null;
try(PreparedStatement select = con.prepareStatement(request))
{
    insert.setString(1, id);
    rs = select.executeQuery();
    rs.next();
    System.out.println(rs.getString("COLUMN"));
}
catch(SQLException e)
{
    throw e;
}
finally
{
    con.close();
}

My result is 02658947530232010038892587183C. (this is an example).

But now i want insert a value in my table. If i try:

String id = "02658947530232010038892587183C";
String request = "INSERT INTO TABLE (COLUMN) VALUES (X'?')";
Connection con = getConnectionDb2z();
try(PreparedStatement insert = con.prepareStatement(request))
{
    insert.setString(1, id);
    insert.executeUpdate();
}

Java does not recognize ? parameter from "(X'?')` and i have an error:

Exception in thread "main" com.ibm.db2.jcc.am.SqlSyntaxErrorException: invalid parameter.

If i try:

String id = "X'02658947530232010038892587183C'";
String request = "INSERT INTO TABLE (COLUMN) VALUES (?)";
Connection con = getConnectionDb2z();
try(PreparedStatement insert = con.prepareStatement(request))
{
    insert.setString(1, id);
    insert.executeUpdate();
}

I also get an error:

Exception in thread "main" com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001

Because it tries to insert X'02658947530232010038892587183C' as a string.

The only way I find to work it's this:

String id = "02658947530232010038892587183C";
String request = "INSERT INTO TABLE (COLUMN) VALUES (X'?')";
Connection con = getConnectionDb2z();
try(PreparedStatement insert = con.prepareStatement(request.replace("?", id))
{
    insert.executeUpdate();
}

However, this approach is not viable. Is there a way to do this properly ?

For reminder my database is a DB2 Z/OS and the function UNHEX doesn't exist.

More explanation:
In my request, X'?' is a function that is used to pack hexadecimal values.

The length COLUMN in my TABLE is 7. My length id is 14, I need to pack this id to insert him in my bdd. And when I want to read it in my java code.

I unpack the id with the function HEX(id). But in DB2 Z/OS the function UNHEX dosen't exist, we need to use X'id', but like you can see PreparedStatement dosen't recognize this as a function.

When i pack this id 02658947530232010038892587183C with the function X'' the result in data base with SELECT COLUMN FROM TABLE is Áiåë�i g (the result you see is not the exact one I get because there are characters that the site cannot read).

And if i use the function HEX() in my select for unpack the id SELECT HEX(COLUMN) FROM TABLE the result will be I updated my question to show what I need to get when I pack my id. 02658947530232010038892587183C.

Answer 1

Why don't you just convert it from Java?

String id = "0038892587183C";
int idAsInt=Integer.parseInt(id, 16);
String request = "INSERT INTO TABLE (COLUMN) VALUES (?)";
Connection con = getConnectionDb2z();
try(PreparedStatement insert = con.prepareStatement(request))
{
    insert.setInt(1, idAsInt);
    insert.executeUpdate();
}

This converts id from hexadecimal to an int using Integer.parseInt using a custom base 16 in order to parse it as a hexadecimal number.

Then, it uses PreparedStatement#setInt for setting the parameter without the X''.

If your hex number exceeds the signed 32bit limit of int, you would need to use long (signed 64bit) or BigInteger. Unfortunately, JDBC doesn't allow passing a BigInteger directly, so you would need to use a solution like these mentioned here:

String id = "0038892587183C";
BigInteger idAsBigInt=new BigInteger(id, 16);//parse it to a BigInteger
String request = "INSERT INTO TABLE (COLUMN) VALUES (?)";
Connection con = getConnectionDb2z();
try(PreparedStatement insert = con.prepareStatement(request))
{
    insert.setBigDecimal(1, new BigDecimal(idAsBigInt));//or possibly insert.setString(1, String.valueOf(idAsBigInt));
    insert.executeUpdate();
}