Multiple database

[Tmmet Johnson]

Hi,
I have a question.
In my StatelessSessionBean.,I have a bean method set to transaction attribute ,Required.
Another method set to transaction attribute,RequiredNew as below.
I am not using XA datasource .And, I don�t need a two phase commit.
I get the error message at runtime in methodB-
�An illegal attempt to use multiple resources that have only one-phase capability has occurred within a global transaction�.
Can anyone let me know why I get this error?

MethodA (int i) //method A set to Required
{
//Makes call to Data Access Object to perform update in Oracle database
//Calls method B
methodB(i);

}

methodB( int i) // methodB is set to RequiresNew
{

if(i == 100){
//makes call to Data Access Object to perform insert in Oracle database
}
else{
//makes call to Data Access Object to perform update in DB2 database
}

}

[Babji Reddy]

Looks like you have setup Datasources for Oracle and DB2 in your AppServer and using the datasource to obtain a connetion. you will get this exception when you use two different resources (Database, JMS source etc) in a single EJB Transaction.

One way of getting around is use fixed connection instead of pooled connection, for one of the DBs. And commit/rollback the fixed connection accordingly.

Try making the second method with 'Not supports' transaction attributes, this way the second method will not run in a transaction, but you cannot do commit/rollback on the connection that you use in second method.
(If the exception still occurs, (not likely) make a separate EJB that runs in 'Not supports' transaction mode and call it from method1.

[Roger Chung-Wee]

I believe the problem is caused by accessing multiple resources (databases) within a single transaction. There are two solutions.

1. Switch to XA drivers and XA-capable data sources.

2. Declare separate methods for separate DBs (best).