[h2] org.h2.jdbc.getBytes(String var1) throws exception for json string in VARCHAR type column

[Sidan Kim]

Hi everyone,

I have a question about org.h2.jdbc.getBytes(String var1) for VARCHAR type
column which contains json string.
In my codes I have a DB column in VARCHAR type for serializing my java
class in json.
There is no issue when I insert a row to the column.
But when I fetch the row, I invoke org.h2.jdbc.getBytes(String var1) to
deserialize json to my class and an exception is thrown like below.
org.springframework.jdbc.UncategorizedSQLException: Error attempting to get
column 'MEMBER_INFO' from result set. Cause: org.h2.jdbc.JdbcSQLException:
Hexadecimal string with odd number of characters:
"{""name"":""john"",""phone"":""000-0988"",""gender"":""male""}" [90003-197]
; uncategorized SQLException for SQL []; SQL state [90003]; error code
[90003]; Hexadecimal string with odd number of characters:
"{""name"":""john"",""phone"":""000-0988"",""gender"":""male""}"
[90003-197]; nested exception is org.h2.jdbc.JdbcSQLException: Hexadecimal
string with odd number of characters:
"{""name"":""john"",""phone"":""000-0988"",""gender"":""male""}" [90003-197]

at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy60.selectOne(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy109.selectById(Unknown Source)
Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number
of characters:
"{""name"":""john"",""phone"":""000-0988"",""gender"":""male""}" [90003-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.util.StringUtils.convertHexToBytes(StringUtils.java:913)
at org.h2.value.Value.convertTo(Value.java:1075)
at org.h2.value.Value.convertTo(Value.java:617)
at org.h2.value.Value.convertTo(Value.java:592)
at org.h2.value.Value.getBytes(Value.java:448)
at org.h2.jdbc.JdbcResultSet.getBytes(JdbcResultSet.java:1086)
at com.sun.proxy.$Proxy114.getBytes(Unknown Source)

Same code and query for MySQL worked fine.
I know it works well with org.h2.jdbc.getString().
I'm just wondering why same interface works differently between h2 and
mysql.

Thanks!

[Evgenij Ryazanov]

Hello.

You need to build H2 from its current sources and enable MySQL
compatibility mode in it to resolve this issue.
Sources are here:
https://github.com/h2database/h2database
You need a jar target:
http://h2database.com/html/build.html#building

getBytes() is not a proper method to read VARCHAR columns so do not expect
that it will work in the same way in different databases.