6 Replies Latest reply on Mar 17, 2017 8:30 PM by Steven Hawkins

    Getting SQLException instead of BatchUpdateException.

    Kulbhushan Chaskar Master

      Hi,

       

      I am performing batch update operation (executeBatch()) using preparedStatement.

       

      Code :

       

      try{

        PreparedStatement pstmt=connection.prepareStatement(query);

         pstmt.setInt(1, 2);

        pstmt.setString(2,"test2");

        pstmt.setString(3, "Test3_");

        pstmt.addBatch();

       

        pstmt.setInt(1, 2);

        pstmt.setString(2,"test2");

        pstmt.setString(3, "Test3_");

        pstmt.addBatch();

        int arrResult[]=pstmt.executeBatch();

        }

        catch (BatchUpdateException ex) {

        int[] updateCount = ex.getUpdateCounts();

        int count1 = 1;

        for (int i : updateCount) {

        if  (i == Statement.EXECUTE_FAILED) {

        System.out.println("Error on request " + count1 +": Execute failed");

        } else {

        System.out.println("Request " + count1 +": OK");

        }

        count1++;

       

        }

        }

        catch(SQLException e){

        e.printStackTrace();

        }

       

      Exception :

       

      org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 aakashsConn: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?] SQL: INSERT INTO `aakashs`.`newtable1` (`col1`, `col2`) 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.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.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.$Proxy3.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.executeSql(StatementImpl.java:423)

        at org.teiid.jdbc.PreparedStatementImpl.executeBatch(PreparedStatementImpl.java:300)

        at TestPreapreStmntWithTeiid.WithTeiid.executeSqlUpdate(WithTeiid.java:633)

        at TestPreapreStmntWithTeiid.WithTeiid.testWith_MySqlXA_Db(WithTeiid.java:99)

        at TestPreapreStmntWithTeiid.WithTeiid.main(WithTeiid.java:49)

      Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 aakashsConn: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?] SQL: INSERT INTO `aakashs`.`newtable1` (`col1`, `col2`) 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(Unknown Source)

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

        at java.lang.Thread.run(Unknown Source)

      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 `aakashs`.`newtable1` (`col1`, `col2`) 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.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.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: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$1.call(DataTierTupleSource.java:112)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

        at java.util.concurrent.FutureTask.run(Unknown Source)

        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

        ... 6 more

      Caused by: java.sql.SQLException: Remote java.sql.BatchUpdateException: Duplicate entry '2' 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:1190)

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

        ... 20 more

      Caused by: java.sql.SQLIntegrityConstraintViolationException: Remote com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2' 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)

        ... 24 more

       

       

       

      When I am running the above program through Teiid I am getting SQLException instead of BatchUpdateException  ,whereas running the same program with JDBC I am getting BatchUpdateException which is expected.

       

      As  Teiid is throwing SQLException while inserting duplicate record on primary key column insetead of BatchUpdateException ,I am not able to get the ex.getUpdateCounts() which is the method of BatchUpdateException class.

       

      How do I get the BatchUpdateException (expected) instead of SQLException through Teiid Or Is there any other way to get updateCounts  when there is any Exception occurred while inserting the data into database using PreparedStatment in Teiid.

        • 1. Re: Getting SQLException instead of BatchUpdateException.
          Steven Hawkins Master

          The issue is that [TEIID-4243] Use BatchUpdateException - JBoss Issue Tracker only addressed statement batches, not prepared statement batches of values.  That will need to be added to get the exception you expect.

          • 2. Re: Getting SQLException instead of BatchUpdateException.
            Kulbhushan Chaskar Master

            As per the [TEIID-4243] Use BatchUpdateException - JBoss Issue Tracker  the issue has been resolved in Teiid 9.1 version.

             

            I am also using Teiid 9.1.3 version but still I am getting the same exception.

            • 3. Re: Getting SQLException instead of BatchUpdateException.
              Steven Hawkins Master

              I'm saying that a code change is still needed.  TEIID-4243 only covered batches of non-prepared statements in the JDBC translator case - it did not implement the BatchUpdateException handling for prepared statement value batching.  Can you log an issue for that?

              • 4. Re: Getting SQLException instead of BatchUpdateException.
                Kulbhushan Chaskar Master

                I have changed my logic to insert the data,now I am using Statement instead of PreparedStatement for executeBatch.

                 

                Code:

                               try{

                String sqlQuery = "INSERT INTO testConnection.testDb.testTable(col1,col2) VALUES (1,'name')";

                String sqlQuery1 = "INSERT INTO testConnection.testDb.testTable(col1,col2) VALUES (1,'name')";

                Statement stmt=null;

                stmt = connection.createStatement();

                 

                stmt.addBatch(sqlQuery);

                stmt.addBatch(sqlQuery1);

                int arrResult[]=stmt.executeBatch();

                }

                catch (BatchUpdateException ex) {

                int[] updateCount = ex.getUpdateCounts();

                int count1 = 1;

                for (int i : updateCount) {

                if  (i == Statement.EXECUTE_FAILED) {

                System.out.println("Error on request " + count1 +": Execute failed");

                } else {

                System.out.println("Request " + count1 +": OK");

                }

                count1++;

                 

                }

                }

                catch(SQLException e){

                e.printStackTrace();

                 

                }

                 

                When I am executing the above code ,first record is getting inserted successfully but for second record(duplicate record) it is throwing me below exception instead of BatchUpdateException.

                 

                Exception:

                 

                org.teiid.net.socket.SocketServerInstanceImpl exceptionOccurred

                WARNING: Unable to read: unexpected exception

                java.io.OptionalDataException

                  at java.io.ObjectInputStream.readObject0(Unknown Source)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.client.ResultsMessage.readExternal(ResultsMessage.java:286)

                  at java.io.ObjectInputStream.readExternalData(Unknown Source)

                  at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

                  at java.io.ObjectInputStream.readObject0(Unknown Source)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.net.socket.Message.readExternal(Message.java:56)

                  at java.io.ObjectInputStream.readExternalData(Unknown Source)

                  at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

                  at java.io.ObjectInputStream.readObject0(Unknown Source)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:108)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:117)

                  at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:323)

                  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                  at sun.reflect.NativeMethodAccessorImpl.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.$Proxy3.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.executeSql(StatementImpl.java:423)

                  at org.teiid.jdbc.StatementImpl.executeBatch(StatementImpl.java:348)

                  at TestPreapreStmntWithTeiid.WithTeiid.executeSqlUpdate(WithTeiid.java:642)

                  at TestPreapreStmntWithTeiid.WithTeiid.testWith_MySqlXA_Db(WithTeiid.java:99)

                  at TestPreapreStmntWithTeiid.WithTeiid.main(WithTeiid.java:49)

                 

                org.teiid.jdbc.TeiidSQLException: org.teiid.net.socket.SingleInstanceCommunicationException

                  at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                  at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                  at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:578)

                  at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:423)

                  at org.teiid.jdbc.StatementImpl.executeBatch(StatementImpl.java:348)

                  at TestPreapreStmntWithTeiid.WithTeiid.executeSqlUpdate(WithTeiid.java:642)

                  at TestPreapreStmntWithTeiid.WithTeiid.testWith_MySqlXA_Db(WithTeiid.java:99)

                  at TestPreapreStmntWithTeiid.WithTeiid.main(WithTeiid.java:49)

                Caused by: org.teiid.net.socket.SingleInstanceCommunicationException

                  at org.teiid.net.socket.SocketServerInstanceImpl.exceptionOccurred(SocketServerInstanceImpl.java:243)

                  at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:327)

                  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                  at sun.reflect.NativeMethodAccessorImpl.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.$Proxy3.read(Unknown Source)

                  at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:422)

                  at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)

                  ... 5 more

                Caused by: java.io.OptionalDataException

                  at java.io.ObjectInputStream.readObject0(Unknown Source)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.client.ResultsMessage.readExternal(ResultsMessage.java:286)

                  at java.io.ObjectInputStream.readExternalData(Unknown Source)

                  at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

                  at java.io.ObjectInputStream.readObject0(Unknown Source)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.net.socket.Message.readExternal(Message.java:56)

                  at java.io.ObjectInputStream.readExternalData(Unknown Source)

                  at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

                  at java.io.ObjectInputStream.readObject0(Unknown Source)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:108)

                  at java.io.ObjectInputStream.readObject(Unknown Source)

                  at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:117)

                  at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:323)

                  ... 13 more

                • 5. Re: Getting SQLException instead of BatchUpdateException.
                  Steven Hawkins Master

                  That's not an expected exception.  I don't see that with a simple scenario.  What are your client and server versions?  It could be the server logic needs more checking of the client version in the scenario.