Inserting BLOB's in database

[Chinmay Bajikar]

Hi all,
I have a problem with inserting a BLOB in database.
Like say I have a File object or a huge String to be inserted into a BLOB column of the database.
How do I do it?
like the PreparedStatement.setBlob(1,BLOB),requires me to have a BLOB object beforehand.
Where do I get this BLOB object from?
In essence,How do I construct a BLOB object which will have my abstract data(File,String) in it?
Thanks,
Chinmay

[Rene Larsen]

First you need to insert a new record (or update)

// insert into some_table (id, chart_object) values(1, empty_blob());

then select the record

// select chart_object from some_table where id = 1 for update;

now you should have a blob from the database that you can insert into.

BLOB writeBlob = null;
if (rs.next()) {
  writeBlob = (BLOB) rs.getBlob(1);
}
else {
  // throw som exception
}
byte[] data = // Object as byte array
OutputStream outStream = writeBlob.getBinaryOutputStream();
outStream.write(data);
outStream.close();
outStream = null;
con.commit();

Rene
[ April 13, 2003: Message edited by: Rene Larsen ]

[Chinmay Bajikar]

Thanks Rene,
I tried something else and it worked fine.
I used PreparedStatement.setBinaryStream() for a BLOB column.
It worked fine.
Also the question, why is it such a tedious job to insert a BLOB column.
Why cant I just create a BLOB object directly and get going?
Thanks again for the reply,
Chinmay....

[Chinmay Bajikar]

Hi Rene,
I just checked ur code again and I m afraid there is no "getBinaryOutputStream();" in Blob.
There is a "getBinaryStream()" though which is a InputStream.
Plzz lemm know if there is anything wrong or if I m plain confused.
Also here is something I read from the O'Rielly
JDBC book,
"The storage of Blob is a little different from there retrieval.While you can use the setBlob() API,JDBC BLOB interfaces provide no database independent way of constructing BLOB objects.It is expected that this topic will be addressed in
JDBC 3.0.One needs to either write their own implementation or tie oneself to the driver vendor's implementation.
But a more database independent way of doing this is using the setBinaryStream() or setObject() API's for binary data."
Thanks,
Chinmay

[Rene Larsen]

If you use the standard JDBC blob 'Blob' - then you only have 'getBinaryStream()' but if you use the blob from the oracle JDBC API 'BLOB' then you have the 'getBinaryOutputStream()' avalable.
If you look at my code you will see that I'm casting the blob I'm getting from the database.
Rene
[ April 17, 2003: Message edited by: Rene Larsen ]

[Chinmay Bajikar]

Hi Rene,
Sorry for bothering u again.
What u said is correct.
In fact when i try to insert data values with length greater than 4000 using setBinaryStream()/setObject(),I get an exception saying inserted data value is too large.
I suppose i will have to use the Oracle JDBC API's
suggested by u
(even if that means tying myself to the driver specific api's)
Thanks for evrything,
bye,
Chinmay
[ April 18, 2003: Message edited by: Chinmay Bajikar ]