H2 jdbc exception - Hexadecimal string with odd number of characters

Question

The following stmt works on mysql but not on H2 Database

insert into XXX(content) select convert('{ "text" : "testsee", "url" : "http://hjh.com", "phone" : "" }', BINARY) as content from ...

SQL State : 90003 Error Code : 90003 Message : Hexadecimal string with odd number of characters: "{ ""text"" : ""testsee"", ""url"" : ""http://hjh.com"", ""phone"" : """" }"; SQL statement: Can anyone tell me how can i fix this issue? content column is of type blob. This is a spring boot microservice where tests run on H2. The stmt is written in puresql, and executed by flyway during start up.

argoth · Accepted Answer · 2018-08-31 16:55:09Z

2

This occours because the database is trying to convert a Java String to a BLOB (byte string), change the type of the column to defined as BLOB to VARCHAR(size) or TEXT.

You can also check this link https://dev.mysql.com/doc/refman/8.0/en/blob.html

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.

edited Aug 31, 2018 at 16:55

answered Aug 31, 2018 at 16:49

argoth

1,2639 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dkat Over a year ago

I cannot change the column type to Varchar.. it contains data upto 1MB. These are pure sql scripts: update etui_template SET content = (select convert('{"text" : "testsee", "url" : "", "phone" : "", "driverDistraction" : false, "expirationDate" : 0, "freeTextApplication" : 1}', BINARY) as c

argoth Over a year ago

I updated my answer. Please check if it can help you.

dkat Over a year ago

Actually cannot change the column type.. i need to find a way thhat the same sql script gets executed on MYSQL and H2 database

argoth Over a year ago

So, you cannot save a character strings within a blob. Blob`s are designed for byte strings. A not elegant way to solve you problem would be convert you strings to hex before save in database.

dkat Over a year ago

Actually I am converting to bytes using : select convert('{ "text" : "testsee", "url" : "hjh.com", "phone" : "" }', BINARY) as content

pedrociarlini · Accepted Answer · 2023-03-14 12:40:06Z

0

I found in this source, a way to insert hex data into H2 database. But first it's necessary use some online tool to convert text to hex and then use a insert like this one

-- data = 'Text to me inserted'
insert into TABLE (blob_column) values ( X'5465787420746f206d6520696e736572746564' )

answered Mar 14, 2023 at 12:40

pedrociarlini

911 silver badge11 bronze badges

Collectives™ on Stack Overflow

H2 jdbc exception - Hexadecimal string with odd number of characters

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related