CODEWITHSUNDEEP
mysqlspring-booth2flyway
dkat
dkat

Reputation: 223

H2 jdbc exception - Hexadecimal string with odd number of characters

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.

Upvotes: 3

Views: 13910

Answers (2)

pedrociarlini
pedrociarlini

Reputation: 91

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' )

Upvotes: 0

argoth
argoth

Reputation: 1253

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.

Upvotes: 2

Related Questions