Unable to perform transactions on flat file.

[kulbhushanc]

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

[rareddy]

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

[kulbhushanc]

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");

  }

[rareddy]

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.

[kulbhushanc]

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.

[rareddy]

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?

[kulbhushanc]

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

[rareddy]

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

[kulbhushanc]

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