1 21 22 package org.apache.derbyTesting.functionTests.tests.jdbcapi; 23 24 import java.sql.Connection ; 25 import java.sql.DriverManager ; 26 import java.sql.ResultSet ; 27 import java.sql.SQLException ; 28 import java.sql.Savepoint ; 29 import java.sql.Statement ; 30 31 import org.apache.derby.tools.ij; 32 import org.apache.derbyTesting.functionTests.util.TestUtil; 33 41 42 43 public class savepointJdbc30_JSR169 { 44 45 static private boolean isDerbyNet = false; 46 47 static private String [] testObjects = { "table t1", "table t2", "table savepoint"}; 48 49 50 51 public static void main(String [] args) { 52 Connection con = null, con2 = null; 53 Statement s; 54 System.out.println("Test savepointJdbc30 starting"); 55 56 try 57 { 58 ij.getPropertyArg(args); 61 con = ij.startJBMS(); 62 con2 = ij.startJBMS(); 63 runTests("regular connections", con,con2); 64 65 con.close(); 66 con2.close(); 67 68 } 69 catch (SQLException e) { 70 dumpSQLExceptions(e); 71 } 72 catch (Throwable e) { 73 System.out.println("FAIL -- unexpected exception:"); 74 e.printStackTrace(System.out); 75 } 76 77 78 } 79 80 public static void runTests(String tag, Connection con, Connection con2) 81 throws SQLException { 82 83 Statement s; 84 System.out.println("Test savepointJdbc30 starting for " + tag); 85 isDerbyNet = TestUtil.isNetFramework(); 86 con.setAutoCommit(true); con2.setAutoCommit(false); 88 s = con.createStatement(); 89 90 91 setUpTest(s); 92 93 System.out.println("Tests common to DRDA and embedded Cloudscape"); 100 genericTests(con, con2, s); 101 102 System.out.println("Next try non-DRDA tests"); 103 if (!isDerbyNet) 104 nonDRDATests(con, s); 105 106 107 con.setAutoCommit(true); 108 TestUtil.cleanUpTest(s, testObjects); 109 110 s.close(); 111 112 } 113 114 115 static void nonDRDATests(Connection con, Statement s) 120 throws SQLException { 121 ResultSet rs1, rs2, rs1WithHold, rs2WithHold; 122 Savepoint savepoint1, savepoint2, savepoint3, savepoint4; 123 124 con.setAutoCommit(false); 127 System.out.println("Test40 - named savepoint can't conflict with internally generated name for unnamed savepoints"); 132 savepoint1 = con.setSavepoint(); 133 savepoint2 = con.setSavepoint("i.SAVEPT0"); 134 con.rollback(); 135 136 System.out.println("Test41a - Rollback to a savepoint, then try to release savepoint created after that savepoint"); 138 139 savepoint1 = con.setSavepoint(); 140 s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); 141 142 savepoint2 = con.setSavepoint("s1"); 143 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 144 145 savepoint3 = con.setSavepoint("s2"); 146 s.executeUpdate("INSERT INTO T1 VALUES(3,1)"); 147 148 con.rollback(savepoint2); 150 rs1 = s.executeQuery("select count(*) from t1"); 151 rs1.next(); 152 if(rs1.getInt(1) != 1) { 153 System.out.println("ERROR: There should have been 1 row in the table, but found " + rs1.getInt(1) + " rows"); 154 return; 155 } 156 157 try 159 { 160 con.releaseSavepoint(savepoint3); 161 System.out.println("FAIL 41a release of rolled back savepoint"); 162 } 163 catch (SQLException se) { 164 System.out.println("Expected Exception is " + se.getMessage()); 165 } 166 167 System.out.println("Test41b - Rollback to a savepoint, then try to rollback savepoint created after that savepoint"); 169 try 170 { 171 con.rollback(savepoint3); 172 System.out.println("FAIL 41b release of rolled back savepoint"); 173 } 174 catch (SQLException se) { 175 System.out.println("Expected Exception is " + se.getMessage()); 176 } 177 178 con.rollback(savepoint1); 180 rs1 = s.executeQuery("select count(*) from t1"); 181 rs1.next(); 182 if(rs1.getInt(1) != 0) { 183 System.out.println("ERROR: There should have been no rows in the table, but found " + rs1.getInt(1) + " rows"); 184 return; 185 } 186 con.rollback(); 187 188 System.out.println("Test42 - Rollback/commit the transaction, then try to use savepoint from that transaction"); 190 savepoint1 = con.setSavepoint(); 191 savepoint2 = con.setSavepoint("s1"); 192 con.rollback(); 193 try { 194 con.rollback(savepoint1); 195 System.out.println("FAIL 42 release of rolled back savepoint"); 196 } catch (SQLException se) { 197 System.out.println("Expected Exception is " + se.getMessage()); 198 } 199 savepoint1 = con.setSavepoint(); 201 savepoint2 = con.setSavepoint("s1"); 202 con.commit(); 203 try { 204 con.rollback(savepoint1); 205 System.out.println("FAIL 42 rollback of rolled back savepoint"); 206 } catch (SQLException se) { 207 System.out.println("Expected Exception is " + se.getMessage()); 208 } 209 210 System.out.println("Test43 - Release and reuse a savepoint name"); 212 savepoint1 = con.setSavepoint("s1"); 213 try { 214 savepoint2 = con.setSavepoint("s1"); 215 System.out.println("FAIL 43"); 216 } catch (SQLException se) { 217 System.out.println("Expected Exception is " + se.getMessage()); 218 } 219 con.releaseSavepoint(savepoint1); 220 savepoint2 = con.setSavepoint("s1"); 221 con.rollback(); 222 223 System.out.println("Test 45 reuse savepoint name after rollback - should not work"); 225 savepoint1 = con.setSavepoint("MyName"); 226 con.rollback(savepoint1); 227 try { 228 savepoint2 = con.setSavepoint("MyName"); 229 System.out.println("FAIL 45 reuse of savepoint name after rollback should fail"); 230 } catch (SQLException se) { 231 System.out.println("Expected Exception is " + se.getMessage()); 232 } 233 con.rollback(); 234 235 System.out.println("Test 46 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint"); 237 Statement sWithHold = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT ); 238 con.setAutoCommit(false); 239 s.executeUpdate("DELETE FROM T1"); 240 s.executeUpdate("INSERT INTO T1 VALUES(19,1)"); 241 s.executeUpdate("INSERT INTO T1 VALUES(19,2)"); 242 s.executeUpdate("INSERT INTO T1 VALUES(19,3)"); 243 rs1 = s.executeQuery("select * from t1"); 244 rs1.next(); 245 rs1WithHold = sWithHold.executeQuery("select * from t1"); 246 rs1WithHold.next(); 247 savepoint1 = con.setSavepoint(); 248 rs2 = s.executeQuery("select * from t1"); 249 rs2.next(); 250 rs2WithHold = sWithHold.executeQuery("select * from t1"); 251 rs2WithHold.next(); 252 con.rollback(savepoint1); 253 try { rs1.next(); 255 System.out.println("FAIL 46 shouldn't be able to use a resultset (declared before the savepoint unit) after the rollback to savepoint"); 256 } catch (SQLException se) { 257 System.out.println("Expected Exception is " + se.getMessage()); 258 } 259 try { rs1WithHold.next(); 261 System.out.println("FAIL 46 shouldn't be able to use a holdable resultset (declared before the savepoint unit) after the rollback to savepoint"); 262 } catch (SQLException se) { 263 System.out.println("Expected Exception is " + se.getMessage()); 264 } 265 try { rs2.next(); 267 System.out.println("FAIL 46 shouldn't be able to use a resultset (declared within the savepoint unit) after the rollback to savepoint"); 268 } catch (SQLException se) { 269 System.out.println("Expected Exception is " + se.getMessage()); 270 } 271 try { rs2WithHold.next(); 273 System.out.println("FAIL 46 shouldn't be able to use a holdable resultset (declared within the savepoint unit) after the rollback to savepoint"); 274 } catch (SQLException se) { 275 System.out.println("Expected Exception is " + se.getMessage()); 276 } 277 con.rollback(); 278 279 System.out.println("Test 47 multiple tests for getSavepointId()"); 281 savepoint1 = con.setSavepoint(); 282 savepoint2 = con.setSavepoint(); 283 System.out.println(savepoint1.getSavepointId()); 284 System.out.println(savepoint2.getSavepointId()); 285 con.releaseSavepoint(savepoint2); 286 savepoint2 = con.setSavepoint(); 287 System.out.println(savepoint2.getSavepointId()); 288 con.commit(); 289 savepoint2 = con.setSavepoint(); 290 System.out.println(savepoint2.getSavepointId()); 291 con.rollback(); 292 savepoint2 = con.setSavepoint(); 293 System.out.println(savepoint2.getSavepointId()); 294 con.rollback(); 295 296 System.out.println("Test 48 No nested SQL savepoints allowed."); 298 savepoint1 = con.setSavepoint(); 299 savepoint2 = con.setSavepoint(); 300 System.out.println("Following SQL savepoint will fail because we are trying to nest it inside JDBC savepoint"); 301 try { 302 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 303 System.out.println("FAIL 48 shouldn't be able set SQL savepoint nested inside JDBC/SQL savepoints"); 304 } catch (SQLException se) { 305 System.out.println("Expected Exception is " + se.getMessage()); 306 } 307 con.releaseSavepoint(savepoint2); 309 try { 310 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 311 System.out.println("FAIL 48 Should have gotten exception for nested SQL savepoint"); 312 } catch (SQLException se) { 313 System.out.println("Expected Exception is " + se.getMessage()); 314 } 315 con.releaseSavepoint(savepoint1); s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 317 con.rollback(); 318 } 319 320 static void genericTests(Connection con, Connection con2, Statement s) 322 throws SQLException { 323 324 ResultSet rs1, rs2, rs1WithHold, rs2WithHold; 325 Savepoint savepoint1, savepoint2, savepoint3, savepoint4; 326 327 con.setAutoCommit(true); try 331 { 332 System.out.println("Test1 - no unnamed savepoints allowed if autocommit is true"); 333 con.setSavepoint(); System.out.println("FAIL 1 - auto commit on"); 335 } 336 catch (SQLException se) { 337 System.out.println("Expected Exception is " + se.getMessage()); 338 } 339 try { 341 System.out.println("Test1a - no named savepoints allowed if autocommit is true"); 342 con.setSavepoint("notallowed"); System.out.println("FAIL 1a - auto commit on"); 344 } catch (SQLException se) { 345 System.out.println("Expected Exception is " + se.getMessage()); 346 } 347 348 con.setAutoCommit(false); 350 System.out.println("Test2 - Release and reuse a savepoint name"); 352 savepoint1 = con.setSavepoint("s1"); 353 con.releaseSavepoint(savepoint1); 354 savepoint2 = con.setSavepoint("s1"); 355 con.rollback(); 356 357 try { 359 System.out.println("Test3 - null name not allowed for named savepoints"); 360 con.setSavepoint(null); 361 System.out.println("FAIL 3 null savepoint "); 362 } catch (SQLException se) { 363 System.out.println("Expected Exception is " + se.getMessage()); 364 } 365 con.rollback(); 366 367 System.out.println("Test4 - Verify names/ids of named/unnamed savepoints"); 372 try { 373 savepoint1 = con.setSavepoint(); 374 savepoint1.getSavepointId(); 375 savepoint1.getSavepointName(); 377 System.out.println("FAIL 4 getSavepointName on id savepoint "); 378 } catch (SQLException se) { 379 System.out.println("Expected Exception is " + se.getMessage()); 380 } 381 con.rollback(); 382 try { 383 savepoint1 = con.setSavepoint("s1"); 384 savepoint1.getSavepointName(); 385 savepoint1.getSavepointId(); 387 System.out.println("FAIL 4 getSavepointId on named savepoint "); 388 } catch (SQLException se) { 389 System.out.println("Expected Exception is " + se.getMessage()); 390 } 391 con.rollback(); 392 393 System.out.println("Test5a - create two savepoints in two different transactions" + 397 " and release the first one in the subsequent transaction"); 398 savepoint1 = con.setSavepoint("s1"); 399 con.commit(); 400 savepoint2 = con.setSavepoint("s2"); 408 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 409 try { 410 con.releaseSavepoint(savepoint1); 411 System.out.println("FAIL 5a - release savepoint from a different transaction did not raise error"); 412 } catch (SQLException se) { 413 System.out.println("Expected Exception is " + se.getMessage()); 414 } 415 con.commit(); 416 417 System.out.println("Test5b - create two savepoints in two different transactions" + 420 " and rollback the first one in the subsequent transaction"); 421 savepoint1 = con.setSavepoint("s1"); 422 con.commit(); 423 savepoint2 = con.setSavepoint("s2"); 431 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 432 try { 433 con.rollback(savepoint1); 434 System.out.println("FAIL 5b - rollback savepoint from a different transaction did not raise error"); 435 } catch (SQLException se) { 436 System.out.println("Expected Exception is " + se.getMessage()); 437 } 438 con.commit(); 439 440 System.out.println("Test6a - create a savepoint, release it, create another with" + 443 " same name and release the first one"); 444 savepoint1 = con.setSavepoint("s1"); 445 con.releaseSavepoint(savepoint1); 446 savepoint2 = con.setSavepoint("s2"); 452 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 453 try { 454 con.releaseSavepoint(savepoint1); 455 System.out.println("FAIL 6a - releasing a released savepoint did not raise error"); 456 } catch (SQLException se) { 457 System.out.println("Expected Exception is " + se.getMessage()); 458 } 459 con.commit(); 460 461 System.out.println("Test6b - create a savepoint, release it, create another with" + 464 " same name and rollback the first one"); 465 savepoint1 = con.setSavepoint("s1"); 466 con.releaseSavepoint(savepoint1); 467 savepoint2 = con.setSavepoint("s2"); 473 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 474 try { 475 con.rollback(savepoint1); 476 System.out.println("FAIL 6b - rollback a released savepoint did not raise error"); 477 } catch (SQLException se) { 478 System.out.println("Expected Exception is " + se.getMessage()); 479 } 480 con.commit(); 481 482 495 496 System.out.println("Test6c - Try to use a savepoint from another connection for release"); 497 savepoint1 = con.setSavepoint("s1"); 498 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 499 try { 500 con2.releaseSavepoint(savepoint1); 501 System.out.println("FAIL 6c - releasing another transaction's savepoint did not raise error"); 502 } catch (SQLException se) { 503 System.out.println("Expected Exception is " + se.getMessage()); 504 } 505 con.commit(); 506 con2.commit(); 507 508 509 System.out.println("Test7a - swap savepoints across connections with release"); 511 savepoint1 = con2.setSavepoint("s1"); 512 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 513 savepoint2 = con.setSavepoint("s1"); 514 try { 515 con.releaseSavepoint(savepoint1); 516 System.out.println("FAIL 7a - releasing a another transaction's savepoint did not raise error"); 517 } catch (SQLException se) { 518 System.out.println("Expected Exception is " + se.getMessage()); 519 } 520 con.commit(); 521 con2.commit(); 522 523 System.out.println("Test7b - swap savepoints across connections with rollback"); 525 savepoint1 = con2.setSavepoint("s1"); 526 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 527 savepoint2 = con.setSavepoint("s1"); 528 try { 529 con.rollback(savepoint1); 530 System.out.println("FAIL 7b - rolling back a another transaction's savepoint did not raise error"); 531 } catch (SQLException se) { 532 System.out.println("Expected Exception is " + se.getMessage()); 533 } 534 con.commit(); 535 con2.commit(); 536 537 587 System.out.println("Test 9 test savepoint name"); 589 savepoint1 = con.setSavepoint("myname"); 590 String savepointName = savepoint1.getSavepointName(); 591 if (!savepointName.equals("myname")) 592 System.out.println("fail - savepoint name mismatch"); 593 con.rollback(); 594 595 System.out.println("Test 10 test savepoint name case sensitivity"); 597 savepoint1 = con.setSavepoint("MyName"); 598 savepointName = savepoint1.getSavepointName(); 599 if (!savepointName.equals("MyName")) 600 System.out.println("fail - savepoint name mismatch"); 601 con.rollback(); 602 603 System.out.println("Test 11 rolling back a savepoint multiple times - should work"); 605 savepoint1 = con.setSavepoint("MyName"); 606 con.rollback(savepoint1); 607 try { 608 con.rollback(savepoint1); 609 } catch (SQLException se) { 610 System.out.println("FAIL 11 second rollback failed"); 611 System.out.println("Exception is " + se.getMessage()); 612 } 613 con.rollback(); 614 615 System.out.println("Test 12 releasing a savepoint multiple times - should not work"); 617 savepoint1 = con.setSavepoint("MyName"); 618 con.releaseSavepoint(savepoint1); 619 try { 620 con.releaseSavepoint(savepoint1); 621 System.out.println("FAIL 12 releasing a savepoint multiple times should fail"); 622 } catch (SQLException se) { 623 System.out.println("Expected Exception is " + se.getMessage()); 624 } 625 con.rollback(); 626 627 System.out.println("Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off"); 629 savepoint1 = con.setSavepoint("MyName"); 630 con.setAutoCommit(true); 631 con.setAutoCommit(false); 632 savepoint2 = con.setSavepoint("MyName1"); 633 try { con.releaseSavepoint(savepoint1); 635 System.out.println("FAIL 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off"); 636 } catch (SQLException se) { 637 System.out.println("Expected Exception is " + se.getMessage()); 638 } 639 con.releaseSavepoint(savepoint2); 640 con.rollback(); 641 642 System.out.println("Test 14 A non-user initiated transaction rollback should release the internal savepoint array"); 644 Statement s1, s2; 645 s1 = con.createStatement(); 646 s1.executeUpdate("insert into t1 values(1,1)"); 647 s1.executeUpdate("insert into t1 values(2,0)"); 648 con.commit(); 649 s1.executeUpdate("update t1 set c11=c11+1 where c12 > 0"); 650 s2 = con2.createStatement(); 651 savepoint1 = con2.setSavepoint("MyName"); 652 try { s2.executeUpdate("update t1 set c11=c11+1 where c12 < 1"); 654 System.out.println("FAIL 14 should have gotten lock time out"); 655 } catch (SQLException se) { 656 System.out.println("Expected Exception is " + se.getMessage()); 657 } 658 try { con2.releaseSavepoint(savepoint1); 660 System.out.println("FAIL 14 A non-user initiated transaction rollback should release the internal savepoint array"); 661 } catch (SQLException se) { 662 System.out.println("Expected Exception is " + se.getMessage()); 663 } 664 con.rollback(); 665 con2.rollback(); 666 s.execute("delete from t1"); 667 con.commit(); 668 669 System.out.println("Test 15 check savepoints in batch"); 671 s.execute("delete from t1"); 672 s.addBatch("insert into t1 values(1,1)"); 673 s.addBatch("insert into t1 values(1,1)"); 674 savepoint1 = con.setSavepoint(); 675 s.addBatch("insert into t1 values(1,1)"); 676 s.executeBatch(); 677 con.rollback(savepoint1); 678 int val = count(con,s); 679 if (val != 0) 680 System.out.println("FAIL 15 savepoint should have been set before batch"); 681 con.rollback(); 682 683 System.out.println("Test 16 grammar check for savepoint sq1"); 685 try { 686 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS"); 687 System.out.println("FAIL 16 Should have gotten exception for missing ON ROLLBACK RETAIN CURSORS"); 688 } catch (SQLException se) { 689 System.out.println("Expected Exception is " + se.getMessage()); 690 } 691 try { 692 s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN CURSORS"); 693 System.out.println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN CURSORS"); 694 } catch (SQLException se) { 695 System.out.println("Expected Exception is " + se.getMessage()); 696 } 697 try { 698 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN LOCKS"); 699 System.out.println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN LOCKS"); 700 } catch (SQLException se) { 701 System.out.println("Expected Exception is " + se.getMessage()); 702 } 703 try { 704 s.executeUpdate("SAVEPOINT s1 UNIQUE UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 705 System.out.println("FAIL 16 Should have gotten exception for multiple UNIQUE keywords"); 706 } catch (SQLException se) { 707 System.out.println("Expected Exception is " + se.getMessage()); 708 } 709 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN LOCKS"); 710 s.executeUpdate("RELEASE TO SAVEPOINT s1"); 711 con.rollback(); 712 713 System.out.println("Test 17 No nested savepoints allowed when using SQL to set savepoints."); 715 System.out.println("Test 17a Test with UNIQUE clause."); 716 s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 717 try { 718 s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 719 System.out.println("FAIL 17a Should have gotten exception for nested savepoints"); 720 } catch (SQLException se) { 721 System.out.println("Expected Exception is " + se.getMessage()); 722 } 723 s.executeUpdate("RELEASE TO SAVEPOINT s1"); 724 s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 725 con.rollback(); 726 727 System.out.println("Test 17b Test without UNIQUE clause."); 728 System.out.println("Since no nesting is allowed, skipping UNIQUE still gives error for trying to define another savepoint"); 729 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 730 try { 731 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 732 System.out.println("FAIL 17b Should have gotten exception for nested savepoints"); 733 } catch (SQLException se) { 734 System.out.println("Expected Exception is " + se.getMessage()); 735 } 736 con.rollback(); 737 738 System.out.println("Test 18 No nested SQL savepoints allowed inside JDBC savepoint."); 740 savepoint1 = con.setSavepoint(); 741 System.out.println("Following SQL savepoint will fail because we are trying to nest it inside JDBC savepoint"); 742 try { 743 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 744 System.out.println("FAIL 18 shouldn't be able set SQL savepoint nested inside JDBC savepoints"); 745 } catch (SQLException se) { 746 System.out.println("Expected Exception is " + se.getMessage()); 747 } 748 con.releaseSavepoint(savepoint1); 750 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 751 con.rollback(); 752 753 System.out.println("Test 19 No nested SQL savepoints allowed inside SQL savepoint."); 755 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 756 System.out.println("Following SQL savepoint will fail because we are trying to nest it inside SQL savepoint"); 757 try { 758 s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 759 System.out.println("FAIL 19 shouldn't be able set SQL savepoint nested inside SQL savepoint"); 760 } catch (SQLException se) { 761 System.out.println("Expected Exception is " + se.getMessage()); 762 } 763 s.executeUpdate("RELEASE TO SAVEPOINT s1"); 765 s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 766 con.rollback(); 767 768 System.out.println("Test 20 Rollback of SQL savepoint works same as rollback of JDBC savepoint."); 770 s.executeUpdate("DELETE FROM T1"); 771 con.commit(); 772 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 773 s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); 774 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 775 s.executeUpdate("INSERT INTO T1 VALUES(3,1)"); 776 s.executeUpdate("ROLLBACK TO SAVEPOINT s1"); 778 rs1 = s.executeQuery("select count(*) from t1"); 779 rs1.next(); 780 if(rs1.getInt(1) != 0) { 781 System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows"); 782 return; 783 } 784 con.rollback(); 785 786 System.out.println("Test 21 After releasing the SQL savepoint, rollback the transaction and should see everything undone."); 788 s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 789 s.executeUpdate("INSERT INTO T1 VALUES(1,1)"); 790 s.executeUpdate("INSERT INTO T1 VALUES(2,1)"); 791 s.executeUpdate("INSERT INTO T1 VALUES(3,1)"); 792 s.executeUpdate("RELEASE TO SAVEPOINT s1"); 794 con.rollback(); 795 rs1 = s.executeQuery("select count(*) from t1"); 796 rs1.next(); 797 if(rs1.getInt(1) != 0) { 798 System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows"); 799 return; 800 } 801 con.rollback(); 802 803 System.out.println("Test 22 Should not be able to create a SQL savepoint starting with name SYS"); 805 try { 806 s.executeUpdate("SAVEPOINT SYSs2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 807 System.out.println("FAIL 22 shouldn't be able to create a SQL savepoint starting with name SYS"); 808 } catch (SQLException se) { 809 System.out.println("Expected Exception is " + se.getMessage()); 810 } 811 con.rollback(); 812 813 System.out.println("Test 23 Should be able to use non-reserved keywords savepoint and release as identifiers"); 815 System.out.println("Create table with savepoint and release as identifiers"); 816 s.execute("create table savepoint (savepoint int, release int)"); 817 rs1 = s.executeQuery("select count(*) from savepoint"); 818 rs1.next(); 819 if(rs1.getInt(1) != 0) { 820 System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows"); 821 return; 822 } 823 System.out.println("Create a savepoint with name savepoint"); 824 s.execute("SAVEPOINT savepoint ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 825 s.executeUpdate("INSERT INTO savepoint VALUES(1,1)"); 826 System.out.println("Release the savepoint with name savepoint"); 827 s.execute("RELEASE SAVEPOINT savepoint"); 828 rs1 = s.executeQuery("select count(*) from savepoint"); 829 rs1.next(); 830 if(rs1.getInt(1) != 1) { 831 System.out.println("ERROR: There should have been 1 rows in the table, but found " + rs1.getInt(1) + " rows"); 832 return; 833 } 834 System.out.println("Create a savepoint with name release"); 835 s.execute("SAVEPOINT release ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS"); 836 s.executeUpdate("INSERT INTO savepoint VALUES(2,1)"); 837 System.out.println("Rollback to the savepoint with name release"); 838 s.execute("ROLLBACK TO SAVEPOINT release"); 839 rs1 = s.executeQuery("select count(*) from savepoint"); 840 rs1.next(); 841 if(rs1.getInt(1) != 1) { 842 System.out.println("ERROR: There should have been 1 rows in the table, but found " + rs1.getInt(1) + " rows"); 843 return; 844 } 845 System.out.println("Release the savepoint with name release"); 846 s.execute("RELEASE SAVEPOINT release"); 847 con.rollback(); 848 849 System.out.println("Test 24 Savepoint name can't exceed 128 characters"); 851 try { 852 savepoint1 = con.setSavepoint("MyName1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890"); 853 System.out.println("FAIL 24 shouldn't be able to create a SQL savepoint with name exceeding 128 characters"); 854 } catch (SQLException se) { 855 System.out.println("Expected Exception is " + se.getMessage()); 856 } 857 con.rollback(); 858 859 System.out.println("Test 25 Should not be able to create a SQL savepoint starting with name SYS through jdbc"); 861 try { 862 savepoint1 = con.setSavepoint("SYSs2"); 863 System.out.println("FAIL 25 shouldn't be able to create a SQL savepoint starting with name SYS through jdbc"); 864 } catch (SQLException se) { 865 System.out.println("Expected Exception is " + se.getMessage()); 866 } 867 con.rollback(); 868 869 s1.close(); 870 s2.close(); 871 872 System.out.println("Test 26a rollback of null savepoint"); 877 try { 878 con.rollback((Savepoint ) null); 879 System.out.println("FAIL 26a rollback of null savepoint did not raise error "); 880 } catch (SQLException se) { 881 System.out.println("Expected Exception is " + se.getMessage()); 882 } 883 System.out.println("Test 26b release of null savepoint"); 885 try { 886 con.releaseSavepoint((Savepoint ) null); 887 System.out.println("FAIL 26b release of null savepoint did not raise error "); 888 } catch (SQLException se) { 889 System.out.println("Expected Exception is " + se.getMessage()); 890 } 891 } 892 893 static void setUpTest(Statement s) 895 throws SQLException { 896 897 try { 898 899 s.execute("drop table t1"); 900 s.execute("drop table t2"); 901 s.execute("drop table savepoint"); 902 } catch (SQLException se) { 903 } 905 906 907 s.execute("create table t1 (c11 int, c12 smallint)"); 908 s.execute("create table t2 (c11 int)"); 909 910 } 911 912 static private int count(Connection con, Statement s) throws SQLException { 913 int count = 0; 914 ResultSet rs = s.executeQuery("select count(*) from t1"); 915 rs.next(); 916 count = rs.getInt(1); 917 rs.close(); 918 return count; 919 } 920 921 public static void doConnectionSetSavepointUnnamed() throws Throwable 922 { 923 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 924 Savepoint s1 = conn.setSavepoint(); 925 Statement s = conn.createStatement(); 926 s.executeUpdate("insert into t2 values(1)"); 927 conn.rollback(s1); 928 } 929 930 public static void doConnectionSetSavepointNamed() throws Throwable 931 { 932 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 933 Savepoint s1 = conn.setSavepoint("s1"); 934 Statement s = conn.createStatement(); 935 s.executeUpdate("insert into t2 values(1)"); 936 conn.rollback(s1); 937 } 938 939 public static void doConnectionRollbackSavepoint() throws Throwable 940 { 941 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 942 conn.rollback((Savepoint ) null); 943 Statement s = conn.createStatement(); 944 s.executeUpdate("insert into t2 values(1)"); 945 } 946 947 public static void doConnectionReleaseSavepoint() throws Throwable 948 { 949 Connection conn = DriverManager.getConnection("jdbc:default:connection"); 950 conn.releaseSavepoint((Savepoint ) null); 951 Statement s = conn.createStatement(); 952 s.executeUpdate("insert into t2 values(1)"); 953 } 954 955 public static void dumpSQLExceptions (SQLException se) { 956 System.out.println("FAIL -- unexpected exception"); 957 while (se != null) { 958 System.out.print("SQLSTATE("+se.getSQLState()+"):"); 959 se.printStackTrace(System.out); 960 se = se.getNextException(); 961 } 962 } 963 } 964 | Popular Tags |