Ramesh ,
The above error has been resolved but i am facing another problem while i am going to insert data into view which was created using two different datasources(mysql and sqlserver):
Following are my configuration files:
standalone-teiid.xml:
<xa-datasource jndi-name="java:/mysqlXA" pool-name="mysqlXA" enabled="true" use-ccm="true" statistics-enabled="false">
<xa-datasource-property name="PortNumber">
3306
</xa-datasource-property>
<xa-datasource-property name="ServerName">
xxxx
</xa-datasource-property>
<xa-datasource-property name="DatabaseName">
aakash_test
</xa-datasource-property>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
<driver>test_mysql</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
<interleaving>false</interleaving>
<pad-xid>false</pad-xid>
<wrap-xa-resource>false</wrap-xa-resource>
</xa-pool>
<security>
<user-name>root</user-name>
<password>root</password>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
</validation>
<timeout>
<set-tx-query-timeout>false</set-tx-query-timeout>
<blocking-timeout-millis>0</blocking-timeout-millis>
<idle-timeout-minutes>0</idle-timeout-minutes>
<query-timeout>0</query-timeout>
<use-try-lock>0</use-try-lock>
<allocation-retry>0</allocation-retry>
<allocation-retry-wait-millis>0</allocation-retry-wait-millis>
<xa-resource-timeout>0</xa-resource-timeout>
</timeout>
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</xa-datasource>
<xa-datasource jndi-name="java:/sqlserverDS" pool-name="sqlserverDS" enabled="true" use-java-context="true" use-ccm="true">
<xa-datasource-property name="PortNumber">
1433
</xa-datasource-property>
<xa-datasource-property name="DatabaseName">
xxxxx
</xa-datasource-property>
<xa-datasource-property name="ServerName">
xxxxx
</xa-datasource-property>
<xa-datasource-property name="Instance">
xxxxx
</xa-datasource-property>
<xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
<driver>sqlserver</driver>
<xa-pool>
<min-pool-size>10</min-pool-size>
<max-pool-size>20</max-pool-size>
<prefill>false</prefill>
<use-strict-min>false</use-strict-min>
<flush-strategy>FailingConnectionOnly</flush-strategy>
<is-same-rm-override>true</is-same-rm-override>
<no-tx-separate-pools>true</no-tx-separate-pools>
</xa-pool>
<security>
<user-name>root</user-name>
<password>root</password>
</security>
</xa-datasource>
<drivers>
<driver name="test_mysql" module="com.mysql">
<driver-class>com.mysql.jdbc.Driver</driver-class>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
</driver>
<driver name="sqlserver" module="com.sqlserver">
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
</driver>
</drivers>
mysql-xa-vdb.xml :
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="EmployeeVDBXA" version="1">
<description>A Dynamic VDB</description>
<property name="UseConnectorMetadata" value="false"/>
<model name="join" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIEW SvcView (CustName string,CustAddress string, cust_id long,item string,amount long) OPTIONS (UPDATABLE true)AS
SELECT CustName,CustAddress, cust_id,item,amount
FROM "DQ_2012.dbo.Customer" INNER JOIN "aakash_test.order" ON CustId = cust_id;
CREATE TRIGGER ON SvcView INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO DQ_2012.dbo.Customer (CustName,CustAddress) VALUES (NEW.CustName, NEW.CustAddress);
INSERT INTO aakash_test.order (cust_id,item,amount) VALUES (NEW.cust_id,NEW.item,NEW.amount);
END
]]>
</metadata>
</model>
<model visible="true" type="PHYSICAL" name="mysqlModel">
<source name="sqlTest2" translator-name="mysql5" connection-jndi-name="java:/mysqlXA"/>
</model>
<model visible="true" type="PHYSICAL" name="sqlServerModel">
<source name="sqlTest" translator-name="sqlserver" connection-jndi-name="java:/sqlserverDS"/>
</model>
</vdb>
Insert query:
select * from SvcView;
Result :
Connie,706 Clyde Station,1,Item1,23120
Jackie,1353 Middleton Crescent,2,Item2,342342
Jeannie,814 Long Run,3,Item3,23423423
But when i am using below insert query it does not work:
insert into SvcView(CustName,CustAddress, cust_id,item,amount)values('testCust101','testAdd101',101,'testItem101',34234)
I am getting below error:
org.teiid.jdbc.TeiidSQLException: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 sqlTest2: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@324
54e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastValidated=1450431
523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@
481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:624)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:72)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:539)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37)
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)
at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:220)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:255)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:110)
at com.sun.proxy.$Proxy1.read(Unknown Source)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:349)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:548)
at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:347)
at JDBCClientInsert.execute(JDBCClientInsert.java:74)
at JDBCClientInsert.main(JDBCClientInsert.java:42)
Caused by: [TeiidProcessingException] TEIID30504: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 sqlTest2: TEIID11009 java.sql.
SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnec
tionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastV
alidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManage
dConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]
1 [TeiidException] TEIID11009: Error Code:TEIID11009 Message:Remote org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceEx
ception: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED m
anaged connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1
450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA]
xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]
2 [SQLException]Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.con
nectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0
lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed63
4e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]
3 [TeiidRuntimeException]IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0
:ffff0a1e7f4c:-2aee09e6:5673d41a:34 status: ActionStatus.ABORT_ONLY >
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:374)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)
at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)
at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)
at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)
at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:604)
at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:69)
at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:385)
at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:294)
at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:268)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
at org.teiid.query.processor.proc.ForEachRowPlan.nextBatch(ForEachRowPlan.java:147)
at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: [TeiidException] TEIID11009: Error Code:TEIID11009 Message:Remote org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.R
esourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@32454e63[state=DE
STROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastValidated=1450431523940 lastChe
ckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=
mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]
1 [SQLException]Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.con
nectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0
lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed63
4e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]
2 [TeiidRuntimeException]IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0
:ffff0a1e7f4c:-2aee09e6:5673d41a:34 status: ActionStatus.ABORT_ONLY >
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:271)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:68)
at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:194)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:313)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy56.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)
... 36 more
Caused by: java.sql.SQLException: Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.
jboss.jca.core.connectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b con
nection handles=0 lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.stra
tegy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=nul
l]
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)
at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:67)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:267)
... 47 more
Caused by: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff0a1e7f4c:
-2aee09e6:5673d41a:34 status: ActionStatus.ABORT_ONLY >
at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:970)
at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:412)
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:554)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:922)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:740)
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)
... 49 more
-------------------------------