To do this in an EJB you need to add a container-transaction element in
your ejb deployment descriptor, and mark your methods which write blobs
as "Required". This means the container will put it all inside a
transaction automatically, you don't need to call setAutoCommit() or
anything else. This works with pooled connections.
Note also that you can do this without using the oracle-specific code
that you have, it is possible to do it with standard JDBC code, using
PreparedStatement.setBinaryStream() (or maybe setCharacterStream() in
your case), but the oracle 8.x thin driver doesn't support this, you need
the OCI driver. I think they fixed this in version 9.
This question might be more appropriate on an Oracle or WebLogic mailing
list though, because it has nothing to do with JRun.
On Tuesday 04 September 2001 09:07, Mark A. Sandee wrote:
> Thanks for your reply. I was on vacation and now I'm temporarily
> working on some COM stuff (yuck),
> but I'll try out your ideas as soon as I get back on it.
> Thanks again.
>
> -----Original Message-----
> From: Vijay Menon [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 08, 2001 8:25 PM
> To: JRun-Talk
> Subject: Re: Setting Blob and Clob data in Oracle from an EJB.
>
>
> 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();
> > }
> > }
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
