Insert BLOB from a file into a sql script to embed

Question

I'm creating a SQL script to create a new schema and insert some values to an embed H2 database for use with integration tests in a Spring Boot application. One of the values I need to insert is a BLOB field on the sql table.

I've succesfully used the FILE_READ function as described here.

INSERT INTO MY_TABLE(ID, NAME, LOGO) 
VALUES('1', 'test1', FILE_READ('C:/myproject/logo.png'));

That function works well with full paths but I'm not been able to do that with relative paths. That doesn't work well when the sources are downloaded and compiled (plus testing) in any other machine than mine.

I need to insert into a sql script a BLOB field from a binary file, loaded from a relative path from the project that owns that script.

I've searched and found this aproach: insert a BLOB via a sql script? But RAWTOHEX function seems to work with Strings, and my input is a binary file.

Any ideas?

Answer 1

From FILE_READ documentation:

File names and URLs are supported. To read a stream from the classpath, use the prefix classpath:

Seems that the use of a relative path it's not possible; then a possible solution is to include the file with the desired binary content in the classpath and access it using classpath: in FILE_READ. This way you can deploy it in any other machine without worries about the absolute paths.

By code using RunScript

So if before perform your test you setup the DB running the script by code using something like:

RunScript.execute(conn, new FileReader("yourScript.sql"));

Then add the logo.png as a resource of your project this way you can refer it inside the script using classpath: notation: FILE_READ('classpath:/your/package/resource/logo.png').

Using RunScript from command line tool

If you use the command line tool, you can create a .jar to package your resources, e.g resource.jar and add it to classpath in your cmd:

java -cp h2*.jar;resource.jar org.h2.tools.RunScript -url jdbc:h2:~/test -script yourScript.sql

Then as the previous case in your script you can refer your binary file using FILE_READ('classpath:/your/package/resource/logo.png')

Hope it helps,