Weblogic, Oracle and Blobs — oh my!
The problem — upload a file and put it into a Blob in an Oracle database. The file upload was easy (Thanks Jakarta!) but saving it to the database was a different story. I found many, many examples on the web, but none of them quite worked. Then I read a post about someone struggling with the same thing, only with Weblogic 6. I’m using 8.1, but I used the idea and it worked! I’m still astounded by it.
The problem is that Weblogic’s database layer doesn’t inherit well. A class loader is probably the cause. Oh goody - I love class loader problems! But BEA snuck a helper class in to make this easy.
Actually, I’m not sure if the real problem is Weblogic, Oracle or even the people that gave us the “Blob standard”, but whatever it is, the solution is messy. It’s takes two updates to the same row! My apologies for WordPress messing up the code.
sqlStmt.append("UPDATE table_name");
sqlStmt.append(" BLOB_FIELD=empty_blob()");
sqlStmt.append(" where IDX=?");
pStmt = con.prepareStatement(sqlStmt.toString());
pStmt.setString(1,user);
pStmt.setTimestamp(2,nowSQL);
pStmt.setString(3,trainSymbol);
pStmt.executeUpdate();
con.commit();
// now we need turn off autocommit
boolean lastAuto = con.getAutoCommit();
con.setAutoCommit(false);
// then select the blob for update
StringBuffer sqlStmt2 = new StringBuffer();
sqlStmt2.append("select BLOB_FIELD from table_name");
sqlStmt2.append(" where IDX=? for UPDATE");
pStmt = con.prepareStatement(sqlStmt2.toString());
pStmt.setString(1,trainSymbol);
ResultSet rs2 = pStmt.executeQuery();
while (rs2.next()) {
Object o = rs2.getObject(1);
weblogic.jdbc.wrapper.Blob cast1 = (weblogic.jdbc.wrapper.Blob) o;
BLOB myblob = (BLOB) cast1.getVendorObj();
OutputStream outstream = myblob.getBinaryOutputStream();
outstream.write(blobArray);
outstream.flush();
outstream.close();
}
rs2.close();
con.commit();
con.setAutoCommit(lastAuto);
Note the first thing I did was not to insert the Blob directly into the database — instead I added an empty blob. Why? Because Oracle says so.
Then I turn off Auto Commit, because you can’t to a SELECT... FOR UPDATE with Auto Commit turn on. Why? Cuz Oracle says so.
Why do we need to do a SELECT... FOR UPDATE anyway? Because Oracle, or the blob people, say so.
Why did you need another ResultSet? Weird things happened when I didn’t. I’m not sure who to blame for that — Oracle, BEA, or the person who wrote the DAO class I had to put this into.
What’s with this cast to weblogic.jdbc.wrapper.Blob? I need to do that to get Oracle’s Blob, and casting from java.sql.Blob object didn’t work. Why not? Because BEA says so. I need to do that cast so I could get Oracle’s Blob from getVenderObj()
The rest is elementary — I get an output stream from the blob, write a byte array to it (i.e. blobArray and then cleaned up.