SOS - Oracle BLOB insertion problem with JBOSS Minerva connection pool

Hi ,
Im using Jboss and oracle with oracle thin JDBC driver with the minerva connection pool of JBoss server for connection pooling.

I would like to know which is the best solution for inserting BLOB's from the entity bean.Since the entities are pooled and cached in the app server should i use it to put the BLOB/CLOB columns inside the entity beans or should i use a stateless session bean to do it ?

I tried pushing the blob from the Entity bean but its not going more than 2Kb into it , if the size is more than 2k then the blob is NULL in the database using the setBinaryStream() of jdbc.If you would like to see the code which im using inside the Entity i will post the piece of code here

thanx and regards

Bhushan
PS: I have to make this thing work in coming 2 days.
ivaturyAsked:
Who is Participating?
 
TimYatesConnect With a Mentor Commented:
Hehe -- stoopid cut and paste...

public static OracleResultSet getOracleResultSet( ResultSet r )
{
  ResultSet rslt = ((org.opentools.minerva.jdbc.ResultSetInPool)r).getUnderlyingResultSet() ; // JB2.2
  return ((OracleResultSet)rslt) ;
}
0
 
TimYatesCommented:
Ahhh, ok...

First off, you will have to use the BMP method with your entity beans (as CMP just won't cut it)...

Secondly, the code you will need:

This is the ejbStore method out of our working JBoss project, so I hope it helps:

---------------------------------

  public void ejbStore()
  {
    if( !dirty ) return ;
    super.ejbStore();
    Connection connection = null;
    PreparedStatement statement = null;
    try
    {
      connection = dataSource.getConnection();
      connection.setAutoCommit( false );
      statement = connection.prepareStatement("UPDATE DISCUSSION SET XML = empty_clob() WHERE DISCUSSIONID = ?");
      statement.setString(1, discussionid);
      if (statement.executeUpdate() < 1)
      {
        throw new NoSuchEntityException("Row does not exist");
      }
      statement.close();
      statement = null;

      // Ok, now we have added the row, we can set the clob data....
      // 1: lock the row...
      statement = connection.prepareStatement( "SELECT XML FROM DISCUSSION WHERE DISCUSSIONID = ? FOR UPDATE" ) ;
      statement.setString( 1, discussionid );
      ResultSet rslt = statement.executeQuery() ;
      if( rslt.next() )
      {
        CLOB cl = DSName.getOracleResultSet( rslt ).getCLOB( 1 ) ;
        Writer wr = cl.getCharacterOutputStream() ;
        wr.write( xml.toString() );
        wr.close();
      }
      rslt.close();
      rslt = null ;
      statement.close() ;
      statement = null ;

      connection.close();
      connection = null;
      dirty = false ;
    }
    catch( IOException e )
    {
      throw new EJBException( "Arse...  stream error...  data was not updated...  oh well... : " + e.toString() );
    }
    catch(SQLException e)
    {
      throw new EJBException("Error executing SQL UPDATE DISCUSSION SET XML = ? WHERE DISCUSSIONID = ?: " + e.toString());
    }
    finally
    {
      try
      {
        if (statement != null)
        {
          statement.close();
        }
      }
      catch(SQLException e)
      {
      }
      try
      {
        if (connection != null)
        {
          connection.close();
        }
      }
      catch(SQLException e)
      {
      }
    }
  }

----------------------------------

As you can see, the important bit is that you set the clob column to empty_clob() first, then lock it for updating, then write to the handle this returns...

I hope this helps you!!

Good luck!  This is one of the final hurdles :-)

And nice choice with JBoss :-)  It r0x0rs :-)

Tim.

PS:  I can recommend the jboss-user mailing list (you can get to it from the sourceforge project page for JBoss), and the forums at JBoss.org :-)

0
 
TimYatesCommented:
PPS:  Hehe...the "dirty" bit is just something we added to stop Entity Beans storing when they had not changed, as this slowed things down a bit...  But as it happens, we could probably have just changed our container options -- heehehe, well I guess we were learning as we went on too (and still are) :-)

Hope this has helped you!!

Good Luck!!
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TimYatesCommented:
BAH!!  Just noticed as well, that this bit:

if( rslt.next() )
{
 CLOB cl = DSName.getOracleResultSet( rslt ).getCLOB( 1 ) ;
 Writer wr = cl.getCharacterOutputStream() ;
 wr.write( xml.toString() );
 wr.close();
}

Calls this static method (inside my DSName class)

public static OracleResultSet getOracleResultSet( ResultSet r )
{
 ResultSet rslt = ((org.opentools.minerva.jdbc.ResultSetInPool)r).getUnderlyingResultSet() ; // JBOSS 2.2
}

As you have to get the underlying result set from the JBoss one...  As you can see...this code is for JBoss 2.2, and the packaging may have changed between then, and the version you have, but this shouldn't be too hard to find..

You can only do getCLOB on an OracleResultSet :-(

Good luck again!!

Tim.
0
 
ivaturyAuthor Commented:
Just wokeup to see the comment :)

1)Yeah we are using BMP and not CMP - I will check the code and post in back again once it works
BTW I had posted in JBOSS on source forge and forums on jboss.org but , after their server crash atleaset for now most of the stuff is not there ,Regarding BLOB very few answers are there

Bhushan
0
 
ivaturyAuthor Commented:
Thanx TIM , I got it working with ur suggestion

I think its woth 200 , since my post in JBOSS forums also didnt yeild any fruitful results , no straight forward answers there neither any work-arounds

I think i can now close this post


regards

Bhushan
Cheif Architect Products
SM
0
 
TimYatesCommented:
No problems!

Glad I could help :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.