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'));