Thanks for the suggestion, but we have a requirement a little down the
road to use Oracle snapshots to maintain identical copies of the database
at three locations. This is what caused us to move our long raw columns
(which are not supported in snapshots) to blobs and clobs.
Keeping this data in files referenced from Oracle is not supported by
snapshots either (i.e. the references will be kept in sync, but the
actual data in the files won't be copied around).
-----Original Message-----
From: Ben Groeneveld [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 08, 2001 12:28 PM
To: JRun-Talk
Subject: Re: Setting Blob and Clob data in Oracle from an EJB.
Depending on your application, consider storing the blobs directly on
disk. Filesystems can be efficient and fast. Just keep a reference to
them in oracle. BenG.
Mark A. Sandee wrote:
>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
