Solved

SOS - Oracle BLOB insertion problem with JBOSS Minerva connection pool

Posted on 2002-03-06
7
480 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
  • 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 35

Accepted Solution

by:
TimYates earned 200 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
triangle challenge 4 77
changePi Challenge 15 76
JAVA part two 5 41
micro services spring boot application error 3 33
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now