I have the following table definition
create table samples (
channel text,
eventType text,
data json NOT NULL
);
I have also tried defining the data
column as clob
,text
,java_object
,varchar
,other
.
I am using the following API to insert data in h2:
def insert(sample: Sample): Unit = DB localTx { implicit session =>
val propertiesJson = new PGobject()
propertiesJson.setType("json")
propertiesJson.setValue(sample.properties.toJson.toString)
sql"""
insert into samples
(channel,eventType,data) values (${sample.channel}, ${sample.eventType},$propertiesJson )
""".update().apply()
}
and this one to retrieve data
def find(channel: String): List[Sample] = DB readOnly { implicit session =>
sql"""
select * from samples where channel = $channel
""".map(rs => {
Sample(
channel = rs.string("channel"),
properties = rs.string("data").parseJson.convertTo[Map[String, String]],
eventType = rs.string("eventType")
)
}
).list().apply()
}
I'm using implicit conversions using spray
and the scalikejdbc
driver.
There are the different errors I am getting depending on the data type of the data
column.
For CLOB
,VARCHAR
,TEXT
and JAVA_OBJECT
: I can insert data in h2 but when trying to retrieve I get
spray.json.JsonParser$ParsingException: Unexpected character 'a' at input index 0 (line 1, position 1), expected JSON Value: aced00057372001c6f72672e706f737467726573716c2e7574696c2e50476f626a656374f903de2682bdcb3b0200024c0004747970657400124c6a6176612f6c616e672f537472696e673b4c000576616c756571007e000178707400046a736f6e74001f7b2270726f7041223a2276616c41222c2270726f7042223a2276616c42227d
For JSON
. I cannot even insert data into h2. I'm getting
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "OTHER to JSON" [22018-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
... 114 more
When using JSON
I also tried this format json
directive proposed here
See also json literal grammar. Mapped to byte[]. To set a JSON value with java.lang.String in a PreparedStatement use a FORMAT JSON data format (INSERT INTO TEST(ID, DATA) VALUES (?, ? FORMAT JSON)). Without the data format VARCHAR values are converted to a JSON string values.
but the error was still the same.
So any ideas? How can I successfully insert and retrieve JSON data from an h2 database? Is there anything wrong with my approach?
I'm not familiar with Scala, but you definitely can't use PGobject
with H2, this class is specific to PgJDBC. To pass a JSON value to H2, you need to use a plain byte array (byte[]
in Java, Array[Byte]
in Scala); the passed array should contain JSON text in UTF-8, UTF-16, or UTF-32 encoding. You can also use a java.lang.String
if you wish, but it will require FORMAT JSON
clause in SQL after parameter.
To read a JSON value from H2 it would be better to use ResultSet.getBytes(…)
in Java/JDBC and WrappedResultSet.bytes(…)
in ScalikeJDBC, it will return byte array with JSON text in UTF-8 encoding. Currently you're using a string(…)
method, it should work too at least with H2 1.4.200, but such behavior is not documented and may be changed in the future releases.
These suggestions are for builtin JSON data type of H2.