Insert/Select binary

[k-mack]

Hi,

I am having trouble understanding how to select the contents of a blob field. When inserting the binary data I use Vert.x's JsonObject.putBinary(byte[]) which encodes it to a Base64 string. When I select this field, I receive a JsonArray of integers. After iterating over the JsonArray and casting each entry to a char, I decode the string with Base64 class and I end up with something that looks like Java's string representation of a byte array.

JsonArray array = returnedResult.get(j); // blob field returned
StringBuilder sb = new StringBuilder();
for (Object o : array) {
  sb.append((char) ((byte) o));
}
System.out.println("received: " + sb.toString());
System.out.println("decoded: " + Base64.decode(sb.toString()));

The output looks like this:

received: TWF0dEBNYXR0LmNvbSxCcmlhbkBCcmlhbi5jb20sRGVycmlja0BEZXJyaWNrLmNvbSxPd2VuQE93
ZW4uY29tLEJpbGxAQmlsbC5jb20sTWlrZUBNaWtlLmNvbQ==
decoded: [B@3c43e349

I'm not positive, but it looks like the code is executing line 240 of the ConnectionHandler.

Can anyone help me with understanding how to interact with binary fields with this module?

[Narigo]

Actually we haven't done anything with BLOB fields yet and that's probably the main trouble here. No tests for it -> might not be working as intended.

Let me try to add a test and see what can be done. In our projects, we actually used the file system to write our binary data and used this module (among other things) as an index over them.

[Narigo]

@k-mack I've added a new test for BLOBs and it seems to work. The problem is that Base64.decode() gives you a byte[].

Try this instead:

System.out.println("decoded: " + new String(Base64.decode(sb.toString())));

Does that help?

[Narigo]

The test is here, haven't tried it with MySQL yet, but it seems to work in PostgreSQL using a BYTEA column type: https://github.com/vert-x/mod-mysql-postgresql/blob/blobs/src/test/scala/io/vertx/asyncsql/test/BaseSqlTests.scala#L470-L486

I don't really like the Base64.decode usage here at all. But I guess that's a problem more related to Vert.x itself, if you need to use byte arrays in Json...

[k-mack]

@Narigo I'm embarrassed that I didn't pay more attention to the method signature of Base64.decode(String) when I was testing this out. Thanks for your help with this!

[Narigo]

@k-mack the API is clearly not the best for writing and reading BLOBs, so no need to feel embarassed ;)

With Vert.x 3, the usage of this module will change and it might be easier to use. Still, as it will need to serialize the BLOBs over the event bus, if you use it, you will need to rely on the JSON API from Vert.x itself.