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 )

友情链接