storing BLOBs in Oracle (again)

[Heinrich Götzger <go...@gmx.de>]

Hello,

there has been a lot of discussion about writing and reading BLOBs
already on the list.
But none of them satisfies me right now.

The requirement is to store files of any type (images, docs, or even
movies) and almost any size (not just a view kilos) into the db and make
them accessible later within a web application.

We probably do not want to hold the file in the memory, as we do not
need it there at all. It's just from the disc to db and back.

In an earlier project, where all the db-access has been done in god old
well-written JDBC code we used to have a routine to store which just
uses an inputstream from the caller and writes it directly into an
outputstream which comes from the resultset like this:

[...]
SELECT content FROM record WHERE id = ? FOR UPDATE
[...]
Blob blob = rs.getBlob(1);
OutputStream out = ((BLOB) blob).getBinaryOutputStream();
[...]

So I'm not sure if this is possible in any way with iBATIS at all.
Or would we just store the content of the stream in a local array and
pass it to the DB using the iBATIS way as described here:

http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+use+a+BLOB+or+CLOB

Is there an upper limit for the array size?

How is it with performance?

Thanks for sharing some thoughts.

Cheers

Heinrich

PS: And happy new year, btw.

Re: storing BLOBs in Oracle (again)

[Chema <de...@gmail.com>]

I think that the lastest iBatis version + JDBC driver 10g release solve all
about this.
Then, use byte[] arrays and BLOB JDBC datatype into your SQLMap config file.




2008/1/1, Heinrich Götzger <go...@gmx.de>:
>
> Hello,
>
> there has been a lot of discussion about writing and reading BLOBs
> already on the list.
> But none of them satisfies me right now.
>
> The requirement is to store files of any type (images, docs, or even
> movies) and almost any size (not just a view kilos) into the db and make
> them accessible later within a web application.
>
> We probably do not want to hold the file in the memory, as we do not
> need it there at all. It's just from the disc to db and back.
>
> In an earlier project, where all the db-access has been done in god old
> well-written JDBC code we used to have a routine to store which just
> uses an inputstream from the caller and writes it directly into an
> outputstream which comes from the resultset like this:
>
> [...]
> SELECT content FROM record WHERE id = ? FOR UPDATE
> [...]
> Blob blob = rs.getBlob(1);
> OutputStream out = ((BLOB) blob).getBinaryOutputStream();
> [...]
>
> So I'm not sure if this is possible in any way with iBATIS at all.
> Or would we just store the content of the stream in a local array and
> pass it to the DB using the iBATIS way as described here:
>
>
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+use+a+BLOB+or+CLOB
>
> Is there an upper limit for the array size?
>
> How is it with performance?
>
> Thanks for sharing some thoughts.
>
> Cheers
>
> Heinrich
>
> PS: And happy new year, btw.
>
>