0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30_JSR169
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
0023:
0024: import java.sql.Connection;
0025: import java.sql.DriverManager;
0026: import java.sql.ResultSet;
0027: import java.sql.SQLException;
0028: import java.sql.Savepoint;
0029: import java.sql.Statement;
0030:
0031: import org.apache.derby.tools.ij;
0032: import org.apache.derbyTesting.functionTests.util.TestUtil;
0033:
0034: /**
0035: * Test the new class Savepoint in jdbc 30.
0036: * Also, test some mix and match of defining savepoints through JDBC and sql
0037: * Testing both callable and prepared statements meta data
0038: *
0039: * Do not put tests in this file that are not compatible with JSR169
0040: * @author mamta
0041: */
0042:
0043: public class savepointJdbc30_JSR169 {
0044:
0045: static private boolean isDerbyNet = false;
0046:
0047: static private String[] testObjects = { "table t1", "table t2",
0048: "table savepoint" };
0049:
0050: public static void main(String[] args) {
0051: Connection con = null, con2 = null;
0052: Statement s;
0053: System.out.println("Test savepointJdbc30 starting");
0054:
0055: try {
0056: // use the ij utility to read the property file and
0057: // make the initial connection.
0058: ij.getPropertyArg(args);
0059: con = ij.startJBMS();
0060: con2 = ij.startJBMS();
0061: runTests("regular connections", con, con2);
0062:
0063: con.close();
0064: con2.close();
0065:
0066: } catch (SQLException e) {
0067: dumpSQLExceptions(e);
0068: } catch (Throwable e) {
0069: System.out.println("FAIL -- unexpected exception:");
0070: e.printStackTrace(System.out);
0071: }
0072:
0073: }
0074:
0075: public static void runTests(String tag, Connection con,
0076: Connection con2) throws SQLException {
0077:
0078: Statement s;
0079: System.out.println("Test savepointJdbc30 starting for " + tag);
0080: isDerbyNet = TestUtil.isNetFramework();
0081: con.setAutoCommit(true); // make sure it is true
0082: con2.setAutoCommit(false);
0083: s = con.createStatement();
0084:
0085: /* Create the table and do any other set-up */
0086: setUpTest(s);
0087:
0088: //JCC translates the JDBC savepoint calls into equivalent SQL statements.
0089: //In addition, we do not allow nested savepoints when
0090: //coming through SQL statements. Because of this restriction, we can't run most of the
0091: //JDBC savepoint tests under DRDA framework. The JDBC tests have nested JDBC savepoint
0092: //calls and they fail when run under JCC(because they get translated into nested SQL savepoints).
0093: //Hence, splitting the test cases into non-DRDA and more generic tests.
0094: System.out
0095: .println("Tests common to DRDA and embedded Cloudscape");
0096: genericTests(con, con2, s);
0097:
0098: System.out.println("Next try non-DRDA tests");
0099: if (!isDerbyNet)
0100: nonDRDATests(con, s);
0101:
0102: con.setAutoCommit(true);
0103: TestUtil.cleanUpTest(s, testObjects);
0104:
0105: s.close();
0106:
0107: }
0108:
0109: //The following tests have nested savepoints through JDBC calls. When coming through JCC,
0110: //these nested JDBC savepoint calls are translated into equivalent SQL savepoint statements.
0111: //But we do not allow nested savepoints coming through SQL statments
0112: //and hence these tests can't be run under DRDA framework.
0113: static void nonDRDATests(Connection con, Statement s)
0114: throws SQLException {
0115: ResultSet rs1, rs2, rs1WithHold, rs2WithHold;
0116: Savepoint savepoint1, savepoint2, savepoint3, savepoint4;
0117:
0118: //Setting autocommit to false will allow savepoints
0119: con.setAutoCommit(false); // make sure it is false
0120:
0121: //Test40 - We internally generate a unique name for unnamed savepoints. If a
0122: //named savepoint uses the currently used internal savepoint name, we won't
0123: //get an exception thrown for it because we prepend external saves with "e."
0124: //to avoid name conflicts.
0125: System.out
0126: .println("Test40 - named savepoint can't conflict with internally generated name for unnamed savepoints");
0127: savepoint1 = con.setSavepoint();
0128: savepoint2 = con.setSavepoint("i.SAVEPT0");
0129: con.rollback();
0130:
0131: //Test41 - Rolling back to a savepoint will release all the savepoints created after that savepoint.
0132: System.out
0133: .println("Test41a - Rollback to a savepoint, then try to release savepoint created after that savepoint");
0134:
0135: savepoint1 = con.setSavepoint();
0136: s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
0137:
0138: savepoint2 = con.setSavepoint("s1");
0139: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0140:
0141: savepoint3 = con.setSavepoint("s2");
0142: s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
0143:
0144: //Rollback to first named savepoint s1. This will internally release the second named savepoint s2.
0145: con.rollback(savepoint2);
0146: rs1 = s.executeQuery("select count(*) from t1");
0147: rs1.next();
0148: if (rs1.getInt(1) != 1) {
0149: System.out
0150: .println("ERROR: There should have been 1 row in the table, but found "
0151: + rs1.getInt(1) + " rows");
0152: return;
0153: }
0154:
0155: //Trying to release second named savepoint s2 should throw exception.
0156: try {
0157: con.releaseSavepoint(savepoint3);
0158: System.out
0159: .println("FAIL 41a release of rolled back savepoint");
0160: } catch (SQLException se) {
0161: System.out.println("Expected Exception is "
0162: + se.getMessage());
0163: }
0164:
0165: //Trying to rollback second named savepoint s2 should throw exception.
0166: System.out
0167: .println("Test41b - Rollback to a savepoint, then try to rollback savepoint created after that savepoint");
0168: try {
0169: con.rollback(savepoint3);
0170: System.out
0171: .println("FAIL 41b release of rolled back savepoint");
0172: } catch (SQLException se) {
0173: System.out.println("Expected Exception is "
0174: + se.getMessage());
0175: }
0176:
0177: //Release the unnamed named savepoint.
0178: con.rollback(savepoint1);
0179: rs1 = s.executeQuery("select count(*) from t1");
0180: rs1.next();
0181: if (rs1.getInt(1) != 0) {
0182: System.out
0183: .println("ERROR: There should have been no rows in the table, but found "
0184: + rs1.getInt(1) + " rows");
0185: return;
0186: }
0187: con.rollback();
0188:
0189: //Test42 - Rollback/commit on a connection will release all the savepoints created for that transaction
0190: System.out
0191: .println("Test42 - Rollback/commit the transaction, then try to use savepoint from that transaction");
0192: savepoint1 = con.setSavepoint();
0193: savepoint2 = con.setSavepoint("s1");
0194: con.rollback();
0195: try {
0196: con.rollback(savepoint1);
0197: System.out
0198: .println("FAIL 42 release of rolled back savepoint");
0199: } catch (SQLException se) {
0200: System.out.println("Expected Exception is "
0201: + se.getMessage());
0202: }
0203: //Testing commit next
0204: savepoint1 = con.setSavepoint();
0205: savepoint2 = con.setSavepoint("s1");
0206: con.commit();
0207: try {
0208: con.rollback(savepoint1);
0209: System.out
0210: .println("FAIL 42 rollback of rolled back savepoint");
0211: } catch (SQLException se) {
0212: System.out.println("Expected Exception is "
0213: + se.getMessage());
0214: }
0215:
0216: //Test43 - After releasing a savepoint, should be able to reuse it.
0217: System.out
0218: .println("Test43 - Release and reuse a savepoint name");
0219: savepoint1 = con.setSavepoint("s1");
0220: try {
0221: savepoint2 = con.setSavepoint("s1");
0222: System.out.println("FAIL 43");
0223: } catch (SQLException se) {
0224: System.out.println("Expected Exception is "
0225: + se.getMessage());
0226: }
0227: con.releaseSavepoint(savepoint1);
0228: savepoint2 = con.setSavepoint("s1");
0229: con.rollback();
0230:
0231: // Test 45 reuse savepoint name after rollback - should not work
0232: System.out
0233: .println("Test 45 reuse savepoint name after rollback - should not work");
0234: savepoint1 = con.setSavepoint("MyName");
0235: con.rollback(savepoint1);
0236: try {
0237: savepoint2 = con.setSavepoint("MyName");
0238: System.out
0239: .println("FAIL 45 reuse of savepoint name after rollback should fail");
0240: } catch (SQLException se) {
0241: System.out.println("Expected Exception is "
0242: + se.getMessage());
0243: }
0244: con.rollback();
0245:
0246: // Test 46 bug 5145 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint
0247: System.out
0248: .println("Test 46 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint");
0249: Statement sWithHold = con.createStatement(
0250: ResultSet.TYPE_FORWARD_ONLY,
0251: ResultSet.CONCUR_READ_ONLY,
0252: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0253: con.setAutoCommit(false);
0254: s.executeUpdate("DELETE FROM T1");
0255: s.executeUpdate("INSERT INTO T1 VALUES(19,1)");
0256: s.executeUpdate("INSERT INTO T1 VALUES(19,2)");
0257: s.executeUpdate("INSERT INTO T1 VALUES(19,3)");
0258: rs1 = s.executeQuery("select * from t1");
0259: rs1.next();
0260: rs1WithHold = sWithHold.executeQuery("select * from t1");
0261: rs1WithHold.next();
0262: savepoint1 = con.setSavepoint();
0263: rs2 = s.executeQuery("select * from t1");
0264: rs2.next();
0265: rs2WithHold = sWithHold.executeQuery("select * from t1");
0266: rs2WithHold.next();
0267: con.rollback(savepoint1);
0268: try {//resultset declared outside the savepoint unit should be closed at this point after the rollback to savepoint
0269: rs1.next();
0270: System.out
0271: .println("FAIL 46 shouldn't be able to use a resultset (declared before the savepoint unit) after the rollback to savepoint");
0272: } catch (SQLException se) {
0273: System.out.println("Expected Exception is "
0274: + se.getMessage());
0275: }
0276: try {//holdable resultset declared outside the savepoint unit should be closed at this point after the rollback to savepoint
0277: rs1WithHold.next();
0278: System.out
0279: .println("FAIL 46 shouldn't be able to use a holdable resultset (declared before the savepoint unit) after the rollback to savepoint");
0280: } catch (SQLException se) {
0281: System.out.println("Expected Exception is "
0282: + se.getMessage());
0283: }
0284: try {//resultset declared within the savepoint unit should be closed at this point after the rollback to savepoint
0285: rs2.next();
0286: System.out
0287: .println("FAIL 46 shouldn't be able to use a resultset (declared within the savepoint unit) after the rollback to savepoint");
0288: } catch (SQLException se) {
0289: System.out.println("Expected Exception is "
0290: + se.getMessage());
0291: }
0292: try {//holdable resultset declared within the savepoint unit should be closed at this point after the rollback to savepoint
0293: rs2WithHold.next();
0294: System.out
0295: .println("FAIL 46 shouldn't be able to use a holdable resultset (declared within the savepoint unit) after the rollback to savepoint");
0296: } catch (SQLException se) {
0297: System.out.println("Expected Exception is "
0298: + se.getMessage());
0299: }
0300: con.rollback();
0301:
0302: // Test 47 multiple tests for getSavepointId()
0303: System.out
0304: .println("Test 47 multiple tests for getSavepointId()");
0305: savepoint1 = con.setSavepoint();
0306: savepoint2 = con.setSavepoint();
0307: System.out.println(savepoint1.getSavepointId());
0308: System.out.println(savepoint2.getSavepointId());
0309: con.releaseSavepoint(savepoint2);
0310: savepoint2 = con.setSavepoint();
0311: System.out.println(savepoint2.getSavepointId());
0312: con.commit();
0313: savepoint2 = con.setSavepoint();
0314: System.out.println(savepoint2.getSavepointId());
0315: con.rollback();
0316: savepoint2 = con.setSavepoint();
0317: System.out.println(savepoint2.getSavepointId());
0318: con.rollback();
0319:
0320: // Test 48
0321: System.out.println("Test 48 No nested SQL savepoints allowed.");
0322: savepoint1 = con.setSavepoint();
0323: savepoint2 = con.setSavepoint();
0324: System.out
0325: .println("Following SQL savepoint will fail because we are trying to nest it inside JDBC savepoint");
0326: try {
0327: s
0328: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0329: System.out
0330: .println("FAIL 48 shouldn't be able set SQL savepoint nested inside JDBC/SQL savepoints");
0331: } catch (SQLException se) {
0332: System.out.println("Expected Exception is "
0333: + se.getMessage());
0334: }
0335: //rollback JDBC savepoint but still can't have SQL savepoint because there is still one JDBC savepoint
0336: con.releaseSavepoint(savepoint2);
0337: try {
0338: s
0339: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0340: System.out
0341: .println("FAIL 48 Should have gotten exception for nested SQL savepoint");
0342: } catch (SQLException se) {
0343: System.out.println("Expected Exception is "
0344: + se.getMessage());
0345: }
0346: con.releaseSavepoint(savepoint1); //rollback last JDBC savepoint and now try SQL savepoint again
0347: s
0348: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0349: con.rollback();
0350: }
0351:
0352: //These tests do not allow savepoint nesting and hence can be run under DRDA too
0353: static void genericTests(Connection con, Connection con2,
0354: Statement s) throws SQLException {
0355:
0356: ResultSet rs1, rs2, rs1WithHold, rs2WithHold;
0357: Savepoint savepoint1, savepoint2, savepoint3, savepoint4;
0358:
0359: //Test1 and Test1a fail under DRDA (bug 5384).
0360: //Test1 - No savepoint allowed when auto commit is true
0361: con.setAutoCommit(true); // make sure it is true
0362: try {
0363: System.out
0364: .println("Test1 - no unnamed savepoints allowed if autocommit is true");
0365: con.setSavepoint(); // will throw exception because auto commit is true
0366: System.out.println("FAIL 1 - auto commit on");
0367: } catch (SQLException se) {
0368: System.out.println("Expected Exception is "
0369: + se.getMessage());
0370: }
0371: //Test1a - No savepoint allowed when auto commit is true
0372: try {
0373: System.out
0374: .println("Test1a - no named savepoints allowed if autocommit is true");
0375: con.setSavepoint("notallowed"); // will throw exception because auto commit is true
0376: System.out.println("FAIL 1a - auto commit on");
0377: } catch (SQLException se) {
0378: System.out.println("Expected Exception is "
0379: + se.getMessage());
0380: }
0381:
0382: con.setAutoCommit(false); // make sure it is false
0383:
0384: //Test2 - After releasing a savepoint, should be able to reuse it.
0385: System.out
0386: .println("Test2 - Release and reuse a savepoint name");
0387: savepoint1 = con.setSavepoint("s1");
0388: con.releaseSavepoint(savepoint1);
0389: savepoint2 = con.setSavepoint("s1");
0390: con.rollback();
0391:
0392: //Test3 - Named savepoints can't pass null for name
0393: try {
0394: System.out
0395: .println("Test3 - null name not allowed for named savepoints");
0396: con.setSavepoint(null);
0397: System.out.println("FAIL 3 null savepoint ");
0398: } catch (SQLException se) {
0399: System.out.println("Expected Exception is "
0400: + se.getMessage());
0401: }
0402: con.rollback();
0403:
0404: //Test4 - Verify names/ids of named/unnamed savepoints
0405: //named savepoints don't have an id.
0406: //unnamed savepoints don't have a name (internally, all our savepoints have names,
0407: //but for unnamed savepoint, that is not exposed thro jdbc api)
0408: System.out
0409: .println("Test4 - Verify names/ids of named/unnamed savepoints");
0410: try {
0411: savepoint1 = con.setSavepoint();
0412: savepoint1.getSavepointId();
0413: //following should throw exception for un-named savepoint
0414: savepoint1.getSavepointName();
0415: System.out
0416: .println("FAIL 4 getSavepointName on id savepoint ");
0417: } catch (SQLException se) {
0418: System.out.println("Expected Exception is "
0419: + se.getMessage());
0420: }
0421: con.rollback();
0422: try {
0423: savepoint1 = con.setSavepoint("s1");
0424: savepoint1.getSavepointName();
0425: //following should throw exception for named savepoint
0426: savepoint1.getSavepointId();
0427: System.out
0428: .println("FAIL 4 getSavepointId on named savepoint ");
0429: } catch (SQLException se) {
0430: System.out.println("Expected Exception is "
0431: + se.getMessage());
0432: }
0433: con.rollback();
0434:
0435: // TEST 5a and 5b for bug 4465
0436: // test 5a - create two savepoints in two different transactions
0437: // and release the first one in the subsequent transaction
0438: System.out
0439: .println("Test5a - create two savepoints in two different transactions"
0440: + " and release the first one in the subsequent transaction");
0441: savepoint1 = con.setSavepoint("s1");
0442: con.commit();
0443: //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
0444: //for savepoints. The reason for that is as follows
0445: //JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
0446: //different connections are named the same, then the release savepoint below will get converted to
0447: //RELEASE TO SAVEPOINT s1 and that succeeds because the 2nd connection does have a savepoint named s1.
0448: //Hence we don't really check what we intended to check which is trying to release a savepoint created
0449: //in a different transaction
0450: savepoint2 = con.setSavepoint("s2");
0451: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0452: try {
0453: con.releaseSavepoint(savepoint1);
0454: System.out
0455: .println("FAIL 5a - release savepoint from a different transaction did not raise error");
0456: } catch (SQLException se) {
0457: System.out.println("Expected Exception is "
0458: + se.getMessage());
0459: }
0460: con.commit();
0461:
0462: // test 5b - create two savepoints in two different transactions
0463: // and rollback the first one in the subsequent transaction
0464: System.out
0465: .println("Test5b - create two savepoints in two different transactions"
0466: + " and rollback the first one in the subsequent transaction");
0467: savepoint1 = con.setSavepoint("s1");
0468: con.commit();
0469: //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
0470: //for savepoints. The reason for that is as follows
0471: //JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
0472: //different connections are named the same, then the rollback savepoint below will get converted to
0473: //ROLLBACK TO SAVEPOINT s1 and that succeeds because the 2nd connection does have a savepoint named s1.
0474: //Hence we don't really check what we intended to check which is trying to rollback a savepoint created
0475: //in a different transaction
0476: savepoint2 = con.setSavepoint("s2");
0477: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0478: try {
0479: con.rollback(savepoint1);
0480: System.out
0481: .println("FAIL 5b - rollback savepoint from a different transaction did not raise error");
0482: } catch (SQLException se) {
0483: System.out.println("Expected Exception is "
0484: + se.getMessage());
0485: }
0486: con.commit();
0487:
0488: // test 6a - create a savepoint release it and then create another with the same name.
0489: // and release the first one
0490: System.out
0491: .println("Test6a - create a savepoint, release it, create another with"
0492: + " same name and release the first one");
0493: savepoint1 = con.setSavepoint("s1");
0494: con.releaseSavepoint(savepoint1);
0495: //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
0496: //for savepoints. The reason for that is as follows
0497: //JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
0498: //a transaction are named the same, then the release savepoint below will get converted to
0499: //RELEASE TO SAVEPOINT s1 and that succeeds because there is a valid savepoint named s1.
0500: savepoint2 = con.setSavepoint("s2");
0501: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0502: try {
0503: con.releaseSavepoint(savepoint1);
0504: System.out
0505: .println("FAIL 6a - releasing a released savepoint did not raise error");
0506: } catch (SQLException se) {
0507: System.out.println("Expected Exception is "
0508: + se.getMessage());
0509: }
0510: con.commit();
0511:
0512: // test 6b - create a savepoints release it and then create another with the same name.
0513: // and rollback the first one
0514: System.out
0515: .println("Test6b - create a savepoint, release it, create another with"
0516: + " same name and rollback the first one");
0517: savepoint1 = con.setSavepoint("s1");
0518: con.releaseSavepoint(savepoint1);
0519: //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
0520: //for savepoints. The reason for that is as follows
0521: //JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
0522: //a transaction are named the same, then the rollback savepoint below will get converted to
0523: //ROLLBACK TO SAVEPOINT s1 and that succeeds because there is a valid savepoint named s1.
0524: savepoint2 = con.setSavepoint("s2");
0525: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0526: try {
0527: con.rollback(savepoint1);
0528: System.out
0529: .println("FAIL 6b - rollback a released savepoint did not raise error");
0530: } catch (SQLException se) {
0531: System.out.println("Expected Exception is "
0532: + se.getMessage());
0533: }
0534: con.commit();
0535:
0536: /* TEST case just for bug 4467
0537: // Test 10 - create a named savepoint with the a generated name
0538: savepoint1 = con2.setSavepoint("SAVEPT0");
0539:
0540: // what exactly is the correct behaviour here?
0541: try {
0542: savepoint2 = con2.setSavepoint();
0543: }
0544: catch (SQLException se) {
0545: System.out.println("Expected Exception is " + se.getMessage());
0546: }
0547: con2.commit();
0548: */
0549:
0550: System.out
0551: .println("Test6c - Try to use a savepoint from another connection for release");
0552: savepoint1 = con.setSavepoint("s1");
0553: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0554: try {
0555: con2.releaseSavepoint(savepoint1);
0556: System.out
0557: .println("FAIL 6c - releasing another transaction's savepoint did not raise error");
0558: } catch (SQLException se) {
0559: System.out.println("Expected Exception is "
0560: + se.getMessage());
0561: }
0562: con.commit();
0563: con2.commit();
0564:
0565: /* BUG 4468 - should not be able to pass a savepoint from a different transaction for release/rollback */
0566: // Test 7a - swap savepoints across connections
0567: System.out
0568: .println("Test7a - swap savepoints across connections with release");
0569: savepoint1 = con2.setSavepoint("s1");
0570: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0571: savepoint2 = con.setSavepoint("s1");
0572: try {
0573: con.releaseSavepoint(savepoint1);
0574: System.out
0575: .println("FAIL 7a - releasing a another transaction's savepoint did not raise error");
0576: } catch (SQLException se) {
0577: System.out.println("Expected Exception is "
0578: + se.getMessage());
0579: }
0580: con.commit();
0581: con2.commit();
0582:
0583: // Test 7b - swap savepoints across connections
0584: System.out
0585: .println("Test7b - swap savepoints across connections with rollback");
0586: savepoint1 = con2.setSavepoint("s1");
0587: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0588: savepoint2 = con.setSavepoint("s1");
0589: try {
0590: con.rollback(savepoint1);
0591: System.out
0592: .println("FAIL 7b - rolling back a another transaction's savepoint did not raise error");
0593: } catch (SQLException se) {
0594: System.out.println("Expected Exception is "
0595: + se.getMessage());
0596: }
0597: con.commit();
0598: con2.commit();
0599:
0600: /*
0601: * following section attempts to call statement in a method to do a negative test
0602: * because savepoints are not supported in a trigger
0603: * however, this cannot be done because a call is not supported in a trigger.
0604: * leaving the test here for later reference for when we support the SQL version
0605: *
0606: // bug 4507 - Test 8 test all 4 savepoint commands inside the trigger code
0607: System.out.println("Test 8a set savepoint(unnamed) command inside the trigger code");
0608: s.executeUpdate("create trigger trig1 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionSetSavepointUnnamed()");
0609: try {
0610:
0611: s.executeUpdate("insert into t1 values(1,1)");
0612: System.out.println("FAIL 8a set savepoint(unnamed) command inside the trigger code");
0613: } catch (SQLException se) {
0614: System.out.println("Expected Exception is " + se.getMessage());
0615: }
0616: s.executeUpdate("drop trigger trig1");
0617:
0618: System.out.println("Test 8b set savepoint(named) command inside the trigger code");
0619: s.executeUpdate("create trigger trig2 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionSetSavepointNamed()");
0620: try {
0621: s.executeUpdate("insert into t1 values(1,1)");
0622: System.out.println("FAIL 8b set savepoint(named) command inside the trigger code");
0623: } catch (SQLException se) {
0624: System.out.println("Expected Exception is " + se.getMessage());
0625: }
0626: s.executeUpdate("drop trigger trig2");
0627:
0628: System.out.println("Test 8c release savepoint command inside the trigger code");
0629: s.executeUpdate("create trigger trig3 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionReleaseSavepoint()");
0630: try {
0631: s.executeUpdate("insert into t1 values(1,1)");
0632: System.out.println("FAIL 8c release savepoint command inside the trigger code");
0633: } catch (SQLException se) {
0634: System.out.println("Expected Exception is " + se.getMessage());
0635: }
0636: s.executeUpdate("drop trigger trig3");
0637:
0638: System.out.println("Test 8d rollback savepoint command inside the trigger code");
0639: s.executeUpdate("create trigger trig4 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionRollbackSavepoint()");
0640: try {
0641: s.executeUpdate("insert into t1 values(1,1)");
0642: System.out.println("FAIL 8d rollback savepoint command inside the trigger code");
0643: } catch (SQLException se) {
0644: System.out.println("Expected Exception is " + se.getMessage());
0645: }
0646: s.executeUpdate("drop trigger trig4");
0647: con.rollback();
0648: *///end commented out test 8
0649: // Test 9 test savepoint name and verify case sensitivity
0650: System.out.println("Test 9 test savepoint name");
0651: savepoint1 = con.setSavepoint("myname");
0652: String savepointName = savepoint1.getSavepointName();
0653: if (!savepointName.equals("myname"))
0654: System.out.println("fail - savepoint name mismatch");
0655: con.rollback();
0656:
0657: // Test 10 test savepoint name case sensitivity
0658: System.out
0659: .println("Test 10 test savepoint name case sensitivity");
0660: savepoint1 = con.setSavepoint("MyName");
0661: savepointName = savepoint1.getSavepointName();
0662: if (!savepointName.equals("MyName"))
0663: System.out.println("fail - savepoint name mismatch");
0664: con.rollback();
0665:
0666: // Test 11 rolling back a savepoint multiple times - should work
0667: System.out
0668: .println("Test 11 rolling back a savepoint multiple times - should work");
0669: savepoint1 = con.setSavepoint("MyName");
0670: con.rollback(savepoint1);
0671: try {
0672: con.rollback(savepoint1);
0673: } catch (SQLException se) {
0674: System.out.println("FAIL 11 second rollback failed");
0675: System.out.println("Exception is " + se.getMessage());
0676: }
0677: con.rollback();
0678:
0679: // Test 12 releasing a savepoint multiple times - should not work
0680: System.out
0681: .println("Test 12 releasing a savepoint multiple times - should not work");
0682: savepoint1 = con.setSavepoint("MyName");
0683: con.releaseSavepoint(savepoint1);
0684: try {
0685: con.releaseSavepoint(savepoint1);
0686: System.out
0687: .println("FAIL 12 releasing a savepoint multiple times should fail");
0688: } catch (SQLException se) {
0689: System.out.println("Expected Exception is "
0690: + se.getMessage());
0691: }
0692: con.rollback();
0693:
0694: // Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off
0695: System.out
0696: .println("Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off");
0697: savepoint1 = con.setSavepoint("MyName");
0698: con.setAutoCommit(true);
0699: con.setAutoCommit(false);
0700: savepoint2 = con.setSavepoint("MyName1");
0701: try {//shouldn't be able to use savepoint from earlier tranasaction after setting autocommit on and off
0702: con.releaseSavepoint(savepoint1);
0703: System.out
0704: .println("FAIL 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off");
0705: } catch (SQLException se) {
0706: System.out.println("Expected Exception is "
0707: + se.getMessage());
0708: }
0709: con.releaseSavepoint(savepoint2);
0710: con.rollback();
0711:
0712: // Test 14 cause a transaction rollback and that should release the internal savepoint array
0713: System.out
0714: .println("Test 14 A non-user initiated transaction rollback should release the internal savepoint array");
0715: Statement s1, s2;
0716: s1 = con.createStatement();
0717: s1.executeUpdate("insert into t1 values(1,1)");
0718: s1.executeUpdate("insert into t1 values(2,0)");
0719: con.commit();
0720: s1.executeUpdate("update t1 set c11=c11+1 where c12 > 0");
0721: s2 = con2.createStatement();
0722: savepoint1 = con2.setSavepoint("MyName");
0723: try {//following will get lock timeout which will rollback transaction on c2
0724: s2.executeUpdate("update t1 set c11=c11+1 where c12 < 1");
0725: System.out
0726: .println("FAIL 14 should have gotten lock time out");
0727: } catch (SQLException se) {
0728: System.out.println("Expected Exception is "
0729: + se.getMessage());
0730: }
0731: try {//the transaction rollback above should have removed the savepoint MyName
0732: con2.releaseSavepoint(savepoint1);
0733: System.out
0734: .println("FAIL 14 A non-user initiated transaction rollback should release the internal savepoint array");
0735: } catch (SQLException se) {
0736: System.out.println("Expected Exception is "
0737: + se.getMessage());
0738: }
0739: con.rollback();
0740: con2.rollback();
0741: s.execute("delete from t1");
0742: con.commit();
0743:
0744: // Test 15 check savepoints in batch
0745: System.out.println("Test 15 check savepoints in batch");
0746: s.execute("delete from t1");
0747: s.addBatch("insert into t1 values(1,1)");
0748: s.addBatch("insert into t1 values(1,1)");
0749: savepoint1 = con.setSavepoint();
0750: s.addBatch("insert into t1 values(1,1)");
0751: s.executeBatch();
0752: con.rollback(savepoint1);
0753: int val = count(con, s);
0754: if (val != 0)
0755: System.out
0756: .println("FAIL 15 savepoint should have been set before batch");
0757: con.rollback();
0758:
0759: // Test 16 grammar check for savepoint sq1
0760: System.out.println("Test 16 grammar check for savepoint sq1");
0761: try {
0762: s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS");
0763: System.out
0764: .println("FAIL 16 Should have gotten exception for missing ON ROLLBACK RETAIN CURSORS");
0765: } catch (SQLException se) {
0766: System.out.println("Expected Exception is "
0767: + se.getMessage());
0768: }
0769: try {
0770: s
0771: .executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN CURSORS");
0772: System.out
0773: .println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN CURSORS");
0774: } catch (SQLException se) {
0775: System.out.println("Expected Exception is "
0776: + se.getMessage());
0777: }
0778: try {
0779: s
0780: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN LOCKS");
0781: System.out
0782: .println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN LOCKS");
0783: } catch (SQLException se) {
0784: System.out.println("Expected Exception is "
0785: + se.getMessage());
0786: }
0787: try {
0788: s
0789: .executeUpdate("SAVEPOINT s1 UNIQUE UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0790: System.out
0791: .println("FAIL 16 Should have gotten exception for multiple UNIQUE keywords");
0792: } catch (SQLException se) {
0793: System.out.println("Expected Exception is "
0794: + se.getMessage());
0795: }
0796: s
0797: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN LOCKS");
0798: s.executeUpdate("RELEASE TO SAVEPOINT s1");
0799: con.rollback();
0800:
0801: // Test 17
0802: System.out
0803: .println("Test 17 No nested savepoints allowed when using SQL to set savepoints.");
0804: System.out.println("Test 17a Test with UNIQUE clause.");
0805: s
0806: .executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0807: try {
0808: s
0809: .executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0810: System.out
0811: .println("FAIL 17a Should have gotten exception for nested savepoints");
0812: } catch (SQLException se) {
0813: System.out.println("Expected Exception is "
0814: + se.getMessage());
0815: }
0816: s.executeUpdate("RELEASE TO SAVEPOINT s1");
0817: s
0818: .executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0819: con.rollback();
0820:
0821: System.out.println("Test 17b Test without UNIQUE clause.");
0822: System.out
0823: .println("Since no nesting is allowed, skipping UNIQUE still gives error for trying to define another savepoint");
0824: s
0825: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0826: try {
0827: s
0828: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0829: System.out
0830: .println("FAIL 17b Should have gotten exception for nested savepoints");
0831: } catch (SQLException se) {
0832: System.out.println("Expected Exception is "
0833: + se.getMessage());
0834: }
0835: con.rollback();
0836:
0837: // Test 18
0838: System.out
0839: .println("Test 18 No nested SQL savepoints allowed inside JDBC savepoint.");
0840: savepoint1 = con.setSavepoint();
0841: System.out
0842: .println("Following SQL savepoint will fail because we are trying to nest it inside JDBC savepoint");
0843: try {
0844: s
0845: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0846: System.out
0847: .println("FAIL 18 shouldn't be able set SQL savepoint nested inside JDBC savepoints");
0848: } catch (SQLException se) {
0849: System.out.println("Expected Exception is "
0850: + se.getMessage());
0851: }
0852: //rollback the JDBC savepoint. Now since there are no user defined savepoints, we can define SQL savepoint
0853: con.releaseSavepoint(savepoint1);
0854: s
0855: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0856: con.rollback();
0857:
0858: // Test 19
0859: System.out
0860: .println("Test 19 No nested SQL savepoints allowed inside SQL savepoint.");
0861: s
0862: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0863: System.out
0864: .println("Following SQL savepoint will fail because we are trying to nest it inside SQL savepoint");
0865: try {
0866: s
0867: .executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0868: System.out
0869: .println("FAIL 19 shouldn't be able set SQL savepoint nested inside SQL savepoint");
0870: } catch (SQLException se) {
0871: System.out.println("Expected Exception is "
0872: + se.getMessage());
0873: }
0874: //rollback the SQL savepoint. Now since there are no user defined savepoints, we can define SQL savepoint
0875: s.executeUpdate("RELEASE TO SAVEPOINT s1");
0876: s
0877: .executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0878: con.rollback();
0879:
0880: // Test 20
0881: System.out
0882: .println("Test 20 Rollback of SQL savepoint works same as rollback of JDBC savepoint.");
0883: s.executeUpdate("DELETE FROM T1");
0884: con.commit();
0885: s
0886: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0887: s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
0888: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0889: s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
0890: //Rollback to SQL savepoint and should see changes rolledback
0891: s.executeUpdate("ROLLBACK TO SAVEPOINT s1");
0892: rs1 = s.executeQuery("select count(*) from t1");
0893: rs1.next();
0894: if (rs1.getInt(1) != 0) {
0895: System.out
0896: .println("ERROR: There should have been 0 rows in the table, but found "
0897: + rs1.getInt(1) + " rows");
0898: return;
0899: }
0900: con.rollback();
0901:
0902: // Test 21
0903: System.out
0904: .println("Test 21 After releasing the SQL savepoint, rollback the transaction and should see everything undone.");
0905: s
0906: .executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0907: s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
0908: s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
0909: s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
0910: //Release the SQL savepoint and then rollback the transaction and should see changes rolledback
0911: s.executeUpdate("RELEASE TO SAVEPOINT s1");
0912: con.rollback();
0913: rs1 = s.executeQuery("select count(*) from t1");
0914: rs1.next();
0915: if (rs1.getInt(1) != 0) {
0916: System.out
0917: .println("ERROR: There should have been 0 rows in the table, but found "
0918: + rs1.getInt(1) + " rows");
0919: return;
0920: }
0921: con.rollback();
0922:
0923: // Test 22
0924: System.out
0925: .println("Test 22 Should not be able to create a SQL savepoint starting with name SYS");
0926: try {
0927: s
0928: .executeUpdate("SAVEPOINT SYSs2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0929: System.out
0930: .println("FAIL 22 shouldn't be able to create a SQL savepoint starting with name SYS");
0931: } catch (SQLException se) {
0932: System.out.println("Expected Exception is "
0933: + se.getMessage());
0934: }
0935: con.rollback();
0936:
0937: // Test 23 - bug 5817 - make savepoint and release non-reserved keywords
0938: System.out
0939: .println("Test 23 Should be able to use non-reserved keywords savepoint and release as identifiers");
0940: System.out
0941: .println("Create table with savepoint and release as identifiers");
0942: s
0943: .execute("create table savepoint (savepoint int, release int)");
0944: rs1 = s.executeQuery("select count(*) from savepoint");
0945: rs1.next();
0946: if (rs1.getInt(1) != 0) {
0947: System.out
0948: .println("ERROR: There should have been 0 rows in the table, but found "
0949: + rs1.getInt(1) + " rows");
0950: return;
0951: }
0952: System.out.println("Create a savepoint with name savepoint");
0953: s
0954: .execute("SAVEPOINT savepoint ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0955: s.executeUpdate("INSERT INTO savepoint VALUES(1,1)");
0956: System.out.println("Release the savepoint with name savepoint");
0957: s.execute("RELEASE SAVEPOINT savepoint");
0958: rs1 = s.executeQuery("select count(*) from savepoint");
0959: rs1.next();
0960: if (rs1.getInt(1) != 1) {
0961: System.out
0962: .println("ERROR: There should have been 1 rows in the table, but found "
0963: + rs1.getInt(1) + " rows");
0964: return;
0965: }
0966: System.out.println("Create a savepoint with name release");
0967: s
0968: .execute("SAVEPOINT release ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
0969: s.executeUpdate("INSERT INTO savepoint VALUES(2,1)");
0970: System.out
0971: .println("Rollback to the savepoint with name release");
0972: s.execute("ROLLBACK TO SAVEPOINT release");
0973: rs1 = s.executeQuery("select count(*) from savepoint");
0974: rs1.next();
0975: if (rs1.getInt(1) != 1) {
0976: System.out
0977: .println("ERROR: There should have been 1 rows in the table, but found "
0978: + rs1.getInt(1) + " rows");
0979: return;
0980: }
0981: System.out.println("Release the savepoint with name release");
0982: s.execute("RELEASE SAVEPOINT release");
0983: con.rollback();
0984:
0985: // Test 24
0986: System.out
0987: .println("Test 24 Savepoint name can't exceed 128 characters");
0988: try {
0989: savepoint1 = con
0990: .setSavepoint("MyName1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890");
0991: System.out
0992: .println("FAIL 24 shouldn't be able to create a SQL savepoint with name exceeding 128 characters");
0993: } catch (SQLException se) {
0994: System.out.println("Expected Exception is "
0995: + se.getMessage());
0996: }
0997: con.rollback();
0998:
0999: // Test 25
1000: System.out
1001: .println("Test 25 Should not be able to create a SQL savepoint starting with name SYS through jdbc");
1002: try {
1003: savepoint1 = con.setSavepoint("SYSs2");
1004: System.out
1005: .println("FAIL 25 shouldn't be able to create a SQL savepoint starting with name SYS through jdbc");
1006: } catch (SQLException se) {
1007: System.out.println("Expected Exception is "
1008: + se.getMessage());
1009: }
1010: con.rollback();
1011:
1012: s1.close();
1013: s2.close();
1014:
1015: // bug 4451 - Test 26a pass Null value to rollback
1016: // bug 5374 - Passing a null savepoint to rollback or release method
1017: // used to give a npe in JCC
1018: // it should give a SQLException aying "Cannot rollback to a null savepoint"
1019: System.out.println("Test 26a rollback of null savepoint");
1020: try {
1021: con.rollback((Savepoint) null);
1022: System.out
1023: .println("FAIL 26a rollback of null savepoint did not raise error ");
1024: } catch (SQLException se) {
1025: System.out.println("Expected Exception is "
1026: + se.getMessage());
1027: }
1028: // Test 26b pass Null value to releaseSavepoint
1029: System.out.println("Test 26b release of null savepoint");
1030: try {
1031: con.releaseSavepoint((Savepoint) null);
1032: System.out
1033: .println("FAIL 26b release of null savepoint did not raise error ");
1034: } catch (SQLException se) {
1035: System.out.println("Expected Exception is "
1036: + se.getMessage());
1037: }
1038: }
1039:
1040: //Set up the test by creating the table used by the rest of the test.
1041: static void setUpTest(Statement s) throws SQLException {
1042:
1043: try {
1044: /* Drop the tables, just in case they're there from another test */
1045: s.execute("drop table t1");
1046: s.execute("drop table t2");
1047: s.execute("drop table savepoint");
1048: } catch (SQLException se) {
1049: //System.out.println("Expected Exception is " + se.getMessage());
1050: }
1051:
1052: /* Create a table */
1053: s.execute("create table t1 (c11 int, c12 smallint)");
1054: s.execute("create table t2 (c11 int)");
1055:
1056: }
1057:
1058: static private int count(Connection con, Statement s)
1059: throws SQLException {
1060: int count = 0;
1061: ResultSet rs = s.executeQuery("select count(*) from t1");
1062: rs.next();
1063: count = rs.getInt(1);
1064: rs.close();
1065: return count;
1066: }
1067:
1068: public static void doConnectionSetSavepointUnnamed()
1069: throws Throwable {
1070: Connection conn = DriverManager
1071: .getConnection("jdbc:default:connection");
1072: Savepoint s1 = conn.setSavepoint();
1073: Statement s = conn.createStatement();
1074: s.executeUpdate("insert into t2 values(1)");
1075: conn.rollback(s1);
1076: }
1077:
1078: public static void doConnectionSetSavepointNamed() throws Throwable {
1079: Connection conn = DriverManager
1080: .getConnection("jdbc:default:connection");
1081: Savepoint s1 = conn.setSavepoint("s1");
1082: Statement s = conn.createStatement();
1083: s.executeUpdate("insert into t2 values(1)");
1084: conn.rollback(s1);
1085: }
1086:
1087: public static void doConnectionRollbackSavepoint() throws Throwable {
1088: Connection conn = DriverManager
1089: .getConnection("jdbc:default:connection");
1090: conn.rollback((Savepoint) null);
1091: Statement s = conn.createStatement();
1092: s.executeUpdate("insert into t2 values(1)");
1093: }
1094:
1095: public static void doConnectionReleaseSavepoint() throws Throwable {
1096: Connection conn = DriverManager
1097: .getConnection("jdbc:default:connection");
1098: conn.releaseSavepoint((Savepoint) null);
1099: Statement s = conn.createStatement();
1100: s.executeUpdate("insert into t2 values(1)");
1101: }
1102:
1103: public static void dumpSQLExceptions(SQLException se) {
1104: System.out.println("FAIL -- unexpected exception");
1105: while (se != null) {
1106: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
1107: se.printStackTrace(System.out);
1108: se = se.getNextException();
1109: }
1110: }
1111: }
|