> Hi Tim.
Hiya!!
> 1. Oracle version you are using (8.1.6. I believe) ?
8.1.5
> 2. do you have "jboss.xa.xidclass=oracle.jdbc.xa.OracleXid" in
> jboss.properties ?
no.
> 3. do you use "org.jboss.minerva.xa.XADataSourceImpl" or
> "oracle.jdbc.xa.client.OracleXADataSource" ?
org.jboss.minerva.xa.XADataSourceImpl
> 4. can you post a complete entity bean code that deals with clob/blob ?
(to
> see all little stuff that is being discussed lately, such as
"setAutoCommit"
> and "select ... for update")
Ok, here it comes... a simple Entity bean which maps to a table with only
two fields, one is a varchar2 id, and the other is a clob
I use a global class called DSName to get the OracleResult set and set the
DataSource... I have pasted this into the bottom...
=========================BMP:
package entity;
import java.sql.*;
import javax.ejb.*;
import javax.sql.DataSource;
import java.util.*;
import java.io.* ;
import oracle.jdbc.driver.* ;
import oracle.sql.* ;
public class DiscussionBeanBMP extends DiscussionBean
{
DataSource dataSource;
public String ejbCreate(String discussionid, String xml) throws
CreateException
{
super.ejbCreate(discussionid, xml);
try
{
//First see if the object already exists
ejbFindByPrimaryKey(discussionid);
//If so, then we have to throw an exception
throw new DuplicateKeyException("Primary key already exists");
}
catch(ObjectNotFoundException e)
{
//Otherwise we can go ahead and create it...
}
Connection connection = null;
PreparedStatement statement = null;
try
{
connection = dataSource.getConnection();
statement = connection.prepareStatement("INSERT INTO DISCUSSION
(DISCUSSIONID, XML) VALUES (?, empty_clob() )");
statement.setString(1, discussionid);
if (statement.executeUpdate() != 1)
{
throw new CreateException("Error adding row");
}
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();
wr = null ;
}
rslt.close();
rslt = null ;
statement.close() ;
statement = null ;
connection.close();
connection = null;
return discussionid;
}
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 INSERT INTO DISCUSSION
(DISCUSSIONID, XML) VALUES (?, ?): " + e.toString());
}
finally
{
try
{
if (statement != null)
{
statement.close();
}
}
catch(SQLException e)
{
}
try
{
if (connection != null)
{
connection.close();
}
}
catch(SQLException e)
{
}
}
}
public String ejbCreate(String discussionid) throws CreateException
{
return ejbCreate(discussionid, null);
}
public void ejbRemove() throws RemoveException
{
super.ejbRemove();
Connection connection = null;
PreparedStatement statement = null;
try
{
connection = dataSource.getConnection();
statement = connection.prepareStatement("DELETE FROM DISCUSSION WHERE
DISCUSSIONID = ?");
statement.setString(1, discussionid);
if (statement.executeUpdate() < 1)
{
throw new RemoveException("Error deleting row");
}
statement.close();
statement = null;
connection.close();
connection = null;
}
catch(SQLException e)
{
throw new EJBException("Error executing SQL DELETE FROM DISCUSSION
WHERE DISCUSSIONID = ?: " + e.toString());
}
finally
{
try
{
if (statement != null)
{
statement.close();
}
}
catch(SQLException e)
{
}
try
{
if (connection != null)
{
connection.close();
}
}
catch(SQLException e)
{
}
}
}
public void ejbLoad()
{
discussionid = (String) entityContext.getPrimaryKey();
Connection connection = null;
PreparedStatement statement = null;
try
{
connection = dataSource.getConnection();
statement = connection.prepareStatement("SELECT XML FROM DISCUSSION
WHERE DISCUSSIONID = ?");
statement.setString(1, discussionid);
ResultSet resultSet = statement.executeQuery();
if (!resultSet.next())
{
throw new NoSuchEntityException("Row does not exist");
}
Reader is = resultSet.getCharacterStream( 1 ) ;
char[] buf = new char[ 10000 ] ;
int cc ;
String sxml = "" ;
while( ( cc = is.read( buf ) ) != -1 )
{
sxml += new String( buf, 0, cc ) ;
}
is.close();
xml = sxml ;
statement.close();
statement = null;
connection.close();
connection = null;
}
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 SELECT XML FROM DISCUSSION
WHERE DISCUSSIONID = ?: " + e.toString());
}
finally
{
try
{
if (statement != null)
{
statement.close();
}
}
catch(SQLException e)
{
}
try
{
if (connection != null)
{
connection.close();
}
}
catch(SQLException e)
{
}
}
super.ejbLoad();
}
public void ejbStore()
{
super.ejbStore();
Connection connection = null;
PreparedStatement statement = null;
try
{
connection = dataSource.getConnection();
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;
}
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)
{
}
}
}
public String ejbFindByPrimaryKey(String key) throws
ObjectNotFoundException
{
Connection connection = null;
PreparedStatement statement = null;
try
{
connection = dataSource.getConnection();
statement = connection.prepareStatement("SELECT DISCUSSIONID FROM
DISCUSSION WHERE DISCUSSIONID = ?");
statement.setString(1, key);
ResultSet resultSet = statement.executeQuery();
if (!resultSet.next())
{
throw new ObjectNotFoundException("Primary key does not exist");
}
statement.close();
statement = null;
connection.close();
connection = null;
return key;
}
catch(SQLException e)
{
throw new EJBException("Error executing SQL SELECT DISCUSSIONID FROM
DISCUSSION WHERE DISCUSSIONID = ?: " + e.toString());
}
finally
{
try
{
if (statement != null)
{
statement.close();
}
}
catch(SQLException e)
{
}
try
{
if (connection != null)
{
connection.close();
}
}
catch(SQLException e)
{
}
}
}
public Collection ejbFindAll()
{
Connection connection = null;
PreparedStatement statement = null;
try
{
connection = dataSource.getConnection();
statement = connection.prepareStatement("SELECT DISCUSSIONID FROM
DISCUSSION");
ResultSet resultSet = statement.executeQuery();
Vector keys = new Vector();
while (resultSet.next())
{
String discussionid = resultSet.getString(1);
keys.addElement(discussionid);
}
statement.close();
statement = null;
connection.close();
connection = null;
return keys;
}
catch(SQLException e)
{
throw new EJBException("Error executing SQL SELECT DISCUSSIONID FROM
DISCUSSION: " + e.toString());
}
finally
{
try
{
if (statement != null)
{
statement.close();
}
}
catch(SQLException e)
{
}
try
{
if (connection != null)
{
connection.close();
}
}
catch(SQLException e)
{
}
}
}
public void setEntityContext(EntityContext entityContext)
{
super.setEntityContext(entityContext);
try
{
javax.naming.Context context = new javax.naming.InitialContext();
dataSource = (DataSource) context.lookup(DSName.name);
}
catch(Exception e)
{
throw new EJBException("Error looking up dataSource:" + e.toString());
}
}
}
=========================DSName
package entity;
import java.sql.*;
import javax.ejb.*;
import javax.sql.DataSource;
import java.util.*;
import java.io.* ;
import oracle.jdbc.driver.* ;
import oracle.sql.* ;
public class DSName
{
// swap these two depending on where you are deploying to...
public static final String name = "java:/DataSource" ; // JBOSS ON THE
BEACONSBOX!!!
// public static final String name = "java:comp/env/jdbc/DataSource" ; //
IAS ON YOUR MACHINE!!!
public DSName() {
}
public static OracleResultSet getOracleResultSet( ResultSet r )
{
ResultSet rslt =
((org.jboss.minerva.jdbc.ResultSetInPool)r).getUnderlyingResultSet() ; //
JBOSS
return ((OracleResultSet)rslt) ; // JBOSS
// return (OracleResultSet)r ; // IAS!!!
}
}
> 5. jdbc driver you are using (oci, thin or whatever)
jdbc:oracle:thin
> 6. Whatever else you can think about
Hmmm... Not much else I can think of... This seems to work for me, but it
has not been tested under high strain, and if it doesn't work under high
strain, then I guess I will be looking for another job with less stress!!
(Unless we can figure out what is wrong ;-) )
> we WILL have jboss manipulating lobs on oracle !!! (and than we will CMP
it...)
Hee hee!! It's nice to find another Oracle'r on the list :-)
I _really_ hope this helps...
Let me know if there is anything else you would like...
Sorry to everyone else for spamming the list with this...
See y'all!!
Tim.
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
List Help?: [EMAIL PROTECTED]
