db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrn...@apache.org
Subject svn commit: r504641 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/jdbcapi/
Date Wed, 07 Feb 2007 18:41:30 GMT
Author: myrnavl
Date: Wed Feb  7 10:41:28 2007
New Revision: 504641

URL: http://svn.apache.org/viewvc?view=rev&rev=504641
Log:
DERBY-2293 - basic conversion of test jdbcapi/batchUpdate.java to junit test jdbcapi.BatchUpdateTest


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BatchUpdateTest.java   (with props)
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/batchUpdate.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/batchUpdate.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/batchUpdate_app.properties
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/batchUpdate_derby.properties
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNet.exclude
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClient.exclude
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/JDBCHarnessJavaTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNet.exclude
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNet.exclude?view=diff&rev=504641&r1=504640&r2=504641
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNet.exclude (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNet.exclude Wed Feb  7 10:41:28 2007
@@ -1,7 +1,6 @@
 # excluding resultsetStream.java because this test uses java.io.FileInputStream throughout the test
 # excluding TestErrorStreamTarget.java since it's not relevant for clients
 # excluding scrollCursors2.java because updatable resultsets & scroll sensitive cursors are not supported
-# excluding batchUpdate.java for it hits a problem in networkserver ('beetle' 5561)
 # excluding statementJdbc20.java because this tests fetch_reverse throughout the test
 # excluding jdbcapi/testRelative.java because this is a new test contributed by Shreyas Kaushik and needs to be debugged with JCC  in order to get it running with network server
 # excluding jdbcapi/SetQueryTimeoutTest.java because neither the JCC driver nor the ClientDriver support setQueryTimeout() yet.
@@ -16,7 +15,6 @@
 jdbcapi/resultsetStream.java
 lang/errorStream.java
 lang/scrollCursors2.java
-jdbcapi/batchUpdate.java
 jdbcapi/statementJdbc20.java
 jdbcapi/testRelative.java
 jdbcapi/rsgetXXXcolumnNames.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClient.exclude
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClient.exclude?view=diff&rev=504641&r1=504640&r2=504641
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClient.exclude (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClient.exclude Wed Feb  7 10:41:28 2007
@@ -1,6 +1,5 @@
 # excluding TestErrorStreamTarget.java since it's not relevant for clients
 # excluding scrollCursors2.java because updatable resultsets & scroll sensitive cursors are not supported
-# excluding batchUpdate.java for it hits a problem in networkserver ('beetle' 5561)
 # excluding statementJdbc20.java because this tests fetch_reverse throughout the test
 # excluding jdbcapi/statementJdbc30.java - Client behaves differently. Need to look into this
 # excluding jdbcapi/dataSourceReference.java - client side only tests, tests all data sources
@@ -10,7 +9,6 @@
 lang/errorStream.java
 # lang/scrollCursors2.java
 #
-jdbcapi/batchUpdate.java
 jdbcapi/statementJdbc20.java
 jdbcapi/statementJdbc30.java
 jdbcapi/dataSourceReference.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall?view=diff&rev=504641&r1=504640&r2=504641
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall Wed Feb  7 10:41:28 2007
@@ -1,7 +1,6 @@
 lang/scrollCursors1.sql
 lang/scrollCursors2.java
 lang/scrollCursors3.sql
-jdbcapi/batchUpdate.java
 jdbcapi/getCurConnJdbc20.sql
 jdbcapi/statementJdbc20.java
 jdbcapi/connectionJdbc20.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BatchUpdateTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BatchUpdateTest.java?view=auto&rev=504641
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BatchUpdateTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BatchUpdateTest.java Wed Feb  7 10:41:28 2007
@@ -0,0 +1,1729 @@
+/*
+
+   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.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.Statement;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.sql.Types;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+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.
+ * There are 5 actual fixtures, and even the setup is executing in batch, to 
+ * verify basic create table and insert statements also work in batch.
+ * The five actual fixtures are:
+ * testStatementBatchUpdatePositive - verifies correct usage with Statements 
+ *                                work as expected 
+ * testStatementBatchUpdateNegative - verifies incorrect usage with Statments
+ *                                gives appropriate errors
+ * testCallableStatementBatchUpdate - verifies usage with callableStatements
+ *                                works as expected
+ * testPreparedStatementBatchUpdatePositive
+ *                              - verifies correct usage with preparedStatements
+ * testPreparedStatementBatchUpdateNegative
+ *                              - verifies incorrect use with preparedStatements
+ * 
+ * The test executes almost all submethods of these fixtures with both
+ * 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 three subtests that are not running with network server are 
+ * identified with //TODO: tags and have an if (usingEmbedded()) block.
+ * 
+ */
+
+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 {
+        Connection conn = getConnection();
+        conn.setAutoCommit(false);
+        Statement stmt = createStatement();
+        stmt.addBatch("create table t1(c1 int)");
+        stmt.addBatch("create procedure Integ() language java " +
+            "parameter style java external name 'java.lang.Integer'");
+        stmt.addBatch("create table datetab(c1 date)");
+        stmt.addBatch("create table timetab(c1 time)");
+        stmt.addBatch("create table timestamptab(c1 timestamp)");
+        stmt.addBatch("create table usertypetab(c1 DATE)");
+
+        int expectedCount[] = {0,0,0,0,0,0};
+        assertBatchUpdateCounts(expectedCount, stmt.executeBatch());
+        
+        // for method checkAssociatedParams
+        stmt.executeUpdate("create table assoc" +
+                "(x char(10) not null primary key, y char(100))");
+        stmt.executeUpdate("create table assocout(x char(10))");
+        
+        conn.commit();
+    }
+
+    protected void tearDown() throws Exception {
+        Statement stmt = createStatement();
+        stmt.executeUpdate("DROP TABLE datetab");
+        stmt.executeUpdate("DROP TABLE timetab");
+        stmt.executeUpdate("DROP TABLE timestamptab");
+        stmt.executeUpdate("DROP TABLE usertypetab");
+        stmt.executeUpdate("DROP PROCEDURE Integ");
+        stmt.executeUpdate("DROP TABLE t1");
+        // for method checkAssociatedParams
+        stmt.executeUpdate("drop table assoc");
+        stmt.executeUpdate("drop table assocout");
+        commit();
+        super.tearDown();
+    }
+    
+    public static Test suite() {
+        TestSuite suite = new TestSuite("BatchUpdateTest");
+        suite.addTest(
+            TestConfiguration.defaultSuite(BatchUpdateTest.class));
+
+        return new CleanDatabaseTestSetup(
+            DatabasePropertyTestSetup.setLockTimeouts(suite, 2, 4));
+     }
+    
+    /* 
+     * helper method to check each count in the return array of batchExecute
+     */
+    public 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 String               The sqlstate to look for.
+     * @param Statement            The Statement that contains the Batch to
+     *                             be executed.
+     * @param int[]                The expectedUpdateCount array.
+     */
+    protected void assertBatchExecuteError( 
+            String expectedError,
+            Statement stmt,
+            int[] expectedUpdateCount) throws SQLException 
+    {
+        int[] updateCount;    
+        try {
+            updateCount = stmt.executeBatch();
+            fail("Expected stmt.batchExecute to fail");
+        } catch (SQLException sqle) {
+            assertSQLState(expectedError, sqle);
+            assertTrue("Expect BatchUpdateException", 
+                (sqle instanceof BatchUpdateException));
+            updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
+            assertBatchUpdateCounts(expectedUpdateCount, updateCount);
+        }
+    }
+
+
+    /** 
+     * 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 String               The sqlstate to look for.
+     * @param PreparedStatement    The PreparedStatement that contains the 
+     *                             batch to be executed.
+     * @param int[]                The expectedUpdateCount array.
+     *                             
+     */
+    protected void assertBatchExecuteError( 
+            String expectedError,
+            PreparedStatement pstmt,
+            int[] expectedUpdateCount) throws SQLException 
+    {
+        int[] updateCount;    
+        try {
+            updateCount = pstmt.executeBatch();
+            fail("Expected pstmt.batchExecute to fail");
+        } catch (SQLException sqle) {
+            assertSQLState(expectedError, sqle);
+            assertTrue("Expect BatchUpdateException", 
+                (sqle instanceof BatchUpdateException));
+            updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
+            assertBatchUpdateCounts(expectedUpdateCount, updateCount);
+        }
+    }
+    
+    /**
+     * Positive tests for statement batch update.
+     *
+     * @exception SQLException      Thrown if some unexpected error happens
+     */
+    public void testStatementBatchUpdatePositive()
+    throws SQLException
+    {
+        Connection conn = getConnection();
+        Statement stmt = createStatement();
+        // try executing a batch which nothing in it.
+        runEmptyStatementBatch(conn, stmt);
+        // try executing a batch which one statement in it.
+        runSingleStatementBatch(conn, stmt);
+        // try executing a batch with 3 different statements in it.
+        runMultipleStatementsBatch(conn, stmt);
+        // try executing a batch with 1000 statements in it.
+        run1000StatementsBatch(conn, stmt);
+        // try batch with autocommit true
+        runAutoCommitTrueBatch(conn, stmt);
+        // try clear batch
+        runCombinationsOfClearBatch(conn, stmt);
+        // confirm associated parameters run ok with batches
+        checkAssociatedParams(conn, stmt);
+        conn.commit();
+    }
+
+    /**
+     * Negative tests for statement batch update.
+     * 
+     * @exception SQLException      Thrown if some unexpected error happens
+     */
+    public void testStatementBatchUpdateNegative() throws SQLException 
+    {
+        Connection conn = getConnection();
+        Connection conn2 = openDefaultConnection();
+        conn.setAutoCommit(false);
+        conn2.setAutoCommit(false);        
+        Statement stmt = conn.createStatement();
+        Statement stmt2 = conn2.createStatement();
+
+        // 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
+        runStatementWithResultSetBatch(conn, stmt);
+
+        // try executing a batch with regular statement intermingled.
+        runStatementNonBatchStuffInBatch(conn, stmt);
+
+        // Below trying various placements of overflow update statement 
+        // in the batch, i.e. as 1st stmt, nth stat and last stmt
+        runStatementWithErrorsBatch(conn, stmt);
+
+        // TODO: When running this with networkserver, we won't be able
+        // to drop t1 afterwards. Needs researching.
+        if (usingEmbedded())
+            // try transaction error, i.e. time out while getting the lock
+            runTransactionErrorBatch(conn, stmt, conn2, stmt2);
+        
+     }
+
+    /**
+     * Tests for callable statement batch update.
+     *
+     * @exception SQLException      Thrown if some unexpected error happens
+     */
+    public void testCallableStatementBatchUpdate()
+    throws SQLException
+    {
+        Connection conn = getConnection();
+        
+        // try callable statements
+        runCallableStatementBatch(conn);
+
+        // try callable statement with output parameters
+        runCallableStatementWithOutputParamBatch(conn);
+    }
+    
+    /**
+     * Positive tests for prepared statement batch update.
+     *
+     *  @exception SQLException      Thrown if some unexpected error happens
+     */
+    public void testPreparedStatementBatchUpdatePositive()
+    throws SQLException 
+    {
+        Connection conn = getConnection();
+        Statement stmt = createStatement();
+
+        //try executing a batch which nothing in it.
+        runEmptyValueSetPreparedBatch(conn, stmt);
+
+        // following fails with NullPointerException with NetworkServer
+        // see DERBY-2112
+        if (usingEmbedded())
+        // try executing a batch with no parameters.
+            runNoParametersPreparedBatch(conn, stmt);
+
+        // try executing a batch which one parameter set in it.
+        runSingleValueSetPreparedBatch(conn, stmt);
+
+        // try executing a batch with 3 parameter sets in it.
+        runMultipleValueSetPreparedBatch(conn, stmt);
+
+        // try executing a batch with 2 parameter sets in it 
+        // and they are set to null.
+        runMultipleValueSetNullPreparedBatch(conn, stmt);
+
+        // try executing a batch with 1000 statements in it.
+        run1000ValueSetPreparedBatch(conn, stmt);
+
+        // try executing batches with various rollback and commit combinations.
+        runPreparedStatRollbackAndCommitCombinations(conn, stmt);
+
+        // try prepared statement batch with autocommit true
+        runAutoCommitTruePreparedStatBatch(conn, stmt);
+
+        // try clear batch
+        runCombinationsOfClearPreparedStatBatch(conn, stmt);
+
+    }
+       
+    /**
+     * Negative tests for prepared statement batch update.
+     *
+     *  @exception SQLException      Thrown if some unexpected error happens
+     */
+    public void testPreparedStatementBatchUpdateNegative() throws SQLException 
+    {
+
+        Connection conn = getConnection();
+        Connection conn2 = openDefaultConnection();
+        conn.setAutoCommit(false);
+        conn2.setAutoCommit(false);        
+        Statement stmt = conn.createStatement();
+        Statement stmt2 = conn2.createStatement();
+        
+        // statements which will return a resultset are not allowed in batch
+        // update. The following case should throw an exception for select.
+        runPreparedStmtWithResultSetBatch(conn, stmt);
+
+        // try executing a batch with regular statement intermingled.
+        runPreparedStmtNonBatchStuffInBatch(conn, stmt);
+
+        // Below trying various placements of overflow update statement 
+        // in the batch
+        runPreparedStmtWithErrorsBatch(conn, stmt);
+
+        // TODO: when running this test with NetworkServer, t1 can
+        //       no longer be dropped. Needs research.
+        if (usingEmbedded())
+            // try transaction error, in this particular case time out while 
+            // getting the lock
+            runTransactionErrorPreparedStmtBatch(conn, stmt, conn2, stmt2);
+    }
+	
+    /* Following are methods used in testStatementUpdateBatchPositive */
+    
+    // try executing a batch which nothing in it. Should work.
+    protected void runEmptyStatementBatch(Connection conn, Statement stmt) 
+    throws SQLException {
+        int updateCount[];
+
+        // try executing a batch which nothing in it. Should work.
+        println("Positive Statement: clear the batch and run the empty batch");
+        stmt.clearBatch();
+        updateCount = stmt.executeBatch();
+        assertEquals("expected updateCount of 0", 0, updateCount.length);
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+
+    // try executing a batch which single statement in it. Should work.
+    protected void runSingleStatementBatch(Connection conn, Statement stmt) 
+    throws SQLException {
+
+        println("Positive Statement: testing 1 statement batch");
+        stmt.addBatch("insert into t1 values(2)");
+
+        assertBatchUpdateCounts(new int[] {1}, stmt.executeBatch());
+            
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try executing a batch with 3 different statements in it.
+    protected void runMultipleStatementsBatch(
+            Connection conn, Statement stmt) throws SQLException {
+        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();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("expect 2 rows total", 2, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+
+    // try executing a batch with 1000 statements in it.
+    protected void run1000StatementsBatch(Connection conn, Statement stmt) 
+    throws SQLException {
+        int updateCount[];
+        ResultSet rs;
+
+        println("Positive Statement: 1000 statements batch");
+        for (int i=0; i<1000; i++){
+            stmt.addBatch("insert into t1 values(1)");
+        }
+        updateCount = stmt.executeBatch();
+        assertEquals("1000 statement in the batch, expect update count 1000", 
+            1000, updateCount.length);
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("1000 statement in the batch, expect 1000 rows",
+            1000, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+
+    // try batch with autocommit true
+    protected void runAutoCommitTrueBatch(Connection conn, Statement stmt) 
+    throws SQLException {
+        ResultSet rs;
+
+        conn.setAutoCommit(true);
+        // 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());
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("expect 0 rows", 0,rs.getInt(1));
+        rs.close();
+
+        //turn it true again after the above negative test
+        conn.setAutoCommit(false);
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+
+    //  try combinations of clear batch.
+    protected void runCombinationsOfClearBatch(
+            Connection conn, Statement stmt) throws SQLException {
+        ResultSet rs;
+
+        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();
+
+        assertEquals("Batch should be cleared, there should be no update count",
+            0, stmt.executeBatch().length);
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        JDBC.assertEmpty(rs);
+        rs.close();
+
+        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());
+        rs = stmt.executeQuery("select count(*) from t1");
+        JDBC.assertFullResultSet(rs, new String[][] {{"3"}}, true);
+
+        rs.close();
+        stmt.executeUpdate("delete from t1");
+        conn.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. 
+     */
+    protected void checkAssociatedParams(Connection conn, Statement stmt)
+    throws SQLException 
+    {
+        int i;
+        conn.setAutoCommit(false);
+        println("Positive Statement: testing associated parameters");
+        PreparedStatement checkps = conn.prepareStatement(
+            "select x from assocout order by x");
+        PreparedStatement ps = conn.prepareStatement(
+            "insert into assoc values (?, 'hello')");
+        for ( i = 10; i < 60; i++)
+        {
+            ps.setString(1, new Integer(i).toString());
+            ps.executeUpdate();     
+        }
+
+        ps = conn.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());
+        checkps.execute();
+        ResultSet rs = checkps.getResultSet();
+        JDBC.assertFullResultSet(
+            rs, new String[][] {{"21"},{"33"},{"49"}}, true);
+                
+        stmt.executeUpdate("delete from assocout");
+
+        ps = conn.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());
+        checkps.execute();
+        rs = checkps.getResultSet();
+        JDBC.assertFullResultSet(rs, expectedStrArray, true);
+                
+        stmt.executeUpdate("delete from assocout");
+        ps = conn.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());
+        checkps.execute();
+        rs = checkps.getResultSet();
+        JDBC.assertFullResultSet(rs, expectedStrArray2, true);
+    }
+
+    /* Following are methods used in testStatementBatchUpdateNegative */
+
+    // 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
+    protected void runStatementWithResultSetBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        
+        ResultSet rs;
+
+        // 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 int[] {});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", stmt, new int[] {-3, 1});
+        
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals(
+                "There should be no rows in the table", 0, rs.getInt(1));
+        else if (usingDerbyNetClient())
+            assertEquals("There will be 1 row in the table", 1, rs.getInt(1));
+        rs.close();
+        
+        // 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 int[] {1});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", stmt, new int[] {1,-3,1});
+            
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals(
+                "There should be 1 row in the table", 1, rs.getInt(1));
+        else if (usingDerbyNetClient())
+            assertEquals("There are 3 rows in the table", 3, rs.getInt(1));
+        rs.close();
+
+        // 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 int[] {1,1});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", stmt, new int[] {1,1,-3});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals(
+                "There should now be 3 rows in the table", 3, rs.getInt(1));
+        else if (usingDerbyNetClient())
+            assertEquals(
+                "There should now be 5 rows in the table", 5, rs.getInt(1));
+        rs.close();
+
+        conn.rollback();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table after rollback", 
+                0, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try executing a batch with regular statement intermingled.
+    protected void runStatementNonBatchStuffInBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        
+        int[] updateCount=null;
+        ResultSet rs;
+
+        // 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 int[] {-3,1});           
+            // pull level with embedded situation
+            conn.rollback();
+        }
+        // do clearBatch so we can proceed
+        stmt.clearBatch();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table", 0, rs.getInt(1));
+        rs.close();
+
+        try
+        {
+            // trying executeQuery after addBatch
+            println("Negative Statement: " +
+                "statement testing executeQuery in the middle of batch");
+            stmt.addBatch("insert into t1 values(1)");
+            stmt.executeQuery("SELECT * FROM SYS.SYSTABLES");
+            updateCount = stmt.executeBatch();
+            if (usingEmbedded())
+                fail("Expected executeBatch to fail with embedded");
+            else if (usingDerbyNetClient())
+            {   
+                assertBatchUpdateCounts(new int[] {1}, updateCount);
+                // set to same spot as embedded
+                conn.rollback();
+            }
+        } 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();
+        }
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table", 0, rs.getInt(1));
+        rs.close();
+
+        println("Negative Statement: " +
+            "statement testing executeUpdate in the middle of batch");
+        try
+        {
+            // trying executeUpdate after addBatch
+            println("Negative Statement: " +
+                "statement testing executeUpdate in the middle of batch");
+            stmt.addBatch("insert into t1 values(1)");
+            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 int[] {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();
+        }
+        
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals("There should be no rows in the table", 
+                0, rs.getInt(1));
+        else if (usingDerbyNetClient())
+            assertEquals("There should be 3 rows in the table", 
+                3, rs.getInt(1));
+        rs.close();
+
+        conn.rollback();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table", 0, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // Below trying various placements of overflow update statement in the 
+    // batch, i.e. as 1st stmt, nth stmt and last stmt
+    protected void runStatementWithErrorsBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        
+        ResultSet rs;
+
+        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 int[] {});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", stmt, new int[] {-3,1});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals("there should be 1 row in the table", 
+                    1, rs.getInt(1));
+        if (usingDerbyNetClient())
+            assertEquals("there should be 2 rows in the table", 
+                    2, rs.getInt(1));
+        rs.close();
+
+        // 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 int[] {1});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", stmt, new int[] {1,-3,1});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals("expected: 2 rows", 2, rs.getInt(1));
+        if (usingDerbyNetClient())
+            assertEquals("expected: 4 rows", 4, rs.getInt(1));
+        rs.close();
+
+        // 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 int[] {1,1});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", stmt, new int[] {1,1,-3});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        if (usingEmbedded())
+            assertEquals("expected: 4 rows", 4, rs.getInt(1));
+        if (usingDerbyNetClient())
+            assertEquals("expected: 6 rows", 6, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try transaction error, in this particular case time out while
+    // getting the lock
+    protected void runTransactionErrorBatch(
+        Connection conn, Statement stmt,
+        Connection conn2, Statement stmt2) throws SQLException {
+
+        int[] updateCount = null;
+
+        try
+        {
+            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");
+                      
+            stmt.executeBatch();
+            updateCount = stmt2.executeBatch();
+            fail ("Batch is expected to fail");
+        } catch (SQLException sqle) {
+            /* Ensure the exception is time out while getting lock */
+            assertSQLState("40XL1",sqle);
+            assertTrue("we should get a BatchUpdateException", 
+                (sqle instanceof BatchUpdateException));
+            updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
+            if (updateCount != null) {
+                assertEquals("first statement in the batch caused time out" +
+                    " while getting the lock, expect no update count",
+                    0, updateCount.length);
+            }
+        }
+        conn.rollback();
+        conn2.rollback();
+        stmt.clearBatch();
+        stmt2.clearBatch();
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    /* Following are methods used in testCallableStatementBatchUpdate */
+
+    // try callable statements
+    protected void runCallableStatementBatch(Connection conn)
+    throws SQLException {
+        conn.setAutoCommit(false);
+        
+        println("Positive Callable Statement: " +
+            "statement testing callable statement batch");
+        CallableStatement cs = conn.prepareCall("insert into t1 values(?)");
+
+        cs.setInt(1, 1);
+        cs.addBatch();
+        cs.setInt(1,2);
+        cs.addBatch();
+        try
+        {
+            executeBatchCallableStatement(cs);
+        }
+        catch (SQLException sqle)
+        {   
+            fail("The executeBatch should have succeeded");
+        }
+        cleanUpCallableStatement(conn, 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 = conn.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();
+        try
+        {
+            executeBatchCallableStatement(cs);
+        }
+        catch (SQLException sqle)
+        {   
+            fail("The executeBatch should have succeeded");
+        }
+        cleanUpCallableStatement(conn, cs, "datetab");
+
+        cs = conn.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();
+        try
+        {
+            executeBatchCallableStatement(cs);
+        }
+        catch (SQLException sqle)
+        {   
+            fail("The executeBatch should have succeeded");
+        }
+        cleanUpCallableStatement(conn, cs, "timestamptab");
+
+        cs = conn.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();
+        try
+        {
+            executeBatchCallableStatement(cs);
+        }
+        catch (SQLException sqle)
+        {   
+            fail("The executeBatch should have succeeded");
+        }
+        cleanUpCallableStatement(conn, cs, "timestamptab");
+
+        // Try with a user type
+        cs = conn.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();
+        try
+        {
+            executeBatchCallableStatement(cs);
+        }
+        catch (SQLException sqle)
+        {   
+            fail("The executeBatch should have succeeded");
+        }
+        cleanUpCallableStatement(conn, cs, "usertypetab");
+    }
+    
+    // helper method to runCallableStatementBatch 
+    // 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 runCallableStatementBatch - 
+    // removes all rows from table
+    protected static void cleanUpCallableStatement(
+        Connection conn, CallableStatement cs, String tableName)
+    throws SQLException
+    {
+        cs.close();
+        conn.rollback();
+        cs = conn.prepareCall("delete from " + tableName);
+        cs.executeUpdate();
+        cs.close();
+        conn.commit();
+    }
+    
+    // try callable statements with output parameters
+    // TODO: isolate the procedure(s) from lang/outparams? 
+    protected void runCallableStatementWithOutputParamBatch(Connection conn) 
+    throws SQLException {
+        String CLASS_NAME;
+        if(JDBC.vmSupportsJDBC3())
+            CLASS_NAME = 
+               "org.apache.derbyTesting.functionTests.tests.lang.outparams30.";
+        else
+            CLASS_NAME = 
+                "org.apache.derbyTesting.functionTests.tests.lang.outparams.";
+
+        println("Negative Callable Statement: " +
+            "callable statement with output parameters in the batch");
+        Statement s = conn.createStatement();
+
+        s.execute("CREATE PROCEDURE " +
+                "takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
+                "EXTERNAL NAME '" + CLASS_NAME + "takesString'" +
+        " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
+
+        CallableStatement cs = conn.prepareCall("call takesString(?,?)");
+        try
+        {
+            cs.registerOutParameter(1, Types.CHAR);
+            cs.setInt(2, Types.INTEGER);
+            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();
+        conn.rollback();
+        conn.commit();
+    }
+
+    /* Following are methods used in 
+     * testPreparedStatementBatchUpdatePositive */    
+    
+    // try executing a batch which nothing in it. Should work.
+    protected void runEmptyValueSetPreparedBatch(
+        Connection conn, Statement stmt) 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 = 
+            conn.prepareStatement("insert into t1 values(?)");
+
+        assertBatchUpdateCounts(new int[] {}, pStmt.executeBatch());
+
+        pStmt.close();
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try prepared statement batch with just no settable parameters.
+    protected void runNoParametersPreparedBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        ResultSet rs;
+
+        println("Positive Prepared Stat: no settable parameters");
+        PreparedStatement pStmt = 
+            conn.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();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 3 rows", 3, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try prepared statement batch with just one set of values.
+    protected void runSingleValueSetPreparedBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        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 = 
+            conn.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();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 1 row", 1, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try executing a batch with 3 different parameter sets in it.
+    protected void runMultipleValueSetPreparedBatch(
+            Connection conn, Statement stmt) throws SQLException {
+        ResultSet rs;
+
+        // 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 = 
+            conn.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();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 3 rows", 3, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.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
+    protected void runMultipleValueSetNullPreparedBatch(
+            Connection conn, Statement stmt) throws SQLException {
+        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 = 
+            conn.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();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 2 rows", 2, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try executing a batch with 1000 statements in it.
+    protected void run1000ValueSetPreparedBatch(
+        Connection conn, Statement stmt) 
+    throws SQLException {
+        int updateCount[];
+        ResultSet rs;
+
+        println("Positive Prepared Stat: 1000 parameter set batch");
+        PreparedStatement pStmt = 
+            conn.prepareStatement("insert into t1 values(?)");
+        for (int i=0; i<1000; i++){
+            pStmt.setInt(1, 1);
+            pStmt.addBatch();
+        }
+        updateCount = pStmt.executeBatch();
+
+        assertEquals("there were 1000 parameters set in the batch," +
+            " update count length should be 1000",
+            1000, updateCount.length);
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 1000 rows in the table",
+                1000, rs.getInt(1));
+        rs.close();
+
+        pStmt.close();
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+
+    // try executing batches with various rollback and commit combinations.
+    protected void runPreparedStatRollbackAndCommitCombinations(
+        Connection conn, Statement stmt) throws SQLException {
+
+        ResultSet rs;
+
+        println("Positive Prepared Stat: batch, rollback," +
+                " batch and commit combinations");
+        PreparedStatement pStmt = 
+            conn.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());
+
+        conn.rollback();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 0 rows after rollback", 0, rs.getInt(1));
+        rs.close();
+
+        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());
+
+        conn.commit();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 2 rows", 2, rs.getInt(1));
+        
+        rs.close();
+
+        // 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());
+
+        conn.commit();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 4 rows", 4, rs.getInt(1));
+        rs.close();
+
+        // 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());
+
+        conn.rollback();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 4 rows", 4, rs.getInt(1));
+        rs.close();
+
+        // 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());
+
+        conn.commit();
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 8 rows", 8, rs.getInt(1));
+
+        rs.close();
+        pStmt.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+
+    // try prepared statement batch with autocommit true
+    protected void runAutoCommitTruePreparedStatBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        ResultSet rs;
+
+        conn.setAutoCommit(true);
+        // prepared statement batch with autocommit true
+        println("Positive Prepared Stat: testing batch with autocommit true");
+        PreparedStatement pStmt = 
+            conn.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());
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 3 rows in the table", 3, rs.getInt(1));
+        rs.close();
+        pStmt.close();
+
+        // turn it true again after the above negative test
+        conn.setAutoCommit(false);
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try combinations of clear batch.
+    protected void runCombinationsOfClearPreparedStatBatch(
+        Connection conn, Statement stmt) throws SQLException {
+
+        int updateCount[];
+        ResultSet rs;
+
+        println("Positive Prepared Stat: add 3 statements, " +
+            "clear batch and execute batch");
+        PreparedStatement pStmt = 
+            conn.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();
+        updateCount = pStmt.executeBatch();
+
+        assertEquals("there were 3 statements in the batch, " +
+            "update count should be 3",
+            3, updateCount.length);
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 3 rows in the table", 3, rs.getInt(1));
+        rs.close();
+        pStmt.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    /* Methods used in testPreparedStatementBatchUpdateNegative */
+    
+    // statements which will return a resultset are not allowed in
+    // batch Updates. Following case should throw an exception for select.
+    protected void runPreparedStmtWithResultSetBatch(
+        Connection conn, Statement stmt) throws SQLException {
+
+        ResultSet rs;
+
+        println("Negative Prepared Stat: testing select in the batch");
+        PreparedStatement pStmt = 
+            conn.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 int[] {});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ117", pStmt, new int[] {-3});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table",
+            0, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try executing a batch with regular statement intermingled.
+    protected void runPreparedStmtNonBatchStuffInBatch(
+            Connection conn, Statement stmt) throws SQLException {
+        int updateCount[] = null;
+        ResultSet rs;
+
+        try
+        {
+            // trying execute in the middle of batch
+            println("Negative Prepared Stat: " +
+                "testing execute in the middle of batch");
+            PreparedStatement pStmt = 
+                conn.prepareStatement("select * from t1 where c1=?");
+            pStmt.setInt(1, 1);
+            pStmt.addBatch();
+            pStmt.execute();
+            updateCount = pStmt.executeBatch();
+            fail("Expected executeBatch to fail");
+        } 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);
+            // do clearBatch so we can proceed
+            stmt.clearBatch();
+        }
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table", 
+            0, rs.getInt(1));
+        rs.close();
+
+        try
+        {
+            // trying executeQuery in the middle of batch
+            println("Negative Prepared Statement: " +
+                "testing executeQuery in the middle of batch");
+            PreparedStatement pStmt = 
+                conn.prepareStatement("select * from t1 where c1=?");
+            pStmt.setInt(1, 1);
+            pStmt.addBatch();
+            pStmt.executeQuery();
+            updateCount = pStmt.executeBatch();
+            fail("Expected executeBatch to fail");
+        } 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);
+            // do clearBatch so we can proceed
+            stmt.clearBatch();
+        }
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table", 
+            0, rs.getInt(1));
+        rs.close();
+
+        try
+        {
+            //  trying executeUpdate in the middle of batch
+            println("Negative Prepared Stat: " +
+                        "testing executeUpdate in the middle of batch");
+            PreparedStatement pStmt = 
+                conn.prepareStatement("select * from t1 where c1=?");
+            pStmt.setInt(1, 1);
+            pStmt.addBatch();
+            pStmt.executeUpdate();
+            updateCount = pStmt.executeBatch();
+            fail("Expected executeBatch to fail");
+        } 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);
+            // do clearBatch so we can proceed
+            stmt.clearBatch();
+        }
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be no rows in the table", 
+            0, rs.getInt(1));
+        rs.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // Below trying placements of overflow update statement in the batch
+    protected void runPreparedStmtWithErrorsBatch(
+        Connection conn, Statement stmt) throws SQLException {
+        
+        ResultSet rs;
+        PreparedStatement pStmt = null;
+
+        stmt.executeUpdate("insert into t1 values(1)");
+
+        println("Negative Prepared Stat: " +
+            "testing overflow as first set of values");
+        pStmt = conn.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 int[] {});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", pStmt, new int[] {-3});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 1 row in the table", 1, rs.getInt(1));
+        rs.close();
+
+        println("Negative Prepared Stat: " +
+            "testing overflow as nth set of values");
+        pStmt = conn.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 int[] {1});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", pStmt, new int[] {1,-3,1});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 1 row in the table", 1, rs.getInt(1));
+        rs.close();
+
+        // trying select as the last statement
+        println("Negative Prepared Stat: " +
+            "testing overflow as last set of values");
+        pStmt = conn.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 int[] {1,1});
+        else if (usingDerbyNetClient())
+            assertBatchExecuteError("XJ208", pStmt, new int[] {1,1,-3});
+
+        rs = stmt.executeQuery("select count(*) from t1");
+        rs.next();
+        assertEquals("There should be 1 row in the table", 1, rs.getInt(1));
+        rs.close();
+        pStmt.close();
+
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+    
+    // try transaction error, in this particular case 
+    // time out while getting the lock
+    protected void runTransactionErrorPreparedStmtBatch(
+            Connection conn, Statement stmt,
+            Connection conn2, Statement stmt2) throws SQLException {
+
+        int updateCount[] = null;
+        
+        try
+        {
+            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 = 
+                conn.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();
+
+            pStmt1.executeBatch();
+            updateCount = pStmt2.executeBatch();
+            fail ("Batch is expected to fail");
+        } catch (SQLException sqle) {
+            /* Check that the exception is time out while 
+             * getting the lock */
+            assertSQLState("40XL1", sqle);
+            assertTrue("Expect BatchUpdateException", 
+                (sqle instanceof BatchUpdateException));
+            updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
+            if (updateCount != null) {
+                assertEquals("first statement in the batch caused time out" +
+                    " while getting the lock, there should be no update count", 
+                    0, updateCount.length);
+            }
+        }
+
+        conn.rollback();
+        conn2.rollback();
+        stmt.executeUpdate("delete from t1");
+        conn.commit();
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/BatchUpdateTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/JDBCHarnessJavaTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/JDBCHarnessJavaTest.java?view=diff&rev=504641&r1=504640&r2=504641
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/JDBCHarnessJavaTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/JDBCHarnessJavaTest.java Wed Feb  7 10:41:28 2007
@@ -46,7 +46,6 @@
     private static final String[] JDBCAPI_TESTS_BOTH =
     {
             // from old jdbc20.runall
-            // "batchUpdate", runs in embedded only
             "connectionJdbc20",
             // "statementJdbc20", runs in embedded only
             "resultsetJdbc20",           
@@ -89,7 +88,6 @@
     private static final String[] JDBCAPI_TESTS_EMEBDDED=
     {
         // excluded from derby client in old harness
-        "batchUpdate",
         "statementJdbc20",
         "statementJdbc30",
         

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java?view=diff&rev=504641&r1=504640&r2=504641
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java Wed Feb  7 10:41:28 2007
@@ -48,6 +48,7 @@
 		TestSuite suite = new TestSuite("jdbcapi");
 
 		suite.addTest(ConcurrencyTest.suite());
+		suite.addTest(BatchUpdateTest.suite());
 		suite.addTest(HoldabilityTest.suite());
         suite.addTest(LobLengthTest.suite()); 
 		suite.addTest(ProcedureTest.suite());
@@ -59,6 +60,7 @@
         suite.addTest(ResultSetCloseTest.suite());
 		suite.addTest(BlobClob4BlobTest.suite());
 		suite.addTest(CharacterStreamsTest.suite());
+		suite.addTest(BatchUpdateTest.suite());
 		suite.addTest(StreamTest.suite());
         
         if (JDBC.vmSupportsJDBC3())



Mime
View raw message