Cannot transfer blob from MySQL to H2

Question

I have some blob hexadecimal values that I exported from a MySQL database using MAMP. I tried to add those values to a H2 database but I keep getting the error :

    org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number of characters: "80273753912952185238922745508880797601482513992540829416944108482201678079823009423240796439787688076562876906583780517704249256776629049440054030707230103901187860416530898080584543691951511619479802824069658267005899514817053628822676904964354186008390099680162497341280836375590099576180828248580706583256766125057680523862652582731318422096302127828322568212884418137380647350895148022669223845592468192584084729728626393575544"; SQL statement:
INSERT INTO `StatisticsExplanationActivity` VALUES(2, '2012-06-01 11:36:36', '0', '2012-06-01 11:36:37', 1, 16, 0x00aced0005737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a6578700000000577040000000a7400196d757365756d507265666572656e63655f6f7269656e74616c7400166d757365756d507265666572656e63655f70686f746f740014696465616c686f6c79646179735f75726261696e7400176e696768744576656e696e67486f6262795f6d757369637400106d75736963616c54617374655f706f7078) -- (2, '2012-06-01 11:36:36', '0', '2012-06-01 11:36:37', 1, 16, 80273753912952185238922745508880797601482513992540829416944108482201678079823009423240796439787688076562876906583780517704249256776629049440054030707230103901187860416530898080584543691951511619479802824069658267005899514817053628822676904964354186008390099680162497341280836375590099576180828248580706583256766125057680523862652582731318422096302127828322568212884418137380647350895148022669223845592468192584084729728626393575544) [90003-166]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.util.StringUtils.convertHexToBytes(StringUtils.java:990)

The same INSERT line works fine with MySQL. I saw on some other topic that this may happen when the H2's multi-threaded mode is enabled. I wanted to give it a try but I have no idea how to set that parameter using Play! 1.2.x.

EDIT: Tried the MULTI_THREADED thing but still getting the same error. EDIT2: Forgot to post the hexadecimal number I used :

0xaced0005737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a6578700000000577040000000a7400196d757365756d507265666572656e63655f6f7269656e74616c7400166d757365756d507265666572656e63655f70686f746f740014696465616c686f6c79646179735f75726261696e7400176e696768744576656e696e67486f6262795f6d757369637400106d75736963616c54617374655f706f7078

Have you tried padding blobs with odd number of characters with a `0` in the front? — F21, Jul 24 '12 at 08:47
Yes but no use because in the error message it's complaining about the decimal version of the number (wich won't change by adding the 0's). — user1502150, Jul 24 '12 at 08:52
Most likely the 0x00aced0.. is interpreted as a number (a Java BigDecimal), and then the database tries to convert the toString() representation as a binary. This fails in 50% of the cases. So instead of 0x00aced0... use '00aced0...' (quoted) — Thomas Mueller, Jul 24 '12 at 13:52
@Thomas: Thank you that solved the problem. I tried to use quotes but didn't have the idea of removing the '0x'. Can you please add an answer so I can mark It as correct ? — user1502150, Jul 24 '12 at 14:54

score 2 · Accepted Answer · answered Jul 24 '12 at 15:00

2

The H2 database interprets the 0x00aced0... as a number (a Java BigDecimal), and then it tries to convert the toString() representation (the decimal number) as a hex encoded binary.

This is not what you want of course, and it fails in roughly 50% of the cases.

So instead of 0x00aced0... you would need to use '00aced0...' (quoted)

answered Jul 24 '12 at 15:00

Thomas Mueller

48,905
14
116
132

I'm having a similar problem, except it won't persist the `Blob` at all in the first place. I'm using the standard `Blob` implementation and JPA, so I can't actually add quotes. What could I do in this case (short of abandoning H2)? – Andrew Wyld Jan 07 '13 at 15:36
I guess your problem and this problem are unrelated. I suggest to open a new question. – Thomas Mueller Jan 07 '13 at 15:38
Already got one open: http://stackoverflow.com/questions/14197779/uploading-a-file-in-java-play-1-2-3-then-storing-file-as-byte-array-causes-persi I found this question by googling "Hexadecimal string with odd number of characters" – Andrew Wyld Jan 07 '13 at 15:40

score 1 · Answer 2 · answered May 12 '22 at 14:17

You should use x'aced00057372...' instead of 0xaced00057372....

Copy/Paste from H2 documentation:

Bytes

X'{hex}'

A binary string value. The hex value is not case sensitive and may contain space characters as separators. If there are more than one group of quoted hex values, groups must be separated with whitespace.

Example:

X''

X'01FF'

X'01 bc 2a'

X'01' '02'

Cannot transfer blob from MySQL to H2

2 Answers2