0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJavaJDBC30
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.lang;
0023:
0024: import java.sql.Connection;
0025: import java.sql.PreparedStatement;
0026: import java.sql.ResultSet;
0027: import java.sql.ResultSetMetaData;
0028: import java.sql.Savepoint;
0029: import java.sql.Statement;
0030: import java.sql.SQLException;
0031:
0032: import javax.sql.ConnectionPoolDataSource;
0033: import javax.sql.PooledConnection;
0034:
0035: import org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource;
0036:
0037: import org.apache.derby.tools.ij;
0038: import org.apache.derby.tools.JDBCDisplayUtil;
0039: import org.apache.derbyTesting.functionTests.util.TestUtil;
0040:
0041: /**
0042: * Test for declared global temporary tables (introduced in Cloudscape 5.2) and pooled connection close and jdbc 3.0 specific features
0043: * The jdbc3.0 specific featuers are holdable cursors, savepoints.
0044: * The rest of the temp table test are in declareGlobalTempTableJava class. The reason for a different test
0045: * class is that the holdability and savepoint support is under jdk14 and higher. But we want to be able to run the non-holdable
0046: * tests under all the jdks we support and hence splitting the tests into two separate tests. Also, the reason for pooled connection close
0047: * is because DRDA doesn't yet have support for pooled connection and hence can't pull this test into other temp table test which runs under
0048: * both DRDA and plain Cloudscape.
0049: */
0050:
0051: public class declareGlobalTempTableJavaJDBC30 {
0052:
0053: static private boolean isDerbyNet = false;
0054:
0055: /*
0056: ** There is a small description prior to each sub-test describing what is being tested.
0057: */
0058: public static void main(String[] args) {
0059: boolean passed = true;
0060:
0061: Connection con = null;
0062: Statement s = null;
0063:
0064: /* Run all parts of this test, and catch any exceptions */
0065: try {
0066: System.out
0067: .println("Test declaredGlobalTempTableJava starting");
0068:
0069: /* Load the JDBC Driver class */
0070: // use the ij utility to read the property file and
0071: // make the initial connection.
0072: ij.getPropertyArg(args);
0073: con = ij.startJBMS();
0074: isDerbyNet = TestUtil.isNetFramework();
0075:
0076: con.setAutoCommit(false);
0077: s = con.createStatement();
0078:
0079: /* Test temp tables with holdable cursors and with ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS */
0080: /* Test temp tables rollback behavior in combination with savepoints */
0081: passed = testHoldableCursorsAndSavepoints(con, s) && passed;
0082:
0083: /* Test pooled connection close behavior */
0084: passed = testPooledConnectionClose() && passed;
0085:
0086: con.close();
0087:
0088: } catch (Throwable e) {
0089: System.out.println("FAIL -- unexpected exception " + e);
0090: JDBCDisplayUtil.ShowException(System.out, e);
0091: e.printStackTrace();
0092: passed = false;
0093: }
0094:
0095: if (passed)
0096: System.out.println("PASS");
0097:
0098: System.out.println("Test declaredGlobalTempTable finished");
0099: }
0100:
0101: /**
0102: * Test temp tables with holdable cursors and with ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS
0103: * Test temp tables rollback behavior in combination with savepoints
0104: *
0105: * @param conn The Connection
0106: * @param s A Statement on the Connection
0107: *
0108: * @return true if it succeeds, false if it doesn't
0109: *
0110: * @exception SQLException Thrown if some unexpected error happens
0111: */
0112:
0113: static boolean testHoldableCursorsAndSavepoints(Connection con,
0114: Statement s) throws SQLException {
0115: boolean passed = true;
0116:
0117: try {
0118: System.out
0119: .println("TEST1 : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors");
0120:
0121: System.out
0122: .println("Temp table t1 with held open cursors on it. Data should be preserved in t1 at commit time");
0123: //create a statement with hold cursors over commit
0124: Statement s1 = con.createStatement(
0125: ResultSet.TYPE_FORWARD_ONLY,
0126: ResultSet.CONCUR_READ_ONLY,
0127: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0128: s1
0129: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
0130: s1.executeUpdate("insert into session.t1 values(11, 1)");
0131: s1.executeUpdate("insert into session.t1 values(12, 2)");
0132: ResultSet rs1 = s1
0133: .executeQuery("select count(*) from SESSION.t1"); //should return count of 2
0134: dumpRS(rs1);
0135:
0136: rs1 = s1.executeQuery("select * from SESSION.t1"); //hold cursor open on t1. Commit should preserve the rows
0137: rs1.next();
0138:
0139: System.out
0140: .println("Temp tables t2 & t3 with one held open cursor on them together. Data should be preserved in t2 & t3 at commit time");
0141: Statement s2 = con.createStatement(
0142: ResultSet.TYPE_FORWARD_ONLY,
0143: ResultSet.CONCUR_READ_ONLY,
0144: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0145: s2
0146: .executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
0147: s2.executeUpdate("insert into session.t2 values(21, 1)");
0148: s2.executeUpdate("insert into session.t2 values(22, 2)");
0149: ResultSet rs23 = s2
0150: .executeQuery("select count(*) from SESSION.t2"); //should return count of 2
0151: dumpRS(rs23);
0152:
0153: s2
0154: .executeUpdate("declare global temporary table SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
0155: s2.executeUpdate("insert into session.t3 values(31, 1)");
0156: s2.executeUpdate("insert into session.t3 values(32, 2)");
0157: rs23 = s2.executeQuery("select count(*) from SESSION.t3"); //should return count of 2
0158: dumpRS(rs23);
0159:
0160: rs23 = s2
0161: .executeQuery("select * from SESSION.t2, SESSION.t3 where c22=c32"); //hold cursor open on t2 & t3. Commit should preseve the rows
0162: rs23.next();
0163:
0164: System.out
0165: .println("Temp table t4 with one held cursor but it is closed before commit. Data should be deleted from t4 at commit time");
0166: Statement s3 = con.createStatement(
0167: ResultSet.TYPE_FORWARD_ONLY,
0168: ResultSet.CONCUR_READ_ONLY,
0169: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0170: s3
0171: .executeUpdate("declare global temporary table SESSION.t4(c41 int, c42 int) on commit delete rows not logged");
0172: s3.executeUpdate("insert into session.t4 values(41, 1)");
0173: s3.executeUpdate("insert into session.t4 values(42, 2)");
0174: ResultSet rs4 = s3
0175: .executeQuery("select count(*) from SESSION.t4"); //should return count of 2
0176: dumpRS(rs4);
0177:
0178: rs4 = s3.executeQuery("select * from SESSION.t4"); //hold cursor open on t4 but close it before commit.
0179: rs4.next();
0180: rs4.close();
0181:
0182: con.commit();
0183:
0184: System.out.println("After commit, verify all the 4 tables");
0185:
0186: System.out
0187: .println("Temp table t1 will have the data intact after commit");
0188: rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
0189: dumpRS(rs1);
0190:
0191: System.out
0192: .println("Temp table t2 will have the data intact after commit");
0193: rs23 = s2.executeQuery("select count(*) from SESSION.t2"); //should return count of 2
0194: dumpRS(rs23);
0195:
0196: System.out
0197: .println("Temp table t3 will have the data intact after commit");
0198: rs23 = s2.executeQuery("select count(*) from SESSION.t3"); //should return count of 2
0199: dumpRS(rs23);
0200:
0201: System.out
0202: .println("Temp table t4 will have no data after commit");
0203: rs4 = s3.executeQuery("select count(*) from SESSION.t4"); //should return count of 0
0204: dumpRS(rs4);
0205:
0206: s.executeUpdate("drop table SESSION.t1");
0207: s.executeUpdate("drop table SESSION.t2");
0208: s.executeUpdate("drop table SESSION.t3");
0209: s.executeUpdate("drop table SESSION.t4");
0210:
0211: con.commit();
0212: System.out.println("TEST1 PASSED");
0213: } catch (Throwable e) {
0214: System.out.println("Unexpected message: " + e.getMessage());
0215: e.printStackTrace(System.out);
0216: con.rollback();
0217: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0218: System.out.println("TEST1 FAILED");
0219: }
0220:
0221: try {
0222: System.out
0223: .println("TEST1a : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors on prepared statement");
0224:
0225: System.out
0226: .println("Temp table t1 with held open cursors on it. Data should be preserved in t1 at commit time");
0227: Statement s1 = con.createStatement();
0228: s1
0229: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
0230: s1.executeUpdate("insert into session.t1 values(11, 1)");
0231: s1.executeUpdate("insert into session.t1 values(12, 2)");
0232:
0233: //create a prepared statement with hold cursors over commit
0234: PreparedStatement ps1 = con.prepareStatement(
0235: "select count(*) from SESSION.t1",
0236: ResultSet.TYPE_FORWARD_ONLY,
0237: ResultSet.CONCUR_READ_ONLY,
0238: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0239: ResultSet rs1 = ps1.executeQuery(); //should return count of 2
0240: dumpRS(rs1);
0241:
0242: PreparedStatement ps2 = con.prepareStatement(
0243: "select * from SESSION.t1",
0244: ResultSet.TYPE_FORWARD_ONLY,
0245: ResultSet.CONCUR_READ_ONLY,
0246: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0247: ResultSet rs11 = ps2.executeQuery(); //hold cursor open on t1. Commit should preserve the rows
0248: rs11.next(); //notice that we didn't close rs11 with hold cursor on commit
0249:
0250: System.out
0251: .println("Temp table t2 with one held cursor but it is closed before commit. Data should be deleted from t2 at commit time");
0252: s1
0253: .executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
0254: s1.executeUpdate("insert into session.t2 values(21, 1)");
0255: s1.executeUpdate("insert into session.t2 values(22, 2)");
0256:
0257: //create a prepared statement with hold cursors over commit
0258: PreparedStatement ps3 = con.prepareStatement(
0259: "select count(*) from SESSION.t2",
0260: ResultSet.TYPE_FORWARD_ONLY,
0261: ResultSet.CONCUR_READ_ONLY,
0262: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0263: ResultSet rs2 = ps3.executeQuery(); //should return count of 2
0264: dumpRS(rs2);
0265:
0266: PreparedStatement ps4 = con.prepareStatement(
0267: "select * from SESSION.t2",
0268: ResultSet.TYPE_FORWARD_ONLY,
0269: ResultSet.CONCUR_READ_ONLY,
0270: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0271: rs2 = ps4.executeQuery(); //hold cursor open on t2 but close it before commit.
0272: rs2.next();
0273: rs2.close();
0274:
0275: con.commit();
0276:
0277: System.out.println("After commit, verify both the tables");
0278:
0279: System.out
0280: .println("Temp table t1 will have the data intact after commit");
0281: rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
0282: dumpRS(rs1);
0283: //Need to close the held cursor on t1 before t1 can be dropped
0284: rs11.close();
0285:
0286: System.out
0287: .println("Temp table t2 will have no data after commit");
0288: rs2 = s1.executeQuery("select count(*) from SESSION.t2"); //should return count of 0
0289: dumpRS(rs2);
0290:
0291: s.executeUpdate("drop table SESSION.t1");
0292: s.executeUpdate("drop table SESSION.t2");
0293:
0294: con.commit();
0295: System.out.println("TEST1a PASSED");
0296: } catch (Throwable e) {
0297: System.out.println("Unexpected message: " + e.getMessage());
0298: con.rollback();
0299: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0300: System.out.println("TEST1a FAILED");
0301: }
0302:
0303: try {
0304: System.out
0305: .println("TEST2 : Declare a temporary table with ON COMMIT PRESERVE ROWS and various combinations of holdability");
0306:
0307: System.out
0308: .println("Temp table t1 with held open cursors on it. Data should be preserved, holdability shouldn't matter");
0309: //create a statement with hold cursors over commit
0310: Statement s1 = con.createStatement(
0311: ResultSet.TYPE_FORWARD_ONLY,
0312: ResultSet.CONCUR_READ_ONLY,
0313: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0314: s1
0315: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0316: s1.executeUpdate("insert into session.t1 values(11, 1)");
0317: s1.executeUpdate("insert into session.t1 values(12, 2)");
0318: ResultSet rs1 = s1
0319: .executeQuery("select count(*) from SESSION.t1"); //should return count of 2
0320: dumpRS(rs1);
0321:
0322: rs1 = s1.executeQuery("select * from SESSION.t1"); //hold cursor open on t1.
0323: rs1.next();
0324:
0325: con.commit();
0326:
0327: System.out.println("After commit, verify the table");
0328:
0329: System.out
0330: .println("Temp table t1 will have data after commit");
0331: rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
0332: dumpRS(rs1);
0333:
0334: s.executeUpdate("drop table SESSION.t1");
0335: con.commit();
0336: System.out.println("TEST2 PASSED");
0337: } catch (Throwable e) {
0338: System.out.println("Unexpected message: " + e.getMessage());
0339: con.rollback();
0340: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0341: System.out.println("TEST2 FAILED");
0342: }
0343:
0344: try {
0345: System.out
0346: .println("TEST3A : Savepoint and Rollback behavior");
0347:
0348: System.out.println(" In the transaction:");
0349: System.out
0350: .println(" Create savepoint1 and declare temp table t1");
0351: Savepoint savepoint1 = con.setSavepoint();
0352: s
0353: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0354: PreparedStatement pStmt = con
0355: .prepareStatement("insert into SESSION.t1 values (?, ?)");
0356: pStmt.setInt(1, 11);
0357: pStmt.setInt(2, 1);
0358: pStmt.execute();
0359: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0360: dumpRS(rs1);
0361:
0362: System.out
0363: .println(" Create savepoint 2, drop temp table t1, rollback savepoint 2");
0364: Savepoint savepoint2 = con.setSavepoint();
0365: s.executeUpdate("drop table SESSION.t1");
0366: try {
0367: rs1 = s.executeQuery("select * from SESSION.t1");
0368: } catch (Throwable e) {
0369: System.out.println("Expected message: "
0370: + e.getMessage());
0371: }
0372: con.rollback(savepoint2);
0373:
0374: System.out
0375: .println(" select should pass, rollback savepoint 1, select should fail");
0376: rs1 = s.executeQuery("select * from SESSION.t1");
0377: dumpRS(rs1);
0378: con.rollback(savepoint1);
0379: rs1 = s.executeQuery("select * from SESSION.t1");
0380:
0381: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0382: System.out.println("TEST3A FAILED");
0383: } catch (Throwable e) {
0384: System.out.println("Expected message: " + e.getMessage());
0385: con.commit();
0386: System.out.println("TEST3A PASSED");
0387: }
0388:
0389: try {
0390: System.out
0391: .println("TEST3B : Savepoint and Rollback behavior");
0392:
0393: System.out.println(" In the transaction:");
0394: System.out
0395: .println(" Create savepoint1 and declare temp table t1");
0396: Savepoint savepoint1 = con.setSavepoint();
0397: s
0398: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0399:
0400: System.out
0401: .println(" Create savepoint2 and declare temp table t2");
0402: Savepoint savepoint2 = con.setSavepoint();
0403: s
0404: .executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
0405:
0406: System.out
0407: .println(" Release savepoint 1 and select from temp table t1 & t2");
0408: con.releaseSavepoint(savepoint1);
0409: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0410: dumpRS(rs1);
0411: rs1 = s.executeQuery("select * from SESSION.t2");
0412: dumpRS(rs1);
0413:
0414: System.out
0415: .println(" Drop temp table t2(explicit drop), rollback transaction(implicit drop of t1)");
0416: s.executeUpdate("drop table SESSION.t2");
0417: con.rollback();
0418:
0419: System.out
0420: .println(" Select from temp table t1 and t2 will fail");
0421: try {
0422: rs1 = s.executeQuery("select * from SESSION.t1");
0423: } catch (Throwable e) {
0424: System.out.println("Expected message: "
0425: + e.getMessage());
0426: }
0427: try {
0428: rs1 = s.executeQuery("select * from SESSION.t2");
0429: } catch (Throwable e) {
0430: System.out.println("Expected message: "
0431: + e.getMessage());
0432: }
0433: con.commit();
0434: System.out.println("TEST3B PASSED");
0435: } catch (Throwable e) {
0436: System.out.println("Unexpected message: " + e.getMessage());
0437: con.rollback();
0438: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0439: System.out.println("TEST3B FAILED");
0440: }
0441:
0442: try {
0443: System.out
0444: .println("TEST3C : Savepoint and Rollback behavior");
0445:
0446: System.out.println(" In the transaction:");
0447: System.out
0448: .println(" Create savepoint1 and declare temp table t1");
0449: Savepoint savepoint1 = con.setSavepoint();
0450: s
0451: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0452:
0453: System.out
0454: .println(" Create savepoint2 and declare temp table t2");
0455: Savepoint savepoint2 = con.setSavepoint();
0456: s
0457: .executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
0458:
0459: System.out
0460: .println(" Release savepoint 1 and select from temp table t1 and t2");
0461: con.releaseSavepoint(savepoint1);
0462: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0463: dumpRS(rs1);
0464: rs1 = s.executeQuery("select * from SESSION.t2");
0465: dumpRS(rs1);
0466:
0467: System.out
0468: .println(" Create savepoint3 and rollback savepoint3(should not touch t1 and t2)");
0469: Savepoint savepoint3 = con.setSavepoint();
0470: con.rollback(savepoint3);
0471:
0472: System.out
0473: .println(" select from temp tables t1 and t2 should pass");
0474: rs1 = s.executeQuery("select * from SESSION.t1");
0475: dumpRS(rs1);
0476: rs1 = s.executeQuery("select * from SESSION.t2");
0477: dumpRS(rs1);
0478:
0479: System.out
0480: .println(" Rollback transaction and select from temp tables t1 and t2 should fail");
0481: con.rollback();
0482: try {
0483: rs1 = s.executeQuery("select * from SESSION.t1");
0484: } catch (Throwable e) {
0485: System.out.println("Expected message: "
0486: + e.getMessage());
0487: }
0488: try {
0489: rs1 = s.executeQuery("select * from SESSION.t2");
0490: } catch (Throwable e) {
0491: System.out.println("Expected message: "
0492: + e.getMessage());
0493: }
0494:
0495: con.commit();
0496: System.out.println("TEST3C PASSED");
0497: } catch (Throwable e) {
0498: System.out.println("Unexpected message: " + e.getMessage());
0499: con.rollback();
0500: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0501: System.out.println("TEST3C FAILED");
0502: }
0503:
0504: try {
0505: System.out
0506: .println("TEST3D : Savepoint and Rollback behavior");
0507:
0508: System.out.println(" In the transaction:");
0509: System.out
0510: .println(" Create savepoint1 and declare temp table t1");
0511: Savepoint savepoint1 = con.setSavepoint();
0512: s
0513: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0514:
0515: System.out
0516: .println(" Create savepoint2 and drop temp table t1");
0517: Savepoint savepoint2 = con.setSavepoint();
0518: s.executeUpdate("drop table SESSION.t1");
0519:
0520: System.out
0521: .println(" Rollback savepoint2 and select temp table t1");
0522: con.rollback(savepoint2);
0523: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0524: dumpRS(rs1);
0525:
0526: System.out
0527: .println(" Commit transaction and select temp table t1");
0528: con.commit();
0529: rs1 = s.executeQuery("select * from SESSION.t1");
0530: dumpRS(rs1);
0531:
0532: s.executeUpdate("drop table SESSION.t1");
0533: con.commit();
0534: System.out.println("TEST3D PASSED");
0535: } catch (Throwable e) {
0536: System.out.println("Unexpected message: " + e.getMessage());
0537: con.rollback();
0538: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0539: System.out.println("TEST3D FAILED");
0540: }
0541:
0542: try {
0543: System.out
0544: .println("TEST3E : Savepoint and Rollback behavior");
0545:
0546: System.out.println(" In the transaction:");
0547: System.out
0548: .println(" Create savepoint1 and declare temp table t1");
0549: Savepoint savepoint1 = con.setSavepoint();
0550: s
0551: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0552:
0553: System.out
0554: .println(" Create savepoint2 and drop temp table t1");
0555: Savepoint savepoint2 = con.setSavepoint();
0556: s.executeUpdate("drop table SESSION.t1");
0557:
0558: System.out
0559: .println(" Rollback savepoint 1 and select from temp table t1 should fail");
0560: con.rollback(savepoint1);
0561: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0562:
0563: con.rollback();
0564: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0565: System.out.println("TEST3E FAILED");
0566: } catch (Throwable e) {
0567: System.out.println("Expected message: " + e.getMessage());
0568: con.commit();
0569: System.out.println("TEST3E PASSED");
0570: }
0571:
0572: try {
0573: System.out
0574: .println("TEST3F : Savepoint and Rollback behavior");
0575:
0576: System.out.println(" In the transaction:");
0577: System.out
0578: .println(" declare temp table t1 and drop temp table t1");
0579: s
0580: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0581: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0582: dumpRS(rs1);
0583: s.executeUpdate("drop table SESSION.t1");
0584: System.out.println(" rollback, select on t1 should fail");
0585: con.rollback();
0586: rs1 = s.executeQuery("select * from SESSION.t1");
0587:
0588: con.rollback();
0589: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0590: System.out.println("TEST3F FAILED");
0591: } catch (Throwable e) {
0592: System.out.println("Expected message: " + e.getMessage());
0593: con.commit();
0594: System.out.println("TEST3F PASSED");
0595: }
0596:
0597: try {
0598: System.out
0599: .println("TEST3G : Savepoint and Rollback behavior");
0600:
0601: System.out.println(" In the transaction:");
0602: System.out.println(" declare temp table t1 and commit");
0603: s
0604: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0605: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0606: dumpRS(rs1);
0607: con.commit();
0608: System.out.println(" In the transaction:");
0609: System.out
0610: .println(" drop temp table t1 and rollback, select on t1 should pass");
0611: s.executeUpdate("drop table SESSION.t1");
0612: con.rollback();
0613: rs1 = s.executeQuery("select * from SESSION.t1");
0614: dumpRS(rs1);
0615:
0616: s.executeUpdate("drop table SESSION.t1");
0617: con.commit();
0618: System.out.println("TEST3G PASSED");
0619: } catch (Throwable e) {
0620: System.out.println("Unexpected message: " + e.getMessage());
0621: con.rollback();
0622: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0623: System.out.println("TEST3G FAILED");
0624: }
0625:
0626: try {
0627: System.out
0628: .println("TEST3H : Savepoint and commit behavior");
0629:
0630: System.out.println(" In the transaction:");
0631: System.out.println(" declare temp table t1 and commit");
0632: s
0633: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0634: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0635: dumpRS(rs1);
0636: con.commit();
0637: System.out.println(" In the transaction:");
0638: System.out
0639: .println(" drop temp table t1 and commit, select on t1 should fail");
0640: s.executeUpdate("drop table SESSION.t1");
0641: con.commit();
0642: rs1 = s.executeQuery("select * from SESSION.t1");
0643:
0644: con.rollback();
0645: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0646: System.out.println("TEST3H FAILED");
0647: } catch (Throwable e) {
0648: System.out.println("Expected message: " + e.getMessage());
0649: con.commit();
0650: System.out.println("TEST3H PASSED");
0651: }
0652:
0653: try {
0654: System.out
0655: .println("TEST3I : Savepoint and Rollback behavior");
0656:
0657: System.out.println(" In the transaction:");
0658: System.out.println(" declare temp table t1 and rollback");
0659: s
0660: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0661: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0662: dumpRS(rs1);
0663: con.rollback();
0664: rs1 = s.executeQuery("select * from SESSION.t1");
0665:
0666: con.rollback();
0667: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0668: System.out.println("TEST3I FAILED");
0669: } catch (Throwable e) {
0670: System.out.println("Expected message: " + e.getMessage());
0671: con.commit();
0672: System.out.println("TEST3I PASSED");
0673: }
0674:
0675: try {
0676: System.out
0677: .println("TEST3J : Savepoint and Rollback behavior");
0678:
0679: System.out.println(" In the transaction:");
0680: System.out
0681: .println(" declare temp table t1 with 2 columns and commit");
0682: s
0683: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
0684: s.executeUpdate("insert into SESSION.t1 values(11, 11)");
0685: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0686: dumpRS(rs1);
0687: con.commit();
0688: System.out
0689: .println(" Create savepoint1 and drop temp table t1 with 2 columns");
0690: Savepoint savepoint1 = con.setSavepoint();
0691: s.executeUpdate("drop table SESSION.t1");
0692: System.out
0693: .println(" declare temp table t1 but this time with 3 columns");
0694: s
0695: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int, c13 int not null) on commit preserve rows not logged");
0696: s
0697: .executeUpdate("insert into SESSION.t1 values(22, 22, 22)");
0698: rs1 = s.executeQuery("select * from SESSION.t1");
0699: dumpRS(rs1);
0700: System.out
0701: .println(" Create savepoint2 and drop temp table t1 with 3 columns");
0702: Savepoint savepoint2 = con.setSavepoint();
0703: s.executeUpdate("drop table SESSION.t1");
0704: con.rollback();
0705: System.out
0706: .println(" select from temp table t1 here should have 2 columns");
0707: rs1 = s.executeQuery("select * from SESSION.t1");
0708: dumpRS(rs1);
0709: s.executeUpdate("drop table SESSION.t1");
0710:
0711: con.commit();
0712: System.out.println("TEST3J PASSED");
0713: } catch (Throwable e) {
0714: System.out.println("Unexpected message: " + e.getMessage());
0715: con.rollback();
0716: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0717: System.out.println("TEST3J FAILED");
0718: }
0719:
0720: try {
0721: System.out
0722: .println("TEST3K : Savepoint and Rollback behavior");
0723:
0724: System.out.println(" In the transaction:");
0725: System.out
0726: .println(" declare temp table t1 & t2, insert few rows and commit");
0727: s
0728: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
0729: s
0730: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
0731: s.executeUpdate("insert into SESSION.t1 values(11, 1)");
0732: s.executeUpdate("insert into session.t2 values(21, 1)");
0733: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0734: dumpRS(rs1);
0735: rs1 = s.executeQuery("select * from SESSION.t2");
0736: dumpRS(rs1);
0737: con.commit();
0738:
0739: System.out
0740: .println(" In the next transaction, insert couple more rows in t1 & t2 and ");
0741: s.executeUpdate("insert into SESSION.t1 values(12, 2)");
0742: s.executeUpdate("insert into SESSION.t2 values(22, 2)");
0743: rs1 = s.executeQuery("select * from SESSION.t1");
0744: dumpRS(rs1);
0745: rs1 = s.executeQuery("select * from SESSION.t2");
0746: dumpRS(rs1);
0747:
0748: System.out
0749: .println(" Create savepoint1 and update some rows in t1 and inspect the data");
0750: Savepoint savepoint1 = con.setSavepoint();
0751: s
0752: .executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
0753: rs1 = s.executeQuery("select * from SESSION.t1");
0754: dumpRS(rs1);
0755:
0756: System.out
0757: .println(" update t2 with where clause such that no rows get modified in t2 and inspect the data");
0758: s
0759: .executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>2");
0760: rs1 = s.executeQuery("select * from SESSION.t2");
0761: dumpRS(rs1);
0762:
0763: System.out
0764: .println(" Rollback to savepoint1 and we should loose all the rows in t1");
0765: con.rollback(savepoint1);
0766: rs1 = s.executeQuery("select * from SESSION.t1");
0767: dumpRS(rs1);
0768: System.out
0769: .println(" temp table t2 should also have no rows because attempt was made to modify it (even though nothing actually got modified in t2 in the savepoint)");
0770: rs1 = s.executeQuery("select * from SESSION.t2");
0771: dumpRS(rs1);
0772:
0773: System.out
0774: .println(" Commit the transaction and should see no data in t1 and t2");
0775: con.commit();
0776: rs1 = s.executeQuery("select * from SESSION.t1");
0777: dumpRS(rs1);
0778: rs1 = s.executeQuery("select * from SESSION.t2");
0779: dumpRS(rs1);
0780:
0781: s.executeUpdate("drop table SESSION.t1");
0782: s.executeUpdate("drop table SESSION.t2");
0783: con.commit();
0784: System.out.println("TEST3K PASSED");
0785: } catch (Throwable e) {
0786: System.out.println("Unexpected message: " + e.getMessage());
0787: con.rollback();
0788: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0789: System.out.println("TEST3K FAILED");
0790: }
0791:
0792: try {
0793: System.out
0794: .println("TEST3L : Savepoint and Rollback behavior");
0795:
0796: System.out.println(" In the transaction:");
0797: System.out
0798: .println(" declare temp table t1 & t2, insert few rows and commit");
0799: s
0800: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
0801: s
0802: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
0803: s.executeUpdate("insert into SESSION.t1 values(11, 1)");
0804: s.executeUpdate("insert into session.t2 values(21, 1)");
0805: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0806: dumpRS(rs1);
0807: rs1 = s.executeQuery("select * from SESSION.t2");
0808: dumpRS(rs1);
0809: con.commit();
0810:
0811: System.out
0812: .println(" In the next transaction, insert couple more rows in t1 & t2 and ");
0813: s.executeUpdate("insert into SESSION.t1 values(12, 2)");
0814: s.executeUpdate("insert into session.t2 values(22, 2)");
0815: rs1 = s.executeQuery("select * from SESSION.t1");
0816: dumpRS(rs1);
0817: rs1 = s.executeQuery("select * from SESSION.t2");
0818: dumpRS(rs1);
0819:
0820: System.out
0821: .println(" Create savepoint1 and update some rows in t1 and inspect the data");
0822: Savepoint savepoint1 = con.setSavepoint();
0823: s
0824: .executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
0825: rs1 = s.executeQuery("select * from SESSION.t1");
0826: dumpRS(rs1);
0827:
0828: System.out
0829: .println(" update t2 with where clause such that no rows get modified in t2 and inspect the data");
0830: s
0831: .executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>3");
0832: rs1 = s.executeQuery("select * from SESSION.t2");
0833: dumpRS(rs1);
0834:
0835: System.out
0836: .println(" Rollback to savepoint1 and we should loose all the rows in t1");
0837: con.rollback(savepoint1);
0838: rs1 = s.executeQuery("select * from SESSION.t1");
0839: dumpRS(rs1);
0840: System.out
0841: .println(" temp table t2 should also have no rows because attempt was made to modfiy it (even though nothing actually got modified in t2 in the savepoint)");
0842: rs1 = s.executeQuery("select * from SESSION.t2");
0843: dumpRS(rs1);
0844:
0845: System.out
0846: .println(" Rollback the transaction and should see no data in t1 and t2");
0847: con.rollback();
0848: rs1 = s.executeQuery("select * from SESSION.t1");
0849: dumpRS(rs1);
0850: rs1 = s.executeQuery("select * from SESSION.t2");
0851: dumpRS(rs1);
0852:
0853: s.executeUpdate("drop table SESSION.t1");
0854: s.executeUpdate("drop table SESSION.t2");
0855: con.commit();
0856: System.out.println("TEST3L PASSED");
0857: } catch (Throwable e) {
0858: System.out.println("Unexpected message: " + e.getMessage());
0859: con.rollback();
0860: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0861: System.out.println("TEST3L FAILED");
0862: }
0863:
0864: try {
0865: System.out
0866: .println("TEST3M : Savepoint and Rollback behavior");
0867:
0868: System.out.println(" In the transaction:");
0869: System.out
0870: .println(" declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
0871: s
0872: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
0873: s
0874: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
0875: s
0876: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
0877: s
0878: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
0879: s.executeUpdate("insert into SESSION.t1 values(11, 1)");
0880: s.executeUpdate("insert into SESSION.t2 values(21, 1)");
0881: s.executeUpdate("insert into SESSION.t3 values(31, 1)");
0882: s.executeUpdate("insert into SESSION.t4 values(41, 1)");
0883: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0884: dumpRS(rs1);
0885: rs1 = s.executeQuery("select * from SESSION.t2");
0886: dumpRS(rs1);
0887: rs1 = s.executeQuery("select * from SESSION.t3");
0888: dumpRS(rs1);
0889: rs1 = s.executeQuery("select * from SESSION.t4");
0890: dumpRS(rs1);
0891: con.commit();
0892:
0893: System.out
0894: .println(" In the next transaction, insert couple more rows in t1 & t2 & t3 and ");
0895: s.executeUpdate("insert into SESSION.t1 values(12, 2)");
0896: s.executeUpdate("insert into session.t2 values(22, 2)");
0897: s.executeUpdate("insert into session.t3 values(32, 2)");
0898: rs1 = s.executeQuery("select * from SESSION.t1");
0899: dumpRS(rs1);
0900: rs1 = s.executeQuery("select * from SESSION.t2");
0901: dumpRS(rs1);
0902: rs1 = s.executeQuery("select * from SESSION.t3");
0903: dumpRS(rs1);
0904:
0905: System.out
0906: .println(" Create savepoint1 and delete some rows from t1 and inspect the data in t1");
0907: Savepoint savepoint1 = con.setSavepoint();
0908: s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
0909: rs1 = s.executeQuery("select * from SESSION.t1");
0910: dumpRS(rs1);
0911:
0912: System.out
0913: .println(" Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
0914: Savepoint savepoint2 = con.setSavepoint();
0915: s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
0916: rs1 = s.executeQuery("select * from SESSION.t2");
0917: dumpRS(rs1);
0918:
0919: System.out
0920: .println(" Release savepoint2 and now savepoint1 should keep track of changes made to t1 and t2, inspect the data in t1 & t2");
0921: con.releaseSavepoint(savepoint2);
0922: rs1 = s.executeQuery("select * from SESSION.t1");
0923: dumpRS(rs1);
0924: rs1 = s.executeQuery("select * from SESSION.t2");
0925: dumpRS(rs1);
0926:
0927: System.out
0928: .println(" Rollback savepoint1 and should see no data in t1 and t2, inspect the data");
0929: con.rollback(savepoint1);
0930: rs1 = s.executeQuery("select * from SESSION.t1");
0931: dumpRS(rs1);
0932: rs1 = s.executeQuery("select * from SESSION.t2");
0933: dumpRS(rs1);
0934:
0935: System.out
0936: .println(" Should see data in t3 since it was not touched in the savepoint that was rolled back");
0937: rs1 = s.executeQuery("select * from SESSION.t3");
0938: dumpRS(rs1);
0939:
0940: System.out
0941: .println(" Rollback the transaction and should see no data in t1 and t2 and t3");
0942: con.rollback();
0943: rs1 = s.executeQuery("select * from SESSION.t1");
0944: dumpRS(rs1);
0945: rs1 = s.executeQuery("select * from SESSION.t2");
0946: dumpRS(rs1);
0947: rs1 = s.executeQuery("select * from SESSION.t3");
0948: dumpRS(rs1);
0949:
0950: System.out
0951: .println(" Should see data in t4 since it was not touched in the transaction that was rolled back");
0952: rs1 = s.executeQuery("select * from SESSION.t4");
0953: dumpRS(rs1);
0954:
0955: s.executeUpdate("drop table SESSION.t1");
0956: s.executeUpdate("drop table SESSION.t2");
0957: s.executeUpdate("drop table SESSION.t3");
0958: s.executeUpdate("drop table SESSION.t4");
0959: con.commit();
0960: System.out.println("TEST3M PASSED");
0961: } catch (Throwable e) {
0962: System.out.println("Unexpected message: " + e.getMessage());
0963: con.rollback();
0964: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0965: System.out.println("TEST3M FAILED");
0966: }
0967:
0968: try {
0969: System.out
0970: .println("TEST3N : Savepoint and Rollback behavior");
0971:
0972: System.out.println(" In the transaction:");
0973: System.out
0974: .println(" declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
0975: s
0976: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
0977: s
0978: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
0979: s
0980: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
0981: s
0982: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
0983: s.executeUpdate("insert into SESSION.t1 values(11, 1)");
0984: s.executeUpdate("insert into SESSION.t1 values(12, 2)");
0985: s.executeUpdate("insert into SESSION.t2 values(21, 1)");
0986: s.executeUpdate("insert into SESSION.t2 values(22, 2)");
0987: s.executeUpdate("insert into SESSION.t3 values(31, 1)");
0988: s.executeUpdate("insert into SESSION.t4 values(41, 1)");
0989: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
0990: dumpRS(rs1);
0991: rs1 = s.executeQuery("select * from SESSION.t2");
0992: dumpRS(rs1);
0993: rs1 = s.executeQuery("select * from SESSION.t3");
0994: dumpRS(rs1);
0995: rs1 = s.executeQuery("select * from SESSION.t4");
0996: dumpRS(rs1);
0997: con.commit();
0998:
0999: System.out
1000: .println(" In the next transaction, insert couple more rows in t3 ");
1001: s.executeUpdate("insert into SESSION.t3 values(31, 2)");
1002: rs1 = s.executeQuery("select * from SESSION.t3");
1003: dumpRS(rs1);
1004:
1005: System.out
1006: .println(" Create savepoint1 and delete some rows from t1 and inspect the data in t1");
1007: Savepoint savepoint1 = con.setSavepoint();
1008: s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
1009: rs1 = s.executeQuery("select * from SESSION.t1");
1010: dumpRS(rs1);
1011:
1012: System.out
1013: .println(" delete from t2 with where clause such that no rows are deleted from t2 and inspect the data in t2");
1014: s.executeUpdate("DELETE FROM SESSION.t2 where c22>3");
1015: rs1 = s.executeQuery("select * from SESSION.t2");
1016: dumpRS(rs1);
1017:
1018: System.out
1019: .println(" Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
1020: Savepoint savepoint2 = con.setSavepoint();
1021: s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
1022: rs1 = s.executeQuery("select * from SESSION.t2");
1023: dumpRS(rs1);
1024:
1025: System.out
1026: .println(" Rollback the transaction and should see no data in t1 and t2 and t3");
1027: con.rollback();
1028: rs1 = s.executeQuery("select * from SESSION.t1");
1029: dumpRS(rs1);
1030: rs1 = s.executeQuery("select * from SESSION.t2");
1031: dumpRS(rs1);
1032: rs1 = s.executeQuery("select * from SESSION.t3");
1033: dumpRS(rs1);
1034:
1035: System.out
1036: .println(" Should see data in t4 since it was not touched in the transaction that was rolled back");
1037: rs1 = s.executeQuery("select * from SESSION.t4");
1038: dumpRS(rs1);
1039:
1040: s.executeUpdate("drop table SESSION.t1");
1041: s.executeUpdate("drop table SESSION.t2");
1042: s.executeUpdate("drop table SESSION.t3");
1043: s.executeUpdate("drop table SESSION.t4");
1044: con.commit();
1045: System.out.println("TEST3N PASSED");
1046: } catch (Throwable e) {
1047: System.out.println("Unexpected message: " + e.getMessage());
1048: con.rollback();
1049: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1050: System.out.println("TEST3N FAILED");
1051: }
1052:
1053: try {
1054: System.out
1055: .println("TEST3O : Savepoint and Rollback behavior");
1056:
1057: System.out.println(" In the transaction:");
1058: System.out
1059: .println(" declare temp table t1 & t2, insert few rows and commit");
1060: s
1061: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
1062: s
1063: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
1064: s.executeUpdate("insert into SESSION.t1 values(11, 1)");
1065: s.executeUpdate("insert into SESSION.t2 values(21, 1)");
1066: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
1067: dumpRS(rs1);
1068: rs1 = s.executeQuery("select * from SESSION.t2");
1069: dumpRS(rs1);
1070: con.commit();
1071:
1072: System.out
1073: .println(" In the next transaction, insert couple more rows in t1 ");
1074: s.executeUpdate("insert into SESSION.t1 values(12, 2)");
1075: rs1 = s.executeQuery("select * from SESSION.t1");
1076: dumpRS(rs1);
1077:
1078: System.out
1079: .println(" Create savepoint1 and insert one row in t2 and inspect the data in t2");
1080: Savepoint savepoint1 = con.setSavepoint();
1081: s.executeUpdate("insert into SESSION.t2 values(22, 2)");
1082: rs1 = s.executeQuery("select * from SESSION.t2");
1083: dumpRS(rs1);
1084:
1085: System.out
1086: .println(" Rollback savepoint1 and should see no data in t2 but t1 should have data, inspect the data");
1087: con.rollback(savepoint1);
1088: rs1 = s.executeQuery("select * from SESSION.t1");
1089: dumpRS(rs1);
1090: rs1 = s.executeQuery("select * from SESSION.t2");
1091: dumpRS(rs1);
1092:
1093: System.out
1094: .println(" Commit the transaction and should see no data in t2 but t1 should have data");
1095: con.commit();
1096: rs1 = s.executeQuery("select * from SESSION.t1");
1097: dumpRS(rs1);
1098: rs1 = s.executeQuery("select * from SESSION.t2");
1099: dumpRS(rs1);
1100:
1101: s.executeUpdate("drop table SESSION.t1");
1102: s.executeUpdate("drop table SESSION.t2");
1103: con.commit();
1104: System.out.println("TEST3O PASSED");
1105: } catch (Throwable e) {
1106: System.out.println("Unexpected message: " + e.getMessage());
1107: con.rollback();
1108: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1109: System.out.println("TEST3O FAILED");
1110: }
1111:
1112: try {
1113: System.out
1114: .println("TEST3P : Savepoint and Rollback behavior");
1115:
1116: System.out.println(" In the transaction:");
1117: System.out
1118: .println(" declare temp table t1, insert few rows and commit");
1119: s
1120: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
1121: s.executeUpdate("insert into SESSION.t1 values(11, 1)");
1122: s.executeUpdate("insert into SESSION.t1 values(12, 2)");
1123: con.commit();
1124:
1125: System.out.println(" In the transaction:");
1126: System.out
1127: .println(" Create savepoint1 and insert some rows into t1 and inspect the data in t1");
1128: Savepoint savepoint1 = con.setSavepoint();
1129: s.executeUpdate("insert into SESSION.t1 values(13, 3)");
1130:
1131: System.out
1132: .println(" Release savepoint1 and now transaction should keep track of changes made to t1, inspect the data in t1");
1133: con.releaseSavepoint(savepoint1);
1134: ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
1135: dumpRS(rs1);
1136:
1137: System.out
1138: .println(" Rollback the transaction and should still see no data in t1");
1139: con.rollback();
1140: rs1 = s.executeQuery("select * from SESSION.t1");
1141: dumpRS(rs1);
1142:
1143: s.executeUpdate("drop table SESSION.t1");
1144: con.commit();
1145: System.out.println("TEST3P PASSED");
1146: } catch (Throwable e) {
1147: System.out.println("Unexpected message: " + e.getMessage());
1148: con.rollback();
1149: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1150: System.out.println("TEST3P FAILED");
1151: }
1152:
1153: try {
1154: System.out
1155: .println("TEST3Q : Prepared statement test - DML and rollback behavior");
1156: System.out.println(" In the transaction:");
1157: System.out
1158: .println(" Declare temp table t2, insert / update / delete data using various prepared statements and commit");
1159: s
1160: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
1161: PreparedStatement pStmtInsert = con
1162: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1163: pStmtInsert.setInt(1, 21);
1164: pStmtInsert.setInt(2, 1);
1165: pStmtInsert.execute();
1166: pStmtInsert.setInt(1, 22);
1167: pStmtInsert.setInt(2, 2);
1168: pStmtInsert.execute();
1169: pStmtInsert.setInt(1, 23);
1170: pStmtInsert.setInt(2, 2);
1171: pStmtInsert.execute();
1172: PreparedStatement pStmtUpdate = con
1173: .prepareStatement("UPDATE SESSION.t2 SET c22 = 3 where c21=?");
1174: pStmtUpdate.setInt(1, 23);
1175: pStmtUpdate.execute();
1176: PreparedStatement pStmtDelete = con
1177: .prepareStatement("DELETE FROM SESSION.t2 where c21 = ?");
1178: pStmtDelete.setInt(1, 23);
1179: pStmtDelete.execute();
1180:
1181: con.commit();
1182: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1183: dumpRS(rs1);
1184:
1185: System.out.println(" In the next transaction:");
1186: System.out
1187: .println(" Create savepoint1 and insert some rows into t2 using prepared statement and inspect the data in t2");
1188: Savepoint savepoint1 = con.setSavepoint();
1189: pStmtInsert.setInt(1, 23);
1190: pStmtInsert.setInt(2, 2);
1191: pStmtInsert.execute();
1192: rs1 = s.executeQuery("select * from SESSION.t2");
1193: dumpRS(rs1);
1194:
1195: System.out
1196: .println(" Create savepoint2 and update row inserted in savepoint1 using prepared statement and inspect the data in t2");
1197: Savepoint savepoint2 = con.setSavepoint();
1198: pStmtUpdate.setInt(1, 23);
1199: pStmtUpdate.execute();
1200: rs1 = s.executeQuery("select * from SESSION.t2");
1201: dumpRS(rs1);
1202:
1203: System.out
1204: .println(" rollback savepoint2 and should loose all the data from t2");
1205: con.rollback(savepoint2);
1206: rs1 = s.executeQuery("select * from SESSION.t2");
1207: dumpRS(rs1);
1208:
1209: System.out
1210: .println(" Create savepoint3 and insert some rows into t2 using prepared statement and inspect the data in t2");
1211: Savepoint savepoint3 = con.setSavepoint();
1212: pStmtInsert.setInt(1, 21);
1213: pStmtInsert.setInt(2, 1);
1214: pStmtInsert.execute();
1215: pStmtInsert.setInt(1, 22);
1216: pStmtInsert.setInt(2, 2);
1217: pStmtInsert.execute();
1218: pStmtInsert.setInt(1, 23);
1219: pStmtInsert.setInt(2, 333);
1220: pStmtInsert.execute();
1221: rs1 = s.executeQuery("select * from SESSION.t2");
1222: dumpRS(rs1);
1223:
1224: System.out
1225: .println(" Create savepoint4 and update row inserted in savepoint3 using prepared statement and inspect the data in t2");
1226: Savepoint savepoint4 = con.setSavepoint();
1227: pStmtUpdate.setInt(1, 23);
1228: pStmtUpdate.execute();
1229: rs1 = s.executeQuery("select * from SESSION.t2");
1230: dumpRS(rs1);
1231:
1232: System.out
1233: .println(" Release savepoint4 and inspect the data in t2, then delete a row from t2");
1234: con.releaseSavepoint(savepoint4);
1235: rs1 = s.executeQuery("select * from SESSION.t2");
1236: dumpRS(rs1);
1237: pStmtDelete.setInt(1, 23);
1238: pStmtDelete.execute();
1239:
1240: System.out
1241: .println(" Commit transaction and should see data data in t2");
1242: con.commit();
1243: rs1 = s.executeQuery("select * from SESSION.t2");
1244: dumpRS(rs1);
1245:
1246: s.executeUpdate("drop table SESSION.t2");
1247: con.commit();
1248: System.out.println("TEST3Q PASSED");
1249: } catch (Throwable e) {
1250: System.out.println("FAIL " + e.getMessage());
1251: e.printStackTrace(System.out);
1252: con.rollback();
1253: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1254: System.out.println("TEST3Q FAILED");
1255: }
1256:
1257: try {
1258: System.out
1259: .println("TEST4 : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors and temp table as part of subquery");
1260:
1261: System.out
1262: .println("Temp table t1 with no direct held cursor open on it. Data should be deleted from t1 at commit time");
1263: Statement s1 = con.createStatement(
1264: ResultSet.TYPE_FORWARD_ONLY,
1265: ResultSet.CONCUR_READ_ONLY,
1266: ResultSet.HOLD_CURSORS_OVER_COMMIT);
1267: s1.executeUpdate("create table t1(c11 int, c12 int)");
1268: s1
1269: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
1270: s1.executeUpdate("insert into session.t1 values(11, 1)");
1271: s1.executeUpdate("insert into session.t1 values(12, 2)");
1272: ResultSet rs1 = s1
1273: .executeQuery("select count(*) from SESSION.t1"); //should return count of 2
1274: dumpRS(rs1);
1275: rs1 = s1.executeQuery("select count(*) from t1"); //should return count of 0
1276: dumpRS(rs1);
1277: System.out
1278: .println("Insert into real table using temporary table data on a statement with holdability set to true");
1279: s1.executeUpdate("INSERT INTO T1 SELECT * FROM SESSION.T1");
1280: con.commit();
1281:
1282: System.out.println("After commit, verify both the tables");
1283:
1284: System.out
1285: .println("Temp table t1 will have no data after commit");
1286: rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 0
1287: dumpRS(rs1);
1288:
1289: System.out
1290: .println("Physical table t1 will have 2 rows after commit");
1291: rs1 = s1.executeQuery("select count(*) from t1"); //should return count of 2
1292: dumpRS(rs1);
1293:
1294: s.executeUpdate("drop table SESSION.t1");
1295: s.executeUpdate("drop table t1");
1296:
1297: con.commit();
1298: System.out.println("TEST4 PASSED");
1299: } catch (Throwable e) {
1300: System.out.println("Unexpected message: " + e.getMessage());
1301: con.rollback();
1302: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1303: System.out.println("TEST4 FAILED");
1304: }
1305:
1306: return passed;
1307: }
1308:
1309: /**
1310: * Test that global temporary tables declared in a connection handle to pooled connection are dropped at connection handle close time
1311: * and are not available to next connection handle to the same pooled connection
1312: *
1313: * @param conn The Connection
1314: * @param s A Statement on the Connection
1315: *
1316: * @return true if it succeeds, false if it doesn't
1317: *
1318: * @exception SQLException Thrown if some unexpected error happens
1319: */
1320: static boolean testPooledConnectionClose() throws SQLException {
1321: boolean passed = true;
1322: Connection con1 = null, con2 = null;
1323:
1324: try {
1325: System.out
1326: .println("TEST5 : Temporary tables declared in a pooled connection should get dropped when that pooled connection is closed");
1327: ConnectionPoolDataSource dsp;
1328: if (isDerbyNet) {
1329: /* following would require the IBM universal jdbc driver to be available during build...This section needs to be reworked for networkserver
1330: com.ibm.db2.jcc.DB2ConnectionPoolDataSource ds = new com.ibm.db2.jcc.DB2ConnectionPoolDataSource();
1331: ds.setDatabaseName("wombat");
1332: ds.setUser("cs");
1333: ds.setPassword("cs");
1334: hostName = TestUtil.getHostName();
1335: ds.setServerName(hostName);
1336: ds.setPortNumber(1527);
1337: ds.setDriverType(4);
1338: dsp = ds;
1339: */
1340: System.out
1341: .println("test will not build without universal driver");
1342: return passed;
1343:
1344: } else {
1345: EmbeddedConnectionPoolDataSource dscsp = new EmbeddedConnectionPoolDataSource();
1346: dscsp.setDatabaseName("wombat");
1347: //dscsp.setConnectionAttributes("unicode=true");
1348: dsp = dscsp;
1349: }
1350:
1351: PooledConnection pc = dsp.getPooledConnection();
1352: con1 = pc.getConnection();
1353: con1.setAutoCommit(false);
1354: Statement s = con1.createStatement();
1355:
1356: System.out
1357: .println(" In the first connection handle to the pooled connection, create physical session schema, create table t1 in it");
1358: System.out
1359: .println(" Insert some rows in physical SESSION.t1 table. Inspect the data.");
1360: s.executeUpdate("CREATE schema SESSION");
1361: s.executeUpdate("CREATE TABLE SESSION.t1(c21 int)");
1362: s.executeUpdate("insert into SESSION.t1 values(11)");
1363: s.executeUpdate("insert into SESSION.t1 values(12)");
1364: s.executeUpdate("insert into SESSION.t1 values(13)");
1365: ResultSet rs1 = s.executeQuery("select * from SESSION.t1"); //should return 3 rows for the physical table
1366: dumpRS(rs1);
1367:
1368: System.out
1369: .println(" Next declare a temp table with same name as physical table in SESSION schema.");
1370: System.out
1371: .println(" Insert some rows in temporary table SESSION.t1. Inspect the data");
1372: s
1373: .executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
1374: s.executeUpdate("insert into SESSION.t1 values(11,1)");
1375: rs1 = s.executeQuery("select * from SESSION.t1"); //should return 1 row for the temporary table
1376: dumpRS(rs1);
1377: System.out
1378: .println(" Now close the connection handle to the pooled connection");
1379: con1.commit();
1380: con1.close();
1381: con1 = null;
1382:
1383: System.out
1384: .println(" Do another getConnection() to get a new connection handle to the pooled connection");
1385: con2 = pc.getConnection();
1386: s = con2.createStatement();
1387: System.out
1388: .println(" In this new handle, a select * from SESSION.t1 should be looking at the physical session table");
1389: rs1 = s.executeQuery("select * from SESSION.t1");
1390: dumpRS(rs1);
1391:
1392: s.executeUpdate("DROP TABLE SESSION.t1");
1393: if (isDerbyNet)
1394: s.executeUpdate("DROP TABLE SESSION.t1");
1395:
1396: s.executeUpdate("DROP schema SESSION restrict");
1397: con2.commit();
1398: con2.close();
1399: System.out.println("TEST5 PASSED");
1400: } catch (Throwable e) {
1401: System.out.println("Unexpected message: " + e.getMessage());
1402: if (con1 != null)
1403: con1.rollback();
1404: if (con2 != null)
1405: con2.rollback();
1406: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1407: System.out.println("TEST5 FAILED");
1408: }
1409:
1410: return passed;
1411: }
1412:
1413: static private void dumpExpectedSQLExceptions(SQLException se) {
1414: System.out.println("PASS -- expected exception");
1415: while (se != null) {
1416: System.out.println("SQLSTATE(" + se.getSQLState() + "): "
1417: + se);
1418: se = se.getNextException();
1419: }
1420: }
1421:
1422: static private void dumpSQLExceptions(SQLException se) {
1423: System.out.println("FAIL -- unexpected exception");
1424: while (se != null) {
1425: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
1426: se.printStackTrace(System.out);
1427: se = se.getNextException();
1428: }
1429: }
1430:
1431: // lifted from the metadata test
1432: private static void dumpRS(ResultSet s) throws SQLException {
1433: if (s == null) {
1434: System.out.println("<NULL>");
1435: return;
1436: }
1437:
1438: ResultSetMetaData rsmd = s.getMetaData();
1439:
1440: // Get the number of columns in the result set
1441: int numCols = rsmd.getColumnCount();
1442:
1443: if (numCols <= 0) {
1444: System.out.println("(no columns!)");
1445: return;
1446: }
1447:
1448: StringBuffer heading = new StringBuffer("\t ");
1449: StringBuffer underline = new StringBuffer("\t ");
1450:
1451: int len;
1452: // Display column headings
1453: for (int i = 1; i <= numCols; i++) {
1454: if (i > 1) {
1455: heading.append(",");
1456: underline.append(" ");
1457: }
1458: len = heading.length();
1459: heading.append(rsmd.getColumnLabel(i));
1460: len = heading.length() - len;
1461: for (int j = len; j > 0; j--) {
1462: underline.append("-");
1463: }
1464: }
1465: System.out.println(heading.toString());
1466: System.out.println(underline.toString());
1467:
1468: StringBuffer row = new StringBuffer();
1469: // Display data, fetching until end of the result set
1470: while (s.next()) {
1471: row.append("\t{");
1472: // Loop through each column, getting the
1473: // column data and displaying
1474: for (int i = 1; i <= numCols; i++) {
1475: if (i > 1)
1476: row.append(",");
1477: row.append(s.getString(i));
1478: }
1479: row.append("}\n");
1480: }
1481: System.out.println(row.toString());
1482: s.close();
1483: }
1484: }
|