How to insert a file into a blob field from sql statement

[dmic...@googlemail.com]

For instance, if I want to test my sql statement from the H2 GUI front-
end before doing via my JDBC java code.

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...

[Sergi Vladykin]

Hi,
AFAIK by default this is impossible but you can try to write your
function which will
have file name as parameter and will return InputStream, create alias
in sql for it
(LOAD_FILE for example) and then insert like this
INSERT INTO mytable (id,name,file)
VALUES(1,'file.xml',LOAD_FILE('/my/local/path/file.xml'));

[Sergi Vladykin]

See also http://h2database.com/html/features.html#user_defined_functions

[Thomas Mueller]

Hi,

See also http://www.h2database.com/html/functions.html#file_read

Regards,
Thomas

[Thomas Kellerer]

On Apr 9, 4:30 pm, "dmiche...@googlemail.com"

If you can change your SQL tool, you may want to have a look at SQL
Workbench/J.

It extends regular SQL to support the following syntax

INSERT INTO mytable (id,name,file)
VALUES(1,'file.xml', {$blobfile='/my/local/path/file.xml'} );

http://www.sql-workbench.net/manual/using.html#blob-support

It can be downloaded from here: http://www.sql-workbench.net/downloads.html

@Thomas Mueller: I hope you don't mind that I mention my tool here. As
you have listed it on your homepage, I assumed this is OK

Regards
Thomas

[dmic...@googlemail.com]

On 9 Apr, 18:42, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> See alsohttp://www.h2database.com/html/functions.html#file_read
>
> Regards,
> Thomas

I tried that like so:
INSERT INTO mytable (id,name,file) VALUES(1,'file.xml',FILE_READ('/my/
local/path/file.xml'));

The sql script then seems to run (i.e. to errors or warnings) but
then, when I try to visualise the table (via the H2 front-end) and do
a
SELECT * FROM mytable
I get a:
Java heap space
java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2882)
at java.lang.StringValue.from(StringValue.java:24)
at java.lang.String.<init>(String.java:178)
at org.h2.util.ByteUtils.convertBytesToString(ByteUtils.java:154)
at org.h2.util.ByteUtils.convertBytesToString(ByteUtils.java:136)
at org.h2.value.ValueLob.getString(ValueLob.java:563)
at org.h2.jdbc.JdbcResultSet.getString(JdbcResultSet.java:266)
at org.h2.server.web.WebThread.escapeData(WebThread.java:2032)
at org.h2.server.web.WebThread.getResultSet(WebThread.java:1956)
at org.h2.server.web.WebThread.getResult(WebThread.java:1710)
at org.h2.server.web.WebThread.query(WebThread.java:1274)
at org.h2.server.web.WebThread.process(WebThread.java:430)
at org.h2.server.web.WebThread.processRequest(WebThread.java:183)
at org.h2.server.web.WebThread.process(WebThread.java:236)
at org.h2.server.web.WebThread.run(WebThread.java:193)

the file is relatively big 11MB but I wouldn't think this it too
much... (the rest of the db being empty)

[dmic...@googlemail.com]

On 9 Apr, 22:27, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

Sounds cool, I'll have a go at it

[dmic...@googlemail.com]

Just for info, the way to solve the heap size problem is to modify
slightly the h2.sh script and use the -Xms and -Xmx option of the java
launcher
to manually set the memory size allocation. I only had to do this I
was using h2 on a linux system running on a VM under windows, so
memory was actaully quite limited...

cheers
David

On 12 Apr, 10:16, "dmiche...@googlemail.com"

[Thomas Mueller]

Hi,

> java.lang.OutOfMemoryError: Java heap space

> the file is relatively big 11MB but I wouldn't think this it too
> much... (the rest of the db being empty)

The H2 Console is not designed to display large files. It would be
possible to support it with the default memory settings, but currently
the easiest solutions is to increase the heap memory:

java -Xmx512m ...

Regards,
Thomas