2 Replies Latest reply on Jun 6, 2016 8:23 AM by Steven Hawkins

    How do i get success and failure count when PreparedStatement batch excecution failes

    Aakash Sen Newbie

      I am trying to insert 50 rows in employee table.


      data something like this:

      values(1,'testCity1','testPhone1'),(2,'testCity2','testPhone2'),(3,'testCity3','testPhone3'),......,(50,'testCity50','testPhone50');

       

      Employee table is holding one record before insertion i.e.     (13,'testCity13','testphone13');

       

      I am using batch size of 10 , My first 10 rows get inserted successfully but second batch is failed for id : 13 .

       

      I am trying to get failure and success count Using BatchUpdateException .But When batch fails it goes to Exception block directly so I am unable to get BatchUpdateException object.


      Please help me out, How can i get BatchUpdateException object such that i can use its methods to calculate success or failure count.  Or there is any other way to find out success or failure count .



      Below Scenario I am trying :

       

        String sql = "insert into employee (id, city, phone) values (?, ?, ?)"; 

          Connection connection = new getConnection(); 

          PreparedStatement ps = connection.prepareStatement(sql); 

             

          final int batchSize = 10; 

          int count = 0; 

          int successCount = 0;

          int failCount = 0;

          int notAavailable = 0;

          try {  

          for (Employee employee: employees) { 

             

              ps.setString(1, employee.getId()); 

              ps.setString(2, employee.getCity()); 

              ps.setString(3, employee.getPhone()); 

              ps.addBatch(); 

                 

              if(++count % batchSize == 0) { 

                  ps.executeBatch(); 

              } 

          } 

          ps.executeBatch(); // insert remaining records 

      } catch (BatchUpdateException buex) {

                  buex.printStackTrace();

                  LogUtil.error(buex);

                  int[] updateCounts = buex.getUpdateCounts();

                  for (int i = 0; i < updateCounts.length; i++) {

                      if (updateCounts[i] >= 0) {

                          successCount++;

                              

                      } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {

                          notAavailable++;

                          

                      }

      if (updateCounts[i] == Statement.EXECUTE_FAILED) {

                failCount++;

          LogUtil.debug("Failed to execute record at:" +i);

      }

       

                  }

              } catch(Exception ex){

               ex.printStackTrace();

               }

      finally {

                             LogUtil.info("Number of affected rows before Batch Error :: " + successCount);

                     LogUtil.info("Number of affected rows not available:" + notAavailable);

                     LogUtil.info("Failed Count in Batch because of Error:" + failCount);

                             ps.close(); 

                             connection.close(); 

                    }

       

       

       

      Exception which i got is :

       

      org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 DSMYSQL20160524121500218: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?] SQL: INSERT INTO `employee` (id, city, phone) 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:703)

        at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

        at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

        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)

       

      Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 DSMYSQL20160524121500218: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ? , ?] SQL: INSERT INTO 'employee` (id, city, phone) 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: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.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(Unknown Source)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

        ... 1 more

      Caused by: org.teiid.core.TeiidException: 1062 Remote org.teiid.translator.jdbc.JDBCExecutionException: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ? ,?] SQL: INSERT INTO `employee ` (id, city, phone) values (?, ?, ?)]

        at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:276)

        at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:84)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:394)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:356)

        at sun.reflect.GeneratedMethodAccessor110.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

        at java.lang.reflect.Method.invoke(Unknown Source)

        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

        at com.sun.proxy.$Proxy57.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.BatchUpdateException: Duplicate entry '13' for key 1

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

        at java.lang.reflect.Constructor.newInstance(Unknown Source)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

        at com.mysql.jdbc.Util.getInstance(Util.java:387)

        at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)

        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)

        at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)

        at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)

        at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1174)

        at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:240)

        ... 27 more

      Caused by: java.sql.SQLIntegrityConstraintViolationException: Remote com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '13' for key 1

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

        at java.lang.reflect.Constructor.newInstance(Unknown Source)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

        at com.mysql.jdbc.Util.getInstance(Util.java:387)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)

        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)

        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)

        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)

        ... 31 more