Inserting BLOB

[maha raja]

Hai All,
I wanna insert a BLOB in to oracle DB and my sample code is :
Connection conn = null;
ResultSet result = null;
int columns = 0;
PreparedStatement ps = null;
ByteArrayOutputStream byteStream = new ByteArrayOutputStream();
OutputStream out = null;
String sqlInsert = "INSERT INTO TEST Values (?,EMPTY_BLOB())";
String sqlLockRow = "SELECT GENLIST FROM TEST WHERE PROID = ? FOR UPDATE";
String sqlUpdateBlob = "UPDATE TEST SET GENLIST = ? WHERE PROID = ?";
try {
//conn = this.getConnection();
conn = getConn() ;
if (conn != null) {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sqlInsert);
ps.clearParameters();
ps.setInt(1 , proid.intValue());
ps.executeUpdate();
//conn.commit();
System.out.println(" inserted .... ");
conn.commit();
conn.setAutoCommit(false);
//lock new row
ps = conn.prepareStatement(sqlLockRow);
ps.clearParameters();
ps.setInt(1, proid.intValue());
result = ps.executeQuery();
conn.commit();
conn.setAutoCommit(false);
result.next();
// get byte[] from Hashtable
ObjectOutputStream os = new ObjectOutputStream(byteStream);
os.writeObject(aUserData);
os.close();
byte[] hashBytes = byteStream.toByteArray();

//update blob
ps = conn.prepareStatement(sqlUpdateBlob);

oracle.sql.BLOB dbBlob = (oracle.sql.BLOB )result.getBlob(1);
out = ((oracle.sql.BLOB)dbBlob).getBinaryOutputStream();
out.write(hashBytes);
out.flush();
out.close();
ps.setBlob(1,dbBlob);
ps.setInt(2, proid.intValue());
ps.executeUpdate();
conn.commit();
System.out.println(" done ........");
}
}
catch (Exception ex) {
System.out.println(" exception from saveUserHash() : "+ex);
}
system hang when it execute
result = ps.executeQuery();
I don't understand why ? and if any body got any
working code how to insert CLOB or BLOB into oracle that would be grate ...
Looking forward,
Greetings
NPA.

[Rene Larsen]

This is how I do it...

ResultSet rs = con.executeQuery("select tree_id from ask_tree where user_id='someid' for update");
if (rs.next()) {
   int rowId = rs.getInt(1);
   PreparedStatement updateTreeObject = con.prepareStatement("update ask_tree set tree_object=empty_blob() where tree_id=?");
   updateTreeObject.setInt(1, rowId);
   updateTreeObject.execute();
   con.commit();
   updateTreeObject.close();
   updateTreeObject = null;
}
else {
   PreparedStatement insertTreeObject = con.prepareStatement("insert into ask_tree (user_id, tree_object) values(?, empty_blob())");
   insertTreeObject.setString(1, userId);
   insertTreeObject.execute();
   con.commit();
   insertTreeObject.close();
   insertTreeObject = null;
}
rs = con.executeQuery("select tree_object from ask_tree where user_id='someid' for update");
BLOB writeBlob = null;
if (rs.next()) {
   writeBlob = (BLOB) rs.getBlob(1);
}
else {
   log.fatal("handelSaveAskTree(): BLOB object could not be found...");
}
byte[] data = /* some byte[] */
OutputStream outStream = writeBlob.getBinaryOutputStream();
outStream.write(data);
outStream.close();
outStream = null;
con.commit();

Rene
[ June 09, 2003: Message edited by: Rene Larsen ]