insert a BLOB via a sql script?

I have an H2 database (http://www.h2database.com) and I'd like t开发者_开发问答o 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?

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

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 )