4 Replies Latest reply on Jun 1, 2016 5:01 AM by Prakash Jape

    Insert a rows in multiple database by using teiid VDB

    Prakash Jape Newbie

      I 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.

        • 1. Re: Insert a rows in multiple database by using teiid VDB
          Steven Hawkins Master

          > 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.

           

          In this case at least your MySQL source is a local transaction source, so the commit needs to happen using the 13.4. Last Resource Commit Optimization (LRCO) - which may be problematic for MySQL - you'd have to check with the JBoss Transactions / Narayana.

           

          In general if you want full transaction support you need to use XA sources instead.

          • 2. Re: Insert a rows in multiple database by using teiid VDB
            Ramesh Reddy Master

            Prakash,

             

            When you set the "autoCommit = false" you are effectively starting a local transaction over Teiid, but in Teiid that could involve more than single source. In this case Teiid will automatically start a XA transaction which wraps both MySQL and SQLServer connections. So, these must be XA connections for the rollback behavior. The order of insert does not matter, unless you configured one as XA connection and another as not. So, configure both MySQL and SQLServer with XA connections and then issue rollback. If they are not XA connections, you will end up with an exception, as above.

             

            Ramesh..

            • 3. Re: Insert a rows in multiple database by using teiid VDB
              Prakash Jape Newbie

              I am using XA datasources, if I perform same rollback for one database it work, but when I execute two insert that one for each database; then it execute one database insert successfully but throwing error for another. Please find my VDB and Datasource detail as below

               

              Vdb content

              <?xml version="1.0" encoding="UTF-8" standalone="no"?>

              <vdb name="testXADS" version="1">

              <description/>

              <model name="sqlSerXADS1">

              <source connection-jndi-name="java:/SQLSERXADS" name="sqlSerXADS1" translator-name="sqlserver"/>

              </model>

               

              <model name="mysqlXADS1">

              <source connection-jndi-name="java:/MysqlXADS" name="mysqlXADS1" translator-name="mysql"/>

              </model>

              </vdb>

               

               

              Standalone-teiid xml file- These DS setup from jboss admin console -

               

               

                <xa-datasource jndi-name="java:/MysqlXADS" pool-name="MysqlXADS" enabled="true" use-ccm="true">

                                  <xa-datasource-property name="ServerName">

                                      XXXXX

                                  </xa-datasource-property>

                                  <xa-datasource-property name="DatabaseName">

                                      XXXXXXXXXXX

                                  </xa-datasource-property>

                                  <xa-datasource-property name="port">

                                      XXXX

                                  </xa-datasource-property>

                                  <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                                  <driver>mysql</driver>

                                  <security>

                                      <user-name>XXXX</user-name>

                                      <password>XXXXXXXXXXX</password>

                                  </security>

                                  <validation>

                                      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>

                                      <background-validation>true</background-validation>

                                      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

                                  </validation>

                              </xa-datasource>

                              <xa-datasource jndi-name="java:/SQLSERXADS" pool-name="SQLSERXADS" enabled="true" use-java-context="true" use-ccm="true">

                                  <xa-datasource-property name="PortNumber">

                                      XXXX

                                  </xa-datasource-property>

                                  <xa-datasource-property name="DatabaseName">

                                      XXXXXXX

                                  </xa-datasource-property>

                                  <xa-datasource-property name="Instance">

                                      SQL2012

                                  </xa-datasource-property>

                                  <xa-datasource-property name="ServerName">

                                      XXXXXXXX

                                  </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>XXXXXXXXXXX</user-name>

                                      <password>XXXXXXXXX</password>

                                  </security>

                              </xa-datasource>

              • 4. Re: Insert a rows in multiple database by using teiid VDB
                Prakash Jape Newbie

                Thanks Ramesh and Steven

                 

                Even though the used datasource are XA datasource; I had facing this issue because of below properties are set into SqlServer DataSource

                <is-same-rm-override>true</is-same-rm-override>

                <no-tx-separate-pools>true</no-tx-separate-pools>

                 

                I changed the value to false; I am able to perform rollback operation on both database by using single connection. and insertion order also not matters,