Error when trying to retrieve json data from h2 database

Question

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?

Answer 1

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.