PreparedStatement with hextoraw in hsqldb throws java.sql.SQLDataException: data exception: string data, right truncation

Question

I'm transferring data from one hsqldb to another. Both have the same tables etc. The schemas are absolutely identical.

I have some massive binary columns (not my design decision, but I got to work with it). Those I read as a string with the rawtohex function from database A and write to database B using the hextoraw function.

The code looks something like that:

String query = "SELECT rawtohex(PAYLOAD) FROM TABLE_X;"
Statement selectPst = connA.createStatement();
ResultSet data = selectPst.executeQuery(query);

String insert = "INSERT INTO TABLE_X (PAYLOAD) VALUES (hextoraw(?))";
PreparedStatement insStmt = connB.prepareStatement(insert);

data.next();
insStmt.setString(1, data.getString(1));

insStmt.executeUpdate(); // <- java.sql.SQLDataException: data exception: string data, right truncation

Usually, this Exception is thrown if the column is too small for the data. But the data is in a database with the same table and columns and if I do the same thing manually using the hsqldb tool, it works.

I'm grateful for any idea what could possibly cause this!

Looks like the output of `hextoraw` is longer than the column size — Jens, Aug 04 '17 at 07:53
@jens no, that's not it. I tried to execute the exact same statement manually using the tool provided with the hsqldb and it works — toydarian, Aug 04 '17 at 08:51
possible dupplicate: https://stackoverflow.com/questions/7565280/hsqlexception-data-exception ? — Jens, Aug 04 '17 at 08:55

score 0 · Answer 1 · answered Aug 04 '17 at 08:06

Can you try with readBinaryStream and writeBinaryStream methods instead of using rawtohex, e.g.:

String query = "SELECT PAYLOAD FROM TABLE_X;"
Statement selectPst = connA.createStatement();
ResultSet data = selectPst.executeQuery(query);

String insert = "INSERT INTO TABLE_X (PAYLOAD) VALUES (?)";
PreparedStatement insStmt = connB.prepareStatement(insert);

data.next();
insStmt.setBinaryStream(1, data.getBinaryStream(1));

insStmt.executeUpdate();

score 0 · Answer 2 · answered Aug 04 '17 at 09:55

It is not necessary to use RAWTOHEX and HEXTORAW.

String query = "SELECT PAYLOAD FROM TABLE_X;"
Statement selectPst = connA.createStatement();
ResultSet data = selectPst.executeQuery(query);

String insert = "INSERT INTO TABLE_X (PAYLOAD) VALUES (?)";
PreparedStatement insStmt = connB.prepareStatement(insert);

data.next();
byte[] databytes = data.getBytes(1);

// add code here to check if (databytes.length > columnSize)
insStmt.setBytes(1, databytes);

insStmt.executeUpdate(); //

It is possible in some scenarios that the data was inserted into the connA database without the size checks being applied. Adding some size checking code above will show you if this has happened and will allow you to alter the column size in both databases.

PreparedStatement with hextoraw in hsqldb throws java.sql.SQLDataException: data exception: string data, right truncation

2 Answers2