marq
Guest
|
 Posted: Wed Sep 10, 2003 2:25 pm Post subject: Storing a Binary File as a BLOB in Oracle |
 |
|
I cannot believe how convoluted this appears to be in Oracle. I wish
to store a Binary file (roughly 20K) as a BLOB in an Oracle 9i DB.
From previous newsgroup searches, the advice for using BLOBS with
Oracle appears to be a 3 step process;
1) Create an empty blob (using empty_blob()) in your first procedure
call.
2) Execute a 2nd procedure to get a BLOB handle on the empty BLOB
3) Get the BLOBs binary output stream and stream the contents of my
binary file into this.
I have to say this feels so bizarre.. if there's a more
straight-forward approach for dealing with BLOBs in Oracle 9 I'd love
to hear it! ;-)
Anyway, the above 3 steps all work until at the very end when I try to
call either flush() or close() on my outputstream (that has streamed
binary data into the BLOB). When that happens I get an SQL Exception
saying
ORA-00942: table or view does not exist
Anyways heres the relevant code... (it's really only the bottom few
lines that count but im including the whole thing just in case...)
// INITIALLY CREATE AN EMPTY BLOB IN DB
CallableStatement stmt = connection.prepareCall("{ call
CCI_SETAPPLPROD.setProfile(" +
"?," +
"?," +
"empty_blob()," + // (blob)
"?," +
"?, " +
"'" + profile.label + "'," + // label
"'" + profile.profDescription + "'," + // description
"'FALSE')}");
stmt.clearParameters();
if(profile.profileID==null) // profile ID
stmt.setNull(1, Types.VARCHAR);
else stmt.setString(1, profile.profileID);
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.setString(2, profile.version); // version
Trace.getInstance().out("DBProfile::update() - file as str :" +
new String(profile.text));
//stmt.setString(3, new String(profile.text)); // blob as varchar
stmt.setInt(3, profile.type.int_value()); // prof type
stmt.setString(4, profile.lastUpdateDate); // last update date
stmt.registerOutParameter(4, Types.VARCHAR);
// execute insert
executeStoredProc(stmt);
String profileID = stmt.getString(1);
//profile.lastUpdateDate = stmt.getString(1);
stmt.close();
// NOW RETRIEVE THE BLOB AND TRY TO UPDATE IT........
// query + blob update
// Deal with blob - seems very convoluted in oracle..
BLOB objectBlob;
CallableStatement queryStatement = connection.prepareCall("{ ? =
call CCI_GETAPPLPROD.getProfile(" +
"?," + // id
"?," + // version
"?," + // type
"?," + // label
"?)}"); // description
queryStatement.registerOutParameter(1, OracleTypes.CURSOR);
queryStatement.setString(2, profileID);
queryStatement.setNull(3, Types.VARCHAR);
queryStatement.setNull(4, Types.NUMERIC);
queryStatement.setNull(5, Types.VARCHAR);
queryStatement.setNull(6, Types.VARCHAR);
queryStatement.execute();
ResultSet rset =
((OracleCallableStatement)queryStatement).getCursor(1);
oracle.jdbc.driver.OracleResultSet oracleRS =
(oracle.jdbc.driver.OracleResultSet) rset;
oracleRS.next();
objectBlob = oracleRS.getBLOB(5);
System.out.println("got blob :" + objectBlob);
byte[] buffer = new byte[objectBlob.getBufferSize()];
Trace.getInstance().out("DBProfile::update() - BLOB buffer size :"
+ objectBlob.getBufferSize());
int bytesRead = 0;
try {
System.out.println("DBProfile::update() - trying to stream byte
array in BLOB object..");
// STREAM THE BINARY FILE INTO BLOB HERE.....
ByteArrayInputStream inputStream = new
ByteArrayInputStream(profile.getText());
OutputStream outputStream = objectBlob.getBinaryOutputStream();
while ((bytesRead = inputStream.read(buffer)) != -1)
outputStream.write(buffer, 0, bytesRead);
// THIS IS WHERE THE EXCEPTION OCCOURS....
outputStream.flush();
outputStream.close();
inputStream.close();
any help/suggestions/resources greatly appreciated  thanks.
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
