Solved

SOS - Oracle BLOB insertion problem with JBOSS Minerva connection pool

Posted on 2002-03-06
7
483 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

828 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