?
Solved

SOS - Oracle BLOB insertion problem with JBOSS Minerva connection pool

Posted on 2002-03-06
7
Medium Priority
?
487 Views
Last Modified: 2013-11-24
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.
0
Comment
Question by:ivatury
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 6843892
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
 
LVL 35

Expert Comment

by:TimYates
ID: 6843895
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
 
LVL 35

Expert Comment

by:TimYates
ID: 6843898
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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 35

Accepted Solution

by:
TimYates earned 800 total points
ID: 6843900
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
 

Author Comment

by:ivatury
ID: 6846386
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
 

Author Comment

by:ivatury
ID: 6846679
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
 
LVL 35

Expert Comment

by:TimYates
ID: 6846800
No problems!

Glad I could help :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
A solution for Fortify Path Manipulation.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month8 days, 18 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question