aspose file tools*
The moose likes JDBC and the fly likes BLOB insertion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "BLOB insertion" Watch "BLOB insertion" New topic
Author

BLOB insertion

Surendran Velath
Greenhorn

Joined: Apr 23, 2003
Posts: 19
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
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

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


Regards, Rene Larsen
Dropbox Invite
Surendran Velath
Greenhorn

Joined: Apr 23, 2003
Posts: 19
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
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

You need to close the stream after insert.


Rene
Surendran Velath
Greenhorn

Joined: Apr 23, 2003
Posts: 19
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: BLOB insertion