Inserting text into a blob in H2

[Philip Roc]

Hello,

how do you insert text (ex "abc") into a BLOB, in H2 ?

The BLOB field is supposed to contain JSON.

Many thanks.

Cheers,

Philip

[Evgenij Ryazanov]

Hello.

H2 is not different from others, JDBC has java.sql.Blob interface that should be used, use Collection.createBlob() to allocate a BLOB instance, use one of its methods to put your data into it, and use PreparedStatement.setBlob() to pass this BLOB to the command. There are also an alternative PreparedStatement.setBlob() methods that take a java.io.InputStream as a parameter. If you data is small enough, you can use the byte[] with PreparedStatement.setBytes().

If you can build H2 from its current sources, you can use the JSON data type instead of BLOB, but it is not designed for really large JSON documents. If you need to store large JSON data in H2, please let us know.

[Philippe de Rochambeau]

Hi,

thank you for your suggestions.

Can the prepared statement be run from outside (a standalone Java program) the (Spring Boot) project that created an in-memory H2 database containing a table with a BLOB column, or must it run from inside?

Le 9 oct. 2019 à 03:51, Evgenij Ryazanov <kat...@gmail.com> a écrit :



Hello.

H2 is not different from others, JDBC has java.sql.Blob interface that should be used, use Collection.createBlob() to allocate a BLOB instance, use one of its methods to put your data into it, and use PreparedStatement.setBlob() to pass this BLOB to the command. There are also an alternative PreparedStatement.setBlob() methods that take a java.io.InputStream as a parameter. If you data is small enough, you can use the byte[] with PreparedStatement.setBytes().

If you can build H2 from its current sources, you can use the JSON data type instead of BLOB, but it is not designed for really large JSON documents. If you need to store large JSON data in H2, please let us know.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6a8a057f-e96b-4e83-8025-0a632aae4243%40googlegroups.com.

[Evgenij Ryazanov]

BLOB or CLOB values in in-memory database? Bad idea in general, when you use an in-memory database, it should be small enough.

If you want to connect to the database from another process, you need to start a TCP Server inside of the process that owns the database.

org.h2.tools.Server tcp = org.h2.tools.Server.createTcpServer(…);
tcp.start();

Another process should use the remote URL to connect to this server.

You can also launch a separate H2 Server process and connect to it from both your applications.

If you use Hibernate, you can use Hibernate.getLobCreator(…) I guess. Why you want to mix different methods to deal with the database?

[Philip Roc]

Hi Eyvgenij,

here's the situation. My colleagues and I are currently working on a Spring Boot project that uses Oracle. However, although one of the developers has managed to install Oracle 11 on his machine, the 4 others haven't,

probably because of company-wide security policies (who knows). Instead of spending precious development time figuring out why Oracle won't install, we've decided to use H2 instead for development purposes.

The schema-h2.sql script in ou project creates a table containing a BLOB column (when the Spring Boot Profile is "h2").

I would like to store a sample json file (200 lines) in the in-memory DB, to test the GET REST request, during development.

For instance,

GET /myservice/<invoice id>

returns

{ "id": 2, "invoicename" : "123", etc. }

Cheers,

Phil

[Philip Roc]

I've found a solution

INSERT INTO XXX (

    ...

    "JSON_FILE" ,

  ...

 ) VALUES ( 

    ...

    FILE_READ('classpath:/j1.json') ,

...

 ) ;

See https://stackoverflow.com/questions/38353487/insert-blob-from-a-file-into-a-sql-script-to-embed-h2-database