Java API By Example, From Geeks To Geeks.

1 /* 2 3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.batchUpdate 4 5    Licensed to the Apache Software Foundation (ASF) under one or more 6    contributor license agreements. See the NOTICE file distributed with 7    this work for additional information regarding copyright ownership. 8    The ASF licenses this file to You under the Apache License, Version 2.0 9    (the "License"); you may not use this file except in compliance with 10    the License. You may obtain a copy of the License at 11 12       http://www.apache.org/licenses/LICENSE-2.0 13 14    Unless required by applicable law or agreed to in writing, software 15    distributed under the License is distributed on an "AS IS" BASIS, 16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 17    See the License for the specific language governing permissions and 18    limitations under the License. 19 20  */ 21 22 package org.apache.derbyTesting.functionTests.tests.jdbcapi; 23 24 import java.sql.BatchUpdateException ; 25 import java.sql.Connection ; 26 import java.sql.CallableStatement ; 27 import java.sql.Date ; 28 import java.sql.PreparedStatement ; 29 import java.sql.ResultSet ; 30 import java.sql.ResultSetMetaData ; 31 import java.sql.Statement ; 32 import java.sql.SQLException ; 33 import java.sql.Time ; 34 import java.sql.Timestamp ; 35 import java.sql.Types ; 36 37 import org.apache.derby.tools.ij; 38 import org.apache.derby.tools.JDBCDisplayUtil; 39 import org.apache.derbyTesting.functionTests.util.BigDecimalHandler; 40 41 public class batchUpdate { 42      43     private static boolean HAVE_BIG_DECIMAL; 44     private static String CLASS_NAME; 45      46     //Get the class name to be used for the procedures 47 //outparams - J2ME; outparams30 - non-J2ME 48 static{ 49         if(BigDecimalHandler.representation != BigDecimalHandler.BIGDECIMAL_REPRESENTATION) 50             HAVE_BIG_DECIMAL = false; 51         else 52             HAVE_BIG_DECIMAL = true; 53         if(HAVE_BIG_DECIMAL) 54             CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams30."; 55         else 56             CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams."; 57     } 58 59     public static void main(String [] args) { 60         boolean passed = true; 61         Connection conn = null; 62     Connection conn2 = null; 63         try { 64             System.out.println("Test batchUpdate starting"); 65 66             // use the ij utility to read the property file and 67 // make the initial connection. 68 ij.getPropertyArg(args); 69             conn = ij.startJBMS(); 70 71             conn2 = ij.startJBMS(); 72             passed = runTests( conn, conn2); 73         } catch (SQLException se) { 74             passed = false; 75             dumpSQLExceptions(se); 76         } catch (Throwable e) { 77             System.out.println("FAIL -- unexpected exception caught in main():\n"); 78             System.out.println(e.getMessage()); 79             e.printStackTrace(); 80             passed = false; 81         } 82 83         if (passed) 84             System.out.println("PASS"); 85 86         System.out.println("Test batchUpdate finished"); 87   } 88 89     // the runTests method is also used by the wascache/wsc_batchUpdate.java test. 90 public static boolean runTests( Connection conn, Connection conn2) 91     { 92         boolean passed = true; 93         Statement stmt = null; 94         Statement stmt2 = null; 95 96         try 97         { 98             conn.setAutoCommit(false); 99             stmt = conn.createStatement(); 100             conn2.setAutoCommit(false); 101             stmt2 = conn2.createStatement(); 102 103             /* Create the table and do any other set-up */ 104             passed = passed && setUpTest(conn, stmt); 105 106             // Positive tests for statement batch update 107 passed = passed && statementBatchUpdatePositive(conn, stmt); 108 109             // Negative tests for statement batch update 110 passed = passed && statementBatchUpdateNegative(conn, stmt, conn2, stmt2); 111 112             // Positive tests for callable statement batch update 113 passed = passed && callableStatementBatchUpdate(conn, stmt); 114 115             // Positive tests for prepared statement batch update 116 passed = passed && preparedStatementBatchUpdatePositive(conn, stmt); 117 118             // Negative tests for prepared statement batch update 119 passed = passed && preparedStatementBatchUpdateNegative(conn, stmt, conn2, stmt2); 120         } 121         catch (SQLException se) 122         { 123             passed = false; 124             dumpSQLExceptions(se); 125         } 126         catch (Throwable e) 127         { 128             System.out.println("FAIL -- unexpected exception caught in main():\n"); 129             System.out.println(e.getMessage()); 130             e.printStackTrace(); 131             passed = false; 132         } 133         finally 134         { 135             /* Test is finished - clean up after ourselves */ 136             passed = passed && cleanUp(conn, stmt); 137         } 138         return passed; 139     } // end of runTests 140 141     static boolean callableStatementBatchUpdate( Connection conn, Statement stmt) 142         throws SQLException 143     { 144         boolean passed = true; 145 146     //try callable statements 147 passed = passed && runCallableStatementBatch(conn); 148 149     //try callable statement with output parameters 150 passed = passed && runCallableStatementWithOutputParamBatch(conn); 151 152     return passed; 153   } 154 155 156     /** 157      * Positive tests for statement batch update. 158      * 159      * @param conn The connection to use. 160      * 161      * @return Whether or not we were successful. 162      * 163      * @exception SQLException Thrown if some unexpected error happens 164      */ 165     static boolean statementBatchUpdatePositive( Connection conn, Statement stmt) 166         throws SQLException 167     { 168         boolean passed = true; 169 170     //try executing a batch which nothing in it. 171 passed = passed && runEmptyStatementBatch(conn, stmt); 172 173     //try executing a batch which one statement in it. 174 passed = passed && runSingleStatementBatch(conn, stmt); 175 176     //try executing a batch with 3 different statements in it. 177 passed = passed && runMultipleStatementsBatch(conn, stmt); 178 179     //try executing a batch with 1000 statements in it. 180 passed = passed && run1000StatementsBatch(conn, stmt); 181 182     //try batch with autocommit true 183 passed = passed && runAutoCommitTrueBatch(conn, stmt); 184 185     //try clear batch 186 passed = passed && runCombinationsOfClearBatch(conn, stmt); 187 188     // confirm associated parameters run ok with batches 189 passed = passed && checkAssociatedParams(conn, stmt); 190      191     conn.commit(); 192 193     return passed; 194   } 195 196     /** 197      * Negative tests for statement batch update. 198      * 199      * @param conn The connection to use. 200      * 201      * @return Whether or not we were successful. 202      * 203      * @exception SQLException Thrown if some unexpected error happens 204      */ 205     static boolean statementBatchUpdateNegative( Connection conn, Statement stmt, 206     Connection conn2, Statement stmt2) throws SQLException 207     { 208         boolean passed = true; 209 210     //statements which will return a resultset are not allowed in batch update 211 //the following case should throw an exception for select. Below trying 212 //various placements of select statement in the batch, ie as 1st stmt, 213 //nth stat and last stmt 214 passed = passed && runStatementWithResultSetBatch(conn, stmt); 215 216     //try executing a batch with regular statement intermingled. 217 passed = passed && runStatementNonBatchStuffInBatch(conn, stmt); 218 219     //Below trying various placements of overflow update statement in the batch, ie 220 //as 1st stmt, nth stat and last stmt 221 passed = passed && runStatementWithErrorsBatch(conn, stmt); 222 223     //try transaction error, in this particular case time out while getting the lock 224 passed = passed && runTransactionErrorBatch(conn, stmt, conn2, stmt2); 225 226     return passed; 227   } 228 229     /** 230      * Positive tests for prepared statement batch update. 231      * 232      * @param conn The connection to use. 233      * 234      * @return Whether or not we were successful. 235      * 236      * @exception SQLException Thrown if some unexpected error happens 237      */ 238     static boolean preparedStatementBatchUpdatePositive(Connection conn, Statement stmt) 239         throws SQLException 240     { 241         boolean passed = true; 242 243     //try executing a batch which nothing in it. 244 passed = passed && runEmptyValueSetPreparedBatch(conn, stmt); 245 246     //try executing a batch with no parameters. 247 passed = passed && runNoParametersPreparedBatch(conn, stmt); 248 249     //try executing a batch which one parameter set in it. 250 passed = passed && runSingleValueSetPreparedBatch(conn, stmt); 251 252     //try executing a batch with 3 parameter sets in it. 253 passed = passed && runMultipleValueSetPreparedBatch(conn, stmt); 254 255     //try executing a batch with 2 parameter sets in it and they are set to null. 256 passed = passed && runMultipleValueSetNullPreparedBatch(conn, stmt); 257 258     //try executing a batch with 1000 statements in it. 259 passed = passed && run1000ValueSetPreparedBatch(conn, stmt); 260 261     //try executing batches with various rollback and commit combinations. 262 passed = passed && runPreparedStatRollbackAndCommitCombinations(conn, stmt); 263 264     //try prepared statement batch with autocommit true 265 passed = passed && runAutoCommitTruePreparedStatBatch(conn, stmt); 266 267     //try clear batch 268 passed = passed && runCombinationsOfClearPreparedStatBatch(conn, stmt); 269 270     return passed; 271   } 272 273     /** 274      * Negative tests for prepared statement batch update. 275      * 276      * @param conn The connection to use. 277      * 278      * @return Whether or not we were successful. 279      * 280      * @exception SQLException Thrown if some unexpected error happens 281      */ 282     static boolean preparedStatementBatchUpdateNegative(Connection conn, Statement stmt, 283     Connection conn2, Statement stmt2) throws SQLException 284     { 285         boolean passed = true; 286 287     //statements which will return a resultset are not allowed in batch update 288 //the following case should throw an exception for select. 289 passed = passed && runPreparedStmtWithResultSetBatch(conn, stmt); 290 291     //try executing a batch with regular statement intermingled. 292 passed = passed && runPreparedStmtNonBatchStuffInBatch(conn, stmt); 293 294     //Below trying various placements of overflow update statement in the batch 295 passed = passed && runPreparedStmtWithErrorsBatch(conn, stmt); 296 297     //try transaction error, in this particular case time out while getting the lock 298 passed = passed && runTransactionErrorPreparedStmtBatch(conn, stmt, conn2, stmt2); 299 300     return passed; 301   } 302 303 304     static public void dumpSQLExceptions (SQLException se) { 305         System.out.println("FAIL -- unexpected exception"); 306         while (se != null) { 307             System.out.print("SQLSTATE("+se.getSQLState()+"):"); 308             se.printStackTrace(); 309             se = se.getNextException(); 310         } 311     } 312 313     /** 314      * Check to make sure that the given SQLException is an exception 315      * with the expected sqlstate. 316      * 317      * @param e The SQLException to check 318      * @param SQLState The sqlstate to look for 319      * 320      * @return true means the exception is the expected one 321      */ 322 323     private static boolean checkException(SQLException e, 324                                             String SQLState) 325     { 326         String state; 327         String nextState; 328         SQLException next; 329         boolean passed = true; 330 331         state = e.getSQLState(); 332 333 334         if (! SQLState.equals(state)) { 335                 System.out.println("FAIL -- unexpected exception " + e + 336                     "sqlstate: " + state + SQLState); 337                 passed = false; 338             } 339 340         return passed; 341     } 342 343     /** 344      * Clean up after ourselves when testing is done. 345      * 346      * @param conn The Connection 347      * @param s A Statement on the Connection 348      * 349      * @return true if it succeeds, false if it doesn't 350      * 351      * @exception SQLException Thrown if some unexpected error happens 352      */ 353 354     static boolean cleanUp(Connection conn, Statement s) { 355     boolean passed = true; 356         try { 357             /* Drop the table we created */ 358             if (s != null) 359             { 360                 s.execute("drop table t1"); 361                 s.execute("drop table datetab"); 362                 s.execute("drop table timetab"); 363                 s.execute("drop table timestamptab"); 364                 s.execute("drop table usertypetab"); 365                 s.execute("drop procedure Integ"); 366             } 367 368             /* Close the connection */ 369             if (conn != null) { 370                 conn.rollback(); 371                 conn.close(); 372             } 373         } catch (Throwable e) { 374             System.out.println("FAIL -- unexpected exception caught in cleanup()"); 375             JDBCDisplayUtil.ShowException(System.out, e); 376             passed = false; 377         } 378 379         return passed; 380     } 381 382   //Below trying placements of overflow update statement in the batch 383 static boolean runPreparedStmtWithErrorsBatch(Connection conn, Statement stmt) throws SQLException { 384     boolean passed = true; 385     int updateCount[] = null; 386     ResultSet rs; 387     PreparedStatement pStmt = null; 388 389     stmt.executeUpdate("insert into t1 values(1)"); 390 391         try 392         { 393       System.out.println("Negative Prepared Stat: testing overflow as first set of values"); 394       pStmt = conn.prepareStatement("update t1 set c1=(? + 1)"); 395       pStmt.setInt(1, java.lang.Integer.MAX_VALUE); 396       pStmt.addBatch(); 397       updateCount = pStmt.executeBatch(); 398             passed = false; 399         } catch (SQLException sqle) { 400             /* Check to be sure the exception is the one we expect */ 401             passed = passed && checkException(sqle, "22003"); 402       if (sqle instanceof BatchUpdateException ) { 403         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 404         if (updateCount != null) { 405           if (updateCount.length != 0) { 406             System.out.println("ERROR: Overflow is first statement in the batch, so there shouldn't have been any update count"); 407             passed = false; 408           } 409         } 410       } 411         } 412 413     rs = stmt.executeQuery("select count(*) from t1"); 414     rs.next(); 415     if(rs.getInt(1) != 1) { 416       System.out.println("ERROR: There should been 1 row in the table, but found " + rs.getInt(1) + " rows"); 417       passed = false; 418     } 419     rs.close(); 420 421         try 422         { 423       System.out.println("Negative Prepared Stat: testing overflow as nth set of values"); 424       pStmt = conn.prepareStatement("update t1 set c1=(? + 1)"); 425       pStmt.setInt(1, 1); 426       pStmt.addBatch(); 427       pStmt.setInt(1, java.lang.Integer.MAX_VALUE); 428       pStmt.addBatch(); 429       pStmt.setInt(1, 1); 430       pStmt.addBatch(); 431       updateCount = pStmt.executeBatch(); 432             passed = false; 433         } catch (SQLException sqle) { 434             /* Check to be sure the exception is the one we expect */ 435             passed = passed && checkException(sqle, "22003"); 436       if (sqle instanceof BatchUpdateException ) { 437         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 438         if (updateCount.length != 1) { 439           System.out.println("ERROR: Overflow is second statement in the batch, so there should have been only 1 update count"); 440           passed = false; 441         } 442         for (int i=0; i<updateCount.length; i++) { 443           if (updateCount[i] != 1) { 444             System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 445             passed = false; 446           } 447         } 448       } 449         } 450 451     rs = stmt.executeQuery("select count(*) from t1"); 452     rs.next(); 453     if(rs.getInt(1) != 1) { 454       System.out.println("There should been 1 row in the table, but found " + rs.getInt(1) + " rows"); 455       passed = false; 456     } 457     rs.close(); 458 459         try 460         { 461       //trying select as the last statement 462 System.out.println("Negative Prepared Stat: testing overflow as last set of values"); 463       pStmt = conn.prepareStatement("update t1 set c1=(? + 1)"); 464       pStmt.setInt(1, 1); 465       pStmt.addBatch(); 466       pStmt.setInt(1, 1); 467       pStmt.addBatch(); 468       pStmt.setInt(1, java.lang.Integer.MAX_VALUE); 469       pStmt.addBatch(); 470       updateCount = pStmt.executeBatch(); 471             passed = false; 472         } 473         catch (SQLException sqle) { 474             /* Check to be sure the exception is the one we expect */ 475             passed = passed && checkException(sqle, "22003"); 476       if (sqle instanceof BatchUpdateException ) { 477         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 478         if (updateCount.length != 2) { 479           System.out.println("ERROR: Overflow is last statement in the batch, so there should have been only 2 update count"); 480           passed = false; 481         } 482         for (int i=0; i<updateCount.length; i++) { 483           if (updateCount[i] != 1) { 484             System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 485             passed = false; 486           } 487         } 488       } 489         } 490 491     rs = stmt.executeQuery("select count(*) from t1"); 492     rs.next(); 493     if(rs.getInt(1) != 1) { 494       System.out.println("There should been 1 row in the table, but found " + rs.getInt(1) + " rows"); 495       passed = false; 496     } 497     rs.close(); 498     pStmt.close(); 499 500     stmt.executeUpdate("delete from t1"); 501     conn.commit(); 502     return(passed); 503   } 504 505   //Below trying various placements of overflow update statement in the batch, ie 506 //as 1st stmt, nth stat and last stmt 507 static boolean runStatementWithErrorsBatch(Connection conn, Statement stmt) throws SQLException { 508     boolean passed = true; 509     int updateCount[] = null; 510     ResultSet rs; 511 512     stmt.executeUpdate("insert into t1 values(1)"); 513 514         try 515         { 516       //trying select as the first statement 517 System.out.println("Negative Statement: statement testing overflow error as first stat in the batch"); 518       stmt.addBatch("update t1 set c1=2147483647 + 1"); 519       stmt.addBatch("insert into t1 values(1)"); 520       updateCount = stmt.executeBatch(); 521             passed = false; 522         } catch (SQLException sqle) { 523             /* Check to be sure the exception is the one we expect */ 524             passed = passed && checkException(sqle, "22003"); 525       if (sqle instanceof BatchUpdateException ) { 526         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 527         if (updateCount != null) { 528           if (updateCount.length != 0) { 529             System.out.println("ERROR: Overflow is first statement in the batch, so there shouldn't have been any update count"); 530             passed = false; 531           } 532         } 533       } 534         } 535 536     rs = stmt.executeQuery("select count(*) from t1"); 537     rs.next(); 538     if(rs.getInt(1) != 1) { 539       System.out.println("ERROR: There should been 1 row in the table, but found " + rs.getInt(1) + " rows"); 540       passed = false; 541     } 542     rs.close(); 543 544         try 545         { 546       //trying select as the nth statement 547 System.out.println("Negative Statement: statement testing overflow error as nth stat in the batch"); 548       stmt.addBatch("insert into t1 values(1)"); 549       stmt.addBatch("update t1 set c1=2147483647 + 1"); 550       stmt.addBatch("insert into t1 values(1)"); 551       updateCount = stmt.executeBatch(); 552             passed = false; 553         } catch (SQLException sqle) { 554             /* Check to be sure the exception is the one we expect */ 555             passed = passed && checkException(sqle, "22003"); 556       if (sqle instanceof BatchUpdateException ) { 557         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 558         if (updateCount.length != 1) { 559           System.out.println("ERROR: Update is second statement in the batch, so there should have been only 1 update count"); 560           passed = false; 561         } 562         for (int i=0; i<updateCount.length; i++) { 563           if (updateCount[i] != 1) { 564             System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 565             passed = false; 566           } 567         } 568       } 569         } 570 571     rs = stmt.executeQuery("select count(*) from t1"); 572     rs.next(); 573     if(rs.getInt(1) != 2) { 574       System.out.println("There should been 2 row in the table, but found " + rs.getInt(1) + " rows"); 575       passed = false; 576     } 577     rs.close(); 578 579         try 580         { 581       //trying select as the last statement 582 System.out.println("Negative Statement: statement testing overflow error as last stat in the batch"); 583       stmt.addBatch("insert into t1 values(1)"); 584       stmt.addBatch("insert into t1 values(1)"); 585       stmt.addBatch("update t1 set c1=2147483647 + 1"); 586       updateCount = stmt.executeBatch(); 587             passed = false; 588         } 589         catch (SQLException sqle) { 590             /* Check to be sure the exception is the one we expect */ 591             passed = passed && checkException(sqle, "22003"); 592       if (sqle instanceof BatchUpdateException ) { 593         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 594         if (updateCount.length != 2) { 595           System.out.println("ERROR: Update is last statement in the batch, so there should have been only 2 update count"); 596           passed = false; 597         } 598         for (int i=0; i<updateCount.length; i++) { 599           if (updateCount[i] != 1) { 600             System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 601             passed = false; 602           } 603         } 604       } 605         } 606 607     rs = stmt.executeQuery("select count(*) from t1"); 608     rs.next(); 609     if(rs.getInt(1) != 4) { 610       System.out.println("There should been 4 rows in the table, but found " + rs.getInt(1) + " rows"); 611       passed = false; 612     } 613     rs.close(); 614 615     stmt.executeUpdate("delete from t1"); 616     conn.commit(); 617     return(passed); 618   } 619 620   //try transaction error, in this particular case time out while getting the lock 621 static boolean runTransactionErrorPreparedStmtBatch(Connection conn, Statement stmt, 622     Connection conn2, Statement stmt2) throws SQLException { 623     boolean passed = true; 624     int updateCount[] = null; 625     ResultSet rs; 626 627         try 628         { 629       System.out.println("Negative Prepared Stat: testing transaction error, time out while getting the lock"); 630 631       stmt.execute("insert into t1 values(1)"); 632       stmt2.execute("insert into t1 values(2)"); 633 634       PreparedStatement pStmt1 = conn.prepareStatement("update t1 set c1=3 where c1=?"); 635       pStmt1.setInt(1, 2); 636       pStmt1.addBatch(); 637 638       PreparedStatement pStmt2 = conn.prepareStatement("update t1 set c1=4 where c1=?"); 639       pStmt2.setInt(1, 1); 640       pStmt2.addBatch(); 641 642       pStmt1.executeBatch(); 643       pStmt2.executeBatch(); 644         } catch (SQLException sqle) { 645             /* Check to be sure the exception is time out while getting the lock related */ 646             passed = passed && checkException(sqle, "40XL1"); 647       if (sqle instanceof BatchUpdateException ) { 648         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 649         if (updateCount != null) { 650           if (updateCount.length != 0) { 651             System.out.println("ERROR: first statement in the batch caused time out while getting the lock, so there shouldn't have been any update count"); 652             passed = false; 653           } 654         } 655       } 656         } 657 658     conn.rollback(); 659     conn2.rollback(); 660     stmt.executeUpdate("delete from t1"); 661     conn.commit(); 662     return passed; 663   } 664 665   //try transaction error, in this particular case time out while getting the lock 666 static boolean runTransactionErrorBatch(Connection conn, Statement stmt, 667     Connection conn2, Statement stmt2) throws SQLException { 668     boolean passed = true; 669     int updateCount[] = null; 670     ResultSet rs; 671 672         try 673         { 674       System.out.println("Negative Statement: statement testing time out while getting the lock in the batch"); 675 676       stmt.execute("insert into t1 values(1)"); 677       stmt2.execute("insert into t1 values(2)"); 678 679       stmt.addBatch("update t1 set c1=3 where c1=2"); 680       stmt2.addBatch("update t1 set c1=4 where c1=1"); 681 682       stmt.executeBatch(); 683       updateCount = stmt2.executeBatch(); 684         } catch (SQLException sqle) { 685             /* Check to be sure the exception is time out while getting the lock related */ 686             passed = passed && checkException(sqle, "40XL1"); 687       if (sqle instanceof BatchUpdateException ) { 688         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 689         if (updateCount != null) { 690           if (updateCount.length != 0) { 691             System.out.println("ERROR: first statement in the batch caused time out while getting the lock, so there shouldn't have been any update count"); 692             passed = false; 693           } 694         } 695       } 696         } 697 698     conn.rollback(); 699     conn2.rollback(); 700     stmt.clearBatch(); 701     stmt2.clearBatch(); 702     stmt.executeUpdate("delete from t1"); 703     conn.commit(); 704     return passed; 705   } 706 707   //statements which will return a resultset are not allowed in batch update 708 //the following case should throw an exception for select. 709 static boolean runPreparedStmtWithResultSetBatch(Connection conn, Statement stmt) throws SQLException { 710     boolean passed = true; 711     int updateCount[] = null; 712     ResultSet rs; 713 714         try 715         { 716       //trying select as the first statement 717 System.out.println("Negative Prepared Stat: testing select in the batch"); 718       PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?"); 719       pStmt.setInt(1, 1); 720       pStmt.addBatch(); 721       updateCount = pStmt.executeBatch(); 722             passed = false; 723         } catch (SQLException sqle) { 724             /* Check to be sure the exception is the ResultSetReturnNotAllowed */ 725             passed = passed && checkException(sqle, "X0Y79"); 726         if (sqle instanceof BatchUpdateException ) { 727           updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 728           if (updateCount != null) { 729             if (updateCount.length != 0) { 730               System.out.println("ERROR: Select is first statement in the batch, so there shouldn't have been any update count"); 731               passed = false; 732             } 733           } 734       } 735         } 736 737     rs = stmt.executeQuery("select count(*) from t1"); 738     rs.next(); 739     if(rs.getInt(1) != 0) { 740       System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 741       passed = false; 742     } 743     rs.close(); 744 745     stmt.executeUpdate("delete from t1"); 746     conn.commit(); 747     return passed; 748   } 749 750   //try executing a batch with regular statement intermingled. 751 static boolean runPreparedStmtNonBatchStuffInBatch(Connection conn, Statement stmt) throws SQLException { 752     boolean passed = true; 753     int updateCount[] = null; 754     ResultSet rs; 755 756         try 757         { 758       //trying execute in the middle of batch 759 System.out.println("Negative Prepared Stat: testing execute in the middle of batch"); 760       PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?"); 761       pStmt.setInt(1, 1); 762       pStmt.addBatch(); 763       pStmt.execute(); 764       updateCount = pStmt.executeBatch(); 765             passed = false; 766         } catch (SQLException sqle) { 767             /* Check to be sure the exception is the MIDDLE_OF_BATCH */ 768             passed = passed && checkException(sqle, "XJ068"); 769             // do clearBatch so we can proceed 770 if (checkException(sqle, "XJ068")) 771         stmt.clearBatch(); 772         } 773 774     rs = stmt.executeQuery("select count(*) from t1"); 775     rs.next(); 776     if(rs.getInt(1) != 0) { 777       System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 778       passed = false; 779     } 780     rs.close(); 781 782         try 783         { 784       //trying executeQuery in the middle of batch 785 System.out.println("Negative Prepared Stat: testing executeQuery in the middle of batch"); 786       PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?"); 787       pStmt.setInt(1, 1); 788       pStmt.addBatch(); 789       pStmt.executeQuery(); 790       updateCount = pStmt.executeBatch(); 791             passed = false; 792         } catch (SQLException sqle) { 793             /* Check to be sure the exception is the MIDDLE_OF_BATCH */ 794             passed = passed && checkException(sqle, "XJ068"); 795             // do clearBatch so we can proceed 796 if (checkException(sqle, "XJ068")) 797         stmt.clearBatch(); 798         } 799 800     rs = stmt.executeQuery("select count(*) from t1"); 801     rs.next(); 802     if(rs.getInt(1) != 0) { 803       System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 804       passed = false; 805     } 806     rs.close(); 807 808         try 809         { 810       //trying executeUpdate in the middle of batch 811 System.out.println("Negative Prepared Stat: testing executeUpdate in the middle of batch"); 812       PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?"); 813       pStmt.setInt(1, 1); 814       pStmt.addBatch(); 815       pStmt.executeUpdate(); 816       updateCount = pStmt.executeBatch(); 817             passed = false; 818         } catch (SQLException sqle) { 819             /* Check to be sure the exception is the MIDDLE_OF_BATCH */ 820             passed = passed && checkException(sqle, "XJ068"); 821             // do clearBatch so we can proceed 822 if (checkException(sqle, "XJ068")) 823         stmt.clearBatch(); 824         } 825 826     rs = stmt.executeQuery("select count(*) from t1"); 827     rs.next(); 828     if(rs.getInt(1) != 0) { 829       System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 830       passed = false; 831     } 832     rs.close(); 833 834     stmt.executeUpdate("delete from t1"); 835     conn.commit(); 836     return passed; 837   } 838 839   //statements which will return a resultset are not allowed in batch update 840 //the following case should throw an exception for select. Below trying 841 //various placements of select statement in the batch, ie as 1st stmt, 842 //nth stat and last stmt 843 static boolean runStatementWithResultSetBatch(Connection conn, Statement stmt) throws SQLException { 844     boolean passed = true; 845     int updateCount[] = null; 846     ResultSet rs; 847 848         try 849         { 850       //trying select as the first statement 851 System.out.println("Negative Statement: statement testing select as first stat in the batch"); 852       stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS"); 853       stmt.addBatch("insert into t1 values(1)"); 854       updateCount = stmt.executeBatch(); 855             passed = false; 856         } catch (SQLException sqle) { 857             /* Check to be sure the exception is the ResultSetReturnNotAllowed */ 858             passed = passed && checkException(sqle, "X0Y79"); 859         if (sqle instanceof BatchUpdateException ) { 860           updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 861           if (updateCount != null) { 862             if (updateCount.length != 0) { 863               System.out.println("ERROR: Select is first statement in the batch, so there shouldn't have been any update count"); 864               passed = false; 865             } 866           } 867       } 868         } 869 870     rs = stmt.executeQuery("select count(*) from t1"); 871     rs.next(); 872     if(rs.getInt(1) != 0) { 873       System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 874       passed = false; 875     } 876     rs.close(); 877 878         try 879         { 880       //trying select as the nth statement 881 System.out.println("Negative Statement: statement testing select as nth stat in the batch"); 882       stmt.addBatch("insert into t1 values(1)"); 883       stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS"); 884       stmt.addBatch("insert into t1 values(1)"); 885       updateCount = stmt.executeBatch(); 886             passed = false; 887         } catch (SQLException sqle) { 888             /* Check to be sure the exception is the ResultSetReturnNotAllowed */ 889             passed = passed && checkException(sqle, "X0Y79"); 890       if (sqle instanceof BatchUpdateException ) { 891         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 892         if (updateCount.length != 1) { 893           System.out.println("ERROR: Select is second statement in the batch, so there should have been only 1 update count"); 894           passed = false; 895         } 896         for (int i=0; i<updateCount.length; i++) { 897           if (updateCount[i] != 1) { 898             System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 899             passed = false; 900           } 901         } 902       } 903         } 904 905     rs = stmt.executeQuery("select count(*) from t1"); 906     rs.next(); 907     if(rs.getInt(1) != 1) { 908       System.out.println("There should been 1 row in the table, but found " + rs.getInt(1) + " rows"); 909       passed = false; 910     } 911     rs.close(); 912 913         try 914         { 915       //trying select as the last statement 916 System.out.println("Negative Statement: statement testing select as last stat in the batch"); 917       stmt.addBatch("insert into t1 values(1)"); 918       stmt.addBatch("insert into t1 values(1)"); 919       stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS"); 920       updateCount = stmt.executeBatch(); 921             passed = false; 922         } catch (SQLException sqle) { 923             /* Check to be sure the exception is the ResultSetReturnNotAllowed */ 924             passed = passed && checkException(sqle, "X0Y79"); 925       if (sqle instanceof BatchUpdateException ) { 926         updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 927         if (updateCount.length != 2) { 928           System.out.println("ERROR: Select is last statement in the batch, so there should have been only 2 update count"); 929           passed = false; 930         } 931         for (int i=0; i<updateCount.length; i++) { 932           if (updateCount[i] != 1) { 933             System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 934             passed = false; 935           } 936         } 937       } 938         } 939 940     rs = stmt.executeQuery("select count(*) from t1"); 941     rs.next(); 942     if(rs.getInt(1) != 3) { 943       System.out.println("There should been 3 row in the table, but found " + rs.getInt(1) + " rows"); 944       passed = false; 945     } 946     rs.close(); 947 948     conn.rollback(); 949 950     rs = stmt.executeQuery("select count(*) from t1"); 951     rs.next(); 952     if(rs.getInt(1) != 0) { 953       System.out.println("There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 954       passed = false; 955     } 956     rs.close(); 957 958     stmt.executeUpdate("delete from t1"); 959     conn.commit(); 960     return passed; 961   } 962 963   //try executing a batch with regular statement intermingled. 964 static boolean runStatementNonBatchStuffInBatch(Connection conn, Statement stmt) throws SQLException { 965     boolean passed = true; 966     int updateCount[] = null; 967     ResultSet rs; 968 969         try 970         { 971       //trying execute after addBatch 972 System.out.println("Negative Statement: statement testing execute in the middle of batch"); 973       stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS"); 974       stmt.execute("insert into t1 values(1)"); 975       stmt.addBatch("insert into t1 values(1)"); 976       updateCount = stmt.executeBatch(); 977             passed = false; 978         } catch (SQLException sqle) { 979             /* Check to be sure the exception is the MIDDLE_OF_BATCH */ 980             passed = passed && checkException(sqle, "XJ068"); 981             // do clearBatch so we can proceed 982 if (checkException(sqle, "XJ068")) 983         stmt.clearBatch(); 984         } 985 986     rs = stmt.executeQuery("select count(*) from t1"); 987     rs.next(); 988     if(rs.getInt(1) != 0) { 989       System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 990       passed = false; 991     } 992     rs.close(); 993 994         try 995         { 996       //trying executeQuery after addBatch 997 System.out.println("Negative Statement: statement testing executeQuery in the middle of batch"); 998       stmt.addBatch("insert into t1 values(1)"); 999       stmt.executeQuery("SELECT * FROM SYS.SYSTABLES"); 1000      updateCount = stmt.executeBatch(); 1001            passed = false; 1002        } catch (SQLException sqle) { 1003            /* Check to be sure the exception is the MIDDLE_OF_BATCH */ 1004            passed = passed && checkException(sqle, "XJ068"); 1005            // do clearBatch so we can proceed 1006 if (checkException(sqle, "XJ068")) 1007        stmt.clearBatch(); 1008        } 1009 1010    rs = stmt.executeQuery("select count(*) from t1"); 1011    rs.next(); 1012    if(rs.getInt(1) != 0) { 1013      System.out.println("There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 1014      passed = false; 1015    } 1016    rs.close(); 1017 1018        try 1019        { 1020      //trying executeUpdate after addBatch 1021 System.out.println("Negative Statement: statement testing executeUpdate in the middle of batch"); 1022      stmt.addBatch("insert into t1 values(1)"); 1023      stmt.executeUpdate("insert into t1 values(1)"); 1024      stmt.addBatch("insert into t1 values(1)"); 1025      stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS"); 1026      updateCount = stmt.executeBatch(); 1027            passed = false; 1028        } catch (SQLException sqle) { 1029            /* Check to be sure the exception is the MIDDLE_OF_BATCH */ 1030            passed = passed && checkException(sqle, "XJ068"); 1031            // do clearBatch so we can proceed 1032 if (checkException(sqle, "XJ068")) 1033        stmt.clearBatch(); 1034        } 1035 1036    rs = stmt.executeQuery("select count(*) from t1"); 1037    rs.next(); 1038    if(rs.getInt(1) != 0) { 1039      System.out.println("There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 1040      passed = false; 1041    } 1042    rs.close(); 1043 1044    conn.rollback(); 1045 1046    rs = stmt.executeQuery("select count(*) from t1"); 1047    rs.next(); 1048    if(rs.getInt(1) != 0) { 1049      System.out.println("There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 1050      passed = false; 1051    } 1052    rs.close(); 1053 1054    stmt.executeUpdate("delete from t1"); 1055    conn.commit(); 1056    return passed; 1057  } 1058 1059  //try executing batches with various rollback and commit combinations. 1060 static boolean runPreparedStatRollbackAndCommitCombinations(Connection conn, Statement stmt) throws SQLException { 1061    boolean passed = true; 1062    int updateCount[]; 1063    ResultSet rs; 1064 1065    System.out.println("Positive Prepared Stat: batch, rollback, batch and commit combinations"); 1066    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 1067    pStmt.setInt(1, 1); 1068    pStmt.addBatch(); 1069    pStmt.setInt(1, 1); 1070    pStmt.addBatch(); 1071    updateCount = pStmt.executeBatch(); 1072 1073    if (updateCount.length != 2) { 1074      System.out.println("ERROR: there were 2 statements in the batch"); 1075      passed = false; 1076    } 1077 1078    for (int i=0; i<updateCount.length; i++) { 1079      if (updateCount[i] != 1) { 1080        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1081        passed = false; 1082      } 1083    } 1084 1085    conn.rollback(); 1086 1087    rs = stmt.executeQuery("select count(*) from t1"); 1088    rs.next(); 1089    if(rs.getInt(1) != 0) { 1090      System.out.println("ERROR: There should have been 0 rows"); 1091      passed = false; 1092    } 1093    rs.close(); 1094 1095    pStmt.setInt(1, 1); 1096    pStmt.addBatch(); 1097    pStmt.setInt(1, 1); 1098    pStmt.addBatch(); 1099    updateCount = pStmt.executeBatch(); 1100 1101    if (updateCount.length != 2) { 1102      System.out.println("ERROR: there were 2 statements in the batch"); 1103      passed = false; 1104    } 1105 1106    for (int i=0; i<updateCount.length; i++) { 1107      if (updateCount[i] != 1) { 1108        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1109        passed = false; 1110      } 1111    } 1112 1113    conn.commit(); 1114 1115    rs = stmt.executeQuery("select count(*) from t1"); 1116    rs.next(); 1117    if(rs.getInt(1) != 2) { 1118      System.out.println("ERROR: There should have been 2 rows"); 1119      passed = false; 1120    } 1121    rs.close(); 1122 1123    //try batch and commit 1124 System.out.println("Positive Prepared Stat: batch and commit combinations"); 1125    pStmt.setInt(1, 1); 1126    pStmt.addBatch(); 1127    pStmt.setInt(1, 1); 1128    pStmt.addBatch(); 1129    updateCount = pStmt.executeBatch(); 1130 1131    if (updateCount.length != 2) { 1132      System.out.println("ERROR: there were 2 statements in the batch"); 1133      passed = false; 1134    } 1135 1136    for (int i=0; i<updateCount.length; i++) { 1137      if (updateCount[i] != 1) { 1138        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1139        passed = false; 1140      } 1141    } 1142 1143    conn.commit(); 1144 1145    rs = stmt.executeQuery("select count(*) from t1"); 1146    rs.next(); 1147    if(rs.getInt(1) != 4) { 1148      System.out.println("ERROR: There should have been 4 rows"); 1149      passed = false; 1150    } 1151    rs.close(); 1152 1153    //try batch, batch and rollback 1154 System.out.println("Positive Prepared Stat: batch, batch and rollback combinations"); 1155    pStmt.setInt(1, 1); 1156    pStmt.addBatch(); 1157    pStmt.setInt(1, 1); 1158    pStmt.addBatch(); 1159    updateCount = pStmt.executeBatch(); 1160 1161    if (updateCount.length != 2) { 1162      System.out.println("ERROR: there were 2 statements in the batch"); 1163      passed = false; 1164    } 1165 1166    for (int i=0; i<updateCount.length; i++) { 1167      if (updateCount[i] != 1) { 1168        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1169        passed = false; 1170      } 1171    } 1172 1173    pStmt.setInt(1, 1); 1174    pStmt.addBatch(); 1175    pStmt.setInt(1, 1); 1176    pStmt.addBatch(); 1177    updateCount = pStmt.executeBatch(); 1178 1179    if (updateCount.length != 2) { 1180      System.out.println("ERROR: there were 2 statements in the batch"); 1181      passed = false; 1182    } 1183 1184    for (int i=0; i<updateCount.length; i++) { 1185      if (updateCount[i] != 1) { 1186        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1187        passed = false; 1188      } 1189    } 1190 1191    conn.rollback(); 1192 1193    rs = stmt.executeQuery("select count(*) from t1"); 1194    rs.next(); 1195    if(rs.getInt(1) != 4) { 1196      System.out.println("ERROR: There should have been 4 rows"); 1197      passed = false; 1198    } 1199    rs.close(); 1200 1201    //try batch, batch and commit 1202 System.out.println("Positive Prepared Stat: batch, batch and commit combinations"); 1203    pStmt.setInt(1, 1); 1204    pStmt.addBatch(); 1205    pStmt.setInt(1, 1); 1206    pStmt.addBatch(); 1207    updateCount = pStmt.executeBatch(); 1208 1209    if (updateCount.length != 2) { 1210      System.out.println("ERROR: there were 2 statements in the batch"); 1211      passed = false; 1212    } 1213 1214    for (int i=0; i<updateCount.length; i++) { 1215      if (updateCount[i] != 1) { 1216        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1217        passed = false; 1218      } 1219    } 1220 1221    pStmt.setInt(1, 1); 1222    pStmt.addBatch(); 1223    pStmt.setInt(1, 1); 1224    pStmt.addBatch(); 1225    updateCount = pStmt.executeBatch(); 1226 1227    if (updateCount.length != 2) { 1228      System.out.println("ERROR: there were 2 statements in the batch"); 1229      passed = false; 1230    } 1231 1232    for (int i=0; i<updateCount.length; i++) { 1233      if (updateCount[i] != 1) { 1234        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1235        passed = false; 1236      } 1237    } 1238 1239    conn.commit(); 1240 1241    rs = stmt.executeQuery("select count(*) from t1"); 1242    rs.next(); 1243    if(rs.getInt(1) != 8) { 1244      System.out.println("ERROR: There should have been 8 rows"); 1245      passed = false; 1246    } 1247    rs.close(); 1248 1249    pStmt.close(); 1250 1251    stmt.executeUpdate("delete from t1"); 1252    conn.commit(); 1253    return passed; 1254  } 1255 1256  //try executing batches with various rollback and commit combinations. 1257 static boolean runRollbackAndCommitCombinations(Connection conn, Statement stmt) throws SQLException { 1258    boolean passed = true; 1259    int updateCount[]; 1260    ResultSet rs; 1261 1262    System.out.println("Positive Statement: batch, rollback, batch and commit combinations"); 1263    stmt.addBatch("insert into t1 values(1)"); 1264    stmt.addBatch("insert into t1 values(1)"); 1265    updateCount = stmt.executeBatch(); 1266 1267    if (updateCount.length != 2) { 1268      System.out.println("ERROR: there were 2 statements in the batch"); 1269      passed = false; 1270    } 1271 1272    for (int i=0; i<updateCount.length; i++) { 1273      if (updateCount[i] != 1) { 1274        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1275        passed = false; 1276      } 1277    } 1278 1279    conn.rollback(); 1280 1281    rs = stmt.executeQuery("select count(*) from t1"); 1282    rs.next(); 1283    if(rs.getInt(1) != 0) { 1284      System.out.println("ERROR: There should have been 0 rows"); 1285      passed = false; 1286    } 1287    rs.close(); 1288 1289    stmt.addBatch("insert into t1 values(1)"); 1290    stmt.addBatch("insert into t1 values(1)"); 1291    updateCount = stmt.executeBatch(); 1292 1293    if (updateCount.length != 2) { 1294      System.out.println("ERROR: there were 2 statements in the batch"); 1295      passed = false; 1296    } 1297 1298    for (int i=0; i<updateCount.length; i++) { 1299      if (updateCount[i] != 1) { 1300        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1301        passed = false; 1302      } 1303    } 1304 1305    conn.commit(); 1306 1307    rs = stmt.executeQuery("select count(*) from t1"); 1308    rs.next(); 1309    if(rs.getInt(1) != 2) { 1310      System.out.println("ERROR: There should have been 2 rows"); 1311      passed = false; 1312    } 1313    rs.close(); 1314 1315    //try batch and commit 1316 System.out.println("Positive Statement: batch and commit combinations"); 1317    stmt.addBatch("insert into t1 values(1)"); 1318    stmt.addBatch("insert into t1 values(1)"); 1319    updateCount = stmt.executeBatch(); 1320 1321    if (updateCount.length != 2) { 1322      System.out.println("ERROR: there were 2 statements in the batch"); 1323      passed = false; 1324    } 1325 1326    for (int i=0; i<updateCount.length; i++) { 1327      if (updateCount[i] != 1) { 1328        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1329        passed = false; 1330      } 1331    } 1332 1333    conn.commit(); 1334 1335    rs = stmt.executeQuery("select count(*) from t1"); 1336    rs.next(); 1337    if(rs.getInt(1) != 4) { 1338      System.out.println("ERROR: There should have been 4 rows"); 1339      passed = false; 1340    } 1341    rs.close(); 1342 1343    //try batch, batch and rollback 1344 System.out.println("Positive Statement: batch, batch and rollback combinations"); 1345    stmt.addBatch("insert into t1 values(1)"); 1346    stmt.addBatch("insert into t1 values(1)"); 1347    updateCount = stmt.executeBatch(); 1348 1349    if (updateCount.length != 2) { 1350      System.out.println("ERROR: there were 2 statements in the batch"); 1351      passed = false; 1352    } 1353 1354    for (int i=0; i<updateCount.length; i++) { 1355      if (updateCount[i] != 1) { 1356        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1357        passed = false; 1358      } 1359    } 1360 1361    stmt.addBatch("insert into t1 values(1)"); 1362    stmt.addBatch("insert into t1 values(1)"); 1363    updateCount = stmt.executeBatch(); 1364 1365    if (updateCount.length != 2) { 1366      System.out.println("ERROR: there were 2 statements in the batch"); 1367      passed = false; 1368    } 1369 1370    for (int i=0; i<updateCount.length; i++) { 1371      if (updateCount[i] != 1) { 1372        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1373        passed = false; 1374      } 1375    } 1376 1377    conn.rollback(); 1378 1379    rs = stmt.executeQuery("select count(*) from t1"); 1380    rs.next(); 1381    if(rs.getInt(1) != 4) { 1382      System.out.println("ERROR: There should have been 4 rows"); 1383      passed = false; 1384    } 1385    rs.close(); 1386 1387    //try batch, batch and commit 1388 System.out.println("Positive Statement: batch, batch and rollback combinations"); 1389    stmt.addBatch("insert into t1 values(1)"); 1390    stmt.addBatch("insert into t1 values(1)"); 1391    updateCount = stmt.executeBatch(); 1392 1393    if (updateCount.length != 2) { 1394      System.out.println("ERROR: there were 2 statements in the batch"); 1395      passed = false; 1396    } 1397 1398    for (int i=0; i<updateCount.length; i++) { 1399      if (updateCount[i] != 1) { 1400        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1401        passed = false; 1402      } 1403    } 1404 1405    stmt.addBatch("insert into t1 values(1)"); 1406    stmt.addBatch("insert into t1 values(1)"); 1407    updateCount = stmt.executeBatch(); 1408 1409    if (updateCount.length != 2) { 1410      System.out.println("ERROR: there were 2 statements in the batch"); 1411      passed = false; 1412    } 1413 1414    for (int i=0; i<updateCount.length; i++) { 1415      if (updateCount[i] != 1) { 1416        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1417        passed = false; 1418      } 1419    } 1420 1421    conn.commit(); 1422 1423    rs = stmt.executeQuery("select count(*) from t1"); 1424    rs.next(); 1425    if(rs.getInt(1) != 8) { 1426      System.out.println("ERROR: There should have been 8 rows"); 1427      passed = false; 1428    } 1429    rs.close(); 1430 1431    stmt.executeUpdate("delete from t1"); 1432    conn.commit(); 1433    return passed; 1434  } 1435 1436  //try prepared statement batch with autocommit true 1437 static boolean runAutoCommitTruePreparedStatBatch(Connection conn, Statement stmt) throws SQLException { 1438    boolean passed = true; 1439    int updateCount[]; 1440    ResultSet rs; 1441 1442        conn.setAutoCommit(true); 1443   //prepared statement batch with autocommit true 1444 System.out.println("Positive Prepared Stat: testing batch with autocommit true"); 1445    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 1446    pStmt.setInt(1, 1); 1447    pStmt.addBatch(); 1448    pStmt.setInt(1, 1); 1449    pStmt.addBatch(); 1450    pStmt.setInt(1, 1); 1451    pStmt.addBatch(); 1452    updateCount = pStmt.executeBatch(); 1453 1454    if (updateCount.length != 3) { 1455      System.out.println("ERROR: there were 3 statements in the batch"); 1456      passed = false; 1457    } 1458 1459    for (int i=0; i<updateCount.length; i++) { 1460      if (updateCount[i] != 1) { 1461        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1462        passed = false; 1463      } 1464    } 1465 1466    rs = stmt.executeQuery("select count(*) from t1"); 1467    rs.next(); 1468    if(rs.getInt(1) != 3) { 1469      System.out.println("ERROR: There should been 3 rows in the table, but found " + rs.getInt(1) + " rows"); 1470      passed = false; 1471    } 1472    rs.close(); 1473    pStmt.close(); 1474 1475    //turn it true again after the above negative test 1476 conn.setAutoCommit(false); 1477 1478    stmt.executeUpdate("delete from t1"); 1479    conn.commit(); 1480    return passed; 1481  } 1482 1483    //try batch with autocommit true 1484 static boolean runAutoCommitTrueBatch(Connection conn, Statement stmt) throws SQLException { 1485    boolean passed = true; 1486    int updateCount[]; 1487    ResultSet rs; 1488 1489        conn.setAutoCommit(true); 1490     //try batch with autocommit true 1491 System.out.println("Positive Statement: statement testing batch with autocommit true"); 1492    stmt.addBatch("insert into t1 values(1)"); 1493    stmt.addBatch("insert into t1 values(1)"); 1494    stmt.addBatch("delete from t1"); 1495    updateCount = stmt.executeBatch(); 1496 1497    if (updateCount.length != 3) { 1498      System.out.println("ERROR: there were 3 statements in the batch"); 1499      passed = false; 1500    } 1501 1502    for (int i=0; i<(updateCount.length-1); i++) { 1503      if (updateCount[i] != 1) { 1504        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1505        passed = false; 1506      } 1507    } 1508    if (updateCount[2] != 2) { 1509      System.out.println("ERROR: update count for stat 2 should have been 2 but it is " + updateCount[2]); 1510      passed = false; 1511    } 1512 1513    rs = stmt.executeQuery("select count(*) from t1"); 1514    rs.next(); 1515    if(rs.getInt(1) != 0) { 1516      System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows"); 1517      passed = false; 1518    } 1519    rs.close(); 1520 1521    //turn it true again after the above negative test 1522 conn.setAutoCommit(false); 1523 1524    stmt.executeUpdate("delete from t1"); 1525    conn.commit(); 1526    return passed; 1527  } 1528 1529  //try callable statements with output parameters 1530 static boolean runCallableStatementWithOutputParamBatch(Connection conn) throws SQLException { 1531    boolean passed = true; 1532    int updateCount[] = null; 1533    ResultSet rs; 1534 1535    System.out.println("Negative Callable Statement: callable statement with output parameters in the batch"); 1536        Statement s = conn.createStatement(); 1537 1538        s.execute("CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) " + 1539                "EXTERNAL NAME '" + CLASS_NAME + "takesString'" + 1540                " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA"); 1541 1542    CallableStatement cs = conn.prepareCall("call takesString(?,?)"); 1543        try 1544        { 1545 1546        cs.registerOutParameter(1, Types.CHAR); 1547        cs.setInt(2, Types.INTEGER); 1548    cs.addBatch(); 1549        System.out.println("FAIL - addBatch() allowed with registered out parameter"); 1550        passed = false; 1551        } 1552        catch (SQLException sqle) 1553        { 1554            // Check to be sure the exception is callback related 1555 passed = passed && checkException(sqle, "XJ04C"); 1556        } 1557 1558    cs.close(); 1559                s.execute("drop procedure takesString"); 1560                s.close(); 1561    conn.rollback(); 1562    conn.commit(); 1563    return passed; 1564  } 1565 1566  //try callable statements 1567 static boolean runCallableStatementBatch(Connection conn) throws SQLException { 1568    boolean passed = true; 1569    int updateCount[] = null; 1570    ResultSet rs; 1571 1572    System.out.println("Positive Callable Statement: statement testing callable statement batch"); 1573    CallableStatement cs = conn.prepareCall("insert into t1 values(?)"); 1574 1575    cs.setInt(1, 1); 1576    cs.addBatch(); 1577    cs.setInt(1,2); 1578    cs.addBatch(); 1579    try 1580    { 1581        passed = passed && executeBatchCallableStatement(cs); 1582    } 1583    catch (SQLException sqle) 1584    { 1585        /* Check to be sure the exception is callback related */ 1586        passed = passed && checkException(sqle, "XJ04C"); 1587        if (sqle instanceof BatchUpdateException ) 1588        { 1589            updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 1590            if (updateCount != null) 1591            { 1592                if (updateCount.length != 0) 1593                { 1594                    System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count"); 1595                    passed = false; 1596                } 1597            } 1598        } 1599    } 1600 1601    cleanUpCallableStatement(conn, cs, "t1"); 1602 1603    /* Bug 2813 - verify setXXXX() works with 1604     * Date, Time and Timestamp on CallableStatement. 1605     */ 1606    cs = conn.prepareCall("insert into datetab values(?)"); 1607 1608    cs.setDate(1, Date.valueOf("1990-05-05")); 1609    cs.addBatch(); 1610    cs.setDate(1,Date.valueOf("1990-06-06")); 1611    cs.addBatch(); 1612    try 1613    { 1614        passed = passed && executeBatchCallableStatement(cs); 1615    } 1616    catch (SQLException sqle) 1617    { 1618        /* Check to be sure the exception is callback related */ 1619        passed = passed && checkException(sqle, "XJ04C"); 1620        if (sqle instanceof BatchUpdateException ) 1621        { 1622            updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 1623            if (updateCount != null) 1624            { 1625                if (updateCount.length != 0) 1626                { 1627                    System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count"); 1628                    passed = false; 1629                } 1630            } 1631        } 1632    } 1633 1634    cleanUpCallableStatement(conn, cs, "datetab"); 1635  1636    cs = conn.prepareCall("insert into timetab values(?)"); 1637 1638    cs.setTime(1, Time.valueOf("11:11:11")); 1639    cs.addBatch(); 1640    cs.setTime(1, Time.valueOf("12:12:12")); 1641    cs.addBatch(); 1642    try 1643    { 1644        passed = passed && executeBatchCallableStatement(cs); 1645    } 1646    catch (SQLException sqle) 1647    { 1648        /* Check to be sure the exception is callback related */ 1649        passed = passed && checkException(sqle, "XJ04C"); 1650        if (sqle instanceof BatchUpdateException ) 1651        { 1652            updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 1653            if (updateCount != null) 1654            { 1655                if (updateCount.length != 0) 1656                { 1657                    System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count"); 1658                    passed = false; 1659                } 1660            } 1661        } 1662    } 1663 1664    cleanUpCallableStatement(conn, cs, "timestamptab"); 1665  1666    cs = conn.prepareCall("insert into timestamptab values(?)"); 1667 1668    cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1")); 1669    cs.addBatch(); 1670    cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2")); 1671    cs.addBatch(); 1672    try 1673    { 1674        passed = passed && executeBatchCallableStatement(cs); 1675    } 1676    catch (SQLException sqle) 1677    { 1678        /* Check to be sure the exception is callback related */ 1679        passed = passed && checkException(sqle, "XJ04C"); 1680        if (sqle instanceof BatchUpdateException ) 1681        { 1682            updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 1683            if (updateCount != null) 1684            { 1685                if (updateCount.length != 0) 1686                { 1687                    System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count"); 1688                    passed = false; 1689                } 1690            } 1691        } 1692    } 1693 1694    cleanUpCallableStatement(conn, cs, "timestamptab"); 1695 1696    // Try with a user type 1697 cs = conn.prepareCall("insert into usertypetab values(?)"); 1698 1699    cs.setObject(1, Date.valueOf("1990-05-05")); 1700    cs.addBatch(); 1701    cs.setObject(1,Date.valueOf("1990-06-06")); 1702    cs.addBatch(); 1703    try 1704    { 1705        passed = passed && executeBatchCallableStatement(cs); 1706    } 1707    catch (SQLException sqle) 1708    { 1709        /* Check to be sure the exception is callback related */ 1710        passed = passed && checkException(sqle, "XJ04C"); 1711        if (sqle instanceof BatchUpdateException ) 1712        { 1713            updateCount = ((BatchUpdateException )sqle).getUpdateCounts(); 1714            if (updateCount != null) 1715            { 1716                if (updateCount.length != 0) 1717                { 1718                    System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count"); 1719                    passed = false; 1720                } 1721            } 1722        } 1723    } 1724 1725    cleanUpCallableStatement(conn, cs, "usertypetab"); 1726  1727    return passed; 1728    } 1729 1730    private static boolean executeBatchCallableStatement(CallableStatement cs) 1731        throws SQLException 1732    { 1733        boolean passed = true; 1734        int updateCount[]; 1735 1736        updateCount = cs.executeBatch(); 1737        if (updateCount.length != 2) 1738        { 1739            System.out.println("ERROR: there were 2 statements in the batch"); 1740            passed = false; 1741        } 1742        for (int i=0; i<updateCount.length; i++) 1743        { 1744            if (updateCount[i] != 1) 1745            { 1746                System.out.println("ERROR: update count should have been 1 but it's " + updateCount[i]); 1747                passed = false; 1748            } 1749        } 1750 1751        return passed; 1752    } 1753 1754    private static void cleanUpCallableStatement(Connection conn, CallableStatement cs, String tableName) 1755        throws SQLException 1756    { 1757        cs.close(); 1758        conn.rollback(); 1759        cs = conn.prepareCall("delete from " + tableName); 1760        cs.executeUpdate(); 1761        cs.close(); 1762        conn.commit(); 1763    } 1764 1765  //try combinations of clear batch. 1766 static boolean runCombinationsOfClearPreparedStatBatch(Connection conn, Statement stmt) throws SQLException { 1767    boolean passed = true; 1768    int updateCount[]; 1769    ResultSet rs; 1770 1771    System.out.println("Positive Prepared Stat: add 3 statements, clear batch and execute batch"); 1772    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 1773    pStmt.setInt(1, 1); 1774    pStmt.addBatch(); 1775    pStmt.setInt(1, 2); 1776    pStmt.addBatch(); 1777    pStmt.setInt(1, 3); 1778    pStmt.addBatch(); 1779    pStmt.clearBatch(); 1780    updateCount = pStmt.executeBatch(); 1781 1782    if (updateCount.length != 0) { 1783      System.out.println("ERROR: there were 0 statements in the batch"); 1784      passed = false; 1785    } 1786     1787    rs = stmt.executeQuery("select count(*) from t1"); 1788    rs.next(); 1789    if(rs.getInt(1) != 0) { 1790      System.out.println("ERROR: There should been no rows in the table"); 1791      passed = false; 1792    } 1793    rs.close(); 1794 1795    System.out.println("Positive Prepared Stat: add 3 statements, clear batch, add 3 and execute batch"); 1796    pStmt.setInt(1, 1); 1797    pStmt.addBatch(); 1798    pStmt.setInt(1, 2); 1799    pStmt.addBatch(); 1800    pStmt.setInt(1, 3); 1801    pStmt.addBatch(); 1802    pStmt.clearBatch(); 1803    pStmt.setInt(1, 1); 1804    pStmt.addBatch(); 1805    pStmt.setInt(1, 2); 1806    pStmt.addBatch(); 1807    pStmt.setInt(1, 3); 1808    pStmt.addBatch(); 1809    updateCount = pStmt.executeBatch(); 1810 1811    if (updateCount.length != 3) { 1812      System.out.println("ERROR: there were 3 statements in the batch"); 1813      passed = false; 1814    } 1815     1816    rs = stmt.executeQuery("select count(*) from t1"); 1817    rs.next(); 1818    if(rs.getInt(1) != 3) { 1819      System.out.println("ERROR: There should been 3 rows in the table"); 1820      passed = false; 1821    } 1822    rs.close(); 1823    pStmt.close(); 1824 1825    stmt.executeUpdate("delete from t1"); 1826    conn.commit(); 1827    return passed; 1828  } 1829 1830  //try combinations of clear batch. 1831 static boolean runCombinationsOfClearBatch(Connection conn, Statement stmt) throws SQLException { 1832    boolean passed = true; 1833    int updateCount[]; 1834    ResultSet rs; 1835 1836    System.out.println("Positive Statement: add 3 statements, clear batch and execute batch"); 1837    stmt.addBatch("insert into t1 values(2)"); 1838    stmt.addBatch("insert into t1 values(2)"); 1839    stmt.addBatch("insert into t1 values(2)"); 1840    stmt.clearBatch(); 1841    updateCount = stmt.executeBatch(); 1842 1843    if (updateCount.length != 0) { 1844      System.out.println("ERROR: there were 0 statements in the batch"); 1845      passed = false; 1846    } 1847     1848    rs = stmt.executeQuery("select count(*) from t1"); 1849    rs.next(); 1850    if(rs.getInt(1) != 0) { 1851      System.out.println("ERROR: There should been no rows in the table"); 1852      passed = false; 1853    } 1854    rs.close(); 1855 1856    System.out.println("Positive Statement: add 3 statements, clear batch, add 3 and execute batch"); 1857    stmt.addBatch("insert into t1 values(2)"); 1858    stmt.addBatch("insert into t1 values(2)"); 1859    stmt.addBatch("insert into t1 values(2)"); 1860    stmt.clearBatch(); 1861    stmt.addBatch("insert into t1 values(2)"); 1862    stmt.addBatch("insert into t1 values(2)"); 1863    stmt.addBatch("insert into t1 values(2)"); 1864    updateCount = stmt.executeBatch(); 1865 1866    if (updateCount.length != 3) { 1867      System.out.println("ERROR: there were 3 statements in the batch"); 1868      passed = false; 1869    } 1870     1871    rs = stmt.executeQuery("select count(*) from t1"); 1872    rs.next(); 1873    if(rs.getInt(1) != 3) { 1874      System.out.println("ERROR: There should been 3 rows in the table"); 1875      passed = false; 1876    } 1877    rs.close(); 1878 1879    stmt.executeUpdate("delete from t1"); 1880    conn.commit(); 1881    return passed; 1882  } 1883 1884  //try executing a batch with 1000 statements in it. 1885 static boolean run1000ValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException { 1886    boolean passed = true; 1887    int updateCount[]; 1888    ResultSet rs; 1889 1890    System.out.println("Positive Prepared Stat: 1000 parameter set batch"); 1891    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 1892    for (int i=0; i<1000; i++){ 1893      pStmt.setInt(1, 1); 1894      pStmt.addBatch(); 1895    } 1896    updateCount = pStmt.executeBatch(); 1897     1898    if (updateCount.length != 1000) { 1899      System.out.println("ERROR: there were 1000 parameter sets in the batch"); 1900      passed = false; 1901    } 1902 1903    rs = stmt.executeQuery("select count(*) from t1"); 1904    rs.next(); 1905    if(rs.getInt(1) != 1000) { 1906      System.out.println("There should been 1000 rows in the table, but found " + rs.getInt(1) + " rows"); 1907      passed = false; 1908    } 1909    rs.close(); 1910 1911    pStmt.close(); 1912    stmt.executeUpdate("delete from t1"); 1913    conn.commit(); 1914    return passed; 1915  } 1916 1917  //try executing a batch with 1000 statements in it. 1918 static boolean run1000StatementsBatch(Connection conn, Statement stmt) throws SQLException { 1919    boolean passed = true; 1920    int updateCount[]; 1921    ResultSet rs; 1922 1923    System.out.println("Positive Statement: 1000 statements batch"); 1924    for (int i=0; i<1000; i++){ 1925      stmt.addBatch("insert into t1 values(1)"); 1926    } 1927    updateCount = stmt.executeBatch(); 1928     1929    if (updateCount.length != 1000) { 1930      System.out.println("ERROR: there were 1000 statements in the batch"); 1931      passed = false; 1932    } 1933 1934    rs = stmt.executeQuery("select count(*) from t1"); 1935    rs.next(); 1936    if(rs.getInt(1) != 1000) { 1937      System.out.println("There should been 1000 rows in the table, but found " + rs.getInt(1) + " rows"); 1938      passed = false; 1939    } 1940    rs.close(); 1941 1942    stmt.executeUpdate("delete from t1"); 1943    conn.commit(); 1944    return passed; 1945  } 1946 1947  //try executing a batch with 3 different parameter sets in it. 1948 static boolean runMultipleValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException { 1949    boolean passed = true; 1950    int updateCount[]; 1951    ResultSet rs; 1952 1953    //try prepared statement batch with just one set of values 1954 System.out.println("Positive Prepared Stat: set 3 set of parameter values and run the batch"); 1955    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 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    updateCount = pStmt.executeBatch(); 1963    if (updateCount.length != 3) { 1964      System.out.println("ERROR: there were 3 parameter sets in the batch"); 1965      passed = false; 1966    } 1967 1968    for (int i=0; i<updateCount.length; i++) { 1969      if (updateCount[i] != 1) { 1970        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 1971        passed = false; 1972      } 1973    } 1974 1975    pStmt.close(); 1976 1977    rs = stmt.executeQuery("select count(*) from t1"); 1978    rs.next(); 1979    if(rs.getInt(1) != 3) { 1980      System.out.println("ERROR: There should have been 3 rows"); 1981      passed = false; 1982    } 1983    rs.close(); 1984 1985    stmt.executeUpdate("delete from t1"); 1986    conn.commit(); 1987    return passed; 1988  } 1989 1990  //try executing a batch with 3 different statements in it. 1991 static boolean runMultipleStatementsBatch(Connection conn, Statement stmt) throws SQLException { 1992    boolean passed = true; 1993    int updateCount[]; 1994    ResultSet rs; 1995 1996    System.out.println("Positive Statement: testing 2 inserts and 1 update batch"); 1997    stmt.addBatch("insert into t1 values(2)"); 1998    stmt.addBatch("update t1 set c1=4"); 1999    stmt.addBatch("insert into t1 values(3)"); 2000 2001    updateCount = stmt.executeBatch(); 2002 2003    if (updateCount.length != 3) { 2004      System.out.println("ERROR: there were 3 statements in the batch"); 2005      passed = false; 2006    } 2007 2008    for (int i=0; i<updateCount.length; i++) { 2009      if (updateCount[i] != 1) { 2010        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 2011        passed = false; 2012      } 2013    } 2014 2015    rs = stmt.executeQuery("select count(*) from t1 where c1=2"); 2016    rs.next(); 2017    if(rs.getInt(1) != 0) { 2018      System.out.println("ERROR: There should have been 0 rows with c1 = 2"); 2019      passed = false; 2020    } 2021    rs.close(); 2022 2023    rs = stmt.executeQuery("select count(*) from t1 where c1=4"); 2024    rs.next(); 2025    if(rs.getInt(1) != 1) { 2026      System.out.println("ERROR: There should have been 1 row with c1 = 4"); 2027      passed = false; 2028    } 2029    rs.close(); 2030 2031    rs = stmt.executeQuery("select count(*) from t1 where c1=3"); 2032    rs.next(); 2033    if(rs.getInt(1) != 1) { 2034      System.out.println("ERROR: There should have been 1 row with c1 = 3"); 2035      passed = false; 2036    } 2037    rs.close(); 2038 2039    rs = stmt.executeQuery("select count(*) from t1"); 2040    rs.next(); 2041    if(rs.getInt(1) != 2) { 2042      System.out.println("ERROR: There should have been 2 rows"); 2043      passed = false; 2044    } 2045    rs.close(); 2046 2047    stmt.executeUpdate("delete from t1"); 2048    conn.commit(); 2049    return passed; 2050  } 2051 2052  //try prepared statement batch with just one set of values. 2053 static boolean runSingleValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException { 2054    boolean passed = true; 2055    int updateCount[]; 2056    ResultSet rs; 2057 2058    //try prepared statement batch with just one set of values 2059 System.out.println("Positive Prepared Stat: set one set of parameter values and run the batch"); 2060    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 2061    pStmt.setInt(1, 1); 2062    pStmt.addBatch(); 2063    updateCount = pStmt.executeBatch(); 2064    if (updateCount.length != 1) { 2065      System.out.println("ERROR: there was 1 parameter set in the batch"); 2066      passed = false; 2067    } 2068 2069    for (int i=0; i<updateCount.length; i++) { 2070      if (updateCount[i] != 1) { 2071        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 2072        passed = false; 2073      } 2074    } 2075 2076    pStmt.close(); 2077    rs = stmt.executeQuery("select count(*) from t1 where c1=1"); 2078    rs.next(); 2079    if(rs.getInt(1) != 1) { 2080      System.out.println("ERROR: There should have been one rows with c1 = 1"); 2081      passed = false; 2082    } 2083    rs.close(); 2084 2085    rs = stmt.executeQuery("select count(*) from t1"); 2086    rs.next(); 2087    if(rs.getInt(1) != 1) { 2088      System.out.println("ERROR: There should have been 1 row"); 2089      passed = false; 2090    } 2091    rs.close(); 2092 2093    stmt.executeUpdate("delete from t1"); 2094    conn.commit(); 2095    return passed; 2096  } 2097 2098  //try prepared statement batch with just no settable parameters. 2099 static boolean runNoParametersPreparedBatch(Connection conn, Statement stmt) throws SQLException { 2100    boolean passed = true; 2101    int updateCount[]; 2102    ResultSet rs; 2103 2104    System.out.println("Positive Prepared Stat: no settable parameters"); 2105    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(5)"); 2106    pStmt.addBatch(); 2107    pStmt.addBatch(); 2108    pStmt.addBatch(); 2109    updateCount = pStmt.executeBatch(); 2110    if (updateCount.length != 3) { 2111      System.out.println("ERROR: there was 3 parameter set in the batch"); 2112      passed = false; 2113    } 2114 2115    for (int i=0; i<updateCount.length; i++) { 2116      if (updateCount[i] != 1) { 2117        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 2118        passed = false; 2119      } 2120    } 2121 2122    pStmt.close(); 2123    rs = stmt.executeQuery("select count(*) from t1 where c1=5"); 2124    rs.next(); 2125    if(rs.getInt(1) != 3) { 2126      System.out.println("ERROR: There should have been three rows with c1 = 5"); 2127      passed = false; 2128    } 2129    rs.close(); 2130 2131    rs = stmt.executeQuery("select count(*) from t1"); 2132    rs.next(); 2133    if(rs.getInt(1) != 3) { 2134      System.out.println("ERROR: There should have been 3 rows"); 2135      passed = false; 2136    } 2137    rs.close(); 2138 2139    stmt.executeUpdate("delete from t1"); 2140    conn.commit(); 2141    return passed; 2142  } 2143   2144  //try prepared statement batch with just 2 set of values and there value is null. Bug 4002 2145 static boolean runMultipleValueSetNullPreparedBatch(Connection conn, Statement stmt) throws SQLException { 2146    boolean passed = true; 2147    int updateCount[]; 2148    ResultSet rs; 2149 2150    //try prepared statement batch with just one set of values 2151 System.out.println("Positive Prepared Stat: set one set of parameter values to null and run the batch"); 2152    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 2153    pStmt.setNull(1, Types.INTEGER); 2154    pStmt.addBatch(); 2155    pStmt.setNull(1, Types.INTEGER); 2156    pStmt.addBatch(); 2157    updateCount = pStmt.executeBatch(); 2158    if (updateCount.length != 2) { 2159      System.out.println("ERROR: there were 2 parameter set to null in the batch"); 2160      passed = false; 2161    } 2162 2163    for (int i=0; i<updateCount.length; i++) { 2164      if (updateCount[i] != 1) { 2165        System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]); 2166        passed = false; 2167      } 2168    } 2169 2170    pStmt.close(); 2171    rs = stmt.executeQuery("select count(*) from t1 where c1 is null"); 2172    rs.next(); 2173    if(rs.getInt(1) != 2) { 2174      System.out.println("ERROR: There should have been two rows with c1 is null"); 2175      passed = false; 2176    } 2177    rs.close(); 2178 2179    rs = stmt.executeQuery("select count(*) from t1"); 2180    rs.next(); 2181    if(rs.getInt(1) != 2) { 2182      System.out.println("ERROR: There should have been 2 rows"); 2183      passed = false; 2184    } 2185    rs.close(); 2186 2187    stmt.executeUpdate("delete from t1"); 2188    conn.commit(); 2189    return passed; 2190  } 2191 2192  //try executing a batch which single statement in it. Should work. 2193 static boolean runSingleStatementBatch(Connection conn, Statement stmt) throws SQLException { 2194    boolean passed = true; 2195    int updateCount[]; 2196 2197    System.out.println("Positive Statement: testing 1 statement batch"); 2198    stmt.addBatch("insert into t1 values(2)"); 2199    updateCount = stmt.executeBatch(); 2200 2201    if (updateCount.length>1) { 2202      System.out.println("ERROR: Since this is a single statement, there should have been only one update count"); 2203      passed = false; 2204    } 2205 2206    if (updateCount[0] != 1) { 2207      System.out.println("ERROR: update count should have been 1, instead it is " + updateCount[0]); 2208      passed = false; 2209    } 2210 2211    stmt.executeUpdate("delete from t1"); 2212    conn.commit(); 2213    return passed; 2214  } 2215 2216  //try executing a batch which nothing in it. Should work. 2217 static boolean runEmptyValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException { 2218    boolean passed = true; 2219    int updateCount[]; 2220 2221    //try executing a batch which nothing in it. Should work. 2222 System.out.println("Positive Prepared Stat: set no parameter values and run the batch"); 2223    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)"); 2224    updateCount = pStmt.executeBatch(); 2225 2226    if (updateCount.length != 0) { 2227      System.out.println("ERROR: update count should have been zero"); 2228      passed = false; 2229    } 2230 2231    pStmt.close(); 2232    stmt.executeUpdate("delete from t1"); 2233    conn.commit(); 2234    return passed; 2235  } 2236 2237  //try executing a batch which nothing in it. Should work. 2238 static boolean runEmptyStatementBatch(Connection conn, Statement stmt) throws SQLException { 2239    boolean passed = true; 2240    int updateCount[]; 2241 2242    //try executing a batch which nothing in it. Should work. 2243 System.out.println("Positive Statement: clear the batch and run the empty batch"); 2244    stmt.clearBatch(); 2245    updateCount = stmt.executeBatch(); 2246 2247    if (updateCount.length != 0) { 2248      System.out.println("ERROR: Since this is an empty statement, there shouldn't have been any update count"); 2249      passed = false; 2250    } 2251 2252    stmt.executeUpdate("delete from t1"); 2253    conn.commit(); 2254    return passed; 2255  } 2256 2257    /** 2258     * Set up the test. 2259     * 2260     * This method creates the table used by the rest of the test. 2261     * 2262     * @param conn The Connection 2263     * 2264     * @return true if it succeeds, false if it doesn't 2265     * 2266     * @exception SQLException Thrown if some unexpected error happens 2267     */ 2268 2269    static boolean setUpTest(Connection conn, Statement stmt) 2270                    throws SQLException 2271    { 2272        boolean passed = true; 2273        int rows; 2274 2275        /* Create a table */ 2276    stmt.addBatch("create table t1(c1 int)"); 2277    // stmt.addBatch("create class alias for java.lang.Integer"); 2278 stmt.addBatch("create procedure Integ() language java parameter style java external name 'java.lang.Integer'"); 2279    stmt.addBatch("create table datetab(c1 date)"); 2280    stmt.addBatch("create table timetab(c1 time)"); 2281    stmt.addBatch("create table timestamptab(c1 timestamp)"); 2282    stmt.addBatch("create table usertypetab(c1 DATE)"); 2283    stmt.executeBatch(); 2284 2285 2286    conn.commit(); 2287        return passed; 2288    } 2289 2290    /* 2291    ** Associated parameters are extra parameters that are created 2292    ** and associated with the root parameter (the user one) to 2293    ** improve the performance of like. For something like 2294    ** where c1 like ?, we generate extra 'associated' parameters 2295    ** that we use for predicates that we give to the access 2296    ** manager. 2297    */ 2298    static boolean checkAssociatedParams(Connection conn, Statement stmt) throws SQLException 2299    { 2300        int i; 2301        conn.setAutoCommit(false); 2302        System.out.println("Positive Statement: testing associated parameters"); 2303        stmt.executeUpdate("create table assoc(x char(10) not null primary key, y char(100))"); 2304        stmt.executeUpdate("create table assocout(x char(10))"); 2305        PreparedStatement ps = conn.prepareStatement("insert into assoc values (?, 'hello')"); 2306        for (i = 10; i < 60; i++) 2307        { 2308            ps.setString(1, new Integer (i).toString()); 2309            ps.executeUpdate(); 2310        } 2311 2312        ps = conn.prepareStatement("insert into assocout select x from assoc where x like ?"); 2313        ps.setString(1, "33%"); 2314        ps.addBatch(); 2315        ps.setString(1, "21%"); 2316        ps.addBatch(); 2317        ps.setString(1, "49%"); 2318        ps.addBatch(); 2319        int[] updateCount = ps.executeBatch(); 2320        if (updateCount.length != 3) 2321        { 2322            System.out.println("ERROR: unexpected updateCount length "+updateCount.length); 2323            conn.rollback(); 2324            return false; 2325        } 2326 2327        for (i = 0; i < 3; i++) 2328        { 2329            if (updateCount[i] != 1) 2330            { 2331                System.out.println("ERROR: unexpected updateCount["+i+"] value = "+updateCount[i]+". Expected 1"); 2332                conn.rollback(); 2333                return false; 2334            } 2335        } 2336        stmt.execute("select cast(x as int) as myint from assocout order by myint"); 2337        ResultSet rs = stmt.getResultSet(); 2338        for (i = 0; rs.next(); i++) 2339        { 2340            int expect = 0; 2341            switch (i) 2342            { 2343                case 0: 2344                    expect = 21; 2345                    break; 2346                case 1: 2347                    expect = 33; 2348                    break; 2349                case 2: 2350                    expect = 49; 2351                    break; 2352            } 2353            if (rs.getInt(1) != expect) 2354            { 2355                System.out.println("ERROR: didn't find value "+expect+" in assocout table. It would appear that associated parameters aren't working correctly"); 2356                conn.rollback(); 2357                return false; 2358            } 2359        } 2360        stmt.executeUpdate("delete from assocout"); 2361 2362        ps = conn.prepareStatement("insert into assocout select x from assoc where x like ?"); 2363        ps.setString(1, "3%"); 2364        ps.addBatch(); 2365        ps.setString(1, "2%"); 2366        ps.addBatch(); 2367        ps.setString(1, "1%"); 2368        ps.addBatch(); 2369        updateCount = ps.executeBatch(); 2370        if (updateCount.length != 3) 2371        { 2372            System.out.println("ERROR: unexpected updateCount2 length "+updateCount.length); 2373            conn.rollback(); 2374            return false; 2375        } 2376 2377        for (i = 0; i < 3; i++) 2378        { 2379            if (updateCount[i] != 10) 2380            { 2381                System.out.println("ERROR: unexpected updateCount2["+i+"] value = "+updateCount[i]+". Expected 10"); 2382                conn.rollback(); 2383                return false; 2384            } 2385        } 2386 2387        stmt.execute("select cast(x as int) as myint from assocout order by myint"); 2388        rs = stmt.getResultSet(); 2389        for (i = 10; rs.next(); i++) 2390        { 2391            if (rs.getInt(1) != i) 2392            { 2393                System.out.println("ERROR: didn't find value "+i+" in assocout table. It would appear that associated parameters aren't working correctly"); 2394                stmt.execute("select x from assocout order by x"); 2395                dumpRS(stmt.getResultSet()); 2396                conn.rollback(); 2397                return false; 2398            } 2399        } 2400        if (i != 40) 2401        { 2402            System.out.println("ERROR: expected to get 30 values from assocout, but got "+(i-10)+" instead. It would appear that associated parameters aren't working correctly"); 2403            stmt.execute("select x from assocout order by x"); 2404            dumpRS(stmt.getResultSet()); 2405            conn.rollback(); 2406            return false; 2407        } 2408         2409        stmt.executeUpdate("delete from assocout"); 2410 2411        ps = conn.prepareStatement("insert into assocout select x from assoc where x like ?"); 2412        ps.setString(1, "%"); 2413        ps.addBatch(); 2414        ps.setString(1, "666666"); 2415        ps.addBatch(); 2416        ps.setString(1, "%"); 2417        ps.addBatch(); 2418        updateCount = ps.executeBatch(); 2419        if (updateCount.length != 3) 2420        { 2421            System.out.println("ERROR: unexpected updateCount2 length "+updateCount.length); 2422            conn.rollback(); 2423            return false; 2424        } 2425 2426        stmt.execute("select count(x) from assocout"); 2427        rs = stmt.getResultSet(); 2428        rs.next(); 2429        if (rs.getInt(1) != 100) 2430        { 2431            System.out.println("ERROR: count from assocout is not 100 as expected, it is "+rs.getString(1)+". This is after executing like queries using '%'"); 2432            stmt.execute("select x from assocout order by x"); 2433            dumpRS(stmt.getResultSet()); 2434            conn.rollback(); 2435            return false; 2436        } 2437 2438 2439        return true; 2440    } 2441 2442    // lifted from the metadata test 2443 private static void dumpRS(ResultSet s) throws SQLException 2444    { 2445        if (s == null) 2446        { 2447            System.out.println("<NULL>"); 2448            return; 2449        } 2450 2451        ResultSetMetaData rsmd = s.getMetaData(); 2452 2453        // Get the number of columns in the result set 2454 int numCols = rsmd.getColumnCount(); 2455 2456        if (numCols <= 0) 2457        { 2458            System.out.println("(no columns!)"); 2459            return; 2460        } 2461 2462        StringBuffer heading = new StringBuffer ("\t "); 2463        StringBuffer underline = new StringBuffer ("\t "); 2464 2465        int len; 2466        // Display column headings 2467 for (int i=1; i<=numCols; i++) 2468        { 2469            if (i > 1) 2470            { 2471                heading.append(","); 2472                underline.append(" "); 2473            } 2474            len = heading.length(); 2475            heading.append(rsmd.getColumnLabel(i)); 2476            len = heading.length() - len; 2477            for (int j = len; j > 0; j--) 2478            { 2479                underline.append("-"); 2480            } 2481        } 2482        System.out.println(heading.toString()); 2483        System.out.println(underline.toString()); 2484         2485     2486        StringBuffer row = new StringBuffer (); 2487        // Display data, fetching until end of the result set 2488 while (s.next()) 2489        { 2490            row.append("\t{"); 2491            // Loop through each column, getting the 2492 // column data and displaying 2493 for (int i=1; i<=numCols; i++) 2494            { 2495                if (i > 1) row.append(","); 2496                row.append(s.getString(i)); 2497            } 2498            row.append("}\n"); 2499        } 2500        System.out.println(row.toString()); 2501        s.close(); 2502    } 2503} 2504

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags