wood burning stoves*
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 Android Security Essentials Live Lessons this week in the Android 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
 
Similar Threads
Storing large image files
Inserting image into the oracle database
How to insert and retreive zip files in to blob column in oracle database?
reading BLOB type
need help with IO