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?

trashgod · Accepted Answer · 2010-04-09 21:28:25Z

52

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

edited Apr 9, 2010 at 21:28

answered Apr 9, 2010 at 17:42

trashgod

206k33 gold badges264 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dm76 Over a year ago

looks like a very useful function for testing indeed! thanks for the hint

trashgod Over a year ago

@David Michel: I overlooked the essence of your question; it looks like you want FILE_READ, added above.

dm76 Over a year ago

I tried the FILE_READ() function which seems to work fine (no erros or warnings as the script runs) but then I get a 'java.lang.OutOfMemoryError: Java heap space' as I run a 'SELECT * FROM mytable'

trashgod Over a year ago

I guess that could happen if the BLOBs are big enough. Have you tried -Xms and -Xmx? java.sun.com/javase/6/docs/technotes/tools/windows/java.html

dm76 Over a year ago

The file is 11 MB which is not small, but not that big either. The fact that I'm running on a linux VM with VirtualBox so I have limited memory available.. which might explains this. I didn't that one could change the heap max size of the JVM via a command line option, thanks for the hint.

|

lfurini · Accepted Answer · 2021-01-20 08:17:54Z

5

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 )

edited Jan 20, 2021 at 8:17

lfurini

3,8114 gold badges35 silver badges52 bronze badges

answered Apr 9, 2010 at 13:58

GordonBy

3,4176 gold badges33 silver badges53 bronze badges

1 Comment

dm76 Over a year ago

That look promising, I'll have a look a this, although this seems to be a TSQL specific...let's see

Collectives™ on Stack Overflow

insert a BLOB via a sql script?

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related