8 Replies Latest reply on Mar 8, 2017 9:35 AM by Kulbhushan Chaskar

    Unable to perform transactions on flat file.

    Kulbhushan Chaskar Master

      Hi,

       

      I have created Xa-DataSource for sftp file using com.hxtt.sql.HxttXADataSource driver class.

      I am using below program to perform transaction on file.

       

          public static void executeSqlUpdate(String sql, List<String> values) throws Exception {
              boolean isRollBack = false;
              if (sql == null)
                  return;
              Connection connection = getDriverConnection();
              connection.setAutoCommit(false);
              Statement statement = connection.createStatement();
      
      
              for (String value : values) {
                  String insertQuery = sql + "values(" + value + ")";
                  try {
                      statement.executeUpdate(insertQuery);
                  } catch (Exception ex) {
                      ex.printStackTrace();
                  }
              }
              try {
                  if (isRollBack) {
                      connection.rollback();
                  } else {
                      connection.commit();
                  }
              } catch (Exception ex) {
                  ex.printStackTrace();
                  connection.rollback();
              }
              statement.close();
              connection.close();
              System.out.println(" ");
          } 
      

       

       

       

      When I am running the above program multiple times, I am getting results in below order:

       

       

      1st Time : Success

      2nd time : Exception at 'statement.executeUpdate(insertQuery)';

      3rd Time : Success

      4th time : Exception at 'statement.executeUpdate(insertQuery)';

      5th Time : Success

      6th time : Exception at 'statement.executeUpdate(insertQuery)';

      7th Time : Success

      8th time : Exception at 'statement.executeUpdate(insertQuery)';

      .

      .

      .  so on.....

       

      Exception :

       

      org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 DelFileHeaderTrueSftpXa: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['org.teiid.core.types.ClobImpl@e001754', 'org.teiid.core.types.ClobImpl@6cf1cf34'] SQL: INSERT INTO "marketData" ("id", "Name") VALUES (?, ?)]
        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
        at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:723)
        at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)
        at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)
        at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)
        at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)
        at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)
        at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:285)
        at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:323)
        at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)
        at com.sun.proxy.$Proxy1.read(Unknown Source)
        at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:422)
        at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)
        at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1091)
        at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:360)
      
      Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 DelFileHeaderTrueSftpXa: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['org.teiid.core.types.ClobImpl@e001754', 'org.teiid.core.types.ClobImpl@6cf1cf34'] SQL: INSERT INTO "marketData" ("id", "Name") VALUES (?, ?)]
        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:392)
        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:477)
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:349)
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)
        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:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
      Caused by: org.teiid.core.TeiidException: 0 Remote org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['org.teiid.core.types.ClobImpl@e001754', 'org.teiid.core.types.ClobImpl@6cf1cf34'] SQL: INSERT INTO "marketData" ("id", "Name") VALUES (?, ?)]
        at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:330)
        at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:86)
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:405)
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:367)
        at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)
        at com.sun.proxy.$Proxy27.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)
        ... 17 more
      Caused by: java.sql.SQLException: Remote java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK
        at org.jboss.jca.adapters.jdbc.WrapperDataSource.checkTransactionActive(WrapperDataSource.java:245)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransactionActive(WrappedConnection.java:1928)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:1943)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:1917)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:447)
        at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:196)
        at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:260)
        ... 27 more
      

       

      The same program I have tried  with Simple JDBC to test whether the problem with file driver or not but It is running successfully each time with Simple JDBC program.

      I am wondering why it is throwing exception when I am running it through Teiid.

       

      Thanks,

      Kulbhushan Chaskar

        • 1. Re: Unable to perform transactions on flat file.
          Ramesh Reddy Master

          Can you show how your data source is configured and getDriverConnection method?

          • 2. Re: Unable to perform transactions on flat file.
            Kulbhushan Chaskar Master

            Hi Ramesh,

             

            1. Below steps used to configure flat file driver

             

            1. I have placed 'module.xml' file and (Text_JDBC42.jar) sqlserver jar on beolw location

             

            teiid-9.1.3-wildfly-server\teiid-9.1.3\modules\system\layers\base\com\hxtt\text

             

            module.xml file content:

             

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

            <module xmlns="urn:jboss:module:1.0" name="com.hxtt.text">

              <resources>

                <resource-root path="Text_JDBC42.jar"/>

              </resources>

              <dependencies>

                <module name="javax.api"/>

              <module name="com.jsch.remote"/>

              <module name="javax.transaction.api"/>

              </dependencies>

            </module>

             

             

            2. Made  driver tag entry with name as 'textfile'(for Xa datasource) on standalone-teiid.xml file:

             

            <driver name="textfile" module="com.hxtt.text">

                  <driver-class>com.hxtt.sql.text.TextDriver</driver-class>

            <xa-datasource-class>com.hxtt.sql.HxttXADataSource</xa-datasource-class>

            </driver>

             

              

            2. Below steps used to create Xa-Datasource for flat file through jboss admin console

             

            Configuration ->Subsystems->DataSources->XA->Add

             

            -Custom

            -PostgreSQL XA Datasource

            -MySQL XA Datasource

            -Oracle XA Datasource

            -Microsoft SQLServer XA Datasource

            -IBM DB2 XA Datasource

            -Sybase XA Datasource

             

            where I had chosen Custom option to create xa-datasource

             

            Step 1/4: XA Datasoure Attributes

             

            Name: testFileXaDS1

            JNDI Name: java:/testFileXaDS1

             

            Step 2/4: Datasource Class

             

            Name                          textfile

            Module Name              : com.hxtt.text

            Driver Class                 : com.hxtt.sql.text.TextDriver

            XA-DataSource Class   : com.hxtt.sql.HxttXADataSource

             

            Step 3/4: XA Properties

             

            URL : jdbc:text:sftp://testUser:testPwd@hostName/filePath/marketData.txt?_CSV_Separator=,;_CSV_Header=True;fileExtension=txt

             

            Step 4/4: Connection Settings

             

            Username : testUser

            Password : testPwd

             

             

            Below is getDriverConnection() method definition:

             

            static Connection getDriverConnection() throws Exception {

              String url = "jdbc:teiid:"+vdb+"@mm://"+host+":"+port+";showplan=on";

              Class.forName("org.teiid.jdbc.TeiidDriver");

              return DriverManager.getConnection(url,"user","user@123");

              }

            • 3. Re: Unable to perform transactions on flat file.
              Ramesh Reddy Master

              Can you post the standalone.xml's data source part of XML where the connection is made. From above what you did in the web-console. Just that fragment not whole file.

               

              Even given that I am not sure how Hxtt driver managing XA on file source, as file system is not transactional

               

              Also from above what did you mean simple JDBC program, you mean using driver connection instead of XA connection? In Teiid is the datasource is XA, it will wrap the execution in a XA transaction, and driver must be capable of handling that, if not you might see issues like above.

              • 4. Re: Unable to perform transactions on flat file.
                Kulbhushan Chaskar Master

                standalone.xml's data source part of XML where the connection is made:

                 

                 

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

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

                                      jdbc:text:sftp://testUser:testPwd@hostName/filePath/marketData.txt?_CSV_Separator=,;_CSV_Header=True;fileExtension=txt>

                                  </xa-datasource-property>

                               <xa-datasource-class>com.hxtt.sql.HxttXADataSource</xa-datasource-class>

                               <driver>textfile</driver>

                           <security>

                        <user-name>testUser</user-name>

                        <password>testPwd</password>

                       </security>

                </xa-datasource>

                 

                 

                JDBC program:

                 

                private  void insertOperation() throws Exception {

                  Boolean isRollBack = false;

                  List<String> values = new ArrayList<String>();

                  values.add("'1','test'");

                  values.add("'2','test'");

                  values.add("'3','test'");

                  values.add("'4','test'");

                  values.add("'5','test'");

                 

                  values.add("'6','test'");

                  values.add("'7','test'");

                  values.add("'8','test'");

                  values.add("'9','test'");

                  values.add("'10','test'");

                 

                  String insertStmt = "INSERT INTO marketData(id,name)";

                  statement = makeConnection();

                  connection.setAutoCommit(false);

                  for(String value:values){

                  String insertQuery = insertStmt+"values("+value+")";

                  try{

                  noOfRecordsAffected = statement.executeUpdate(insertQuery);

                  }catch(Exception ex){

                    ex.printStackTrace();

                  }

                  }

                 

                  try {

                  if(isRollBack) {

                  connection.rollback();

                  }else{

                  connection.commit();

                  }

                 

                  } catch (Exception ex) {

                  ex.printStackTrace();

                  }finally{

                  connection.close();

                  }

                  }

                 

                  private Statement makeConnection()  throws SQLException{

                        try {

                            com.hxtt.sql.HxttXADataSource xaDataSource = new com.hxtt.sql.HxttXADataSource();

                            xaDataSource.setURL("jdbc:text:sftp://testUser:testPwd@hostName/filePath/marketData.txt?_CSV_Separator=,;_CSV_Header=True;fileExtension=txt");

                            connection = xaDataSource.getConnection();

                            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

                        }

                        catch (Exception e){

                            e.printStackTrace();

                        }

                        return statement;

                    }

                 

                In this program I am using Xa-Connection instead of driver connection  which is working fine but the same Xa-Connection is not working with Teiid.

                • 5. Re: Unable to perform transactions on flat file.
                  Ramesh Reddy Master

                  The reason it works fine outside is there is no XA transaction manager managing your work. When you set setAutcommit to false, then "local" transaction is typically managed by the source, in this case should be file system. If you write your program in the way it engages the XAResource from the XAConnection will you probably will see same exception as Teiid does. Have you verified the rollback behavior with standalone JDBC program case to see it works?

                   

                  In Teiid there is transaction manager calling all the right XA methods on the driver to engage in XA transaction. I am guessing you have a different client to test with Teiid.  What is your need to use XA driver with file source?

                  • 6. Re: Unable to perform transactions on flat file.
                    Kulbhushan Chaskar Master

                    >Have you verified the rollback behavior with standalone JDBC program case to see it works?

                    Yes ,I have verified with standalone JDBC program ,rollback is working fine.

                     

                    Can you please elaborate more on this 'I am guessing you have a different client to test with Teiid.'?

                     

                    >What is your need to use XA driver with file source?

                    I need to perform global transaction across multiple data-Sources.

                     

                    Does it mean that file-system does not support transactions with Xa-Connection?

                    • 7. Re: Unable to perform transactions on flat file.
                      Ramesh Reddy Master

                      >Can you please elaborate more on this 'I am guessing you have a different client to test with Teiid.'?

                      The one you showed above uses the connection to the XATT driver, so Teiid one must be different.

                       

                      > Does it mean that file-system does not support transactions with Xa-Connection?

                      Not unless XATT is like database system that manages the transactions. For example H2 also has way to convert CSV into tables, which supports transactions. If the driver is directly performing operations on file system, then no. File system is not transactional. I did not see before that, you are trying to read/write CSV file as table using a some engine I am currently not aware of.

                       

                      Ramesh..

                      • 8. Re: Unable to perform transactions on flat file.
                        Kulbhushan Chaskar Master

                        >The one you showed above uses the connection to the XATT driver, so Teiid one must be different.

                        I am using below code to connect to the file using HXTT driver

                         

                        static Connection getDriverConnection() throws Exception {

                          String url = "jdbc:teiid:"+vdb+"@mm://"+host+":"+port+";showplan=on";

                          Class.forName("org.teiid.jdbc.TeiidDriver");

                          return DriverManager.getConnection(url,"user","user@123");

                          }

                         

                        The code is same like to connect all other databases like mysql. The code to create XA-datasource has already posted above.