Two datasources at one method

[sa_shadow]

I have a requirement to import data from mssql DB into mine, while it's mysql.
I've created two datasources via components.xml, and both work, but separately. for example:

@In(create = true)
private EntityManager exportEntityManager;

@In(create = true)
private EntityManager entityManager;
....
public void textExport() {
        log.info(a());
        log.info(b());
    }
    private int a() {
        List<ViewStaff> lst = exportEntityManager.createQuery("select s from ViewStaff s").getResultList();
        return lst.size();
    }
    private int b() {
        List<Instructor> lst = entityManager.createQuery("select s from Instructor s").getResultList();
        return lst.size();
    }

When i call only a() or b() - everything works fine, i have number of selected rows. But this code produces exception:

15:57:16,972 WARN  [loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] Transact
ionImple.enlistResource - XAResource.start returned: XAException.XAER_PROTO for < 131075, 29, 27, 1--3ff637f8:442:49117cd4:212a-3ff637f8:442:49117cd4:2130
                                               >
15:57:16,972 ERROR [STDERR] org.jboss.resource.connectionmanager.JBossLocalXAException: Trying to start a new tx when old is not complete! old: < 131075, 29, 27, 1--3ff637f8:442:49
117cd4:20ee-3ff637f8:442:49117cd4:20f5                                                                         >, new < 131075, 29, 27, 1--3ff637f8:442:49117cd4:212a-3ff637f8:442:4
9117cd4:2130                                                                         >, flags 0
15:57:16,972 ERROR [STDERR]     at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.start(TxConnectionManager.java:901)
15:57:16,972 ERROR [STDERR]     at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:776)
15:57:16,972 ERROR [STDERR]     at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:499)
15:57:16,972 ERROR [STDERR]     at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener$TransactionSynchronization.enlist(TxConnectionManager.java:788
...
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
        at java.lang.Thread.run(Thread.java:619)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
        at org.spbstu.cqc.demo.CountQuantityInGroup.a(CountQuantityInGroup.java:49)
        at org.spbstu.cqc.demo.CountQuantityInGroup.textExport(CountQuantityInGroup.java:43)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
        at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
        at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
        at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
        at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
        at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77)
        at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
        at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
        at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
        at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
        at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
        at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
        at org.spbstu.cqc.demo.CountQuantityInGroup_$$_javassist_2.textExport(CountQuantityInGroup_$$_javassist_2.java)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
        at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:274)
        at org.jboss.el.parser.AstMethodSuffix.getValue(AstMethodSuffix.java:59)
        at org.jboss.el.parser.AstMethodSuffix.invoke(AstMethodSuffix.java:65)
        at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
        at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
        at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
        at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
        ... 51 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
        at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
        at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
        at org.hibernate.loader.Loader.doQuery(Loader.java:673)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2220)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
        at org.hibernate.loader.Loader.list(Loader.java:2099)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
        ... 82 more
Caused by: org.jboss.util.NestedSQLException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Thro
wable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117cd4:212a status: ActionStatus.ABORT_ONLY >); - nested throwa
ble: (org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.T
hrowable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117cd4:212a status: ActionStatus.ABORT_ONLY >))
        at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:95)
        at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:47)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
        ... 96 more
Caused by: org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.l
ang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117cd4:212a status: ActionStatus.ABORT_ONLY >)
        at org.jboss.resource.connectionmanager.TxConnectionManager.managedConnectionReconnected(TxConnectionManager.java:358)
        at org.jboss.resource.connectionmanager.BaseConnectionManager2.reconnectManagedConnection(BaseConnectionManager2.java:524)
        at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:405)
        at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:849)
        at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
        ... 98 more
Caused by: javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117
cd4:212a status: ActionStatus.ABORT_ONLY >
        at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener$TransactionSynchronization.checkEnlisted(TxConnectionManager.java:759)
        at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.enlist(TxConnectionManager.java:592)
        at org.jboss.resource.connectionmanager.TxConnectionManager.managedConnectionReconnected(TxConnectionManager.java:352)
        ... 102 more

This is not the complete stack trace but i hope it's enough.
If a try to swap methods call i have same problem.
Tried to annotate methods with @Transactional(TransactionPropagationType.NEVER)
but brought no result.

Thanks for your attention!

[obfuscator]

Hi!

Just to check, are you using distributed (XA) drivers for both sources? This is necessary when having transactions spanning more than one source.

[sa_shadow]

thanks, i've changed driver for mysql, mssql server controlled by other person, i hope he will be able to install driver.

maybe there's a way to split transactions anyway? i mean to get some data using first EntityManager, create some data objects to store data between different transactions and then use another EM.

[rhills]

Hi Artem,

We're currently doing something similar, except we're copying data between a SQLServer DB and an Oracle DB and are using local-tx datasources.  In our Action bean, the only annotation we have on the whole bean is:

@Scope(value = ScopeType.CONVERSATION).

We inject the Hibernate session:

@In private Session session;

Then, for the copy method itself, we simply get a hibernate transaction:

Transaction tx = session.getTransaction();

do the copy process in the normal way (well almost, I actually use a parameterised query to insert/update entities in the target DB rather than updating the entity classes directly as I've found the former method at least twice as quick) and then commit the transaction or roll it back if any exception occurs.

This seems to commit/rollback both DBs without any problems.

So, I guess this is using some slightly older techniques than the nice streamlined stuff we're used to with Seam, but it works well and is quite quick.

HTH,

[sa_shadow]

Thanks everyone, finally i've done what i need, this http://www.seamframework.org/Community/MultipleNonxadatasources helped me a lot.
i have similar thing, i've just turned off tx management for one datasource that is read-only for me. if someone needs it, at persistence.xml:

<persistence-unit name="uName" transaction-type="RESOURCE_LOCAL">
          <provider>org.hibernate.ejb.HibernatePersistence</provider>
          <non-jta-data-source>java:/someDatasource</non-jta-data-source>

and i've defuned this datasource as 'no-tx-datasource' at ds.xml
so now i can inject both entity managers into one component and use wherever i need. Course, only db is writable.