0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.batchUpdate
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
0023:
0024: import java.sql.BatchUpdateException;
0025: import java.sql.Connection;
0026: import java.sql.CallableStatement;
0027: import java.sql.Date;
0028: import java.sql.PreparedStatement;
0029: import java.sql.ResultSet;
0030: import java.sql.ResultSetMetaData;
0031: import java.sql.Statement;
0032: import java.sql.SQLException;
0033: import java.sql.Time;
0034: import java.sql.Timestamp;
0035: import java.sql.Types;
0036:
0037: import org.apache.derby.tools.ij;
0038: import org.apache.derby.tools.JDBCDisplayUtil;
0039: import org.apache.derbyTesting.functionTests.util.BigDecimalHandler;
0040:
0041: public class batchUpdate {
0042:
0043: private static boolean HAVE_BIG_DECIMAL;
0044: private static String CLASS_NAME;
0045:
0046: //Get the class name to be used for the procedures
0047: //outparams - J2ME; outparams30 - non-J2ME
0048: static {
0049: if (BigDecimalHandler.representation != BigDecimalHandler.BIGDECIMAL_REPRESENTATION)
0050: HAVE_BIG_DECIMAL = false;
0051: else
0052: HAVE_BIG_DECIMAL = true;
0053: if (HAVE_BIG_DECIMAL)
0054: CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams30.";
0055: else
0056: CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams.";
0057: }
0058:
0059: public static void main(String[] args) {
0060: boolean passed = true;
0061: Connection conn = null;
0062: Connection conn2 = null;
0063: try {
0064: System.out.println("Test batchUpdate starting");
0065:
0066: // use the ij utility to read the property file and
0067: // make the initial connection.
0068: ij.getPropertyArg(args);
0069: conn = ij.startJBMS();
0070:
0071: conn2 = ij.startJBMS();
0072: passed = runTests(conn, conn2);
0073: } catch (SQLException se) {
0074: passed = false;
0075: dumpSQLExceptions(se);
0076: } catch (Throwable e) {
0077: System.out
0078: .println("FAIL -- unexpected exception caught in main():\n");
0079: System.out.println(e.getMessage());
0080: e.printStackTrace();
0081: passed = false;
0082: }
0083:
0084: if (passed)
0085: System.out.println("PASS");
0086:
0087: System.out.println("Test batchUpdate finished");
0088: }
0089:
0090: // the runTests method is also used by the wascache/wsc_batchUpdate.java test.
0091: public static boolean runTests(Connection conn, Connection conn2) {
0092: boolean passed = true;
0093: Statement stmt = null;
0094: Statement stmt2 = null;
0095:
0096: try {
0097: conn.setAutoCommit(false);
0098: stmt = conn.createStatement();
0099: conn2.setAutoCommit(false);
0100: stmt2 = conn2.createStatement();
0101:
0102: /* Create the table and do any other set-up */
0103: passed = passed && setUpTest(conn, stmt);
0104:
0105: // Positive tests for statement batch update
0106: passed = passed && statementBatchUpdatePositive(conn, stmt);
0107:
0108: // Negative tests for statement batch update
0109: passed = passed
0110: && statementBatchUpdateNegative(conn, stmt, conn2,
0111: stmt2);
0112:
0113: // Positive tests for callable statement batch update
0114: passed = passed && callableStatementBatchUpdate(conn, stmt);
0115:
0116: // Positive tests for prepared statement batch update
0117: passed = passed
0118: && preparedStatementBatchUpdatePositive(conn, stmt);
0119:
0120: // Negative tests for prepared statement batch update
0121: passed = passed
0122: && preparedStatementBatchUpdateNegative(conn, stmt,
0123: conn2, stmt2);
0124: } catch (SQLException se) {
0125: passed = false;
0126: dumpSQLExceptions(se);
0127: } catch (Throwable e) {
0128: System.out
0129: .println("FAIL -- unexpected exception caught in main():\n");
0130: System.out.println(e.getMessage());
0131: e.printStackTrace();
0132: passed = false;
0133: } finally {
0134: /* Test is finished - clean up after ourselves */
0135: passed = passed && cleanUp(conn, stmt);
0136: }
0137: return passed;
0138: } // end of runTests
0139:
0140: static boolean callableStatementBatchUpdate(Connection conn,
0141: Statement stmt) throws SQLException {
0142: boolean passed = true;
0143:
0144: //try callable statements
0145: passed = passed && runCallableStatementBatch(conn);
0146:
0147: //try callable statement with output parameters
0148: passed = passed
0149: && runCallableStatementWithOutputParamBatch(conn);
0150:
0151: return passed;
0152: }
0153:
0154: /**
0155: * Positive tests for statement batch update.
0156: *
0157: * @param conn The connection to use.
0158: *
0159: * @return Whether or not we were successful.
0160: *
0161: * @exception SQLException Thrown if some unexpected error happens
0162: */
0163: static boolean statementBatchUpdatePositive(Connection conn,
0164: Statement stmt) throws SQLException {
0165: boolean passed = true;
0166:
0167: //try executing a batch which nothing in it.
0168: passed = passed && runEmptyStatementBatch(conn, stmt);
0169:
0170: //try executing a batch which one statement in it.
0171: passed = passed && runSingleStatementBatch(conn, stmt);
0172:
0173: //try executing a batch with 3 different statements in it.
0174: passed = passed && runMultipleStatementsBatch(conn, stmt);
0175:
0176: //try executing a batch with 1000 statements in it.
0177: passed = passed && run1000StatementsBatch(conn, stmt);
0178:
0179: //try batch with autocommit true
0180: passed = passed && runAutoCommitTrueBatch(conn, stmt);
0181:
0182: //try clear batch
0183: passed = passed && runCombinationsOfClearBatch(conn, stmt);
0184:
0185: // confirm associated parameters run ok with batches
0186: passed = passed && checkAssociatedParams(conn, stmt);
0187:
0188: conn.commit();
0189:
0190: return passed;
0191: }
0192:
0193: /**
0194: * Negative tests for statement batch update.
0195: *
0196: * @param conn The connection to use.
0197: *
0198: * @return Whether or not we were successful.
0199: *
0200: * @exception SQLException Thrown if some unexpected error happens
0201: */
0202: static boolean statementBatchUpdateNegative(Connection conn,
0203: Statement stmt, Connection conn2, Statement stmt2)
0204: throws SQLException {
0205: boolean passed = true;
0206:
0207: //statements which will return a resultset are not allowed in batch update
0208: //the following case should throw an exception for select. Below trying
0209: //various placements of select statement in the batch, ie as 1st stmt,
0210: //nth stat and last stmt
0211: passed = passed && runStatementWithResultSetBatch(conn, stmt);
0212:
0213: //try executing a batch with regular statement intermingled.
0214: passed = passed && runStatementNonBatchStuffInBatch(conn, stmt);
0215:
0216: //Below trying various placements of overflow update statement in the batch, ie
0217: //as 1st stmt, nth stat and last stmt
0218: passed = passed && runStatementWithErrorsBatch(conn, stmt);
0219:
0220: //try transaction error, in this particular case time out while getting the lock
0221: passed = passed
0222: && runTransactionErrorBatch(conn, stmt, conn2, stmt2);
0223:
0224: return passed;
0225: }
0226:
0227: /**
0228: * Positive tests for prepared statement batch update.
0229: *
0230: * @param conn The connection to use.
0231: *
0232: * @return Whether or not we were successful.
0233: *
0234: * @exception SQLException Thrown if some unexpected error happens
0235: */
0236: static boolean preparedStatementBatchUpdatePositive(
0237: Connection conn, Statement stmt) throws SQLException {
0238: boolean passed = true;
0239:
0240: //try executing a batch which nothing in it.
0241: passed = passed && runEmptyValueSetPreparedBatch(conn, stmt);
0242:
0243: //try executing a batch with no parameters.
0244: passed = passed && runNoParametersPreparedBatch(conn, stmt);
0245:
0246: //try executing a batch which one parameter set in it.
0247: passed = passed && runSingleValueSetPreparedBatch(conn, stmt);
0248:
0249: //try executing a batch with 3 parameter sets in it.
0250: passed = passed && runMultipleValueSetPreparedBatch(conn, stmt);
0251:
0252: //try executing a batch with 2 parameter sets in it and they are set to null.
0253: passed = passed
0254: && runMultipleValueSetNullPreparedBatch(conn, stmt);
0255:
0256: //try executing a batch with 1000 statements in it.
0257: passed = passed && run1000ValueSetPreparedBatch(conn, stmt);
0258:
0259: //try executing batches with various rollback and commit combinations.
0260: passed = passed
0261: && runPreparedStatRollbackAndCommitCombinations(conn,
0262: stmt);
0263:
0264: //try prepared statement batch with autocommit true
0265: passed = passed
0266: && runAutoCommitTruePreparedStatBatch(conn, stmt);
0267:
0268: //try clear batch
0269: passed = passed
0270: && runCombinationsOfClearPreparedStatBatch(conn, stmt);
0271:
0272: return passed;
0273: }
0274:
0275: /**
0276: * Negative tests for prepared statement batch update.
0277: *
0278: * @param conn The connection to use.
0279: *
0280: * @return Whether or not we were successful.
0281: *
0282: * @exception SQLException Thrown if some unexpected error happens
0283: */
0284: static boolean preparedStatementBatchUpdateNegative(
0285: Connection conn, Statement stmt, Connection conn2,
0286: Statement stmt2) throws SQLException {
0287: boolean passed = true;
0288:
0289: //statements which will return a resultset are not allowed in batch update
0290: //the following case should throw an exception for select.
0291: passed = passed
0292: && runPreparedStmtWithResultSetBatch(conn, stmt);
0293:
0294: //try executing a batch with regular statement intermingled.
0295: passed = passed
0296: && runPreparedStmtNonBatchStuffInBatch(conn, stmt);
0297:
0298: //Below trying various placements of overflow update statement in the batch
0299: passed = passed && runPreparedStmtWithErrorsBatch(conn, stmt);
0300:
0301: //try transaction error, in this particular case time out while getting the lock
0302: passed = passed
0303: && runTransactionErrorPreparedStmtBatch(conn, stmt,
0304: conn2, stmt2);
0305:
0306: return passed;
0307: }
0308:
0309: static public void dumpSQLExceptions(SQLException se) {
0310: System.out.println("FAIL -- unexpected exception");
0311: while (se != null) {
0312: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
0313: se.printStackTrace();
0314: se = se.getNextException();
0315: }
0316: }
0317:
0318: /**
0319: * Check to make sure that the given SQLException is an exception
0320: * with the expected sqlstate.
0321: *
0322: * @param e The SQLException to check
0323: * @param SQLState The sqlstate to look for
0324: *
0325: * @return true means the exception is the expected one
0326: */
0327:
0328: private static boolean checkException(SQLException e,
0329: String SQLState) {
0330: String state;
0331: String nextState;
0332: SQLException next;
0333: boolean passed = true;
0334:
0335: state = e.getSQLState();
0336:
0337: if (!SQLState.equals(state)) {
0338: System.out.println("FAIL -- unexpected exception " + e
0339: + "sqlstate: " + state + SQLState);
0340: passed = false;
0341: }
0342:
0343: return passed;
0344: }
0345:
0346: /**
0347: * Clean up after ourselves when testing is done.
0348: *
0349: * @param conn The Connection
0350: * @param s A Statement on the Connection
0351: *
0352: * @return true if it succeeds, false if it doesn't
0353: *
0354: * @exception SQLException Thrown if some unexpected error happens
0355: */
0356:
0357: static boolean cleanUp(Connection conn, Statement s) {
0358: boolean passed = true;
0359: try {
0360: /* Drop the table we created */
0361: if (s != null) {
0362: s.execute("drop table t1");
0363: s.execute("drop table datetab");
0364: s.execute("drop table timetab");
0365: s.execute("drop table timestamptab");
0366: s.execute("drop table usertypetab");
0367: s.execute("drop procedure Integ");
0368: }
0369:
0370: /* Close the connection */
0371: if (conn != null) {
0372: conn.rollback();
0373: conn.close();
0374: }
0375: } catch (Throwable e) {
0376: System.out
0377: .println("FAIL -- unexpected exception caught in cleanup()");
0378: JDBCDisplayUtil.ShowException(System.out, e);
0379: passed = false;
0380: }
0381:
0382: return passed;
0383: }
0384:
0385: //Below trying placements of overflow update statement in the batch
0386: static boolean runPreparedStmtWithErrorsBatch(Connection conn,
0387: Statement stmt) throws SQLException {
0388: boolean passed = true;
0389: int updateCount[] = null;
0390: ResultSet rs;
0391: PreparedStatement pStmt = null;
0392:
0393: stmt.executeUpdate("insert into t1 values(1)");
0394:
0395: try {
0396: System.out
0397: .println("Negative Prepared Stat: testing overflow as first set of values");
0398: pStmt = conn.prepareStatement("update t1 set c1=(? + 1)");
0399: pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
0400: pStmt.addBatch();
0401: updateCount = pStmt.executeBatch();
0402: passed = false;
0403: } catch (SQLException sqle) {
0404: /* Check to be sure the exception is the one we expect */
0405: passed = passed && checkException(sqle, "22003");
0406: if (sqle instanceof BatchUpdateException) {
0407: updateCount = ((BatchUpdateException) sqle)
0408: .getUpdateCounts();
0409: if (updateCount != null) {
0410: if (updateCount.length != 0) {
0411: System.out
0412: .println("ERROR: Overflow is first statement in the batch, so there shouldn't have been any update count");
0413: passed = false;
0414: }
0415: }
0416: }
0417: }
0418:
0419: rs = stmt.executeQuery("select count(*) from t1");
0420: rs.next();
0421: if (rs.getInt(1) != 1) {
0422: System.out
0423: .println("ERROR: There should been 1 row in the table, but found "
0424: + rs.getInt(1) + " rows");
0425: passed = false;
0426: }
0427: rs.close();
0428:
0429: try {
0430: System.out
0431: .println("Negative Prepared Stat: testing overflow as nth set of values");
0432: pStmt = conn.prepareStatement("update t1 set c1=(? + 1)");
0433: pStmt.setInt(1, 1);
0434: pStmt.addBatch();
0435: pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
0436: pStmt.addBatch();
0437: pStmt.setInt(1, 1);
0438: pStmt.addBatch();
0439: updateCount = pStmt.executeBatch();
0440: passed = false;
0441: } catch (SQLException sqle) {
0442: /* Check to be sure the exception is the one we expect */
0443: passed = passed && checkException(sqle, "22003");
0444: if (sqle instanceof BatchUpdateException) {
0445: updateCount = ((BatchUpdateException) sqle)
0446: .getUpdateCounts();
0447: if (updateCount.length != 1) {
0448: System.out
0449: .println("ERROR: Overflow is second statement in the batch, so there should have been only 1 update count");
0450: passed = false;
0451: }
0452: for (int i = 0; i < updateCount.length; i++) {
0453: if (updateCount[i] != 1) {
0454: System.out
0455: .println("ERROR: update count for stat "
0456: + i
0457: + "should have been 1 but it is "
0458: + updateCount[i]);
0459: passed = false;
0460: }
0461: }
0462: }
0463: }
0464:
0465: rs = stmt.executeQuery("select count(*) from t1");
0466: rs.next();
0467: if (rs.getInt(1) != 1) {
0468: System.out
0469: .println("There should been 1 row in the table, but found "
0470: + rs.getInt(1) + " rows");
0471: passed = false;
0472: }
0473: rs.close();
0474:
0475: try {
0476: //trying select as the last statement
0477: System.out
0478: .println("Negative Prepared Stat: testing overflow as last set of values");
0479: pStmt = conn.prepareStatement("update t1 set c1=(? + 1)");
0480: pStmt.setInt(1, 1);
0481: pStmt.addBatch();
0482: pStmt.setInt(1, 1);
0483: pStmt.addBatch();
0484: pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
0485: pStmt.addBatch();
0486: updateCount = pStmt.executeBatch();
0487: passed = false;
0488: } catch (SQLException sqle) {
0489: /* Check to be sure the exception is the one we expect */
0490: passed = passed && checkException(sqle, "22003");
0491: if (sqle instanceof BatchUpdateException) {
0492: updateCount = ((BatchUpdateException) sqle)
0493: .getUpdateCounts();
0494: if (updateCount.length != 2) {
0495: System.out
0496: .println("ERROR: Overflow is last statement in the batch, so there should have been only 2 update count");
0497: passed = false;
0498: }
0499: for (int i = 0; i < updateCount.length; i++) {
0500: if (updateCount[i] != 1) {
0501: System.out
0502: .println("ERROR: update count for stat "
0503: + i
0504: + "should have been 1 but it is "
0505: + updateCount[i]);
0506: passed = false;
0507: }
0508: }
0509: }
0510: }
0511:
0512: rs = stmt.executeQuery("select count(*) from t1");
0513: rs.next();
0514: if (rs.getInt(1) != 1) {
0515: System.out
0516: .println("There should been 1 row in the table, but found "
0517: + rs.getInt(1) + " rows");
0518: passed = false;
0519: }
0520: rs.close();
0521: pStmt.close();
0522:
0523: stmt.executeUpdate("delete from t1");
0524: conn.commit();
0525: return (passed);
0526: }
0527:
0528: //Below trying various placements of overflow update statement in the batch, ie
0529: //as 1st stmt, nth stat and last stmt
0530: static boolean runStatementWithErrorsBatch(Connection conn,
0531: Statement stmt) throws SQLException {
0532: boolean passed = true;
0533: int updateCount[] = null;
0534: ResultSet rs;
0535:
0536: stmt.executeUpdate("insert into t1 values(1)");
0537:
0538: try {
0539: //trying select as the first statement
0540: System.out
0541: .println("Negative Statement: statement testing overflow error as first stat in the batch");
0542: stmt.addBatch("update t1 set c1=2147483647 + 1");
0543: stmt.addBatch("insert into t1 values(1)");
0544: updateCount = stmt.executeBatch();
0545: passed = false;
0546: } catch (SQLException sqle) {
0547: /* Check to be sure the exception is the one we expect */
0548: passed = passed && checkException(sqle, "22003");
0549: if (sqle instanceof BatchUpdateException) {
0550: updateCount = ((BatchUpdateException) sqle)
0551: .getUpdateCounts();
0552: if (updateCount != null) {
0553: if (updateCount.length != 0) {
0554: System.out
0555: .println("ERROR: Overflow is first statement in the batch, so there shouldn't have been any update count");
0556: passed = false;
0557: }
0558: }
0559: }
0560: }
0561:
0562: rs = stmt.executeQuery("select count(*) from t1");
0563: rs.next();
0564: if (rs.getInt(1) != 1) {
0565: System.out
0566: .println("ERROR: There should been 1 row in the table, but found "
0567: + rs.getInt(1) + " rows");
0568: passed = false;
0569: }
0570: rs.close();
0571:
0572: try {
0573: //trying select as the nth statement
0574: System.out
0575: .println("Negative Statement: statement testing overflow error as nth stat in the batch");
0576: stmt.addBatch("insert into t1 values(1)");
0577: stmt.addBatch("update t1 set c1=2147483647 + 1");
0578: stmt.addBatch("insert into t1 values(1)");
0579: updateCount = stmt.executeBatch();
0580: passed = false;
0581: } catch (SQLException sqle) {
0582: /* Check to be sure the exception is the one we expect */
0583: passed = passed && checkException(sqle, "22003");
0584: if (sqle instanceof BatchUpdateException) {
0585: updateCount = ((BatchUpdateException) sqle)
0586: .getUpdateCounts();
0587: if (updateCount.length != 1) {
0588: System.out
0589: .println("ERROR: Update is second statement in the batch, so there should have been only 1 update count");
0590: passed = false;
0591: }
0592: for (int i = 0; i < updateCount.length; i++) {
0593: if (updateCount[i] != 1) {
0594: System.out
0595: .println("ERROR: update count for stat "
0596: + i
0597: + "should have been 1 but it is "
0598: + updateCount[i]);
0599: passed = false;
0600: }
0601: }
0602: }
0603: }
0604:
0605: rs = stmt.executeQuery("select count(*) from t1");
0606: rs.next();
0607: if (rs.getInt(1) != 2) {
0608: System.out
0609: .println("There should been 2 row in the table, but found "
0610: + rs.getInt(1) + " rows");
0611: passed = false;
0612: }
0613: rs.close();
0614:
0615: try {
0616: //trying select as the last statement
0617: System.out
0618: .println("Negative Statement: statement testing overflow error as last stat in the batch");
0619: stmt.addBatch("insert into t1 values(1)");
0620: stmt.addBatch("insert into t1 values(1)");
0621: stmt.addBatch("update t1 set c1=2147483647 + 1");
0622: updateCount = stmt.executeBatch();
0623: passed = false;
0624: } catch (SQLException sqle) {
0625: /* Check to be sure the exception is the one we expect */
0626: passed = passed && checkException(sqle, "22003");
0627: if (sqle instanceof BatchUpdateException) {
0628: updateCount = ((BatchUpdateException) sqle)
0629: .getUpdateCounts();
0630: if (updateCount.length != 2) {
0631: System.out
0632: .println("ERROR: Update is last statement in the batch, so there should have been only 2 update count");
0633: passed = false;
0634: }
0635: for (int i = 0; i < updateCount.length; i++) {
0636: if (updateCount[i] != 1) {
0637: System.out
0638: .println("ERROR: update count for stat "
0639: + i
0640: + "should have been 1 but it is "
0641: + updateCount[i]);
0642: passed = false;
0643: }
0644: }
0645: }
0646: }
0647:
0648: rs = stmt.executeQuery("select count(*) from t1");
0649: rs.next();
0650: if (rs.getInt(1) != 4) {
0651: System.out
0652: .println("There should been 4 rows in the table, but found "
0653: + rs.getInt(1) + " rows");
0654: passed = false;
0655: }
0656: rs.close();
0657:
0658: stmt.executeUpdate("delete from t1");
0659: conn.commit();
0660: return (passed);
0661: }
0662:
0663: //try transaction error, in this particular case time out while getting the lock
0664: static boolean runTransactionErrorPreparedStmtBatch(
0665: Connection conn, Statement stmt, Connection conn2,
0666: Statement stmt2) throws SQLException {
0667: boolean passed = true;
0668: int updateCount[] = null;
0669: ResultSet rs;
0670:
0671: try {
0672: System.out
0673: .println("Negative Prepared Stat: testing transaction error, time out while getting the lock");
0674:
0675: stmt.execute("insert into t1 values(1)");
0676: stmt2.execute("insert into t1 values(2)");
0677:
0678: PreparedStatement pStmt1 = conn
0679: .prepareStatement("update t1 set c1=3 where c1=?");
0680: pStmt1.setInt(1, 2);
0681: pStmt1.addBatch();
0682:
0683: PreparedStatement pStmt2 = conn
0684: .prepareStatement("update t1 set c1=4 where c1=?");
0685: pStmt2.setInt(1, 1);
0686: pStmt2.addBatch();
0687:
0688: pStmt1.executeBatch();
0689: pStmt2.executeBatch();
0690: } catch (SQLException sqle) {
0691: /* Check to be sure the exception is time out while getting the lock related */
0692: passed = passed && checkException(sqle, "40XL1");
0693: if (sqle instanceof BatchUpdateException) {
0694: updateCount = ((BatchUpdateException) sqle)
0695: .getUpdateCounts();
0696: if (updateCount != null) {
0697: if (updateCount.length != 0) {
0698: System.out
0699: .println("ERROR: first statement in the batch caused time out while getting the lock, so there shouldn't have been any update count");
0700: passed = false;
0701: }
0702: }
0703: }
0704: }
0705:
0706: conn.rollback();
0707: conn2.rollback();
0708: stmt.executeUpdate("delete from t1");
0709: conn.commit();
0710: return passed;
0711: }
0712:
0713: //try transaction error, in this particular case time out while getting the lock
0714: static boolean runTransactionErrorBatch(Connection conn,
0715: Statement stmt, Connection conn2, Statement stmt2)
0716: throws SQLException {
0717: boolean passed = true;
0718: int updateCount[] = null;
0719: ResultSet rs;
0720:
0721: try {
0722: System.out
0723: .println("Negative Statement: statement testing time out while getting the lock in the batch");
0724:
0725: stmt.execute("insert into t1 values(1)");
0726: stmt2.execute("insert into t1 values(2)");
0727:
0728: stmt.addBatch("update t1 set c1=3 where c1=2");
0729: stmt2.addBatch("update t1 set c1=4 where c1=1");
0730:
0731: stmt.executeBatch();
0732: updateCount = stmt2.executeBatch();
0733: } catch (SQLException sqle) {
0734: /* Check to be sure the exception is time out while getting the lock related */
0735: passed = passed && checkException(sqle, "40XL1");
0736: if (sqle instanceof BatchUpdateException) {
0737: updateCount = ((BatchUpdateException) sqle)
0738: .getUpdateCounts();
0739: if (updateCount != null) {
0740: if (updateCount.length != 0) {
0741: System.out
0742: .println("ERROR: first statement in the batch caused time out while getting the lock, so there shouldn't have been any update count");
0743: passed = false;
0744: }
0745: }
0746: }
0747: }
0748:
0749: conn.rollback();
0750: conn2.rollback();
0751: stmt.clearBatch();
0752: stmt2.clearBatch();
0753: stmt.executeUpdate("delete from t1");
0754: conn.commit();
0755: return passed;
0756: }
0757:
0758: //statements which will return a resultset are not allowed in batch update
0759: //the following case should throw an exception for select.
0760: static boolean runPreparedStmtWithResultSetBatch(Connection conn,
0761: Statement stmt) throws SQLException {
0762: boolean passed = true;
0763: int updateCount[] = null;
0764: ResultSet rs;
0765:
0766: try {
0767: //trying select as the first statement
0768: System.out
0769: .println("Negative Prepared Stat: testing select in the batch");
0770: PreparedStatement pStmt = conn
0771: .prepareStatement("select * from t1 where c1=?");
0772: pStmt.setInt(1, 1);
0773: pStmt.addBatch();
0774: updateCount = pStmt.executeBatch();
0775: passed = false;
0776: } catch (SQLException sqle) {
0777: /* Check to be sure the exception is the ResultSetReturnNotAllowed */
0778: passed = passed && checkException(sqle, "X0Y79");
0779: if (sqle instanceof BatchUpdateException) {
0780: updateCount = ((BatchUpdateException) sqle)
0781: .getUpdateCounts();
0782: if (updateCount != null) {
0783: if (updateCount.length != 0) {
0784: System.out
0785: .println("ERROR: Select is first statement in the batch, so there shouldn't have been any update count");
0786: passed = false;
0787: }
0788: }
0789: }
0790: }
0791:
0792: rs = stmt.executeQuery("select count(*) from t1");
0793: rs.next();
0794: if (rs.getInt(1) != 0) {
0795: System.out
0796: .println("ERROR: There should been no rows in the table, but found "
0797: + rs.getInt(1) + " rows");
0798: passed = false;
0799: }
0800: rs.close();
0801:
0802: stmt.executeUpdate("delete from t1");
0803: conn.commit();
0804: return passed;
0805: }
0806:
0807: //try executing a batch with regular statement intermingled.
0808: static boolean runPreparedStmtNonBatchStuffInBatch(Connection conn,
0809: Statement stmt) throws SQLException {
0810: boolean passed = true;
0811: int updateCount[] = null;
0812: ResultSet rs;
0813:
0814: try {
0815: //trying execute in the middle of batch
0816: System.out
0817: .println("Negative Prepared Stat: testing execute in the middle of batch");
0818: PreparedStatement pStmt = conn
0819: .prepareStatement("select * from t1 where c1=?");
0820: pStmt.setInt(1, 1);
0821: pStmt.addBatch();
0822: pStmt.execute();
0823: updateCount = pStmt.executeBatch();
0824: passed = false;
0825: } catch (SQLException sqle) {
0826: /* Check to be sure the exception is the MIDDLE_OF_BATCH */
0827: passed = passed && checkException(sqle, "XJ068");
0828: // do clearBatch so we can proceed
0829: if (checkException(sqle, "XJ068"))
0830: stmt.clearBatch();
0831: }
0832:
0833: rs = stmt.executeQuery("select count(*) from t1");
0834: rs.next();
0835: if (rs.getInt(1) != 0) {
0836: System.out
0837: .println("ERROR: There should been no rows in the table, but found "
0838: + rs.getInt(1) + " rows");
0839: passed = false;
0840: }
0841: rs.close();
0842:
0843: try {
0844: //trying executeQuery in the middle of batch
0845: System.out
0846: .println("Negative Prepared Stat: testing executeQuery in the middle of batch");
0847: PreparedStatement pStmt = conn
0848: .prepareStatement("select * from t1 where c1=?");
0849: pStmt.setInt(1, 1);
0850: pStmt.addBatch();
0851: pStmt.executeQuery();
0852: updateCount = pStmt.executeBatch();
0853: passed = false;
0854: } catch (SQLException sqle) {
0855: /* Check to be sure the exception is the MIDDLE_OF_BATCH */
0856: passed = passed && checkException(sqle, "XJ068");
0857: // do clearBatch so we can proceed
0858: if (checkException(sqle, "XJ068"))
0859: stmt.clearBatch();
0860: }
0861:
0862: rs = stmt.executeQuery("select count(*) from t1");
0863: rs.next();
0864: if (rs.getInt(1) != 0) {
0865: System.out
0866: .println("ERROR: There should been no rows in the table, but found "
0867: + rs.getInt(1) + " rows");
0868: passed = false;
0869: }
0870: rs.close();
0871:
0872: try {
0873: //trying executeUpdate in the middle of batch
0874: System.out
0875: .println("Negative Prepared Stat: testing executeUpdate in the middle of batch");
0876: PreparedStatement pStmt = conn
0877: .prepareStatement("select * from t1 where c1=?");
0878: pStmt.setInt(1, 1);
0879: pStmt.addBatch();
0880: pStmt.executeUpdate();
0881: updateCount = pStmt.executeBatch();
0882: passed = false;
0883: } catch (SQLException sqle) {
0884: /* Check to be sure the exception is the MIDDLE_OF_BATCH */
0885: passed = passed && checkException(sqle, "XJ068");
0886: // do clearBatch so we can proceed
0887: if (checkException(sqle, "XJ068"))
0888: stmt.clearBatch();
0889: }
0890:
0891: rs = stmt.executeQuery("select count(*) from t1");
0892: rs.next();
0893: if (rs.getInt(1) != 0) {
0894: System.out
0895: .println("ERROR: There should been no rows in the table, but found "
0896: + rs.getInt(1) + " rows");
0897: passed = false;
0898: }
0899: rs.close();
0900:
0901: stmt.executeUpdate("delete from t1");
0902: conn.commit();
0903: return passed;
0904: }
0905:
0906: //statements which will return a resultset are not allowed in batch update
0907: //the following case should throw an exception for select. Below trying
0908: //various placements of select statement in the batch, ie as 1st stmt,
0909: //nth stat and last stmt
0910: static boolean runStatementWithResultSetBatch(Connection conn,
0911: Statement stmt) throws SQLException {
0912: boolean passed = true;
0913: int updateCount[] = null;
0914: ResultSet rs;
0915:
0916: try {
0917: //trying select as the first statement
0918: System.out
0919: .println("Negative Statement: statement testing select as first stat in the batch");
0920: stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
0921: stmt.addBatch("insert into t1 values(1)");
0922: updateCount = stmt.executeBatch();
0923: passed = false;
0924: } catch (SQLException sqle) {
0925: /* Check to be sure the exception is the ResultSetReturnNotAllowed */
0926: passed = passed && checkException(sqle, "X0Y79");
0927: if (sqle instanceof BatchUpdateException) {
0928: updateCount = ((BatchUpdateException) sqle)
0929: .getUpdateCounts();
0930: if (updateCount != null) {
0931: if (updateCount.length != 0) {
0932: System.out
0933: .println("ERROR: Select is first statement in the batch, so there shouldn't have been any update count");
0934: passed = false;
0935: }
0936: }
0937: }
0938: }
0939:
0940: rs = stmt.executeQuery("select count(*) from t1");
0941: rs.next();
0942: if (rs.getInt(1) != 0) {
0943: System.out
0944: .println("ERROR: There should been no rows in the table, but found "
0945: + rs.getInt(1) + " rows");
0946: passed = false;
0947: }
0948: rs.close();
0949:
0950: try {
0951: //trying select as the nth statement
0952: System.out
0953: .println("Negative Statement: statement testing select as nth stat in the batch");
0954: stmt.addBatch("insert into t1 values(1)");
0955: stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
0956: stmt.addBatch("insert into t1 values(1)");
0957: updateCount = stmt.executeBatch();
0958: passed = false;
0959: } catch (SQLException sqle) {
0960: /* Check to be sure the exception is the ResultSetReturnNotAllowed */
0961: passed = passed && checkException(sqle, "X0Y79");
0962: if (sqle instanceof BatchUpdateException) {
0963: updateCount = ((BatchUpdateException) sqle)
0964: .getUpdateCounts();
0965: if (updateCount.length != 1) {
0966: System.out
0967: .println("ERROR: Select is second statement in the batch, so there should have been only 1 update count");
0968: passed = false;
0969: }
0970: for (int i = 0; i < updateCount.length; i++) {
0971: if (updateCount[i] != 1) {
0972: System.out
0973: .println("ERROR: update count for stat "
0974: + i
0975: + "should have been 1 but it is "
0976: + updateCount[i]);
0977: passed = false;
0978: }
0979: }
0980: }
0981: }
0982:
0983: rs = stmt.executeQuery("select count(*) from t1");
0984: rs.next();
0985: if (rs.getInt(1) != 1) {
0986: System.out
0987: .println("There should been 1 row in the table, but found "
0988: + rs.getInt(1) + " rows");
0989: passed = false;
0990: }
0991: rs.close();
0992:
0993: try {
0994: //trying select as the last statement
0995: System.out
0996: .println("Negative Statement: statement testing select as last stat in the batch");
0997: stmt.addBatch("insert into t1 values(1)");
0998: stmt.addBatch("insert into t1 values(1)");
0999: stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
1000: updateCount = stmt.executeBatch();
1001: passed = false;
1002: } catch (SQLException sqle) {
1003: /* Check to be sure the exception is the ResultSetReturnNotAllowed */
1004: passed = passed && checkException(sqle, "X0Y79");
1005: if (sqle instanceof BatchUpdateException) {
1006: updateCount = ((BatchUpdateException) sqle)
1007: .getUpdateCounts();
1008: if (updateCount.length != 2) {
1009: System.out
1010: .println("ERROR: Select is last statement in the batch, so there should have been only 2 update count");
1011: passed = false;
1012: }
1013: for (int i = 0; i < updateCount.length; i++) {
1014: if (updateCount[i] != 1) {
1015: System.out
1016: .println("ERROR: update count for stat "
1017: + i
1018: + "should have been 1 but it is "
1019: + updateCount[i]);
1020: passed = false;
1021: }
1022: }
1023: }
1024: }
1025:
1026: rs = stmt.executeQuery("select count(*) from t1");
1027: rs.next();
1028: if (rs.getInt(1) != 3) {
1029: System.out
1030: .println("There should been 3 row in the table, but found "
1031: + rs.getInt(1) + " rows");
1032: passed = false;
1033: }
1034: rs.close();
1035:
1036: conn.rollback();
1037:
1038: rs = stmt.executeQuery("select count(*) from t1");
1039: rs.next();
1040: if (rs.getInt(1) != 0) {
1041: System.out
1042: .println("There should been no rows in the table, but found "
1043: + rs.getInt(1) + " rows");
1044: passed = false;
1045: }
1046: rs.close();
1047:
1048: stmt.executeUpdate("delete from t1");
1049: conn.commit();
1050: return passed;
1051: }
1052:
1053: //try executing a batch with regular statement intermingled.
1054: static boolean runStatementNonBatchStuffInBatch(Connection conn,
1055: Statement stmt) throws SQLException {
1056: boolean passed = true;
1057: int updateCount[] = null;
1058: ResultSet rs;
1059:
1060: try {
1061: //trying execute after addBatch
1062: System.out
1063: .println("Negative Statement: statement testing execute in the middle of batch");
1064: stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
1065: stmt.execute("insert into t1 values(1)");
1066: stmt.addBatch("insert into t1 values(1)");
1067: updateCount = stmt.executeBatch();
1068: passed = false;
1069: } catch (SQLException sqle) {
1070: /* Check to be sure the exception is the MIDDLE_OF_BATCH */
1071: passed = passed && checkException(sqle, "XJ068");
1072: // do clearBatch so we can proceed
1073: if (checkException(sqle, "XJ068"))
1074: stmt.clearBatch();
1075: }
1076:
1077: rs = stmt.executeQuery("select count(*) from t1");
1078: rs.next();
1079: if (rs.getInt(1) != 0) {
1080: System.out
1081: .println("ERROR: There should been no rows in the table, but found "
1082: + rs.getInt(1) + " rows");
1083: passed = false;
1084: }
1085: rs.close();
1086:
1087: try {
1088: //trying executeQuery after addBatch
1089: System.out
1090: .println("Negative Statement: statement testing executeQuery in the middle of batch");
1091: stmt.addBatch("insert into t1 values(1)");
1092: stmt.executeQuery("SELECT * FROM SYS.SYSTABLES");
1093: updateCount = stmt.executeBatch();
1094: passed = false;
1095: } catch (SQLException sqle) {
1096: /* Check to be sure the exception is the MIDDLE_OF_BATCH */
1097: passed = passed && checkException(sqle, "XJ068");
1098: // do clearBatch so we can proceed
1099: if (checkException(sqle, "XJ068"))
1100: stmt.clearBatch();
1101: }
1102:
1103: rs = stmt.executeQuery("select count(*) from t1");
1104: rs.next();
1105: if (rs.getInt(1) != 0) {
1106: System.out
1107: .println("There should been no rows in the table, but found "
1108: + rs.getInt(1) + " rows");
1109: passed = false;
1110: }
1111: rs.close();
1112:
1113: try {
1114: //trying executeUpdate after addBatch
1115: System.out
1116: .println("Negative Statement: statement testing executeUpdate in the middle of batch");
1117: stmt.addBatch("insert into t1 values(1)");
1118: stmt.executeUpdate("insert into t1 values(1)");
1119: stmt.addBatch("insert into t1 values(1)");
1120: stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
1121: updateCount = stmt.executeBatch();
1122: passed = false;
1123: } catch (SQLException sqle) {
1124: /* Check to be sure the exception is the MIDDLE_OF_BATCH */
1125: passed = passed && checkException(sqle, "XJ068");
1126: // do clearBatch so we can proceed
1127: if (checkException(sqle, "XJ068"))
1128: stmt.clearBatch();
1129: }
1130:
1131: rs = stmt.executeQuery("select count(*) from t1");
1132: rs.next();
1133: if (rs.getInt(1) != 0) {
1134: System.out
1135: .println("There should been no rows in the table, but found "
1136: + rs.getInt(1) + " rows");
1137: passed = false;
1138: }
1139: rs.close();
1140:
1141: conn.rollback();
1142:
1143: rs = stmt.executeQuery("select count(*) from t1");
1144: rs.next();
1145: if (rs.getInt(1) != 0) {
1146: System.out
1147: .println("There should been no rows in the table, but found "
1148: + rs.getInt(1) + " rows");
1149: passed = false;
1150: }
1151: rs.close();
1152:
1153: stmt.executeUpdate("delete from t1");
1154: conn.commit();
1155: return passed;
1156: }
1157:
1158: //try executing batches with various rollback and commit combinations.
1159: static boolean runPreparedStatRollbackAndCommitCombinations(
1160: Connection conn, Statement stmt) throws SQLException {
1161: boolean passed = true;
1162: int updateCount[];
1163: ResultSet rs;
1164:
1165: System.out
1166: .println("Positive Prepared Stat: batch, rollback, batch and commit combinations");
1167: PreparedStatement pStmt = conn
1168: .prepareStatement("insert into t1 values(?)");
1169: pStmt.setInt(1, 1);
1170: pStmt.addBatch();
1171: pStmt.setInt(1, 1);
1172: pStmt.addBatch();
1173: updateCount = pStmt.executeBatch();
1174:
1175: if (updateCount.length != 2) {
1176: System.out
1177: .println("ERROR: there were 2 statements in the batch");
1178: passed = false;
1179: }
1180:
1181: for (int i = 0; i < updateCount.length; i++) {
1182: if (updateCount[i] != 1) {
1183: System.out.println("ERROR: update count for stat " + i
1184: + "should have been 1 but it is "
1185: + updateCount[i]);
1186: passed = false;
1187: }
1188: }
1189:
1190: conn.rollback();
1191:
1192: rs = stmt.executeQuery("select count(*) from t1");
1193: rs.next();
1194: if (rs.getInt(1) != 0) {
1195: System.out.println("ERROR: There should have been 0 rows");
1196: passed = false;
1197: }
1198: rs.close();
1199:
1200: pStmt.setInt(1, 1);
1201: pStmt.addBatch();
1202: pStmt.setInt(1, 1);
1203: pStmt.addBatch();
1204: updateCount = pStmt.executeBatch();
1205:
1206: if (updateCount.length != 2) {
1207: System.out
1208: .println("ERROR: there were 2 statements in the batch");
1209: passed = false;
1210: }
1211:
1212: for (int i = 0; i < updateCount.length; i++) {
1213: if (updateCount[i] != 1) {
1214: System.out.println("ERROR: update count for stat " + i
1215: + "should have been 1 but it is "
1216: + updateCount[i]);
1217: passed = false;
1218: }
1219: }
1220:
1221: conn.commit();
1222:
1223: rs = stmt.executeQuery("select count(*) from t1");
1224: rs.next();
1225: if (rs.getInt(1) != 2) {
1226: System.out.println("ERROR: There should have been 2 rows");
1227: passed = false;
1228: }
1229: rs.close();
1230:
1231: //try batch and commit
1232: System.out
1233: .println("Positive Prepared Stat: batch and commit combinations");
1234: pStmt.setInt(1, 1);
1235: pStmt.addBatch();
1236: pStmt.setInt(1, 1);
1237: pStmt.addBatch();
1238: updateCount = pStmt.executeBatch();
1239:
1240: if (updateCount.length != 2) {
1241: System.out
1242: .println("ERROR: there were 2 statements in the batch");
1243: passed = false;
1244: }
1245:
1246: for (int i = 0; i < updateCount.length; i++) {
1247: if (updateCount[i] != 1) {
1248: System.out.println("ERROR: update count for stat " + i
1249: + "should have been 1 but it is "
1250: + updateCount[i]);
1251: passed = false;
1252: }
1253: }
1254:
1255: conn.commit();
1256:
1257: rs = stmt.executeQuery("select count(*) from t1");
1258: rs.next();
1259: if (rs.getInt(1) != 4) {
1260: System.out.println("ERROR: There should have been 4 rows");
1261: passed = false;
1262: }
1263: rs.close();
1264:
1265: //try batch, batch and rollback
1266: System.out
1267: .println("Positive Prepared Stat: batch, batch and rollback combinations");
1268: pStmt.setInt(1, 1);
1269: pStmt.addBatch();
1270: pStmt.setInt(1, 1);
1271: pStmt.addBatch();
1272: updateCount = pStmt.executeBatch();
1273:
1274: if (updateCount.length != 2) {
1275: System.out
1276: .println("ERROR: there were 2 statements in the batch");
1277: passed = false;
1278: }
1279:
1280: for (int i = 0; i < updateCount.length; i++) {
1281: if (updateCount[i] != 1) {
1282: System.out.println("ERROR: update count for stat " + i
1283: + "should have been 1 but it is "
1284: + updateCount[i]);
1285: passed = false;
1286: }
1287: }
1288:
1289: pStmt.setInt(1, 1);
1290: pStmt.addBatch();
1291: pStmt.setInt(1, 1);
1292: pStmt.addBatch();
1293: updateCount = pStmt.executeBatch();
1294:
1295: if (updateCount.length != 2) {
1296: System.out
1297: .println("ERROR: there were 2 statements in the batch");
1298: passed = false;
1299: }
1300:
1301: for (int i = 0; i < updateCount.length; i++) {
1302: if (updateCount[i] != 1) {
1303: System.out.println("ERROR: update count for stat " + i
1304: + "should have been 1 but it is "
1305: + updateCount[i]);
1306: passed = false;
1307: }
1308: }
1309:
1310: conn.rollback();
1311:
1312: rs = stmt.executeQuery("select count(*) from t1");
1313: rs.next();
1314: if (rs.getInt(1) != 4) {
1315: System.out.println("ERROR: There should have been 4 rows");
1316: passed = false;
1317: }
1318: rs.close();
1319:
1320: //try batch, batch and commit
1321: System.out
1322: .println("Positive Prepared Stat: batch, batch and commit combinations");
1323: pStmt.setInt(1, 1);
1324: pStmt.addBatch();
1325: pStmt.setInt(1, 1);
1326: pStmt.addBatch();
1327: updateCount = pStmt.executeBatch();
1328:
1329: if (updateCount.length != 2) {
1330: System.out
1331: .println("ERROR: there were 2 statements in the batch");
1332: passed = false;
1333: }
1334:
1335: for (int i = 0; i < updateCount.length; i++) {
1336: if (updateCount[i] != 1) {
1337: System.out.println("ERROR: update count for stat " + i
1338: + "should have been 1 but it is "
1339: + updateCount[i]);
1340: passed = false;
1341: }
1342: }
1343:
1344: pStmt.setInt(1, 1);
1345: pStmt.addBatch();
1346: pStmt.setInt(1, 1);
1347: pStmt.addBatch();
1348: updateCount = pStmt.executeBatch();
1349:
1350: if (updateCount.length != 2) {
1351: System.out
1352: .println("ERROR: there were 2 statements in the batch");
1353: passed = false;
1354: }
1355:
1356: for (int i = 0; i < updateCount.length; i++) {
1357: if (updateCount[i] != 1) {
1358: System.out.println("ERROR: update count for stat " + i
1359: + "should have been 1 but it is "
1360: + updateCount[i]);
1361: passed = false;
1362: }
1363: }
1364:
1365: conn.commit();
1366:
1367: rs = stmt.executeQuery("select count(*) from t1");
1368: rs.next();
1369: if (rs.getInt(1) != 8) {
1370: System.out.println("ERROR: There should have been 8 rows");
1371: passed = false;
1372: }
1373: rs.close();
1374:
1375: pStmt.close();
1376:
1377: stmt.executeUpdate("delete from t1");
1378: conn.commit();
1379: return passed;
1380: }
1381:
1382: //try executing batches with various rollback and commit combinations.
1383: static boolean runRollbackAndCommitCombinations(Connection conn,
1384: Statement stmt) throws SQLException {
1385: boolean passed = true;
1386: int updateCount[];
1387: ResultSet rs;
1388:
1389: System.out
1390: .println("Positive Statement: batch, rollback, batch and commit combinations");
1391: stmt.addBatch("insert into t1 values(1)");
1392: stmt.addBatch("insert into t1 values(1)");
1393: updateCount = stmt.executeBatch();
1394:
1395: if (updateCount.length != 2) {
1396: System.out
1397: .println("ERROR: there were 2 statements in the batch");
1398: passed = false;
1399: }
1400:
1401: for (int i = 0; i < updateCount.length; i++) {
1402: if (updateCount[i] != 1) {
1403: System.out.println("ERROR: update count for stat " + i
1404: + "should have been 1 but it is "
1405: + updateCount[i]);
1406: passed = false;
1407: }
1408: }
1409:
1410: conn.rollback();
1411:
1412: rs = stmt.executeQuery("select count(*) from t1");
1413: rs.next();
1414: if (rs.getInt(1) != 0) {
1415: System.out.println("ERROR: There should have been 0 rows");
1416: passed = false;
1417: }
1418: rs.close();
1419:
1420: stmt.addBatch("insert into t1 values(1)");
1421: stmt.addBatch("insert into t1 values(1)");
1422: updateCount = stmt.executeBatch();
1423:
1424: if (updateCount.length != 2) {
1425: System.out
1426: .println("ERROR: there were 2 statements in the batch");
1427: passed = false;
1428: }
1429:
1430: for (int i = 0; i < updateCount.length; i++) {
1431: if (updateCount[i] != 1) {
1432: System.out.println("ERROR: update count for stat " + i
1433: + "should have been 1 but it is "
1434: + updateCount[i]);
1435: passed = false;
1436: }
1437: }
1438:
1439: conn.commit();
1440:
1441: rs = stmt.executeQuery("select count(*) from t1");
1442: rs.next();
1443: if (rs.getInt(1) != 2) {
1444: System.out.println("ERROR: There should have been 2 rows");
1445: passed = false;
1446: }
1447: rs.close();
1448:
1449: //try batch and commit
1450: System.out
1451: .println("Positive Statement: batch and commit combinations");
1452: stmt.addBatch("insert into t1 values(1)");
1453: stmt.addBatch("insert into t1 values(1)");
1454: updateCount = stmt.executeBatch();
1455:
1456: if (updateCount.length != 2) {
1457: System.out
1458: .println("ERROR: there were 2 statements in the batch");
1459: passed = false;
1460: }
1461:
1462: for (int i = 0; i < updateCount.length; i++) {
1463: if (updateCount[i] != 1) {
1464: System.out.println("ERROR: update count for stat " + i
1465: + "should have been 1 but it is "
1466: + updateCount[i]);
1467: passed = false;
1468: }
1469: }
1470:
1471: conn.commit();
1472:
1473: rs = stmt.executeQuery("select count(*) from t1");
1474: rs.next();
1475: if (rs.getInt(1) != 4) {
1476: System.out.println("ERROR: There should have been 4 rows");
1477: passed = false;
1478: }
1479: rs.close();
1480:
1481: //try batch, batch and rollback
1482: System.out
1483: .println("Positive Statement: batch, batch and rollback combinations");
1484: stmt.addBatch("insert into t1 values(1)");
1485: stmt.addBatch("insert into t1 values(1)");
1486: updateCount = stmt.executeBatch();
1487:
1488: if (updateCount.length != 2) {
1489: System.out
1490: .println("ERROR: there were 2 statements in the batch");
1491: passed = false;
1492: }
1493:
1494: for (int i = 0; i < updateCount.length; i++) {
1495: if (updateCount[i] != 1) {
1496: System.out.println("ERROR: update count for stat " + i
1497: + "should have been 1 but it is "
1498: + updateCount[i]);
1499: passed = false;
1500: }
1501: }
1502:
1503: stmt.addBatch("insert into t1 values(1)");
1504: stmt.addBatch("insert into t1 values(1)");
1505: updateCount = stmt.executeBatch();
1506:
1507: if (updateCount.length != 2) {
1508: System.out
1509: .println("ERROR: there were 2 statements in the batch");
1510: passed = false;
1511: }
1512:
1513: for (int i = 0; i < updateCount.length; i++) {
1514: if (updateCount[i] != 1) {
1515: System.out.println("ERROR: update count for stat " + i
1516: + "should have been 1 but it is "
1517: + updateCount[i]);
1518: passed = false;
1519: }
1520: }
1521:
1522: conn.rollback();
1523:
1524: rs = stmt.executeQuery("select count(*) from t1");
1525: rs.next();
1526: if (rs.getInt(1) != 4) {
1527: System.out.println("ERROR: There should have been 4 rows");
1528: passed = false;
1529: }
1530: rs.close();
1531:
1532: //try batch, batch and commit
1533: System.out
1534: .println("Positive Statement: batch, batch and rollback combinations");
1535: stmt.addBatch("insert into t1 values(1)");
1536: stmt.addBatch("insert into t1 values(1)");
1537: updateCount = stmt.executeBatch();
1538:
1539: if (updateCount.length != 2) {
1540: System.out
1541: .println("ERROR: there were 2 statements in the batch");
1542: passed = false;
1543: }
1544:
1545: for (int i = 0; i < updateCount.length; i++) {
1546: if (updateCount[i] != 1) {
1547: System.out.println("ERROR: update count for stat " + i
1548: + "should have been 1 but it is "
1549: + updateCount[i]);
1550: passed = false;
1551: }
1552: }
1553:
1554: stmt.addBatch("insert into t1 values(1)");
1555: stmt.addBatch("insert into t1 values(1)");
1556: updateCount = stmt.executeBatch();
1557:
1558: if (updateCount.length != 2) {
1559: System.out
1560: .println("ERROR: there were 2 statements in the batch");
1561: passed = false;
1562: }
1563:
1564: for (int i = 0; i < updateCount.length; i++) {
1565: if (updateCount[i] != 1) {
1566: System.out.println("ERROR: update count for stat " + i
1567: + "should have been 1 but it is "
1568: + updateCount[i]);
1569: passed = false;
1570: }
1571: }
1572:
1573: conn.commit();
1574:
1575: rs = stmt.executeQuery("select count(*) from t1");
1576: rs.next();
1577: if (rs.getInt(1) != 8) {
1578: System.out.println("ERROR: There should have been 8 rows");
1579: passed = false;
1580: }
1581: rs.close();
1582:
1583: stmt.executeUpdate("delete from t1");
1584: conn.commit();
1585: return passed;
1586: }
1587:
1588: //try prepared statement batch with autocommit true
1589: static boolean runAutoCommitTruePreparedStatBatch(Connection conn,
1590: Statement stmt) throws SQLException {
1591: boolean passed = true;
1592: int updateCount[];
1593: ResultSet rs;
1594:
1595: conn.setAutoCommit(true);
1596: //prepared statement batch with autocommit true
1597: System.out
1598: .println("Positive Prepared Stat: testing batch with autocommit true");
1599: PreparedStatement pStmt = conn
1600: .prepareStatement("insert into t1 values(?)");
1601: pStmt.setInt(1, 1);
1602: pStmt.addBatch();
1603: pStmt.setInt(1, 1);
1604: pStmt.addBatch();
1605: pStmt.setInt(1, 1);
1606: pStmt.addBatch();
1607: updateCount = pStmt.executeBatch();
1608:
1609: if (updateCount.length != 3) {
1610: System.out
1611: .println("ERROR: there were 3 statements in the batch");
1612: passed = false;
1613: }
1614:
1615: for (int i = 0; i < updateCount.length; i++) {
1616: if (updateCount[i] != 1) {
1617: System.out.println("ERROR: update count for stat " + i
1618: + "should have been 1 but it is "
1619: + updateCount[i]);
1620: passed = false;
1621: }
1622: }
1623:
1624: rs = stmt.executeQuery("select count(*) from t1");
1625: rs.next();
1626: if (rs.getInt(1) != 3) {
1627: System.out
1628: .println("ERROR: There should been 3 rows in the table, but found "
1629: + rs.getInt(1) + " rows");
1630: passed = false;
1631: }
1632: rs.close();
1633: pStmt.close();
1634:
1635: //turn it true again after the above negative test
1636: conn.setAutoCommit(false);
1637:
1638: stmt.executeUpdate("delete from t1");
1639: conn.commit();
1640: return passed;
1641: }
1642:
1643: //try batch with autocommit true
1644: static boolean runAutoCommitTrueBatch(Connection conn,
1645: Statement stmt) throws SQLException {
1646: boolean passed = true;
1647: int updateCount[];
1648: ResultSet rs;
1649:
1650: conn.setAutoCommit(true);
1651: //try batch with autocommit true
1652: System.out
1653: .println("Positive Statement: statement testing batch with autocommit true");
1654: stmt.addBatch("insert into t1 values(1)");
1655: stmt.addBatch("insert into t1 values(1)");
1656: stmt.addBatch("delete from t1");
1657: updateCount = stmt.executeBatch();
1658:
1659: if (updateCount.length != 3) {
1660: System.out
1661: .println("ERROR: there were 3 statements in the batch");
1662: passed = false;
1663: }
1664:
1665: for (int i = 0; i < (updateCount.length - 1); i++) {
1666: if (updateCount[i] != 1) {
1667: System.out.println("ERROR: update count for stat " + i
1668: + "should have been 1 but it is "
1669: + updateCount[i]);
1670: passed = false;
1671: }
1672: }
1673: if (updateCount[2] != 2) {
1674: System.out
1675: .println("ERROR: update count for stat 2 should have been 2 but it is "
1676: + updateCount[2]);
1677: passed = false;
1678: }
1679:
1680: rs = stmt.executeQuery("select count(*) from t1");
1681: rs.next();
1682: if (rs.getInt(1) != 0) {
1683: System.out
1684: .println("ERROR: There should been no rows in the table, but found "
1685: + rs.getInt(1) + " rows");
1686: passed = false;
1687: }
1688: rs.close();
1689:
1690: //turn it true again after the above negative test
1691: conn.setAutoCommit(false);
1692:
1693: stmt.executeUpdate("delete from t1");
1694: conn.commit();
1695: return passed;
1696: }
1697:
1698: //try callable statements with output parameters
1699: static boolean runCallableStatementWithOutputParamBatch(
1700: Connection conn) throws SQLException {
1701: boolean passed = true;
1702: int updateCount[] = null;
1703: ResultSet rs;
1704:
1705: System.out
1706: .println("Negative Callable Statement: callable statement with output parameters in the batch");
1707: Statement s = conn.createStatement();
1708:
1709: s
1710: .execute("CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) "
1711: + "EXTERNAL NAME '"
1712: + CLASS_NAME
1713: + "takesString'"
1714: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
1715:
1716: CallableStatement cs = conn
1717: .prepareCall("call takesString(?,?)");
1718: try {
1719:
1720: cs.registerOutParameter(1, Types.CHAR);
1721: cs.setInt(2, Types.INTEGER);
1722: cs.addBatch();
1723: System.out
1724: .println("FAIL - addBatch() allowed with registered out parameter");
1725: passed = false;
1726: } catch (SQLException sqle) {
1727: // Check to be sure the exception is callback related
1728: passed = passed && checkException(sqle, "XJ04C");
1729: }
1730:
1731: cs.close();
1732: s.execute("drop procedure takesString");
1733: s.close();
1734: conn.rollback();
1735: conn.commit();
1736: return passed;
1737: }
1738:
1739: //try callable statements
1740: static boolean runCallableStatementBatch(Connection conn)
1741: throws SQLException {
1742: boolean passed = true;
1743: int updateCount[] = null;
1744: ResultSet rs;
1745:
1746: System.out
1747: .println("Positive Callable Statement: statement testing callable statement batch");
1748: CallableStatement cs = conn
1749: .prepareCall("insert into t1 values(?)");
1750:
1751: cs.setInt(1, 1);
1752: cs.addBatch();
1753: cs.setInt(1, 2);
1754: cs.addBatch();
1755: try {
1756: passed = passed && executeBatchCallableStatement(cs);
1757: } catch (SQLException sqle) {
1758: /* Check to be sure the exception is callback related */
1759: passed = passed && checkException(sqle, "XJ04C");
1760: if (sqle instanceof BatchUpdateException) {
1761: updateCount = ((BatchUpdateException) sqle)
1762: .getUpdateCounts();
1763: if (updateCount != null) {
1764: if (updateCount.length != 0) {
1765: System.out
1766: .println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
1767: passed = false;
1768: }
1769: }
1770: }
1771: }
1772:
1773: cleanUpCallableStatement(conn, cs, "t1");
1774:
1775: /* Bug 2813 - verify setXXXX() works with
1776: * Date, Time and Timestamp on CallableStatement.
1777: */
1778: cs = conn.prepareCall("insert into datetab values(?)");
1779:
1780: cs.setDate(1, Date.valueOf("1990-05-05"));
1781: cs.addBatch();
1782: cs.setDate(1, Date.valueOf("1990-06-06"));
1783: cs.addBatch();
1784: try {
1785: passed = passed && executeBatchCallableStatement(cs);
1786: } catch (SQLException sqle) {
1787: /* Check to be sure the exception is callback related */
1788: passed = passed && checkException(sqle, "XJ04C");
1789: if (sqle instanceof BatchUpdateException) {
1790: updateCount = ((BatchUpdateException) sqle)
1791: .getUpdateCounts();
1792: if (updateCount != null) {
1793: if (updateCount.length != 0) {
1794: System.out
1795: .println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
1796: passed = false;
1797: }
1798: }
1799: }
1800: }
1801:
1802: cleanUpCallableStatement(conn, cs, "datetab");
1803:
1804: cs = conn.prepareCall("insert into timetab values(?)");
1805:
1806: cs.setTime(1, Time.valueOf("11:11:11"));
1807: cs.addBatch();
1808: cs.setTime(1, Time.valueOf("12:12:12"));
1809: cs.addBatch();
1810: try {
1811: passed = passed && executeBatchCallableStatement(cs);
1812: } catch (SQLException sqle) {
1813: /* Check to be sure the exception is callback related */
1814: passed = passed && checkException(sqle, "XJ04C");
1815: if (sqle instanceof BatchUpdateException) {
1816: updateCount = ((BatchUpdateException) sqle)
1817: .getUpdateCounts();
1818: if (updateCount != null) {
1819: if (updateCount.length != 0) {
1820: System.out
1821: .println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
1822: passed = false;
1823: }
1824: }
1825: }
1826: }
1827:
1828: cleanUpCallableStatement(conn, cs, "timestamptab");
1829:
1830: cs = conn.prepareCall("insert into timestamptab values(?)");
1831:
1832: cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
1833: cs.addBatch();
1834: cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
1835: cs.addBatch();
1836: try {
1837: passed = passed && executeBatchCallableStatement(cs);
1838: } catch (SQLException sqle) {
1839: /* Check to be sure the exception is callback related */
1840: passed = passed && checkException(sqle, "XJ04C");
1841: if (sqle instanceof BatchUpdateException) {
1842: updateCount = ((BatchUpdateException) sqle)
1843: .getUpdateCounts();
1844: if (updateCount != null) {
1845: if (updateCount.length != 0) {
1846: System.out
1847: .println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
1848: passed = false;
1849: }
1850: }
1851: }
1852: }
1853:
1854: cleanUpCallableStatement(conn, cs, "timestamptab");
1855:
1856: // Try with a user type
1857: cs = conn.prepareCall("insert into usertypetab values(?)");
1858:
1859: cs.setObject(1, Date.valueOf("1990-05-05"));
1860: cs.addBatch();
1861: cs.setObject(1, Date.valueOf("1990-06-06"));
1862: cs.addBatch();
1863: try {
1864: passed = passed && executeBatchCallableStatement(cs);
1865: } catch (SQLException sqle) {
1866: /* Check to be sure the exception is callback related */
1867: passed = passed && checkException(sqle, "XJ04C");
1868: if (sqle instanceof BatchUpdateException) {
1869: updateCount = ((BatchUpdateException) sqle)
1870: .getUpdateCounts();
1871: if (updateCount != null) {
1872: if (updateCount.length != 0) {
1873: System.out
1874: .println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
1875: passed = false;
1876: }
1877: }
1878: }
1879: }
1880:
1881: cleanUpCallableStatement(conn, cs, "usertypetab");
1882:
1883: return passed;
1884: }
1885:
1886: private static boolean executeBatchCallableStatement(
1887: CallableStatement cs) throws SQLException {
1888: boolean passed = true;
1889: int updateCount[];
1890:
1891: updateCount = cs.executeBatch();
1892: if (updateCount.length != 2) {
1893: System.out
1894: .println("ERROR: there were 2 statements in the batch");
1895: passed = false;
1896: }
1897: for (int i = 0; i < updateCount.length; i++) {
1898: if (updateCount[i] != 1) {
1899: System.out
1900: .println("ERROR: update count should have been 1 but it's "
1901: + updateCount[i]);
1902: passed = false;
1903: }
1904: }
1905:
1906: return passed;
1907: }
1908:
1909: private static void cleanUpCallableStatement(Connection conn,
1910: CallableStatement cs, String tableName) throws SQLException {
1911: cs.close();
1912: conn.rollback();
1913: cs = conn.prepareCall("delete from " + tableName);
1914: cs.executeUpdate();
1915: cs.close();
1916: conn.commit();
1917: }
1918:
1919: //try combinations of clear batch.
1920: static boolean runCombinationsOfClearPreparedStatBatch(
1921: Connection conn, Statement stmt) throws SQLException {
1922: boolean passed = true;
1923: int updateCount[];
1924: ResultSet rs;
1925:
1926: System.out
1927: .println("Positive Prepared Stat: add 3 statements, clear batch and execute batch");
1928: PreparedStatement pStmt = conn
1929: .prepareStatement("insert into t1 values(?)");
1930: pStmt.setInt(1, 1);
1931: pStmt.addBatch();
1932: pStmt.setInt(1, 2);
1933: pStmt.addBatch();
1934: pStmt.setInt(1, 3);
1935: pStmt.addBatch();
1936: pStmt.clearBatch();
1937: updateCount = pStmt.executeBatch();
1938:
1939: if (updateCount.length != 0) {
1940: System.out
1941: .println("ERROR: there were 0 statements in the batch");
1942: passed = false;
1943: }
1944:
1945: rs = stmt.executeQuery("select count(*) from t1");
1946: rs.next();
1947: if (rs.getInt(1) != 0) {
1948: System.out
1949: .println("ERROR: There should been no rows in the table");
1950: passed = false;
1951: }
1952: rs.close();
1953:
1954: System.out
1955: .println("Positive Prepared Stat: add 3 statements, clear batch, add 3 and execute batch");
1956: pStmt.setInt(1, 1);
1957: pStmt.addBatch();
1958: pStmt.setInt(1, 2);
1959: pStmt.addBatch();
1960: pStmt.setInt(1, 3);
1961: pStmt.addBatch();
1962: pStmt.clearBatch();
1963: pStmt.setInt(1, 1);
1964: pStmt.addBatch();
1965: pStmt.setInt(1, 2);
1966: pStmt.addBatch();
1967: pStmt.setInt(1, 3);
1968: pStmt.addBatch();
1969: updateCount = pStmt.executeBatch();
1970:
1971: if (updateCount.length != 3) {
1972: System.out
1973: .println("ERROR: there were 3 statements in the batch");
1974: passed = false;
1975: }
1976:
1977: rs = stmt.executeQuery("select count(*) from t1");
1978: rs.next();
1979: if (rs.getInt(1) != 3) {
1980: System.out
1981: .println("ERROR: There should been 3 rows in the table");
1982: passed = false;
1983: }
1984: rs.close();
1985: pStmt.close();
1986:
1987: stmt.executeUpdate("delete from t1");
1988: conn.commit();
1989: return passed;
1990: }
1991:
1992: //try combinations of clear batch.
1993: static boolean runCombinationsOfClearBatch(Connection conn,
1994: Statement stmt) throws SQLException {
1995: boolean passed = true;
1996: int updateCount[];
1997: ResultSet rs;
1998:
1999: System.out
2000: .println("Positive Statement: add 3 statements, clear batch and execute batch");
2001: stmt.addBatch("insert into t1 values(2)");
2002: stmt.addBatch("insert into t1 values(2)");
2003: stmt.addBatch("insert into t1 values(2)");
2004: stmt.clearBatch();
2005: updateCount = stmt.executeBatch();
2006:
2007: if (updateCount.length != 0) {
2008: System.out
2009: .println("ERROR: there were 0 statements in the batch");
2010: passed = false;
2011: }
2012:
2013: rs = stmt.executeQuery("select count(*) from t1");
2014: rs.next();
2015: if (rs.getInt(1) != 0) {
2016: System.out
2017: .println("ERROR: There should been no rows in the table");
2018: passed = false;
2019: }
2020: rs.close();
2021:
2022: System.out
2023: .println("Positive Statement: add 3 statements, clear batch, add 3 and execute batch");
2024: stmt.addBatch("insert into t1 values(2)");
2025: stmt.addBatch("insert into t1 values(2)");
2026: stmt.addBatch("insert into t1 values(2)");
2027: stmt.clearBatch();
2028: stmt.addBatch("insert into t1 values(2)");
2029: stmt.addBatch("insert into t1 values(2)");
2030: stmt.addBatch("insert into t1 values(2)");
2031: updateCount = stmt.executeBatch();
2032:
2033: if (updateCount.length != 3) {
2034: System.out
2035: .println("ERROR: there were 3 statements in the batch");
2036: passed = false;
2037: }
2038:
2039: rs = stmt.executeQuery("select count(*) from t1");
2040: rs.next();
2041: if (rs.getInt(1) != 3) {
2042: System.out
2043: .println("ERROR: There should been 3 rows in the table");
2044: passed = false;
2045: }
2046: rs.close();
2047:
2048: stmt.executeUpdate("delete from t1");
2049: conn.commit();
2050: return passed;
2051: }
2052:
2053: //try executing a batch with 1000 statements in it.
2054: static boolean run1000ValueSetPreparedBatch(Connection conn,
2055: Statement stmt) throws SQLException {
2056: boolean passed = true;
2057: int updateCount[];
2058: ResultSet rs;
2059:
2060: System.out
2061: .println("Positive Prepared Stat: 1000 parameter set batch");
2062: PreparedStatement pStmt = conn
2063: .prepareStatement("insert into t1 values(?)");
2064: for (int i = 0; i < 1000; i++) {
2065: pStmt.setInt(1, 1);
2066: pStmt.addBatch();
2067: }
2068: updateCount = pStmt.executeBatch();
2069:
2070: if (updateCount.length != 1000) {
2071: System.out
2072: .println("ERROR: there were 1000 parameter sets in the batch");
2073: passed = false;
2074: }
2075:
2076: rs = stmt.executeQuery("select count(*) from t1");
2077: rs.next();
2078: if (rs.getInt(1) != 1000) {
2079: System.out
2080: .println("There should been 1000 rows in the table, but found "
2081: + rs.getInt(1) + " rows");
2082: passed = false;
2083: }
2084: rs.close();
2085:
2086: pStmt.close();
2087: stmt.executeUpdate("delete from t1");
2088: conn.commit();
2089: return passed;
2090: }
2091:
2092: //try executing a batch with 1000 statements in it.
2093: static boolean run1000StatementsBatch(Connection conn,
2094: Statement stmt) throws SQLException {
2095: boolean passed = true;
2096: int updateCount[];
2097: ResultSet rs;
2098:
2099: System.out.println("Positive Statement: 1000 statements batch");
2100: for (int i = 0; i < 1000; i++) {
2101: stmt.addBatch("insert into t1 values(1)");
2102: }
2103: updateCount = stmt.executeBatch();
2104:
2105: if (updateCount.length != 1000) {
2106: System.out
2107: .println("ERROR: there were 1000 statements in the batch");
2108: passed = false;
2109: }
2110:
2111: rs = stmt.executeQuery("select count(*) from t1");
2112: rs.next();
2113: if (rs.getInt(1) != 1000) {
2114: System.out
2115: .println("There should been 1000 rows in the table, but found "
2116: + rs.getInt(1) + " rows");
2117: passed = false;
2118: }
2119: rs.close();
2120:
2121: stmt.executeUpdate("delete from t1");
2122: conn.commit();
2123: return passed;
2124: }
2125:
2126: //try executing a batch with 3 different parameter sets in it.
2127: static boolean runMultipleValueSetPreparedBatch(Connection conn,
2128: Statement stmt) throws SQLException {
2129: boolean passed = true;
2130: int updateCount[];
2131: ResultSet rs;
2132:
2133: //try prepared statement batch with just one set of values
2134: System.out
2135: .println("Positive Prepared Stat: set 3 set of parameter values and run the batch");
2136: PreparedStatement pStmt = conn
2137: .prepareStatement("insert into t1 values(?)");
2138: pStmt.setInt(1, 1);
2139: pStmt.addBatch();
2140: pStmt.setInt(1, 2);
2141: pStmt.addBatch();
2142: pStmt.setInt(1, 3);
2143: pStmt.addBatch();
2144: updateCount = pStmt.executeBatch();
2145: if (updateCount.length != 3) {
2146: System.out
2147: .println("ERROR: there were 3 parameter sets in the batch");
2148: passed = false;
2149: }
2150:
2151: for (int i = 0; i < updateCount.length; i++) {
2152: if (updateCount[i] != 1) {
2153: System.out.println("ERROR: update count for stat " + i
2154: + "should have been 1 but it is "
2155: + updateCount[i]);
2156: passed = false;
2157: }
2158: }
2159:
2160: pStmt.close();
2161:
2162: rs = stmt.executeQuery("select count(*) from t1");
2163: rs.next();
2164: if (rs.getInt(1) != 3) {
2165: System.out.println("ERROR: There should have been 3 rows");
2166: passed = false;
2167: }
2168: rs.close();
2169:
2170: stmt.executeUpdate("delete from t1");
2171: conn.commit();
2172: return passed;
2173: }
2174:
2175: //try executing a batch with 3 different statements in it.
2176: static boolean runMultipleStatementsBatch(Connection conn,
2177: Statement stmt) throws SQLException {
2178: boolean passed = true;
2179: int updateCount[];
2180: ResultSet rs;
2181:
2182: System.out
2183: .println("Positive Statement: testing 2 inserts and 1 update batch");
2184: stmt.addBatch("insert into t1 values(2)");
2185: stmt.addBatch("update t1 set c1=4");
2186: stmt.addBatch("insert into t1 values(3)");
2187:
2188: updateCount = stmt.executeBatch();
2189:
2190: if (updateCount.length != 3) {
2191: System.out
2192: .println("ERROR: there were 3 statements in the batch");
2193: passed = false;
2194: }
2195:
2196: for (int i = 0; i < updateCount.length; i++) {
2197: if (updateCount[i] != 1) {
2198: System.out.println("ERROR: update count for stat " + i
2199: + "should have been 1 but it is "
2200: + updateCount[i]);
2201: passed = false;
2202: }
2203: }
2204:
2205: rs = stmt.executeQuery("select count(*) from t1 where c1=2");
2206: rs.next();
2207: if (rs.getInt(1) != 0) {
2208: System.out
2209: .println("ERROR: There should have been 0 rows with c1 = 2");
2210: passed = false;
2211: }
2212: rs.close();
2213:
2214: rs = stmt.executeQuery("select count(*) from t1 where c1=4");
2215: rs.next();
2216: if (rs.getInt(1) != 1) {
2217: System.out
2218: .println("ERROR: There should have been 1 row with c1 = 4");
2219: passed = false;
2220: }
2221: rs.close();
2222:
2223: rs = stmt.executeQuery("select count(*) from t1 where c1=3");
2224: rs.next();
2225: if (rs.getInt(1) != 1) {
2226: System.out
2227: .println("ERROR: There should have been 1 row with c1 = 3");
2228: passed = false;
2229: }
2230: rs.close();
2231:
2232: rs = stmt.executeQuery("select count(*) from t1");
2233: rs.next();
2234: if (rs.getInt(1) != 2) {
2235: System.out.println("ERROR: There should have been 2 rows");
2236: passed = false;
2237: }
2238: rs.close();
2239:
2240: stmt.executeUpdate("delete from t1");
2241: conn.commit();
2242: return passed;
2243: }
2244:
2245: //try prepared statement batch with just one set of values.
2246: static boolean runSingleValueSetPreparedBatch(Connection conn,
2247: Statement stmt) throws SQLException {
2248: boolean passed = true;
2249: int updateCount[];
2250: ResultSet rs;
2251:
2252: //try prepared statement batch with just one set of values
2253: System.out
2254: .println("Positive Prepared Stat: set one set of parameter values and run the batch");
2255: PreparedStatement pStmt = conn
2256: .prepareStatement("insert into t1 values(?)");
2257: pStmt.setInt(1, 1);
2258: pStmt.addBatch();
2259: updateCount = pStmt.executeBatch();
2260: if (updateCount.length != 1) {
2261: System.out
2262: .println("ERROR: there was 1 parameter set in the batch");
2263: passed = false;
2264: }
2265:
2266: for (int i = 0; i < updateCount.length; i++) {
2267: if (updateCount[i] != 1) {
2268: System.out.println("ERROR: update count for stat " + i
2269: + "should have been 1 but it is "
2270: + updateCount[i]);
2271: passed = false;
2272: }
2273: }
2274:
2275: pStmt.close();
2276: rs = stmt.executeQuery("select count(*) from t1 where c1=1");
2277: rs.next();
2278: if (rs.getInt(1) != 1) {
2279: System.out
2280: .println("ERROR: There should have been one rows with c1 = 1");
2281: passed = false;
2282: }
2283: rs.close();
2284:
2285: rs = stmt.executeQuery("select count(*) from t1");
2286: rs.next();
2287: if (rs.getInt(1) != 1) {
2288: System.out.println("ERROR: There should have been 1 row");
2289: passed = false;
2290: }
2291: rs.close();
2292:
2293: stmt.executeUpdate("delete from t1");
2294: conn.commit();
2295: return passed;
2296: }
2297:
2298: //try prepared statement batch with just no settable parameters.
2299: static boolean runNoParametersPreparedBatch(Connection conn,
2300: Statement stmt) throws SQLException {
2301: boolean passed = true;
2302: int updateCount[];
2303: ResultSet rs;
2304:
2305: System.out
2306: .println("Positive Prepared Stat: no settable parameters");
2307: PreparedStatement pStmt = conn
2308: .prepareStatement("insert into t1 values(5)");
2309: pStmt.addBatch();
2310: pStmt.addBatch();
2311: pStmt.addBatch();
2312: updateCount = pStmt.executeBatch();
2313: if (updateCount.length != 3) {
2314: System.out
2315: .println("ERROR: there was 3 parameter set in the batch");
2316: passed = false;
2317: }
2318:
2319: for (int i = 0; i < updateCount.length; i++) {
2320: if (updateCount[i] != 1) {
2321: System.out.println("ERROR: update count for stat " + i
2322: + "should have been 1 but it is "
2323: + updateCount[i]);
2324: passed = false;
2325: }
2326: }
2327:
2328: pStmt.close();
2329: rs = stmt.executeQuery("select count(*) from t1 where c1=5");
2330: rs.next();
2331: if (rs.getInt(1) != 3) {
2332: System.out
2333: .println("ERROR: There should have been three rows with c1 = 5");
2334: passed = false;
2335: }
2336: rs.close();
2337:
2338: rs = stmt.executeQuery("select count(*) from t1");
2339: rs.next();
2340: if (rs.getInt(1) != 3) {
2341: System.out.println("ERROR: There should have been 3 rows");
2342: passed = false;
2343: }
2344: rs.close();
2345:
2346: stmt.executeUpdate("delete from t1");
2347: conn.commit();
2348: return passed;
2349: }
2350:
2351: //try prepared statement batch with just 2 set of values and there value is null. Bug 4002
2352: static boolean runMultipleValueSetNullPreparedBatch(
2353: Connection conn, Statement stmt) throws SQLException {
2354: boolean passed = true;
2355: int updateCount[];
2356: ResultSet rs;
2357:
2358: //try prepared statement batch with just one set of values
2359: System.out
2360: .println("Positive Prepared Stat: set one set of parameter values to null and run the batch");
2361: PreparedStatement pStmt = conn
2362: .prepareStatement("insert into t1 values(?)");
2363: pStmt.setNull(1, Types.INTEGER);
2364: pStmt.addBatch();
2365: pStmt.setNull(1, Types.INTEGER);
2366: pStmt.addBatch();
2367: updateCount = pStmt.executeBatch();
2368: if (updateCount.length != 2) {
2369: System.out
2370: .println("ERROR: there were 2 parameter set to null in the batch");
2371: passed = false;
2372: }
2373:
2374: for (int i = 0; i < updateCount.length; i++) {
2375: if (updateCount[i] != 1) {
2376: System.out.println("ERROR: update count for stat " + i
2377: + "should have been 1 but it is "
2378: + updateCount[i]);
2379: passed = false;
2380: }
2381: }
2382:
2383: pStmt.close();
2384: rs = stmt
2385: .executeQuery("select count(*) from t1 where c1 is null");
2386: rs.next();
2387: if (rs.getInt(1) != 2) {
2388: System.out
2389: .println("ERROR: There should have been two rows with c1 is null");
2390: passed = false;
2391: }
2392: rs.close();
2393:
2394: rs = stmt.executeQuery("select count(*) from t1");
2395: rs.next();
2396: if (rs.getInt(1) != 2) {
2397: System.out.println("ERROR: There should have been 2 rows");
2398: passed = false;
2399: }
2400: rs.close();
2401:
2402: stmt.executeUpdate("delete from t1");
2403: conn.commit();
2404: return passed;
2405: }
2406:
2407: //try executing a batch which single statement in it. Should work.
2408: static boolean runSingleStatementBatch(Connection conn,
2409: Statement stmt) throws SQLException {
2410: boolean passed = true;
2411: int updateCount[];
2412:
2413: System.out
2414: .println("Positive Statement: testing 1 statement batch");
2415: stmt.addBatch("insert into t1 values(2)");
2416: updateCount = stmt.executeBatch();
2417:
2418: if (updateCount.length > 1) {
2419: System.out
2420: .println("ERROR: Since this is a single statement, there should have been only one update count");
2421: passed = false;
2422: }
2423:
2424: if (updateCount[0] != 1) {
2425: System.out
2426: .println("ERROR: update count should have been 1, instead it is "
2427: + updateCount[0]);
2428: passed = false;
2429: }
2430:
2431: stmt.executeUpdate("delete from t1");
2432: conn.commit();
2433: return passed;
2434: }
2435:
2436: //try executing a batch which nothing in it. Should work.
2437: static boolean runEmptyValueSetPreparedBatch(Connection conn,
2438: Statement stmt) throws SQLException {
2439: boolean passed = true;
2440: int updateCount[];
2441:
2442: //try executing a batch which nothing in it. Should work.
2443: System.out
2444: .println("Positive Prepared Stat: set no parameter values and run the batch");
2445: PreparedStatement pStmt = conn
2446: .prepareStatement("insert into t1 values(?)");
2447: updateCount = pStmt.executeBatch();
2448:
2449: if (updateCount.length != 0) {
2450: System.out
2451: .println("ERROR: update count should have been zero");
2452: passed = false;
2453: }
2454:
2455: pStmt.close();
2456: stmt.executeUpdate("delete from t1");
2457: conn.commit();
2458: return passed;
2459: }
2460:
2461: //try executing a batch which nothing in it. Should work.
2462: static boolean runEmptyStatementBatch(Connection conn,
2463: Statement stmt) throws SQLException {
2464: boolean passed = true;
2465: int updateCount[];
2466:
2467: //try executing a batch which nothing in it. Should work.
2468: System.out
2469: .println("Positive Statement: clear the batch and run the empty batch");
2470: stmt.clearBatch();
2471: updateCount = stmt.executeBatch();
2472:
2473: if (updateCount.length != 0) {
2474: System.out
2475: .println("ERROR: Since this is an empty statement, there shouldn't have been any update count");
2476: passed = false;
2477: }
2478:
2479: stmt.executeUpdate("delete from t1");
2480: conn.commit();
2481: return passed;
2482: }
2483:
2484: /**
2485: * Set up the test.
2486: *
2487: * This method creates the table used by the rest of the test.
2488: *
2489: * @param conn The Connection
2490: *
2491: * @return true if it succeeds, false if it doesn't
2492: *
2493: * @exception SQLException Thrown if some unexpected error happens
2494: */
2495:
2496: static boolean setUpTest(Connection conn, Statement stmt)
2497: throws SQLException {
2498: boolean passed = true;
2499: int rows;
2500:
2501: /* Create a table */
2502: stmt.addBatch("create table t1(c1 int)");
2503: // stmt.addBatch("create class alias for java.lang.Integer");
2504: stmt
2505: .addBatch("create procedure Integ() language java parameter style java external name 'java.lang.Integer'");
2506: stmt.addBatch("create table datetab(c1 date)");
2507: stmt.addBatch("create table timetab(c1 time)");
2508: stmt.addBatch("create table timestamptab(c1 timestamp)");
2509: stmt.addBatch("create table usertypetab(c1 DATE)");
2510: stmt.executeBatch();
2511:
2512: conn.commit();
2513: return passed;
2514: }
2515:
2516: /*
2517: ** Associated parameters are extra parameters that are created
2518: ** and associated with the root parameter (the user one) to
2519: ** improve the performance of like. For something like
2520: ** where c1 like ?, we generate extra 'associated' parameters
2521: ** that we use for predicates that we give to the access
2522: ** manager.
2523: */
2524: static boolean checkAssociatedParams(Connection conn, Statement stmt)
2525: throws SQLException {
2526: int i;
2527: conn.setAutoCommit(false);
2528: System.out
2529: .println("Positive Statement: testing associated parameters");
2530: stmt
2531: .executeUpdate("create table assoc(x char(10) not null primary key, y char(100))");
2532: stmt.executeUpdate("create table assocout(x char(10))");
2533: PreparedStatement ps = conn
2534: .prepareStatement("insert into assoc values (?, 'hello')");
2535: for (i = 10; i < 60; i++) {
2536: ps.setString(1, new Integer(i).toString());
2537: ps.executeUpdate();
2538: }
2539:
2540: ps = conn
2541: .prepareStatement("insert into assocout select x from assoc where x like ?");
2542: ps.setString(1, "33%");
2543: ps.addBatch();
2544: ps.setString(1, "21%");
2545: ps.addBatch();
2546: ps.setString(1, "49%");
2547: ps.addBatch();
2548: int[] updateCount = ps.executeBatch();
2549: if (updateCount.length != 3) {
2550: System.out.println("ERROR: unexpected updateCount length "
2551: + updateCount.length);
2552: conn.rollback();
2553: return false;
2554: }
2555:
2556: for (i = 0; i < 3; i++) {
2557: if (updateCount[i] != 1) {
2558: System.out.println("ERROR: unexpected updateCount[" + i
2559: + "] value = " + updateCount[i]
2560: + ". Expected 1");
2561: conn.rollback();
2562: return false;
2563: }
2564: }
2565: stmt
2566: .execute("select cast(x as int) as myint from assocout order by myint");
2567: ResultSet rs = stmt.getResultSet();
2568: for (i = 0; rs.next(); i++) {
2569: int expect = 0;
2570: switch (i) {
2571: case 0:
2572: expect = 21;
2573: break;
2574: case 1:
2575: expect = 33;
2576: break;
2577: case 2:
2578: expect = 49;
2579: break;
2580: }
2581: if (rs.getInt(1) != expect) {
2582: System.out
2583: .println("ERROR: didn't find value "
2584: + expect
2585: + " in assocout table. It would appear that associated parameters aren't working correctly");
2586: conn.rollback();
2587: return false;
2588: }
2589: }
2590: stmt.executeUpdate("delete from assocout");
2591:
2592: ps = conn
2593: .prepareStatement("insert into assocout select x from assoc where x like ?");
2594: ps.setString(1, "3%");
2595: ps.addBatch();
2596: ps.setString(1, "2%");
2597: ps.addBatch();
2598: ps.setString(1, "1%");
2599: ps.addBatch();
2600: updateCount = ps.executeBatch();
2601: if (updateCount.length != 3) {
2602: System.out.println("ERROR: unexpected updateCount2 length "
2603: + updateCount.length);
2604: conn.rollback();
2605: return false;
2606: }
2607:
2608: for (i = 0; i < 3; i++) {
2609: if (updateCount[i] != 10) {
2610: System.out.println("ERROR: unexpected updateCount2["
2611: + i + "] value = " + updateCount[i]
2612: + ". Expected 10");
2613: conn.rollback();
2614: return false;
2615: }
2616: }
2617:
2618: stmt
2619: .execute("select cast(x as int) as myint from assocout order by myint");
2620: rs = stmt.getResultSet();
2621: for (i = 10; rs.next(); i++) {
2622: if (rs.getInt(1) != i) {
2623: System.out
2624: .println("ERROR: didn't find value "
2625: + i
2626: + " in assocout table. It would appear that associated parameters aren't working correctly");
2627: stmt.execute("select x from assocout order by x");
2628: dumpRS(stmt.getResultSet());
2629: conn.rollback();
2630: return false;
2631: }
2632: }
2633: if (i != 40) {
2634: System.out
2635: .println("ERROR: expected to get 30 values from assocout, but got "
2636: + (i - 10)
2637: + " instead. It would appear that associated parameters aren't working correctly");
2638: stmt.execute("select x from assocout order by x");
2639: dumpRS(stmt.getResultSet());
2640: conn.rollback();
2641: return false;
2642: }
2643:
2644: stmt.executeUpdate("delete from assocout");
2645:
2646: ps = conn
2647: .prepareStatement("insert into assocout select x from assoc where x like ?");
2648: ps.setString(1, "%");
2649: ps.addBatch();
2650: ps.setString(1, "666666");
2651: ps.addBatch();
2652: ps.setString(1, "%");
2653: ps.addBatch();
2654: updateCount = ps.executeBatch();
2655: if (updateCount.length != 3) {
2656: System.out.println("ERROR: unexpected updateCount2 length "
2657: + updateCount.length);
2658: conn.rollback();
2659: return false;
2660: }
2661:
2662: stmt.execute("select count(x) from assocout");
2663: rs = stmt.getResultSet();
2664: rs.next();
2665: if (rs.getInt(1) != 100) {
2666: System.out
2667: .println("ERROR: count from assocout is not 100 as expected, it is "
2668: + rs.getString(1)
2669: + ". This is after executing like queries using '%'");
2670: stmt.execute("select x from assocout order by x");
2671: dumpRS(stmt.getResultSet());
2672: conn.rollback();
2673: return false;
2674: }
2675:
2676: return true;
2677: }
2678:
2679: // lifted from the metadata test
2680: private static void dumpRS(ResultSet s) throws SQLException {
2681: if (s == null) {
2682: System.out.println("<NULL>");
2683: return;
2684: }
2685:
2686: ResultSetMetaData rsmd = s.getMetaData();
2687:
2688: // Get the number of columns in the result set
2689: int numCols = rsmd.getColumnCount();
2690:
2691: if (numCols <= 0) {
2692: System.out.println("(no columns!)");
2693: return;
2694: }
2695:
2696: StringBuffer heading = new StringBuffer("\t ");
2697: StringBuffer underline = new StringBuffer("\t ");
2698:
2699: int len;
2700: // Display column headings
2701: for (int i = 1; i <= numCols; i++) {
2702: if (i > 1) {
2703: heading.append(",");
2704: underline.append(" ");
2705: }
2706: len = heading.length();
2707: heading.append(rsmd.getColumnLabel(i));
2708: len = heading.length() - len;
2709: for (int j = len; j > 0; j--) {
2710: underline.append("-");
2711: }
2712: }
2713: System.out.println(heading.toString());
2714: System.out.println(underline.toString());
2715:
2716: StringBuffer row = new StringBuffer();
2717: // Display data, fetching until end of the result set
2718: while (s.next()) {
2719: row.append("\t{");
2720: // Loop through each column, getting the
2721: // column data and displaying
2722: for (int i = 1; i <= numCols; i++) {
2723: if (i > 1)
2724: row.append(",");
2725: row.append(s.getString(i));
2726: }
2727: row.append("}\n");
2728: }
2729: System.out.println(row.toString());
2730: s.close();
2731: }
2732: }
|