/*

   Derby - Class 
       org.apache.derbyTesting.functionTests.tests.jdbcapi.BatchUpdateTest

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.jdbcapi;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.lang.reflect.Method;
import java.sql.BatchUpdateException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * Test BatchUpdate functionality.
 * <P>
 * This test examines the behavior fo BatchUpdate test.
 * One fixture tests creating tables in batch, the other fixtures can be grouped
 * into 5 rough categories:
 *  - tests that verify that correct usage with Statements work as expected
 *    - testEmptyStatementBatch()
 *      try executing a batch which nothing in it.
 *    - testSingleStatementBatch()
 *      try executing a batch which one statement in it.
 *    - testMultipleStatementsBatch()
 *      try executing a batch with 3 different statements in it.
 *    - test1000StatementsBatch()
 *      try executing a batch with 1000 statements in it.
 *    - testAutoCommitTrueBatch()
 *      try batch with autocommit true
 *    - testCombinationsOfClearBatch()
 *      try clear batch
 *    - testAssociatedParams()
 *      confirm associated parameters run ok with batches
 *   
 *  - tests that verify that incorrect usage with Statments give appropriate
 *    errors
 *    - testStatementWithResultSetBatch()
 *      statements which will return a resultset are not allowed in batch
 *      update. The following case should throw an exception for select.
 *      Below trying various placements of select statement in the batch,
 *      i.e. as 1st stmt, nth stmt and last stmt
 *    - testStatementNonBatchStuffInBatch()
 *      try executing a batch with regular statement intermingled.
 *    - testStatementWithErrorsBatch()
 *      Below trying various placements of overflow update statement
 *      in the batch, i.e. as 1st stmt, nth stat and last stmt
 *    - testTransactionErrorBatch()
 *      try transaction error, i.e. time out while getting the lock
 *    
 *  - tests that verify that usage with callableStatements work as expected
 *    - testCallableStatementBatch()
 *      try callable statements
 *    - testCallableStatementWithOutputParamBatch()
 *      try callable statement with output parameters
 *      
 *  - tests that verify that correct usage with preparedStatements work as
 *    expected
 *    - testEmptyValueSetPreparedBatch()
 *      try executing a batch which nothing in it.
 *    - testNoParametersPreparedBatch()
 *      try executing a batch with no parameters. 
 *    - testSingleValueSetPreparedBatch()
 *      try executing a batch which one parameter set in it.
 *    - testMultipleValueSetPreparedBatch()
 *      try executing a batch with 3 parameter sets in it.
 *    - testMultipleValueSetNullPreparedBatch()
 *      try executing a batch with 2 parameter sets in it and they are set 
 *      to null.
 *    - test1000ValueSetPreparedBatch()
 *      try executing a batch with 1000 statements in it.
 *    - testPreparedStatRollbackAndCommitCombinations()
 *      try executing batches with various rollback and commit combinations.
 *    - testAutoCommitTruePreparedStatBatch()
 *      try prepared statement batch with autocommit true
 *    - testCombinationsOfClearPreparedStatBatch()
 *      try clear batch
 *      
 *  - tests that verify that incorrect use with preparedStatements give 
 *    appropriate errors
 *    - testPreparedStmtWithResultSetBatch()
 *      statements which will return a resultset are not allowed in batch
 *      update. The following case should throw an exception for select.
 *    - testPreparedStmtNonBatchStuffInBatch();
 *      try executing a batch with regular statement intermingled.
 *    - testPreparedStmtWithErrorsBatch();
 *      trying various placements of overflow update statement
 *      in the batch
 *    - testTransactionErrorPreparedStmtBatch()
 *      try transaction error, in this particular case time out while
 *      getting the lock
 * 
 * Almost all fixtures but 1 execute with embedded and 
 * NetworkServer/DerbyNetClient - however, there is a difference in 
 * functionality between the two when an error condition is reaches. Thus,
 * the negative tests have if / else if blocks for embedded and client.
 * 
 * The 1 fixture that ise not running with network server is 
 * identified with //TODO: tags and has an if (usingEmbedded()) block and
 * a JIRA issue attached to it.
 * 
 */

public class BatchUpdateTest extends BaseJDBCTestCase {
	
    /** Creates a new instance of BatchUpdateTest */
    public BatchUpdateTest(String name) {
        super(name);
    }

    /**
     * Set up the conection to the database.
     *  This is itself a test of statements creating tables in batch. 
     */
    public void setUp() throws  Exception {
        getConnection().setAutoCommit(false);
        Statement s = createStatement();
        s.execute("delete from t1");
        s.close();
        commit();
    }
    
    public static Test suite() {
        BaseTestSuite suite = new BaseTestSuite("BatchUpdateTest");
        suite.addTest(baseSuite("BatchUpdateTest:embedded"));
        suite.addTest(TestConfiguration.clientServerDecorator(
            baseSuite("BatchUpdateTest:client")));
        return suite;
    }
    
    
    /**
     * embeddedSuite runs tests only in embedded mode. 
     * Used by CollationTest
     * @return embedded Test suite
     */
    public static Test embeddedSuite() {
        
        BaseTestSuite suite = new BaseTestSuite("BatchUpdateTest");
        suite.addTest(baseSuite("BatchUpdateTest:embedded"));
        return suite;
    }
    
    protected static Test baseSuite(String name) {
        BaseTestSuite suite = new BaseTestSuite(name);
        suite.addTestSuite(BatchUpdateTest.class);
        return new CleanDatabaseTestSetup(
                DatabasePropertyTestSetup.setLockTimeouts(suite, 2, 4)) 
        {
            /**
             * Creates the tables used in the test cases.
             * @exception SQLException if a database error occurs
             */
            protected void decorateSQL(Statement stmt) throws SQLException
            {
                stmt.execute("create table t1(c1 int)");
                // for fixture testCallableStatementBatch
                stmt.execute("create table datetab(c1 date)");
                stmt.execute("create table timetab(c1 time)");
                stmt.execute("create table timestamptab(c1 timestamp)");
                stmt.execute("create table usertypetab(c1 DATE)");
                // for fixture testAssociatedParams
                stmt.execute("create table assoc" +
                    "(x char(10) not null primary key, y char(100))");
                stmt.execute("create table assocout(x char(10))");
            }
        };
    } 
    
    /* 
     * helper method to check each count in the return array of a BatchUpdateException
     */
    public  static   void assertBatchUpdateCounts
        ( long[] expectedBatchResult, BatchUpdateException bue )
    {
        assertBatchUpdateCounts( squashLongs( expectedBatchResult ), bue.getUpdateCounts() );
        
        if (JDBC.vmSupportsJDBC42())
        {
            BatchUpdateExceptionWrapper wrapper = new BatchUpdateExceptionWrapper( bue );

            assertEquals( expectedBatchResult, wrapper.getLargeUpdateCounts() );
        }
    }
   
    /** Squash an array of longs into an array of ints */
    public static  int[]   squashLongs( long[] longs )
    {
        int count = (longs == null) ? 0 : longs.length;
        int[]   ints = new int[ count ];
        for ( int i = 0; i < count; i++ ) { ints[ i ] = (int) longs[ i ]; }

        return ints;
    }
    
    /* 
     * helper method to check each count in the return array of batchExecute
     */
    private static void assertBatchUpdateCounts( 
        int[] expectedBatchResult, int[] executeBatchResult )
    {
        assertEquals("length of array should be identical", 
            expectedBatchResult.length, executeBatchResult.length);
        
        for (int i=0; i<expectedBatchResult.length; i++)
        {
            String msg = "mismatch for array index [" + i + "] ; ";
            assertEquals(msg,expectedBatchResult[i],executeBatchResult[i]);
            println("expectedUpdate result #" + i + " : " +
                expectedBatchResult[i]);
            println("actual result #" + i + " : " + executeBatchResult[i]);
        }
    }
    
    /** 
     * helper method to evaluate negative tests where we expect a 
     * batchExecuteException to be returned.
     * @exception SQLException     Thrown if the expected error occurs
     *                             We expect a BatchUpdateException, and
     *                             verify it is so.
     *
     * @param expectedError The sqlstate to look for.
     * @param stmt The Statement that contains the Batch to
     *                             be executed.
     * @param expectedUpdateCount The expectedUpdateCount array.
     */
    public static void assertBatchExecuteError( 
        String expectedError,
        Statement stmt,
        long[] expectedUpdateCount) 
    throws SQLException 
    { 
       try {
            stmt.executeBatch();
            fail("Expected batchExecute to fail");
        } catch (BatchUpdateException bue) {
            assertSQLState(expectedError, bue);
            assertBatchUpdateCounts(expectedUpdateCount, bue);
        } 
    }
    
    /* Fixture that verifies tables can be created in batch */
    public void testMinimalDDLInBatch() throws SQLException {
        
        Statement stmt = createStatement();
        stmt.addBatch("create table ddltsttable1(c1 int)");
        stmt.addBatch("create procedure ddlinteg() language java " +
            "parameter style java external name 'java.lang.Integer'");
        stmt.addBatch("create table ddltable2(c1 date)");
        int expectedCount[] = {0,0,0};
        assertBatchUpdateCounts(expectedCount, stmt.executeBatch());
        ResultSet rs = stmt.executeQuery(
            "select count(*) from SYS.SYSTABLES where CAST(tablename AS VARCHAR(128)) like 'DDL%'");
        JDBC.assertSingleValueResultSet(rs, "2");
        rs = stmt.executeQuery(
            "select count(*) from SYS.SYSALIASES where CAST(alias AS VARCHAR(128)) like 'DDL%'");
        JDBC.assertSingleValueResultSet(rs, "1");
        stmt.close();

        commit();
    }

    
    /* Fixtures that test correct usage of batch handling with Statements */
    
    // try executing a batch which nothing in it. Should work.
    public void testEmptyStatementBatch() throws SQLException {
        Statement stmt = createStatement();
 
        // try executing a batch which nothing in it. Should work.
        println("Positive Statement: clear the batch and run the empty batch");
        stmt.clearBatch();
        assertBatchUpdateCounts(new int[0], stmt.executeBatch());

        stmt.close();
        commit();
    }

    // try executing a batch which single statement in it. Should work.
    public void testSingleStatementBatch() throws SQLException {

        Statement stmt = createStatement();
        println("Positive Statement: testing 1 statement batch");
        stmt.addBatch("insert into t1 values(2)");

        assertBatchUpdateCounts(new int[] {1}, stmt.executeBatch());
        stmt.close();
        commit();
    }
    
    // try executing a batch with 3 different statements in it.
    public void testMultipleStatementsBatch() throws SQLException {

        Statement stmt = createStatement();
        ResultSet rs;

        println("Positive Statement: testing 2 inserts and 1 update batch");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("update t1 set c1=4");
        stmt.addBatch("insert into t1 values(3)");

        assertBatchUpdateCounts(new int[] {1,1,1}, stmt.executeBatch());
        
        rs = stmt.executeQuery("select count(*) from t1 where c1=2");
        rs.next();
        assertEquals("expect 0 rows with c1 = 2", 0, rs.getInt(1));
        rs.close();

        rs = stmt.executeQuery("select count(*) from t1 where c1=4");
        rs.next();
        assertEquals("expect 1 row with c1 = 4", 1, rs.getInt(1));
        rs.close();

        rs = stmt.executeQuery("select count(*) from t1 where c1=3");
        rs.next();
        assertEquals("expect 1 row with c1 = 3", 1, rs.getInt(1));
        rs.close();

        assertTableRowCount("T1", 2);
        
        stmt.close();

        commit();
    }

    /**
     * Regression test case for DERBY-6373.
     */
    public void testMultipleStatementsBatchWithWarnings() throws SQLException {
        Statement s = createStatement();
        s.execute("insert into t1 values 1");

        // Execute a batch of three deletes. All of them should get a warning
        // because no rows matched the WHERE clause.
        s.addBatch("delete from t1 where c1 in (select 0 from t1)");
        s.addBatch("delete from t1 where c1 in (select 0 from t1)");
        s.addBatch("delete from t1 where c1 in (select 0 from t1)");
        s.executeBatch();

        // Used to fail with NullPointerException on the client.
        SQLWarning w = s.getWarnings();

        // Expect one warning per delete on the client. Embedded gives only
        // a single warning.
        assertSQLState("02000", w);
        w = w.getNextWarning();
        if (usingEmbedded()) {
            assertNull(w);
        } else {
            assertSQLState("02000", w);
            w = w.getNextWarning();
            assertSQLState("02000", w);
            w = w.getNextWarning();
            assertNull(w);
        }
    }

    // try executing a batch with 1000 statements in it.
    public void test1000StatementsBatch() throws SQLException {
        int updateCount[];

        Statement stmt = createStatement();

        println("Positive Statement: 1000 statements batch");
        for (int i=0; i<1000; i++){
            stmt.addBatch("insert into t1 values(1)");
        }
        updateCount = stmt.executeBatch();
        
        int[] expectedUpdateCount = new int[1000];
        Arrays.fill(expectedUpdateCount, 1);
        assertBatchUpdateCounts(expectedUpdateCount, updateCount);
        
        assertTableRowCount("T1", 1000);

        stmt.close();

        commit();
    }

    // try batch with autocommit true
    public void testAutoCommitTrueBatch() throws SQLException {

        getConnection().setAutoCommit(true);    
        Statement stmt = createStatement();

        // try batch with autocommit true
        println("Positive Statement: stmt testing with autocommit true");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("delete from t1");
        assertBatchUpdateCounts(new int[] {1,1,2}, stmt.executeBatch());

        assertTableRowCount("T1", 0);
        
        stmt.close();
    }

    //  try combinations of clear batch.
    public void testCombinationsOfClearBatch() throws SQLException {

        Statement stmt = createStatement();

        println("Positive Statement: add 3 statements, clear and execute batch");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.clearBatch();

        // Batch should be cleared, there should be no update count
        assertBatchUpdateCounts(new int[0], stmt.executeBatch());
        assertTableRowCount("T1", 0);

        println("Positive Statement: add 3 statements, clear batch, " +
            "add 3 more statements and execute batch");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.clearBatch();
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");

        assertBatchUpdateCounts(new int[] {1,1,1}, stmt.executeBatch());
        assertTableRowCount("T1", 3);

        stmt.close();
        commit();
    }

    /*
     ** Associated parameters are extra parameters that are created
     ** and associated with the root parameter (the user one) to
     ** improve the performance of like.       For something like
     ** where c1 like ?, we generate extra 'associated' parameters 
     ** that we use for predicates that we give to the access
     ** manager. 
     */
    public void testAssociatedParams() throws SQLException 
    {

        Statement stmt = createStatement();
        int i;
        println("Positive Statement: testing associated parameters");
        PreparedStatement checkps = prepareStatement(
            "select x from assocout order by x");
        PreparedStatement ps = prepareStatement(
            "insert into assoc values (?, 'hello')");
        for ( i = 10; i < 60; i++)
        {
            ps.setString(1, Integer.toString(i));
            ps.executeUpdate();     
        }
        ps.close();

        ps = prepareStatement(
            "insert into assocout select x from assoc where x like ?");
        ps.setString(1, "33%");
        ps.addBatch();
        ps.setString(1, "21%");
        ps.addBatch();
        ps.setString(1, "49%");
        ps.addBatch();
        
        assertBatchUpdateCounts(new int[] {1,1,1}, ps.executeBatch());
        ps.close();
        checkps.execute();
        ResultSet rs = checkps.getResultSet();
        JDBC.assertFullResultSet(
            rs, new String[][] {{"21"},{"33"},{"49"}}, true);
                
        stmt.executeUpdate("delete from assocout");

        ps = prepareStatement(
                "insert into assocout select x from assoc where x like ?");
        ps.setString(1, "3%");
        ps.addBatch(); // expectedCount 10: values 10-19
        ps.setString(1, "2%");
        ps.addBatch(); // values 20-29
        ps.setString(1, "1%");
        ps.addBatch(); // values 30-39

        // set up expected values for check
        String expectedStrArray[][] = new String[30][1];
        for (i=10 ; i < 40 ; i++)
        {
            expectedStrArray[i-10][0] = String.valueOf(i);
        }
   
        assertBatchUpdateCounts( new int[] {10,10,10}, ps.executeBatch());
        ps.close();
        checkps.execute();
        rs = checkps.getResultSet();
        JDBC.assertFullResultSet(rs, expectedStrArray, true);
                
        stmt.executeUpdate("delete from assocout");
        ps = prepareStatement(
            "insert into assocout select x from assoc where x like ?");
        ps.setString(1, "%");// values 10-59
        ps.addBatch();
        ps.setString(1, "666666");
        ps.addBatch();
        ps.setString(1, "%");// values 10-59
        ps.addBatch();
        
        // set up expected values for check
        String expectedStrArray2[][] = new String[100][1];
        int j = 0;
        for (i=10 ; i < 60 ; i++)
        {  
            for (int twice = 0; twice < 2; twice++)
            {
                expectedStrArray2[j][0] = String.valueOf(i);
                j++;
            }
        }
        
        assertBatchUpdateCounts (new int[] {50,0,50}, ps.executeBatch());
        ps.close();
        checkps.execute();
        rs = checkps.getResultSet();
        JDBC.assertFullResultSet(rs, expectedStrArray2, true);
        checkps.close();
        stmt.close();
    }

    /* Fixtures that test incorrect batch usage with Statements */

    // statements which will return a resultset are not allowed in batch
    // update. The following case should throw an exception for select. 
    // Below trying various placements of select statement in the batch,
    // i.e. as 1st stmt, nth stat and last stmt
    public void testStatementWithResultSetBatch() throws SQLException {
        
        Statement stmt = createStatement();

        // trying select as the first statement
        println("Negative Statement: statement testing select as first " +
            "statement in the batch");
        stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
        stmt.addBatch("insert into t1 values(1)");
        if (usingEmbedded())
            /* Ensure the exception is the ResultSetReturnNotAllowed */
            assertBatchExecuteError("X0Y79", stmt, new long[] {});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", stmt, new long[] {-3, 1});
        
        assertTableRowCount("T1",
                usingEmbedded() ? 0 : 1);
        
        // trying select as the nth statement
        println("Negative Statement: " +
            "statement testing select as nth stat in the batch");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
        stmt.addBatch("insert into t1 values(1)");
        if (usingEmbedded())
            /* Ensure the exception is the ResultSetReturnNotAllowed */
            assertBatchExecuteError("X0Y79", stmt, new long[] {1});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", stmt, new long[] {1,-3,1});
            
        assertTableRowCount("T1",
                usingEmbedded() ? 1 : 3);

        // trying select as the last statement
        println("Negative Statement: statement testing select" +
            " as last stat in the batch");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
        if (usingEmbedded())
            /* Ensure the exception is the ResultSetReturnNotAllowed */
            assertBatchExecuteError("X0Y79", stmt, new long[] {1,1});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", stmt, new long[] {1,1,-3});

        assertTableRowCount("T1",
                usingEmbedded() ? 3 : 5);

        rollback();

        assertTableRowCount("T1", 0);

        stmt.close();

        commit();
    }
    
    // try executing a batch with regular statement intermingled.
    public void testStatementNonBatchStuffInBatch() throws SQLException {
        
        Statement stmt = createStatement();
        int[] updateCount=null;

        // trying execute after addBatch
        println("Negative Statement:" +
            " statement testing execute in the middle of batch");
        stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
        /* Check to be sure the exception is the MIDDLE_OF_BATCH */
        /* assertStatementError will do the execute() */
        if (usingEmbedded())
            assertStatementError("XJ068",stmt,"insert into t1 values(1)");
        else if (usingDerbyNetClient())
        {
            stmt.addBatch("insert into t1 values(1)"); 
            assertBatchExecuteError("XJ208",stmt, new long[] {-3,1});           
            // pull level with embedded situation
            rollback();
        }
        // do clearBatch so we can proceed
        stmt.clearBatch();

        assertTableRowCount("T1", 0);

        // trying executeQuery after addBatch
        println("Negative Statement: " +
            "statement testing executeQuery in the middle of batch");
        stmt.addBatch("insert into t1 values(1)");
        if (usingEmbedded())
        {
            try
            {
                stmt.executeQuery("SELECT * FROM SYS.SYSTABLES");
                fail("Expected executeQuerywith embedded");
            } catch (SQLException sqle) {
                /* Check to be sure the exception is the MIDDLE_OF_BATCH */
                assertSQLState("XJ068", sqle);
                // do clearBatch so we can proceed
                stmt.clearBatch();
            }
        }
        else if (usingDerbyNetClient())
        {
            stmt.executeQuery("SELECT * FROM SYS.SYSTABLES");
            updateCount = stmt.executeBatch();
            assertBatchUpdateCounts(new int[] {1}, updateCount);
            // set to same spot as embedded
            rollback();
        }

        assertTableRowCount("T1", 0);

        println("Negative Statement: " +
            "statement testing executeUpdate in the middle of batch");
        // trying executeUpdate after addBatch
        println("Negative Statement: " +
        "statement testing executeUpdate in the middle of batch");
        stmt.addBatch("insert into t1 values(1)");
        try
        {
            stmt.executeUpdate("insert into t1 values(1)");
            stmt.addBatch("insert into t1 values(1)");
            stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
            if (usingDerbyNetClient())
            {
                assertBatchExecuteError("XJ208", stmt, new long[] {1,1,-3});
            }
            else if (usingEmbedded())
            {
                updateCount = stmt.executeBatch();
                fail("Expected executeBatch to fail");
            }
        } catch (SQLException sqle) {
            /* Check to be sure the exception is the MIDDLE_OF_BATCH */
            if (usingEmbedded())
                assertSQLState("XJ068", sqle);
            else if (usingDerbyNetClient())
                assertSQLState("XJ208", sqle);
            
            stmt.clearBatch();
        }
        
        assertTableRowCount("T1",
                usingEmbedded() ? 0 : 3);

        rollback();

        assertTableRowCount("T1", 0);
        stmt.close();

        commit();
    }
    
    // Below trying various placements of overflow update statement in the 
    // batch, i.e. as 1st stmt, nth stmt and last stmt
    public void testStatementWithErrorsBatch() throws SQLException {
        
        Statement stmt = createStatement();

        stmt.executeUpdate("insert into t1 values(1)");

        // trying update as the first statement
        println("Negative Statement: statement testing overflow error" +
            " as first statement in the batch");
        stmt.addBatch("update t1 set c1=2147483647 + 1");
        stmt.addBatch("insert into t1 values(1)");
        /* Check to be sure the exception is the one we expect */
        /* Overflow is first stmt in the batch, so expect no update count */
        if (usingEmbedded())
            assertBatchExecuteError("22003", stmt, new long[] {});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", stmt, new long[] {-3,1});

        assertTableRowCount("T1",
                usingEmbedded() ? 1 : 2);

        // trying update as the nth statement
        println("Negative Statement: statement testing overflow error" +
            " as nth statement in the batch");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("update t1 set c1=2147483647 + 1");
        stmt.addBatch("insert into t1 values(1)");
        /* Check to be sure the exception is the one we expect */
        /* Update is second statement in the batch, expect 1 update count */
        if (usingEmbedded())
            assertBatchExecuteError("22003", stmt, new long[] {1});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", stmt, new long[] {1,-3,1});

        assertTableRowCount("T1",
                usingEmbedded() ? 2 : 4);

        // trying select as the last statement
        println("Negative Statement: statement testing overflow error" +
            " as last stat in the batch");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("insert into t1 values(1)");
        stmt.addBatch("update t1 set c1=2147483647 + 1");
        /* Check to be sure the exception is the one we expect */
        /* Update is last statement in the batch, expect 2 update counts */
        if (usingEmbedded())
            assertBatchExecuteError("22003", stmt, new long[] {1,1});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", stmt, new long[] {1,1,-3});

        assertTableRowCount("T1",
                usingEmbedded() ? 4 : 6);
        stmt.close();

        commit();
    }
    
    // try transaction error, in this particular case time out while
    // getting the lock
    public void testTransactionErrorBatch() throws SQLException {

        // conn is just default connection
        Connection conn = getConnection();
        Connection conn2 = openDefaultConnection();
        conn.setAutoCommit(false);
        conn2.setAutoCommit(false);        
        Statement stmt = conn.createStatement();
        Statement stmt2 = conn2.createStatement();
        
        int[] updateCount = null;

        println("Negative Statement: statement testing time out" +
            " while getting the lock in the batch");

        stmt.execute("insert into t1 values(1)");
        stmt2.execute("insert into t1 values(2)");

        stmt.addBatch("update t1 set c1=3 where c1=2");
        stmt2.addBatch("update t1 set c1=4 where c1=1");

        try
        {
            stmt.executeBatch();
            fail ("Batch is expected to fail");
            updateCount = stmt2.executeBatch();
        } catch (BatchUpdateException bue) {
            /* Ensure the exception is time out while getting lock */
            if (usingEmbedded())
                assertSQLState("40XL1", bue);
            else if (usingDerbyNetClient())
                assertSQLState("XJ208", bue);
            updateCount = ((BatchUpdateException)bue).getUpdateCounts();
            if (updateCount != null) {
                if (usingEmbedded())
                    assertEquals("first statement in the batch caused time out" +
                        " while getting the lock, there should be no update count", 
                        0, updateCount.length);
                else if (usingDerbyNetClient())
                    /* first statement in the batch caused time out while getting
                     *  the lock, there should be 1 update count of -3 */
                    assertBatchUpdateCounts(new int[] {-3}, updateCount);
            }
        }
        conn.rollback();
        conn2.rollback();
        stmt.clearBatch();
        stmt2.clearBatch();
        stmt.close();
        stmt2.close();
        commit();
        conn2.close();
    }
    
    /* Fixtures that test batch updates with CallableStatements */

    // try callable statements
    public void testCallableStatementBatch() throws SQLException {

        println("Positive Callable Statement: " +
            "statement testing callable statement batch");
        CallableStatement cs = prepareCall("insert into t1 values(?)");

        cs.setInt(1, 1);
        cs.addBatch();
        cs.setInt(1,2);
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "t1");

        /* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
         * calls on callableStatement throws ClassNotFoundException 
         * verify setXXXX() works with Date, Time and Timestamp 
         * on CallableStatement.
         */
        cs = prepareCall("insert into datetab values(?)");

        cs.setDate(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setDate(1,Date.valueOf("1990-06-06"));
        cs.addBatch();

        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "datetab");

        cs = prepareCall("insert into timetab values(?)");

        cs.setTime(1, Time.valueOf("11:11:11"));
        cs.addBatch();
        cs.setTime(1, Time.valueOf("12:12:12"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        cs = prepareCall("insert into timestamptab values(?)");

        cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
        cs.addBatch();
        cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        // Try with a user type
        cs = prepareCall("insert into usertypetab values(?)");

        cs.setObject(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setObject(1,Date.valueOf("1990-06-06"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "usertypetab");
    }
    
    // helper method to testCallableStatementBatch 
    // executes and evaluates callable statement
    private static void executeBatchCallableStatement(CallableStatement cs)
    throws SQLException
    {
        int updateCount[];

        updateCount = cs.executeBatch();
        assertEquals("there were 2 statements in the batch", 
            2, updateCount.length);
        for (int i=0; i<updateCount.length; i++) 
        {
            assertEquals("update count should be 1", 1, updateCount[i]);
        }
    }

    // helper method to testCallableStatementBatch - 
    // removes all rows from table
    protected void cleanUpCallableStatement(
        CallableStatement cs, String tableName)
    throws SQLException
    {
        cs.close();
        rollback();
        cs = prepareCall("delete from " + tableName);
        cs.executeUpdate();
        cs.close();
        commit();
    }
    
    // try callable statements with output parameters
    public void testCallableStatementWithOutputParamBatch() 
    throws SQLException {

        println("Negative Callable Statement: " +
            "callable statement with output parameters in the batch");
        Statement s = createStatement();

        s.execute("CREATE PROCEDURE " +
            "takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
            "EXTERNAL NAME '" + this.getClass().getName() + ".takesString'" +
        " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

        CallableStatement cs = prepareCall("call takesString(?,?)");
        cs.registerOutParameter(1, Types.CHAR);
        cs.setInt(2, Types.INTEGER);
        try
        {
            cs.addBatch();
            if (usingEmbedded())
                fail("Expected to see error XJ04C");
            else if (usingDerbyNetClient()) {
                executeBatchCallableStatement(cs);       
            }
        }
        catch (SQLException sqle)
        {
            // Check to be sure the exception is callback related
            assertSQLState("XJ04C", sqle);
        }

        cs.close();
        s.execute("drop procedure takesString");
        s.close();
        rollback();
    }
    
    // helper method to be used as procedure in test 
    // testCallableStatementWithOutputParamBatch
    public static void takesString(String[] outparam, int type) 
    throws Throwable
    {
        // method is stripped from takesString in jdbcapi/outparams.java
        outparam[0] = "3";
    }

    /* Fixtures that test correct usage with PreparedStatements */    
    
    // try executing a batch which nothing in it. Should work.
    public void testEmptyValueSetPreparedBatch() throws SQLException {
        
        // try executing a batch which nothing in it. Should work.
        println("Positive Prepared Stat: " +
            "set no parameter values and run the batch");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");

        assertBatchUpdateCounts(new int[] {}, pStmt.executeBatch());

        pStmt.close();
        commit();
    }
    
    // try prepared statement batch without settable parameters.
    public void testNoParametersPreparedBatch() throws SQLException {

        // Note: also tests for fix of NPE of DERBY-2112
     
        Statement stmt = createStatement();
        ResultSet rs;

        println("Positive Prepared Stat: no settable parameters");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(5)");
        pStmt.addBatch();
        pStmt.addBatch();
        pStmt.addBatch();
        /* 3 parameters were set in the batch, update count length
         *  should be 3 */
        assertBatchUpdateCounts(new int[] {1,1,1}, pStmt.executeBatch());

        pStmt.close();
        rs = stmt.executeQuery("select count(*) from t1 where c1=5");
        rs.next();
        assertEquals("There should be 3 rows with c1 = 5", 3, rs.getInt(1));
        rs.close();

        assertTableRowCount("T1", 3);
        stmt.close();

        commit();
    }
    
    // try prepared statement batch with just one set of values.
    public void testSingleValueSetPreparedBatch() throws SQLException {

        Statement stmt = createStatement();
        ResultSet rs;

        // try prepared statement batch with just one set of values
        println("Positive Prepared Stat: " +
            "set one set of parameter values and run the batch");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* 1 parameter was set in batch, update count length should be 1 */
        assertBatchUpdateCounts(new int[] {1}, pStmt.executeBatch());

        pStmt.close();
        rs = stmt.executeQuery("select count(*) from t1 where c1=1");
        rs.next();
        assertEquals("There should be 1 row with c1=1", 1, rs.getInt(1));
        
        rs.close();

        assertTableRowCount("T1", 1);

        stmt.close();

        commit();
    }
    
    // try executing a batch with 3 different parameter sets in it.
    public void testMultipleValueSetPreparedBatch() throws SQLException {

        // try prepared statement batch with just one set of values
        println("Positive Prepared Stat: " +
            "set 3 set of parameter values and run the batch");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 2);
        pStmt.addBatch();
        pStmt.setInt(1, 3);
        pStmt.addBatch();
        /* 3 parameters were set , update count length should be 3 */
        assertBatchUpdateCounts(new int[] {1,1,1}, pStmt.executeBatch());

        pStmt.close();
        
        assertTableRowCount("T1", 3);

        commit();
    }
    
    // try prepared statement batch with just 2 set of values 
    // and there value is null. 
    // tests fix for 'beetle' bug 4002: Execute batch for
    // preparedStatement gives nullPointerException
    public void testMultipleValueSetNullPreparedBatch() throws SQLException {

        Statement stmt = createStatement();
        ResultSet rs;

        // try prepared statement batch with just one set of values
        println("Positive Prepared Stat: " +
            "set one set of parameter values to null and run the batch");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        pStmt.setNull(1, Types.INTEGER);
        pStmt.addBatch();
        pStmt.setNull(1, Types.INTEGER);
        pStmt.addBatch();
        /* 2 parameters were set in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        pStmt.close();
        rs = stmt.executeQuery("select count(*) from t1 where c1 is null");
        rs.next();
        assertEquals("There should be 2 rows with c1 is null",
            2, rs.getInt(1));
        rs.close();

        assertTableRowCount("T1", 2);

        stmt.close();

        commit();
    }
    
    // try executing a batch with 1000 statements in it.
    public void test1000ValueSetPreparedBatch() throws SQLException {
        
        int updateCount[];

        println("Positive Prepared Stat: 1000 parameter set batch");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        for (int i=0; i<1000; i++){
            pStmt.setInt(1, 1);
            pStmt.addBatch();
        }
        updateCount = pStmt.executeBatch();

        int[] expectedUpdateCount = new int[1000];
        Arrays.fill(expectedUpdateCount, 1);
        assertBatchUpdateCounts(expectedUpdateCount, updateCount);
        
        assertTableRowCount("T1", 1000);

        pStmt.close();
        commit();
    }

    // try executing batches with various rollback and commit combinations.
    public void testPreparedStatRollbackAndCommitCombinations() 
    throws SQLException {

        println("Positive Prepared Stat: batch, rollback," +
            " batch and commit combinations");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        rollback();

        assertTableRowCount("T1", 0);

        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        commit();
        
        assertTableRowCount("T1", 2);

        // try batch and commit
        println("Positive Prepared Stat: batch and commit combinations");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        commit();

        assertTableRowCount("T1", 4);

        // try batch, batch and rollback
        println("Positive Prepared Stat: batch, " +
            "batch and rollback combinations");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        rollback();

        assertTableRowCount("T1", 4);

        // try batch, batch and commit
        println("Positive Prepared Stat: " +
            "batch, batch and commit combinations");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 2 statements in the batch, 
         * update count length should be 2 */
        assertBatchUpdateCounts(new int[] {1,1}, pStmt.executeBatch());

        commit();
        
        assertTableRowCount("T1", 8);

        pStmt.close();

        commit();
    }

    // try prepared statement batch with autocommit true
    public void testAutoCommitTruePreparedStatBatch() throws SQLException {

        getConnection().setAutoCommit(true);    

        // prepared statement batch with autocommit true
        println("Positive Prepared Stat: testing batch with autocommit true");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        /* there were 3 statements in the batch, 
         * update count length should be 3 */
        assertBatchUpdateCounts(new int[] {1,1,1}, pStmt.executeBatch());

        assertTableRowCount("T1", 3);

        pStmt.close();       
    }
    
    // try combinations of clear batch.
    public void testCombinationsOfClearPreparedStatBatch() 
    throws SQLException {

        int updateCount[];

        println("Positive Prepared Stat: add 3 statements, " +
            "clear batch and execute batch");
        PreparedStatement pStmt = 
            prepareStatement("insert into t1 values(?)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 2);
        pStmt.addBatch();
        pStmt.setInt(1, 3);
        pStmt.addBatch();
        pStmt.clearBatch();
        /* there were 0 statements in the batch, 
         * update count length should be 0 */
        assertBatchUpdateCounts(new int[] {}, pStmt.executeBatch());

        println("Positive Prepared Stat: " +
            "add 3 statements, clear batch, add 3 and execute batch");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 2);
        pStmt.addBatch();
        pStmt.setInt(1, 3);
        pStmt.addBatch();
        pStmt.clearBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 2);
        pStmt.addBatch();
        pStmt.setInt(1, 3);
        pStmt.addBatch();

        assertBatchUpdateCounts(new int[] {1,1,1}, pStmt.executeBatch());
        
        assertTableRowCount("T1", 3);

        pStmt.close();

        commit();
    }
    
    /* Fixtures that test incorrect usage with PreparedStatements */
    
    // statements which will return a resultset are not allowed in
    // batch Updates. Following case should throw an exception for select.
    public void testPreparedStmtWithResultSetBatch() throws SQLException {


        println("Negative Prepared Stat: testing select in the batch");
        PreparedStatement pStmt = 
            prepareStatement("select * from t1 where c1=?");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        if (usingEmbedded())
            /* Ensure the exception is the ResultSetReturnNotAllowed */
            /* "Select is first statement in the batch, 
             * so there should not be any update counts */
            assertBatchExecuteError("X0Y79", pStmt, new long[] {});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ117", pStmt, new long[] {-3});
        pStmt.close();

        assertTableRowCount("T1", 0);

        commit();
    }
    
    // try executing a batch with regular statement intermingled.
    public void testPreparedStmtNonBatchStuffInBatch() throws SQLException {
        
        int updateCount[] = null;

        // trying execute in the middle of batch
        println("Negative Prepared Stat: " +
            "testing execute in the middle of batch");
        PreparedStatement pStmt = 
            prepareStatement("select * from t1 where c1=?");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        try
        {
            pStmt.execute();
            if (usingEmbedded())
                fail("Expected executeBatch to fail");
            else if (usingDerbyNetClient())
                updateCount = pStmt.executeBatch();
        } catch (SQLException sqle) {
            if (usingEmbedded())
                /* Check to be sure the exception is the MIDDLE_OF_BATCH */
                assertSQLState("XJ068", sqle);
            else if (usingDerbyNetClient())
                assertSQLState("XJ117", sqle);
        }
        pStmt.close();
        
        
        assertTableRowCount("T1", 0);

        // trying executeQuery in the middle of batch
        println("Negative Prepared Statement: " +
            "testing executeQuery in the middle of batch");
        pStmt = 
            prepareStatement("select * from t1 where c1=?");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        try
        {
            pStmt.executeQuery();
            if (usingEmbedded())
                fail("Expected executeBatch to fail");
            else if (usingDerbyNetClient())
                updateCount = pStmt.executeBatch();
        } catch (SQLException sqle) {
            if (usingEmbedded())
                /* Check to be sure the exception is the MIDDLE_OF_BATCH */
                assertSQLState("XJ068", sqle);
            else if (usingDerbyNetClient())
                assertSQLState("XJ117", sqle);
        }
        pStmt.close();

        assertTableRowCount("T1", 0);

        //  trying executeUpdate in the middle of batch
        println("Negative Prepared Stat: " +
            "testing executeUpdate in the middle of batch");
        pStmt = 
            prepareStatement("select * from t1 where c1=?");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        try
        {
            pStmt.executeUpdate();
            if (usingEmbedded())
                fail("Expected executeBatch to fail");
            else if (usingDerbyNetClient())
                updateCount = pStmt.executeBatch();
        } catch (SQLException sqle) {
            if (usingEmbedded())
                /* Check to be sure the exception is the MIDDLE_OF_BATCH */
                assertSQLState("XJ068", sqle);
            else if (usingDerbyNetClient())
                assertSQLState("X0Y79", sqle);
        }
        pStmt.close();

        assertTableRowCount("T1", 0);

        commit();
    }
    
    // Below trying placements of overflow update statement in the batch
    public void testPreparedStmtWithErrorsBatch() throws SQLException {

        Statement stmt = createStatement();

        PreparedStatement pStmt = null;

        stmt.executeUpdate("insert into t1 values(1)");

        println("Negative Prepared Stat: " +
            "testing overflow as first set of values");
        pStmt = prepareStatement("update t1 set c1=(? + 1)");
        pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
        pStmt.addBatch();
        if (usingEmbedded())
            /* Check to be sure the exception is the one we expect */
            /* Overflow is first statement in the batch, 
             * so there should not be any update count */
            assertBatchExecuteError("22003", pStmt, new long[] {});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", pStmt, new long[] {-3});
        pStmt.close();

        assertTableRowCount("T1", 1);

        println("Negative Prepared Stat: " +
            "testing overflow as nth set of values");
        pStmt = prepareStatement("update t1 set c1=(? + 1)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        if (usingEmbedded())
            /* Check to be sure the exception is the one we expect */
            /* Overflow is second statement in the batch, 
             * so there should be only 1 update count */
            assertBatchExecuteError("22003", pStmt, new long[] {1});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", pStmt, new long[] {1,-3,1});
        pStmt.close();

        assertTableRowCount("T1", 1);

        // trying select as the last statement
        println("Negative Prepared Stat: " +
            "testing overflow as last set of values");
        pStmt = prepareStatement("update t1 set c1=(? + 1)");
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, 1);
        pStmt.addBatch();
        pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
        pStmt.addBatch();
        if (usingEmbedded())
            /* Check to be sure the exception is the one we expect */
            /* Overflow is last statement in the batch, 
             * so there should be 2 update counts */
            assertBatchExecuteError("22003", pStmt, new long[] {1,1});
        else if (usingDerbyNetClient())
            assertBatchExecuteError("XJ208", pStmt, new long[] {1,1,-3});

        assertTableRowCount("T1", 1);
        
        pStmt.close();
        stmt.close();

        commit();
    }
    
    // try transaction error, in this particular case 
    // time out while getting the lock
    public void testTransactionErrorPreparedStmtBatch() throws SQLException {

        Connection conn = getConnection();
        Connection conn2 = openDefaultConnection();
        conn.setAutoCommit(false);
        conn2.setAutoCommit(false);        
        Statement stmt = createStatement();
        Statement stmt2 = conn2.createStatement();

        int updateCount[] = null;

        println("Negative Prepared Statement: " +
            "testing transaction error, time out while getting the lock");

        stmt.execute("insert into t1 values(1)");
        stmt2.execute("insert into t1 values(2)");

        PreparedStatement pStmt1 = 
            prepareStatement("update t1 set c1=3 where c1=?");
        pStmt1.setInt(1, 2);
        pStmt1.addBatch();

        PreparedStatement pStmt2 = 
            conn.prepareStatement("update t1 set c1=4 where c1=?");
        pStmt2.setInt(1, 1);
        pStmt2.addBatch();

        try
        {
            pStmt1.executeBatch();
            updateCount = pStmt2.executeBatch();
            fail ("Batch is expected to fail");
        } catch (BatchUpdateException bue) {
            /* Check that the exception is time out while 
             * getting the lock */
            if (usingEmbedded())
                assertSQLState("40XL1", bue);
            else if (usingDerbyNetClient())
                assertSQLState("XJ208", bue);
            updateCount = ((BatchUpdateException)bue).getUpdateCounts();
            if (updateCount != null) {
                if (usingEmbedded())
                    assertEquals("first statement in the batch caused time out" +
                        " while getting the lock, there should be no update count", 
                        0, updateCount.length);
                else if (usingDerbyNetClient())
                    /* first statement in the batch caused time out while getting
                     *  the lock, there should be 1 update count of -3 */
                    assertBatchUpdateCounts(new int[] {-3}, updateCount);
            }
        }
        
        pStmt1.close();
        pStmt2.close();
        
        stmt.close();
        stmt2.close();

        rollback();
        conn2.rollback();
        conn2.close();
    }

    /**
     * Test that the underlying exception is included in the output when we
     * call printStackTrace() on a BatchUpdateException. Earlier, with the
     * client driver, the underlying cause of a BatchUpdateException could not
     * be seen without calling getNextException().
     */
    public void testUnderlyingExceptionIsVisible() throws SQLException {
        setAutoCommit(false);
        Statement s = createStatement();
        s.addBatch("create table t(x int unique not null)");
        for (int i = 0; i < 3; i++) {
            s.addBatch("insert into t values 1");
        }

        BatchUpdateException bue = null;
        try {
            s.executeBatch();
        } catch (BatchUpdateException e) {
            bue = e;
        }
        assertNotNull("Did not get duplicate key exception", bue);

        StringWriter w = new StringWriter();
        bue.printStackTrace(new PrintWriter(w, true));

        String stackTrace = w.toString();
        if (stackTrace.indexOf("duplicate key") == -1) {
            fail("Could not see 'duplicate key' in printStackTrace()", bue);
        }
    }

    /**
     * Test the behaviour when one of the statements in a batch fails. The
     * embedded driver stops executing the batch when that happens, whereas
     * the client driver continues. The difference between embedded and
     * client is logged as DERBY-4316.
     */
    public void testContinueAfterError() throws SQLException {
        // Turn off auto-commit so that the tables added by the test can be
        // rolled back in tearDown().
        setAutoCommit(false);

        Statement s = createStatement();
        s.execute("create table a(x int)");
        s.execute("create table b(x int primary key)");
        s.execute("create table c(x int references b(x))");

        // Drop the three tables in a batch. Since B is referenced by C, it
        // cannot be dropped before C is dropped. Hence DROP TABLE B will fail.
        s.addBatch("drop table a");
        s.addBatch("drop table b");
        s.addBatch("drop table c");

        // Embedded stops processing the batch on the first failure, and only
        // the update count from the successful statement is returned. The
        // client driver continues after the failure, so it'll also drop C.
        long[] expectedCounts = usingEmbedded() ?
            new long[]{0} : new long[]{0, Statement.EXECUTE_FAILED, 0};

        assertBatchExecuteError("X0Y25", s, expectedCounts);

        // Table A should not exist after the batch was executed.
        assertStatementError("42X05", s, "select * from a");
        // Table B should still exist, since DROP TABLE B failed.
        assertTableRowCount("B", 0);

        // Embedded driver stops after failure, so expect table C to exist,
        // whereas the client driver continues after failure, so expect that
        // it does not exist.
        if (usingEmbedded()) {
            assertTableRowCount("C", 0);
        } else {
            assertStatementError("42X05", s, "select * from c");
        }
    }
    ////////////////////////////////////////////////////////////////////////
    //
    // NESTED JDBC 4.2 WRAPPER AROUND A BatchUpdateException
    //
    ////////////////////////////////////////////////////////////////////////

    /**
     * <p>
     * This wrapper is used to expose JDBC 4.2 methods which can run on
     * VM rev levels lower than Java 8.
     * </p>
     */
    public  static  class   BatchUpdateExceptionWrapper
    {
        private BatchUpdateException    _wrappedException;

        public BatchUpdateExceptionWrapper( BatchUpdateException wrappedException )
        {
            _wrappedException = wrappedException;
        }

        public  BatchUpdateException   getWrappedException() { return _wrappedException; }

        // New methods added by JDBC 4.2
        public  long[] getLargeUpdateCounts()
        {
            return ((long[]) invoke
                (
                 "getLargeUpdateCounts",
                 new Class[] {},
                 new Object[] {}
                 ));
        }

        // Reflection minion
        protected Object  invoke( String methodName, Class[] argTypes, Object[] argValues )
        {
            try {
                Method  method = _wrappedException.getClass().getMethod( methodName, argTypes );

                return method.invoke( _wrappedException, argValues );
            }
            catch (Exception nsme) { printException( nsme ); }

            return null;
        }
        private void    printException( Throwable t ) { BaseJDBCTestCase.println( t.getMessage() ); }
    }
    
}