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
