BLOB insertion

[Surendran Velath]

I tried to insert a LOB - a 4000 long String converted to bytearray into a BLOB column of Oracle.
code is like this:
conn.setAutoCommit(false);
qry = "select blobby from temp for update";
pstt = conn.prepareStatement(qry);
ResultSet rs = pstt.executeQuery();
rs.next();
oracle.sql.BLOB BL1 =
(oracle.sql.BLOB) rs.getBlob(1);
java.io.OutputStream opStream =
BL1.getBinaryOutputStream();
for(int i=0; i<bytearray.length;i++)
{
opStream.write(bytearray[i]);
}
// I tried this too :
// opStream.write(bytearray);
qry = "insert into temp(blobby)values(?)";
pstt = conn.prepareStatement(qry);
pstt.setBlob(1,BL1);
The String is like : "ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~⌂ ......"
But when BL1 was checked(using getBytes()) the value is like :
" T ☺☺♀ ☺ ☺ ☻☻D ?u ?t ☺ ☺ ←?tr♂?tr♂ ☺♂0? ....."
Whatever the original length the BL1 length is 86
What is the correct way of inerting a LOB into a BLOB column ?
Thanks

[Rene Larsen]

Try this post: https://coderanch.com/t/298572/JDBC/java/Inserting-BLOB-database
Rene

[Surendran Velath]

Hi Rene,
Thanks for the reply.
What I have done in the code is exactly what you had suggested in reply to chinmay.
Do you think that there is something wrong with:
opStream.write(bytearray);
Or is it that the BLOB object has the correct value inside it and I am trying to read it in the wrong way with:
byte[] bb = blobObj.getBytes();
for(int i=0; i<bb.length; i++)
{
System.out.print(bb[i]);
}
Please suggest a way out .....
Surendran

[Rene Larsen]

You need to close the stream after insert.

outStream.write(data);
outStream.close();
outStream = null;
con.commit();

Rene

[Surendran Velath]

Hi Rene,
Thanks for your help.
This is the final solution which worked perfectly:
To write Blob:
conn.setAutoCommit(false);
String qry = "";
qry = "insert into temp values(empty_blob())�;
Statement stmt = connection.createStatement();
stmt.execute(qry); // insert a empty blob
stmt.close();
qry = "select blobby from temp for update";
pstt = conn.prepareStatement(qry);
ResultSet rs = pstt.executeQuery(); // write into that empty blob
rs.next();// go to last row if more than one row
Blob b = rs.getBlob(1);
oracle.sql.BLOB BL1 = (oracle.sql.BLOB)b;
java.io.OutputStream opStream = BL1.getBinaryOutputStream();
opStream.write(bytearray);
opStream.close();
opStream = null;
conn.commit();
To read Blob:
oracle.sql.BLOB blobObj = (oracle.sql.BLOB)rs.getBlob(k);
byte[] bb = blobObj.getBytes(1,(int)blobObj.length());
String ss = new String(bb);
System.out.println("\n\nss: "+ss);
[ April 29, 2003: Message edited by: Surendran Velath ]