Hi Mark
I have tried inserting clobs into tables without any problems. As far as I
am aware, if the entire sequence of sql statements is within the
store/create statements, the rollback is automatic. In case of any error,
the entire transaction is rolled back.
However, we are using weblogic as the EJB container. In that, I am using the
following code.
In the create method, 1st I insert an empty clob using the following sql.
INSERT INTO Messages (messageId, subject, text) " +
" VALUES (?, ?, empty_clob())";
this inserts an empty clob object.
Then, retrieve the object by creating a clob object and reading the data in
to it. I use the following code.
oracle.sql.CLOB oClob;
try {
oClob = (oracle.sql.CLOB)rs.getObject(1);
Writer writer = oClob.getCharacterOutputStream();
writer.write(text);
writer.flush();
writer.close();
}
Then, update the row again using
ps.setClob(2, oClob)
I haven't had any problems with this, mainly because I think weblogic
creates a transaction as it enters this method and rolls back if any one
fails.
Cheers
Vijay
----- Original Message -----
From: "Mark A. Sandee" <[EMAIL PROTECTED]>
To: "JRun-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, August 08, 2001 10:02 PM
Subject: Setting Blob and Clob data in Oracle from an EJB.
> 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
