Insert a rows in multiple database by using teiid VDB
praksah May 31, 2016 9:00 AMI am Inserting multiple rows in database by using a VDB. for that I manually deployed VDB which has model for mysql and sqlserver And by using this single VDB; I execute insert statements for mysql and sqlserver in single connection.
Below is the code snippet for getting connection object
static Connection getDriverConnection(String host, String port, String vdb) throws Exception {
String url = "jdbc:teiid:" + vdb + "@mm://" + host + ":" + port + ";showplan=on";
Class.forName("org.teiid.jdbc.TeiidDriver");
return DriverManager.getConnection(url, "user", pwd);
}
Below is the code snippet for executing insert statement
public static void execute(Connection connection, String sql) throws Exception {
try {
Statement statement = connection.createStatement();
int rwo_insert = statement.executeUpdate(sql);
System.out.println("No. of Row inserted => " + rwo_insert);
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
Connection connection = getDriverConnection(host, port, vdb);
connection.setAutoCommit(false);
execute(connection, ins2_SqlServer);
execute(connection, ins1_mySql);
connection.rollback();
I am checking for roll-back functionality. I tried with both type of datasource XA and Non XA
With The XA Datasource; I able to Insert and then able rollback when my insertion order is Mysql first and then SqlServer and all other cases; I am getting below error
Caused by: org.teiid.core.TeiidRuntimeException: Remote javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2811e7b6[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@41bfca6f connection handles=0 lastReturned=1464696282390 lastValidated=1464695865070 lastCheckedOut=1464696282378 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@4deb6a7c mcp=SemaphoreArrayListManagedConnectionPool@25f76ef6[pool=MySqlDS] xaResource=LocalXAResourceImpl@7a5ec0c4[connectionListener=2811e7b6 connectionManager=70a9fdd4 warned=false currentXid=null productName=MySQL productVersion=5.0.67-community-nt jndiName=java:/MySqlDS] txSync=null]
Is database insertion order is matter? if yes then what would be the order in case i added more model in my VDB? And what is the solution for above error please guide.