insert a BLOB via a sql script?

Question

I have an H2 database (http://www.h2database.com) and I'd like to insert a file into a BLOB field via a plain simple sql script (to populate a test database for instance). I know how to do that via the code but I cannot find how to do the sql script itself.

I tried to pass the path, i.e.

INSERT INTO mytable (id,name,file) VALUES(1,'file.xml',/my/local/path/file.xml); 

but this fails.

Within the code (java for instance), it's easy to create a File object and pass that in, but directly from a sql script, I'm stuck ...

Any idea?

Answer 1

For testing, you can insert literal hex bytes or use the RAWTOHEX(string) function, as shown below.

create table a(id integer, item blob); insert into a values(1,'54455354'); insert into a values(2, RAWTOHEX('Test')); select UTF8TOSTRING(item) from a; TEST Test 

Addendum: For loading BLOB fields from a file, FILE_READ(fileNameString) may be a useful alternative.

insert into a values(3, FILE_READ('file.dat')); 

Answer 2

Not h2database, but may help; https://blog.jerrynixon.com/2009/03/tsql-to-insert-imageblog.html

Example code from the linked blog article, should the link break again:

CREATE TABLE MyTable      (id int, image varbinary(max))  INSERT INTO MyTable     SELECT      1         ,(SELECT * FROM OPENROWSET(             BULK 'C:\file.bmp', SINGLE_BLOB) as x )