Java string to query a varbinary(64) column

Question

For a unit test, I need to query a MySQL table for a specific value in a varbinary(64) column. The data is loaded from a csv using jooq.

VARBIN_VALS,...
0001,...
0002,...

I see the value 0001 when I select on the table after loading the test data. I want to find the record with VARBIN_VALS 0001 so I'm trying to construct the bytes array from a java string. I've tried several variations:

"0001".getBytes()
"0001".getBytes(StandardCharsets.UTF_8)
"0001".getBytes(Charsets.UTF_16)

but nothing is matching. As far as I can tell mysql is using utf8mb4. How to convert a java string to bytes array that would match what jooq did?

Test data loaded with:

ContextObject.loadInto(TABLE_A).
loadCSV(SourceFile).fields(...).execute();

And the query is:

ctx.select(TABLE_A.FIELD_OF_INTEREST).
from(TABLE_A).
where(TABLE_A.VARBIN_FIELD.eq(bytesArray)).
fetch(TABLE_A.FIELD_OF_INTEREST);

Answer 1

I think what's happening here is: If your column is of some byte[] type (e.g. VARBINARY), then the Loader API will attempt to decode base64 encoded binary data, see https://github.com/jOOQ/jOOQ/issues/5858

Among all the binary string representations, this seems to be one of the most popular ones, but it's probably not what you were expecting. You were expecting jOOQ to pass along the String directly to the JDBC driver.

A thorough solution for arbitrary string encodings might be implemented in the near future, see: https://github.com/jOOQ/jOOQ/issues/3221, where you can define explicit String to custom type conversion when loading data from CSV (or JSON, etc.)

Until then, I think the problem is clear? For now, you should either:

• Place base64 encoded binary data in your CSV
• Pre-process your CSV and pass the binary data to an Object[] or Record representation, and load that, instead