We have done an enterprise application (we use IBM WAS 5.1) which has MDBs w
hich reads messages from various Queues (IBM Websphere 5.3 with CSD0011). MD
Bs then calls a local stateless session bean (CMT) which indeed calls Oracle
 9i stored procedure based
on message type.Based on the output of the stored procedure (0 - for unsucce
ss & 1 - success) we are rollbacking the transaction.

We faced problem when we rollback. We used ojdbc14.jar. I set Maximum Messag
es as 150 and Maximum session as 1 for listeners in WAS. I created XA enable
d Queue Connection Factory by selected the checkbox Enable XA. I made severa
l changes in various areas
but still the same status.I don't know how to tackle this issue. Sometimes I
 am getting exceptions like :

[11/12/05 17:13:55:297 AST] 5edb3e99 ExceptionUtil E CNTR0019E: Non-appl
ication exception occurred while processing method "callStoredProc". Excepti
on data: com.ibm.websphere.csi.CSITransactionRolledbackException: Transactio
n rolled back
at com.ibm.ejs.csi.TransactionControlImpl.getCurrentTransactionalUOW(Transac
tionControlImpl.java(Compiled Code))
at com.ibm.ejs.csi.TransactionControlImpl.preInvoke(TransactionControlImpl.j
ava(Compiled Code))
at com.ibm.ejs.container.EJSContainer.preInvoke_internal(EJSContainer.java(C
ompiled Code))
at com.ibm.ejs.container.EJSContainer.preInvoke(EJSContainer.java(Inlined Co
mpiled Code))
at fix.procedure. EJSLocalStatelessStoredProcSession_ef2f3
6b3.callStoredProc(
Unknown Source)
at exe.rpt.Group05MDBBean.onMessage(Unknown Source)
at com.ibm.ejs.jms.listener.MDBWrapper$PriviledgedOnMessage.run(MDBWrapper.j
ava(Compiled Code))
at com.ibm.ejs.jms.listener.MDBWrapper.callOnMessage(MDBWrapper.java(Compile
d Code))
at com.ibm.ejs.jms.listener.MDBWrapper.onMessage(MDBWrapper.java(Compiled Co
de))
at com.ibm.mq.jms.MQSession.run(MQSession.java(Compiled Code))
at com.ibm.ejs.jms.JMSSessionHandle.run(JMSSessionHandle.java:923)
at com.ibm.ejs.jms.listener.ServerSession.connectionConsumerOnMessage(Server
Session.java:697)
at com.ibm.ejs.jms.listener.ServerSession.onMessage(ServerSession.java:482)
at com.ibm.ejs.jms.listener.ServerSession.dispatch(ServerSession.java:449)
at sun.reflect.GeneratedMethodAccessor57.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorI
mpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDi
spatcher.java:37)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:91)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:127)
at com.ibm.ejs.jms.listener.ServerSession.run(ServerSession.java:372)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)


1) How to check that Oracle 9i is XA Enabled?
2) Is there anything to do in Oracle Stored Procedure to support distributed
 Transaction?
3) Do I need to change anything in the ejb-jar.xml file to make MDBs      on
Message() method as Required or NotSupported and session bean's StoredProcSe
ssionBean as RequiresNew.
4) Or else do I need to go for Bean Managed Transaction.

PLS. Give me the Solution.


My MDB Code (Some Portions) :
==============================

public void onMessage(javax.jms.Message msg)
{
String txtMsg = null;
try
{
if (msg instanceof BytesMessage)
{
BytesMessage bytesMessage = (BytesMessage)msg;
byte[] buff = new byte[5000];

int totBytesRead = bytesMessage.readBytes(buff);
txtMsg = new String(buff,0,totBytesRead);

MsgParser parser = new MsgParser(txtMsg);
String [] msgPart = parser.getParsedMsg();

if (txtMsg !=null)
{
StoredProcSessionLocal storedProcBean = storedProcHome.create();
storedProcBean.callStoredProc("Group01", msgPart);
}
else
{
logger.info("MsgPart is null. Stored Procedure is not called "+txtMsg);
this.getMessageDrivenContext().setRollbackOnly();
}
}
catch(JMSException jmse)
{
logger.error("JMSException in Group01MDB: " + jmse + " ~~~ "+txtMsg);
if (!this.getMessageDrivenContext.getRollbackOnly()) {
this.getMessageDrivenContext.setRollbackOnly();
}
}
catch(Exception e)
{
logger.error("Exception in Group01MDB: " + e+" ~~~ "+txtMsg);
if (!this.getMessageDrivenContext.getRollbackOnly()) {
this.getMessageDrivenContext.setRollbackOnly();
}
}
}
}


My Stateless Session Bean Code (Some Portions) :
 ========================================
========

public void ejbCreate() throws javax.ejb.CreateException {
logger = Logger.getLogger("StoredProcSessionBean");
try
{
InitialContext initCtx = new InitialContext();
ds = (DataSource) initCtx.lookup("jdbc/fix");
}
catch(Exception e)
{
logger.error("StoredProcSessoionBean : Exception while Creating DataSource "
+e);
}
}


public void ejbRemove() {
try
{
if(cStmt != null)
cStmt.close();
}
catch(SQLException e)
{
logger.error("StoredProcSessoionBean :  Exception while closing callable sta
tement " + e);
}
}


public void callStoredProc(String queueName, String[] msgPart) throws Ex
ception
{
try {

if ((ds == null) || (msgPart.length == 0))
{
logger.fatal("StoredProcSessoionBean : Datasource is null or Message is null
");
this.getSessionContext().setRollbackOnly();
return;
}

long msgId = new Long(msgPart[0]).longValue();
String msgType = msgPart[1];
String message = msgPart[2];

try
{
conn = ds.getXAConnection();
conn.setAutoCommit(false);
}
catch(SQLException e)
{
logger.error("StoredProcSessoionBean : Exception while getting DB Connection
 " + e
this.getSessionContext().setRollbackOnly();
return;
}

try
{
if (msgType.equals("8"))
{
cStmt = conn.prepareCall("{call db_proc_1(?,?,?,?)}");
}
else if (msgType.equals("9"))
{
cStmt = conn.prepareCall("{call db_proc_2(?,?,?,?)}");
}
else if (msgType.equals("B"))
{
cStmt = conn.prepareCall("{call db_proc_3(?,?,?,?)}");
}
else
{
this.getSessionContext().setRollbackOnly();
return;
}

if (cStmt != null)
{
cStmt.registerOutParameter(1, Types.INTEGER);
cStmt.registerOutParameter(2, Types.VARCHAR);
cStmt.setLong(3, msgId);
cStmt.setString(4, msgBuf);
cStmt.execute();
}

if(cStmt.getInt(1) == 1)
{
logger.info("SP Returns Value " + cStmt.getInt(1) + " Message "+ cStmt.getSt
ring(2));
return;
}
else
{
logger.info("SP Returns Value " + cStmt.getInt(1) + " Message "+ cStmt.getSt
ring(2));
this.getSessionContext().setRollbackOnly();
return;
}
}
catch (Exception e) {
logger.fatal("StoredProcSessoionBean : Exception : " + e);
this.getSessionContext().setRollbackOnly();
return;
}
}
finally
{
try
{
if(conn != null) conn.close();
}
catch(SQLException e)
{
logger.error("StoredProcSessoionBean : Exception while closing DB Connection
 " + e);
this.getSessionContext().setRollbackOnly();
return;
}
}
}

[ Post a follow-up to this message ]