You have to use "xa-datasource".
Only this type can work with two types of different database.
Thanks for your reply.
I am struggling getting the xa-datasource to work.
is giving me this kind of error:
2007-05-16 14:17:28,078 ERROR [org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory] End transaction failed for XAResource
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)
<track-connection-by-tx>true</track-connection-by-tx> <new-connection-sql>set autocommit=1</new-connection-sql> <no-tx-separate-pools>true</no-tx-separate-pools>
made it for the xa-datasource.
But the problem remains.
It DOES appear to be a classloader isolation problem.
More specific there is a problem with static sections of classes with identical packages and ClassNames (but in different ears). I believe instances are correctly referenced via jndi.
I will try classloader separation next.
Getting the classloading isolation correct solved the problem.
I defined a distinct loader-repository in a jboss-app.xml in the META-INF of each ear and set
in the ear-deployer.
This results in an application isolation as desired.
It is not necessary to use xa-datasources - but it also works.
The main problems along the way were to find which part of the application prevented the isolation (in my case it was the scheduler-plugin from outside of the scope of the loader-repository of the applications being referenced from a sar within the application). After sorting that out things were easy.
It was a bit of a surprise to me that the tomcat deployer works in a scoped way ensuring web applications are isolated.
This made me believe in the beginning there was no classloading issue.