I managed to set blob and clob data in Oracle 8.1.5 and 8.1.8 using EJBs,
but it was a real kludge.
I was wondering if anyone knew of a better solution.
The problem is that when you access a blob (or clob) field in Oracle you
just get a
blob locator. This locator in turn is used to obtain the blob object to
which the data
can be written. To do this, you have to use SELECT ... FOR UPDATE to lock
the row
to ensure the blob locator remains valid. I guess a transaction is needed
here, but how does
that work with a pooled connection that wants to autocommit after every
statement?
I use the routine below in a Session bean to accomplish the job. It won't
work
without the "FOR UPDATE", saying the row is not locked. Using the "FOR
UPDATE" is not
allowed with AutoCommit true. Setting AutoCommit to false is not allowed
with a pooled
connection. Thus, I could only get this to work by using a non-pooled
connection
which in the code below costs a lot of time for each blob! I'm using JRun
3.0 SP2.
Bottom line is I'd like to use a pooled connection and keep this code in the
BMP EJB.
Can I set blob data in Oracle using a pooled connection?
public void setBlobData(String tableName, String pkey, int pkeyId,
InputStream dataIS)
throws RemoteException {
int chunkSize = 1988; // This number is optimized for the table chunk
size
byte[] buffer = new byte[chunkSize];
try {
// NOTE: The setting of the blob data was moved to session bean
ImageBlob. This was
// done so that a separate non-pooled connection to the database could
be used so
// the command .setAutoCommit(false) could be used. I could not set
the blob data
// without first turning off auto commit which is not allowed with a
pooled connection.
// This is why "source1" is not used here to get the connection.
// Creating a connection outside of the connection pool is not
desirable and should
// only be done if absolutely necessary. i.e. Don't get connections
this way in other
// places.
Class.forName(jdbcDriver);
Connection connection = DriverManager.getConnection(jdbcConnectString,
jdbcUsername, jdbcPassword);
connection.setAutoCommit(false);
try {
BLOB blob;
Statement stmt = connection.createStatement();
String cmd = "SELECT BlobData FROM "+tableName+
" WHERE "+pkey+" = "+pkeyId+" FOR UPDATE";
ResultSet res = stmt.executeQuery(cmd);
if (res.next()) {
// Get blob locator from the result set
blob = ((OracleResultSet)res).getBLOB(1);
OutputStream outstream = blob.getBinaryOutputStream();
int length = -1;
// Copy the data to the BLOB
while ((length = dataIS.read(buffer)) != -1) {
outstream.write(buffer, 0, length);
}
outstream.close();
res.close();
connection.commit();
}
stmt.close();
} catch (IOException e) {
e.printStackTrace();
throw new RemoteException(e.toString());
} finally {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
throw new RemoteException(e.toString());
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
