Inserting BLOB data in HSQL via .sql

user_13459462 0
Viewed 28

I'm writing integration tests in Spring Boot app and I'm using in-memory HSQL db for that. Because of some requirements I need to insert some BLOB data via db_data.sql and I'm searching for the best solution. What I'm using right now is LOAD_FILE('full_path') method, but I don't think I can proceed with that. Problems I see:

  1. I cannot change the way tests are executed, so I cannot set textdb.allow_full_path to true. I mean, on my local machine I am and I'm using it, but it cannot be propagated to other environment. I tried to use System.setProperty("textdb.allow_full_path", "true"); in @BeforeAll method, but it doesn't seem to work. I even tried to set it to false with true passed as VM option and it worked, so I guess my setProperty does literally nothing from database perspective.
  2. I cannot use absolute path on different machines and tests are meant to be executed in many different places.

I have some dirty workarounds and in general I'm able to run the tests, but inserting blobs via sql will be ideal for my purposes. Of course I cannot switch from HSQL to anything else.

EDIT

Loading data from file is not strict requirement for me, some of the data is dummy. I can pass basically everything in my INSERT statement, text, numbers, whatever.

spring-boot-test hsqldb java
1 Answers

The textdb.allow_full_path is processed during static initialization of one of the HSQLDB classes. It should work so long as the property is set prior to any access to HSQLDB.

You can insert long hexadecimal strings for the blob data. For example:

 INSERT INTO mytable (id, blobcolumn) VALUES 2, 'abcdef'
user_4710700
Related