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-09T21:28:25.420

49

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 09 '10 at 21:28

answered Apr 09 '10 at 17:42

trashgod

203,806
29
246
1,045

looks like a very useful function for testing indeed! thanks for the hint – dm76 Apr 09 '10 at 20:17
3

@David Michel: I overlooked the essence of your question; it looks like you want `FILE_READ`, added above. – trashgod Apr 09 '10 at 21:52
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' – dm76 Apr 12 '10 at 14:29
2

I guess that could happen if the BLOBs are big enough. Have you tried `-Xms` and `-Xmx`? http://java.sun.com/javase/6/docs/technotes/tools/windows/java.html – trashgod Apr 13 '10 at 01:11
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. – dm76 Apr 13 '10 at 08:38
You could also look at `SET COMPRESS_LOB`. – trashgod Apr 13 '10 at 12:10
RawToHex worked but seemed it was adding control chanraters to the string and the string seen in java code read from blob column was not what was expected. STRINGTOUTF8 solved the problem. – Yogesh Apr 09 '19 at 07:02

score 5 · Answer 2 · edited Jan 20 '21 at 08:17

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 '21 at 08:17

lfurini

3,729
4
30
48

answered Apr 09 '10 at 13:58

GordonBy

3,099
6
31
53

That look promising, I'll have a look a this, although this seems to be a TSQL specific...let's see – dm76 Apr 09 '10 at 14:19

insert a BLOB via a sql script?

2 Answers2

Linked