0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJava
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.DatabaseMetaData;
0026: import java.sql.PreparedStatement;
0027: import java.sql.ResultSet;
0028: import java.sql.ResultSetMetaData;
0029: import java.sql.Statement;
0030: import java.sql.SQLException;
0031:
0032: import org.apache.derby.tools.ij;
0033: import org.apache.derby.tools.JDBCDisplayUtil;
0034:
0035: /**
0036: * Test for declared global temporary tables introduced in Cloudscape 5.2
0037: * The temp table tests with holdable cursor and savepoints are in declareGlobalTempTableJavaJDBC30 class.
0038: * The reason for a different test class is that the holdability and savepoint support is under jdk14 and higher.
0039: * But we want to be able to run the non-jdk14 specific tests under all the jdks we support and hence splitting
0040: * the tests into 2 different classes
0041: */
0042:
0043: public class declareGlobalTempTableJava {
0044:
0045: /*
0046: ** There is a small description prior to each sub-test describing what is being tested.
0047: */
0048: public static void main(String[] args) {
0049: boolean passed = true;
0050:
0051: Connection con1 = null, con2 = null;
0052: Statement s = null;
0053:
0054: /* Run all parts of this test, and catch any exceptions */
0055: try {
0056: System.out
0057: .println("Test declaredGlobalTempTableJava starting");
0058:
0059: /* Load the JDBC Driver class */
0060: // use the ij utility to read the property file and
0061: // make the initial connection.
0062: ij.getPropertyArg(args);
0063: con1 = ij.startJBMS();
0064: con2 = ij.startJBMS();
0065:
0066: s = con1.createStatement();
0067: con1.setAutoCommit(false);
0068: con2.setAutoCommit(false);
0069:
0070: /* Test this before other tests because this test requires
0071: that session schema has not been created yet */
0072: passed = testDERBY1706(con1, s) && passed;
0073:
0074: /* Test various schema and grammar related cases */
0075: passed = testSchemaNameAndGrammar(con1, s) && passed;
0076:
0077: /* Test various unallowed operations */
0078: passed = testOtherOperations(con1, s, con2) && passed;
0079:
0080: con1.close();
0081: con2.close();
0082:
0083: } catch (Throwable e) {
0084: System.out.println("FAIL -- unexpected exception " + e);
0085: JDBCDisplayUtil.ShowException(System.out, e);
0086: e.printStackTrace();
0087: passed = false;
0088: }
0089:
0090: if (passed)
0091: System.out.println("PASS");
0092:
0093: System.out.println("Test declaredGlobalTempTable finished");
0094: }
0095:
0096: /**
0097: * Test switching to session schema (it doesn't yet exist because
0098: * no create schema session has been issued yet) & then try to create
0099: * first persistent object in it. This used to cause null pointer
0100: * exception (DERBY-1706).
0101: *
0102: * @param conn The Connection
0103: * @param s A Statement on the Connection
0104: *
0105: * @return true if it succeeds, false if it doesn't
0106: *
0107: * @exception SQLException Thrown if some unexpected error happens
0108: */
0109:
0110: static boolean testDERBY1706(Connection con1, Statement s)
0111: throws SQLException {
0112: boolean passed = true;
0113:
0114: try {
0115: System.out
0116: .print("TEST-DERBY1706 : Create a persistent object");
0117: System.out
0118: .print(" in SESSION schema w/o first creating the");
0119: System.out.println(" schema");
0120:
0121: s.executeUpdate("set schema SESSION");
0122: s.executeUpdate("create table DERBY1706(c11 int)");
0123: s.executeUpdate("drop table DERBY1706");
0124: s.executeUpdate("set schema APP");
0125: s.executeUpdate("drop schema SESSION restrict");
0126:
0127: con1.commit();
0128: System.out.println("TEST-DERBY1706 PASSED");
0129: } catch (Throwable e) {
0130: System.out.println("Unexpected message: " + e.getMessage());
0131: con1.rollback();
0132: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0133: System.out.println("TEST-DERBY1706 FAILED");
0134: }
0135:
0136: return passed;
0137: }
0138:
0139: /**
0140: * Test various schema and grammar related cases
0141: *
0142: * @param conn The Connection
0143: * @param s A Statement on the Connection
0144: *
0145: * @return true if it succeeds, false if it doesn't
0146: *
0147: * @exception SQLException Thrown if some unexpected error happens
0148: */
0149:
0150: static boolean testSchemaNameAndGrammar(Connection con1, Statement s)
0151: throws SQLException {
0152: boolean passed = true;
0153:
0154: try {
0155: System.out
0156: .println("TEST1 : global temporary tables can only be in SESSION schema");
0157:
0158: s
0159: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE APP.t2(c21 int) on commit delete rows not logged");
0160:
0161: con1.rollback();
0162: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0163: System.out.println("TEST1 FAILED");
0164: } catch (Throwable e) {
0165: System.out.println("Expected message: " + e.getMessage());
0166: con1.commit();
0167: System.out.println("TEST1 PASSED");
0168: }
0169:
0170: try {
0171: System.out
0172: .print("TEST2A : Declaring a global temporary table while in SYS schema will pass ");
0173: System.out
0174: .println("because temp tables always go in SESSION schema and never in default schema");
0175:
0176: s.executeUpdate("set schema SYS");
0177: s
0178: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t2(c21 int) on commit delete rows not logged");
0179:
0180: con1.commit();
0181: System.out.println("TEST2A PASSED");
0182: } catch (Throwable e) {
0183: System.out.println("Unexpected message: " + e.getMessage());
0184: con1.rollback();
0185: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0186: System.out.println("TEST2A FAILED");
0187: }
0188:
0189: try {
0190: System.out
0191: .println("TEST2B : Drop the declared global temporary table declared in TEST2A while in schema SYS");
0192:
0193: s.executeUpdate("DROP TABLE SESSION.t2");
0194: s.executeUpdate("set schema APP");
0195:
0196: con1.commit();
0197: System.out.println("TEST2B PASSED");
0198: } catch (Throwable e) {
0199: System.out.println("Unexpected message: " + e.getMessage());
0200: con1.rollback();
0201: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0202: System.out.println("TEST2B FAILED");
0203: }
0204:
0205: try {
0206: System.out
0207: .println("TEST3A : positive grammar tests for DECLARE command");
0208: s
0209: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tA(c1 int) not logged");
0210: s
0211: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tB(c1 int) on commit delete rows not logged");
0212: s
0213: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tC(c1 int) not logged on commit delete rows");
0214: s
0215: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tD(c1 int) on commit preserve rows not logged");
0216: s
0217: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tE(c1 int) not logged on commit preserve rows");
0218: s
0219: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tF(c1 int) on rollback delete rows not logged");
0220: s
0221: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tG(c1 int) not logged on rollback delete rows");
0222: s
0223: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tH(c1 int) on commit preserve rows not logged on rollback delete rows");
0224: s
0225: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tI(c1 int) not logged on commit preserve rows on rollback delete rows");
0226: s
0227: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tJ(c1 int) not logged on rollback delete rows on commit preserve rows");
0228: s
0229: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tK(c1 int) on commit delete rows not logged on rollback delete rows");
0230: s
0231: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tL(c1 int) not logged on commit delete rows on rollback delete rows");
0232: s
0233: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tM(c1 int) not logged on rollback delete rows on commit delete rows");
0234:
0235: s.executeUpdate("DROP TABLE SESSION.tA");
0236: s.executeUpdate("DROP TABLE SESSION.tB");
0237: s.executeUpdate("DROP TABLE SESSION.tC");
0238: s.executeUpdate("DROP TABLE SESSION.tD");
0239: s.executeUpdate("DROP TABLE SESSION.tE");
0240: s.executeUpdate("DROP TABLE SESSION.tF");
0241: s.executeUpdate("DROP TABLE SESSION.tG");
0242: s.executeUpdate("DROP TABLE SESSION.tH");
0243: s.executeUpdate("DROP TABLE SESSION.tI");
0244: s.executeUpdate("DROP TABLE SESSION.tJ");
0245: s.executeUpdate("DROP TABLE SESSION.tK");
0246: s.executeUpdate("DROP TABLE SESSION.tL");
0247: s.executeUpdate("DROP TABLE SESSION.tM");
0248: con1.commit();
0249: System.out.println("TEST3A PASSED");
0250: } catch (Throwable e) {
0251: System.out.println("Unexpected message: " + e.getMessage());
0252: con1.rollback();
0253: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0254: System.out.println("TEST3A FAILED");
0255: }
0256:
0257: try {
0258: System.out
0259: .println("TEST3B : negative grammar tests for DECLARE command");
0260:
0261: try {
0262: s
0263: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int)");
0264: } catch (Throwable e) {
0265: System.out
0266: .println(" Expected exception. Attempted to declare a temp table without NOT LOGGED. "
0267: + e.getMessage());
0268: }
0269:
0270: try {
0271: s
0272: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED NOT LOGGED");
0273: } catch (Throwable e) {
0274: System.out
0275: .println(" Expected exception. Attempted to declare a temp table with multiple NOT LOGGED. "
0276: + e.getMessage());
0277: }
0278:
0279: try {
0280: s
0281: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON COMMIT PRESERVE ROWS ON COMMIT DELETE ROWS");
0282: } catch (Throwable e) {
0283: System.out
0284: .println(" Expected exception. Attempted to declare a temp table with multiple ON COMMIT. "
0285: + e.getMessage());
0286: }
0287:
0288: try {
0289: s
0290: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK DELETE ROWS ON ROLLBACK DELETE ROWS");
0291: } catch (Throwable e) {
0292: System.out
0293: .println(" Expected exception. Attempted to declare a temp table with multiple ON ROLLBACK. "
0294: + e.getMessage());
0295: }
0296:
0297: try {
0298: s
0299: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK PRESERVE ROWS");
0300: } catch (Throwable e) {
0301: System.out
0302: .println(" Expected exception. Attempted to declare a temp table with syntax error ON ROLLBACK PRESERVE ROWS. "
0303: + e.getMessage());
0304: }
0305:
0306: try {
0307: s
0308: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) ON ROLLBACK DELETE ROWS ON COMMIT PRESERVE ROWS");
0309: } catch (Throwable e) {
0310: System.out
0311: .println(" Expected exception. Attempted to declare a temp table without NOT LOGGED. "
0312: + e.getMessage());
0313: }
0314:
0315: con1.commit();
0316: System.out.println("TEST3B PASSED");
0317: } catch (Throwable e) {
0318: System.out.println("Unexpected message: " + e.getMessage());
0319: con1.rollback();
0320: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0321: System.out.println("TEST3B FAILED");
0322: }
0323:
0324: return passed;
0325: }
0326:
0327: /**
0328: * Test various other operations on declared global temporary tables
0329: *
0330: * @param con1 Connection to the database
0331: * @param s A Statement on the Connection
0332: * @param con2 Another Connection to the database
0333: *
0334: * @return true if it succeeds, false if it doesn't
0335: *
0336: * @exception SQLException Thrown if some unexpected error happens
0337: */
0338:
0339: static boolean testOtherOperations(Connection con1, Statement s,
0340: Connection con2) throws SQLException {
0341: boolean passed = true;
0342:
0343: try {
0344: System.out
0345: .println("TEST4A : ALTER TABLE not allowed on global temporary tables");
0346:
0347: s
0348: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit delete rows");
0349: s
0350: .executeUpdate("ALTER TABLE SESSION.t2 add column c22 int");
0351:
0352: con1.rollback();
0353: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0354: System.out.println("TEST4A FAILED");
0355: } catch (Throwable e) {
0356: System.out.println("Expected message: " + e.getMessage());
0357: s.executeUpdate("DROP TABLE SESSION.t2");
0358: con1.commit();
0359: System.out.println("TEST4A PASSED");
0360: }
0361:
0362: try {
0363: System.out
0364: .println("TEST4B : ALTER TABLE on physical table in SESSION schema should work");
0365:
0366: s.executeUpdate("CREATE schema SESSION");
0367: s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
0368: s
0369: .executeUpdate("ALTER TABLE SESSION.t2 add column c22 int");
0370: s.executeUpdate("DROP TABLE SESSION.t2");
0371: s.executeUpdate("drop schema SESSION restrict");
0372:
0373: con1.commit();
0374: System.out.println("TEST4B PASSED");
0375: } catch (Throwable e) {
0376: System.out.println("Unexpected message: " + e.getMessage());
0377: con1.rollback();
0378: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0379: System.out.println("TEST4B FAILED");
0380: }
0381:
0382: try {
0383: System.out
0384: .println("TEST5A : LOCK TABLE not allowed on global temporary tables");
0385:
0386: s
0387: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
0388: s.executeUpdate("LOCK TABLE SESSION.t2 IN SHARE MODE");
0389:
0390: con1.rollback();
0391: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0392: System.out.println("TEST5A FAILED");
0393: } catch (Throwable e) {
0394: System.out.println("Expected message: " + e.getMessage());
0395: s.executeUpdate("DROP TABLE SESSION.t2");
0396: con1.commit();
0397: System.out.println("TEST5A PASSED");
0398: }
0399:
0400: try {
0401: System.out
0402: .println("TEST5B : LOCK TABLE on physical table in SESSION schema should work");
0403:
0404: s.executeUpdate("CREATE schema SESSION");
0405: s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
0406: s.executeUpdate("LOCK TABLE SESSION.t2 IN EXCLUSIVE MODE");
0407: s.executeUpdate("DROP TABLE SESSION.t2");
0408: s.executeUpdate("DROP schema SESSION restrict");
0409:
0410: con1.commit();
0411: System.out.println("TEST5B PASSED");
0412: } catch (Throwable e) {
0413: System.out.println("Unexpected message: " + e.getMessage());
0414: con1.rollback();
0415: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0416: System.out.println("TEST5B FAILED");
0417: }
0418:
0419: try {
0420: System.out
0421: .println("TEST6A : RENAME TABLE not allowed on global temporary tables");
0422:
0423: s
0424: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
0425: s.executeUpdate("RENAME TABLE SESSION.t2 TO t3");
0426:
0427: con1.rollback();
0428: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0429: System.out.println("TEST6A FAILED");
0430: } catch (Throwable e) {
0431: System.out.println("Expected message: " + e.getMessage());
0432: s.executeUpdate("DROP TABLE SESSION.t2");
0433: con1.commit();
0434: System.out.println("TEST6A PASSED");
0435: }
0436:
0437: try {
0438: System.out
0439: .println("TEST6B : RENAME TABLE on physical table in SESSION schema should work");
0440:
0441: s.executeUpdate("CREATE schema SESSION");
0442: s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
0443: s.executeUpdate("RENAME TABLE SESSION.t2 TO t3");
0444: s.executeUpdate("DROP TABLE SESSION.t3");
0445: s.executeUpdate("drop schema SESSION restrict");
0446:
0447: con1.commit();
0448: System.out.println("TEST6B PASSED");
0449: } catch (Throwable e) {
0450: System.out.println("Unexpected message: " + e.getMessage());
0451: con1.rollback();
0452: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0453: System.out.println("TEST6B FAILED");
0454: }
0455:
0456: try {
0457: System.out
0458: .println("TEST6C : RENAME COLUMN on physical table in SESSION schema should work");
0459:
0460: s.executeUpdate("CREATE schema SESSION");
0461: s.executeUpdate("SET schema SESSION");
0462: s.executeUpdate("CREATE TABLE t2(c21 int)");
0463: //s.executeUpdate("RENAME COLUMN t2.c21 TO c22");
0464: s.executeUpdate("SET schema APP");
0465: s.executeUpdate("DROP TABLE SESSION.t2");
0466: s.executeUpdate("drop schema SESSION restrict");
0467:
0468: con1.commit();
0469: System.out.println("TEST6C PASSED");
0470: } catch (Throwable e) {
0471: System.out.println("Unexpected message: " + e.getMessage());
0472: con1.rollback();
0473: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0474: System.out.println("TEST6C FAILED");
0475: }
0476:
0477: try {
0478: System.out
0479: .println("TEST8 : generated always as identity not supported for declared global temporary tables");
0480:
0481: s
0482: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int generated always as identity) on commit delete rows not logged");
0483:
0484: con1.rollback();
0485: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0486: System.out.println("TEST8 FAILED");
0487: } catch (Throwable e) {
0488: System.out.println("Expected message: " + e.getMessage());
0489: con1.commit();
0490: System.out.println("TEST8 PASSED");
0491: }
0492:
0493: try {
0494: System.out
0495: .println("TEST9 : long datatypes not supported for declared global temporary tables");
0496:
0497: try {
0498: s
0499: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 blob(3k)) on commit delete rows not logged");
0500: } catch (Throwable e) {
0501: System.out
0502: .println(" Expected exception. Attempted to declare a temp table with blob. "
0503: + e.getMessage());
0504: }
0505:
0506: try {
0507: s
0508: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 clob(3k)) on commit delete rows not logged");
0509: } catch (Throwable e) {
0510: System.out
0511: .println(" Expected exception. Attempted to declare a temp table with clob. "
0512: + e.getMessage());
0513: }
0514:
0515: try {
0516: s
0517: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 long varchar) on commit delete rows not logged");
0518: } catch (Throwable e) {
0519: System.out
0520: .println(" Expected exception. Attempted to declare a temp table with long varchar. "
0521: + e.getMessage());
0522: }
0523:
0524: try {
0525: s
0526: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 \"org.apache.derbyTesting.functionTests.util.ShortHolder\") on commit delete rows not logged");
0527: } catch (Throwable e) {
0528: System.out
0529: .println(" Expected exception. Attempted to declare a temp table with user defined type. "
0530: + e.getMessage());
0531: }
0532:
0533: con1.commit();
0534: System.out.println("TEST9 PASSED");
0535: } catch (Throwable e) {
0536: System.out.println("Unexpected message: " + e.getMessage());
0537: con1.rollback();
0538: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0539: System.out.println("TEST9 FAILED");
0540: }
0541:
0542: try {
0543: System.out
0544: .println("TEST10A : Primary key constraint not allowed on a declared global temporary table.");
0545:
0546: s
0547: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21)) on commit delete rows not logged");
0548:
0549: con1.rollback();
0550: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0551: System.out.println("TEST10A FAILED");
0552: } catch (Throwable e) {
0553: System.out.println("Expected message: " + e.getMessage());
0554: con1.commit();
0555: System.out.println("TEST10A PASSED");
0556: }
0557:
0558: try {
0559: System.out
0560: .println("TEST10B : Primary key constraint allowed on a physical table in SESSION schema.");
0561:
0562: s.executeUpdate("CREATE SCHEMA SESSION");
0563: s
0564: .executeUpdate("CREATE TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21))");
0565: s.executeUpdate("DROP TABLE SESSION.t2");
0566: s.executeUpdate("drop schema SESSION restrict");
0567:
0568: con1.commit();
0569: System.out.println("TEST10B PASSED");
0570: } catch (Throwable e) {
0571: System.out.println("Unexpected message: " + e.getMessage());
0572: con1.rollback();
0573: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0574: System.out.println("TEST10B FAILED");
0575: }
0576:
0577: try {
0578: System.out
0579: .println("TEST10C : Unique key constraint not allowed on a declared global temporary table.");
0580:
0581: s
0582: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null unique) on commit delete rows not logged");
0583:
0584: con1.rollback();
0585: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0586: System.out.println("TEST10C FAILED");
0587: } catch (Throwable e) {
0588: System.out.println("Expected message: " + e.getMessage());
0589: con1.commit();
0590: System.out.println("TEST10C PASSED");
0591: }
0592:
0593: try {
0594: System.out
0595: .println("TEST10D : Foreign key constraint not allowed on a declared global temporary table.");
0596:
0597: s.executeUpdate("CREATE TABLE t1(c11 int not null unique)");
0598: s
0599: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int references t1(c11)) on commit delete rows not logged");
0600:
0601: con1.rollback();
0602: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0603: System.out.println("TEST10D FAILED");
0604: } catch (Throwable e) {
0605: System.out.println("Expected message: " + e.getMessage());
0606: s.executeUpdate("DROP TABLE t1");
0607: con1.commit();
0608: System.out.println("TEST10D PASSED");
0609: }
0610:
0611: try {
0612: System.out
0613: .println("TEST11 : Attempt to declare the same global temporary table twice will fail. Plan to support WITH REPLACE in future");
0614:
0615: s
0616: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
0617: s
0618: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit preserve rows");
0619:
0620: con1.rollback();
0621: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0622: System.out.println("TEST11 FAILED");
0623: } catch (Throwable e) {
0624: System.out.println("Expected message: " + e.getMessage());
0625: s.executeUpdate("DROP TABLE SESSION.t2");
0626: con1.commit();
0627: System.out.println("TEST11 PASSED");
0628: }
0629:
0630: try {
0631: System.out
0632: .println("TEST12 : Try to drop a declared global temporary table that doesn't exist.");
0633:
0634: s.executeUpdate("DROP TABLE SESSION.t2");
0635:
0636: con1.rollback();
0637: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0638: System.out.println("TEST12 FAILED");
0639: } catch (Throwable e) {
0640: System.out.println("Expected message: " + e.getMessage());
0641: con1.commit();
0642: System.out.println("TEST12 PASSED");
0643: }
0644:
0645: try {
0646: System.out
0647: .println("TEST13A : insert into declared global temporary table will pass.");
0648:
0649: s
0650: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2)) on commit delete rows not logged");
0651: s.executeUpdate("insert into SESSION.t2 values (1, 'aa')");
0652: s
0653: .executeUpdate("insert into SESSION.t2 values (2, 'bb'),(3, 'cc'),(4, null)");
0654: s.executeUpdate("CREATE TABLE t1(c11 int, c22 char(2))");
0655: s
0656: .executeUpdate("insert into t1 values (5, null),(6, null),(7, 'gg')");
0657: s
0658: .executeUpdate("insert into SESSION.t2 (select * from t1 where c11>4)");
0659: s
0660: .executeUpdate("insert into SESSION.t2 select * from SESSION.t2");
0661: ResultSet rs1 = s
0662: .executeQuery("select sum(c21) from SESSION.t2");
0663: dumpRS(rs1);
0664: s.executeUpdate("DROP TABLE SESSION.t2");
0665: s.executeUpdate("DROP TABLE t1");
0666:
0667: con1.commit();
0668: System.out.println("TEST13A PASSED");
0669: } catch (Throwable e) {
0670: System.out.println("Unexpected message: " + e.getMessage());
0671: con1.rollback();
0672: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0673: System.out.println("TEST13A FAILED");
0674: }
0675:
0676: try {
0677: System.out
0678: .println("TEST13B : attempt to insert null into non-null column in declared global temporary table will fail.");
0679: System.out
0680: .println("Declare the table with non-null column, insert a row and commit");
0681:
0682: s
0683: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2) not null) on commit delete rows not logged");
0684: s.executeUpdate("insert into SESSION.t2 values (1, 'aa')");
0685: con1.commit();
0686: System.out
0687: .println("In the next transaction, attempt to insert a null value in the table will fail and we will loose all the rows from the table as part of internal rollback");
0688: s.executeUpdate("insert into SESSION.t2 values (2, null)");
0689:
0690: con1.rollback();
0691: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0692: System.out.println("TEST13B FAILED");
0693: } catch (Throwable e) {
0694: System.out.println("Expected message: " + e.getMessage());
0695: System.out.println("should see no data in t2");
0696:
0697: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
0698: dumpRS(rs1);
0699: s.executeUpdate("DROP TABLE SESSION.t2");
0700: con1.commit();
0701: System.out.println("TEST13B PASSED");
0702: }
0703:
0704: try {
0705: System.out
0706: .println("TEST13C : declare a temporary table with default and then insert into it.");
0707:
0708: s
0709: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2) default 'aa', c23 varchar(20) default user ) on commit delete rows not logged");
0710: s
0711: .executeUpdate("insert into SESSION.t2 values (1, 'aa', null)");
0712: s.executeUpdate("insert into SESSION.t2(c21) values (2)");
0713: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
0714: dumpRS(rs1);
0715:
0716: s.executeUpdate("DROP TABLE SESSION.t2");
0717: con1.commit();
0718: System.out.println("TEST13C PASSED");
0719: } catch (Throwable e) {
0720: System.out.println("Unexpected message: " + e.getMessage());
0721: con1.rollback();
0722: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0723: System.out.println("TEST13C FAILED");
0724: }
0725:
0726: try {
0727: System.out
0728: .println("TEST14 : Should be able to create Session schema manually.");
0729:
0730: s.executeUpdate("CREATE schema SESSION");
0731:
0732: con1.commit();
0733: System.out.println("TEST14 PASSED");
0734: } catch (Throwable e) {
0735: System.out.println("Unexpected message: " + e.getMessage());
0736: con1.rollback();
0737: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0738: System.out.println("TEST14 FAILED");
0739: }
0740:
0741: try {
0742: System.out
0743: .println("TEST15 : Session schema can be dropped like any other user-defined schema.");
0744:
0745: s.executeUpdate("drop schema SESSION restrict");
0746:
0747: con1.commit();
0748: System.out.println("TEST15 PASSED");
0749: } catch (Throwable e) {
0750: System.out.println("Unexpected message: " + e.getMessage());
0751: con1.rollback();
0752: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0753: System.out.println("TEST15 FAILED");
0754: }
0755:
0756: try {
0757: System.out
0758: .print("TEST16 : Create a physical SESSION schema, drop it. Next attempt to drop SESSION schema will throw ");
0759: System.out
0760: .println("an exception because now we are dealing with in-memory SESSION schema and it can not be dropped by drop schema.");
0761:
0762: s.executeUpdate("CREATE schema SESSION");
0763: s.executeUpdate("drop schema SESSION restrict");
0764:
0765: System.out
0766: .println("In TEST16, now attempting to drop in-memory SESSION schema");
0767: s.executeUpdate("drop schema SESSION restrict"); //this should fail
0768:
0769: con1.rollback();
0770: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0771: System.out.println("TEST16 FAILED");
0772: } catch (Throwable e) {
0773: System.out.println("Expected message: " + e.getMessage());
0774: con1.commit();
0775: System.out.println("TEST16 PASSED");
0776: }
0777:
0778: try {
0779: System.out
0780: .println("TEST17A : Check constraint not allowed on global temporary table");
0781:
0782: s
0783: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int check (c21 > 0)) on commit delete rows not logged");
0784:
0785: con1.rollback();
0786: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0787: System.out.println("TEST17A FAILED");
0788: } catch (Throwable e) {
0789: System.out.println("Expected message: " + e.getMessage());
0790: con1.commit();
0791: System.out.println("TEST17A PASSED");
0792: }
0793:
0794: try {
0795: System.out
0796: .println("TEST17B : Check constraint allowed on physical SESSION schema table");
0797:
0798: s.executeUpdate("CREATE schema SESSION");
0799: s
0800: .executeUpdate("CREATE TABLE SESSION.t2(c21 int check (c21 > 0))");
0801: s.executeUpdate("DROP TABLE SESSION.t2");
0802: s.executeUpdate("drop schema SESSION restrict");
0803:
0804: con1.commit();
0805: System.out.println("TEST17B PASSED");
0806: } catch (Throwable e) {
0807: System.out.println("Unexpected message: " + e.getMessage());
0808: con1.rollback();
0809: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0810: System.out.println("TEST17B FAILED");
0811: }
0812:
0813: try {
0814: System.out
0815: .println("TEST18 : Test declared temporary table with ON COMMIT DELETE ROWS with and without open cursors");
0816: System.out
0817: .println("Tests with holdable cursor are in a different class since holdability support is only under jdk14 and higher");
0818:
0819: System.out
0820: .println("Temp table t2 with not holdable cursor open on it. Data should get deleted from t2 at commit time");
0821: s
0822: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
0823: s.executeUpdate("insert into SESSION.t2 values(22, 22)");
0824: s.executeUpdate("insert into SESSION.t2 values(23, 23)");
0825:
0826: ResultSet rs2 = s
0827: .executeQuery("select count(*) from SESSION.t2");
0828: dumpRS(rs2);
0829:
0830: rs2 = s.executeQuery("select * from SESSION.t2"); //eventhough this cursor is open, it is not a hold cursor. Commit should delete the rows
0831: rs2.next();
0832:
0833: System.out
0834: .println("Temp table t3 with no open cursors of any kind on it. Data should get deleted from t3 at commit time");
0835: s
0836: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
0837: s.executeUpdate("insert into SESSION.t3 values(32, 32)");
0838: s.executeUpdate("insert into SESSION.t3 values(33, 33)");
0839:
0840: ResultSet rs3 = s
0841: .executeQuery("select count(*) from SESSION.t3");
0842: dumpRS(rs3);
0843:
0844: con1.commit();
0845:
0846: System.out.println("After commit, verify the 2 tables");
0847: System.out
0848: .println("Temp table t2 will have no data after commit");
0849: rs2 = s.executeQuery("select count(*) from SESSION.t2");
0850: dumpRS(rs2);
0851:
0852: System.out
0853: .println("Temp table t3 will have no data after commit");
0854: rs3 = s.executeQuery("select count(*) from SESSION.t3");
0855: dumpRS(rs3);
0856:
0857: s.executeUpdate("DROP TABLE SESSION.t2");
0858: s.executeUpdate("DROP TABLE SESSION.t3");
0859:
0860: con1.commit();
0861: System.out.println("TEST18 PASSED");
0862: } catch (Throwable e) {
0863: System.out.println("Unexpected message: " + e.getMessage());
0864: con1.rollback();
0865: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0866: System.out.println("TEST18 FAILED");
0867: }
0868:
0869: try {
0870: System.out
0871: .println("TEST19 : Declare a temporary table with ON COMMIT PRESERVE ROWS with and without open cursors");
0872: System.out
0873: .println("Tests with holdable cursor are in a different class since holdability support is only under jdk14 and higher");
0874:
0875: System.out
0876: .println("Temp table t2 with not holdable cursor open on it. Data should be preserved, holdability shouldn't matter");
0877: s
0878: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
0879: s.executeUpdate("insert into SESSION.t2 values(22, 22)");
0880: s.executeUpdate("insert into SESSION.t2 values(23, 23)");
0881:
0882: ResultSet rs2 = s
0883: .executeQuery("select count(*) from SESSION.t2");
0884: dumpRS(rs2);
0885:
0886: rs2 = s.executeQuery("select * from SESSION.t2"); //eventhough this cursor is open, it is not a hold cursor.
0887: rs2.next();
0888:
0889: System.out
0890: .println("Temp table t3 with no open cursors of any kind on it. Data should be preserved, holdability shouldn't matter");
0891: s
0892: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
0893: s.executeUpdate("insert into SESSION.t3 values(32, 32)");
0894: s.executeUpdate("insert into SESSION.t3 values(33, 33)");
0895:
0896: ResultSet rs3 = s
0897: .executeQuery("select count(*) from SESSION.t3");
0898: dumpRS(rs3);
0899:
0900: con1.commit();
0901:
0902: System.out.println("After commit, verify the 2 tables");
0903: System.out
0904: .println("Temp table t2 will have data after commit");
0905: rs2 = s.executeQuery("select count(*) from SESSION.t2");
0906: dumpRS(rs2);
0907:
0908: System.out
0909: .println("Temp table t3 will have data after commit");
0910: rs3 = s.executeQuery("select count(*) from SESSION.t3");
0911: dumpRS(rs3);
0912:
0913: s.executeUpdate("DROP TABLE SESSION.t2");
0914: s.executeUpdate("DROP TABLE SESSION.t3");
0915:
0916: con1.commit();
0917: System.out.println("TEST19 PASSED");
0918: } catch (Throwable e) {
0919: System.out.println("Unexpected message: " + e.getMessage());
0920: con1.rollback();
0921: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0922: System.out.println("TEST19 FAILED");
0923: }
0924:
0925: try {
0926: System.out
0927: .println("TEST20A : CREATE INDEX not allowed on global temporary table.");
0928:
0929: s
0930: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
0931: s.executeUpdate("CREATE index t2i1 on SESSION.t2 (c21)");
0932:
0933: con1.rollback();
0934: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0935: System.out.println("TEST20A FAILED");
0936: } catch (Throwable e) {
0937: System.out.println("Expected message: " + e.getMessage());
0938: s.executeUpdate("DROP TABLE SESSION.t2");
0939: con1.commit();
0940: System.out.println("TEST20A PASSED");
0941: }
0942:
0943: try {
0944: System.out
0945: .println("TEST21A : CREATE INDEX on physical table in SESSION schema should work");
0946:
0947: s.executeUpdate("CREATE schema SESSION");
0948: s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
0949: s.executeUpdate("CREATE index t3i1 on SESSION.t3 (c31)");
0950: s.executeUpdate("DROP TABLE SESSION.t3");
0951: s.executeUpdate("drop schema SESSION restrict");
0952:
0953: con1.commit();
0954: System.out.println("TEST21A PASSED");
0955: } catch (Throwable e) {
0956: System.out.println("Unexpected message: " + e.getMessage());
0957: con1.rollback();
0958: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0959: System.out.println("TEST21A FAILED");
0960: }
0961: /*
0962: try
0963: {
0964: System.out.println("TEST22A : CREATE TRIGGER not allowed on global temporary table.");
0965:
0966: s.executeUpdate("CREATE TABLE t1(c11 int)");
0967: s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
0968: s.executeUpdate("CREATE TRIGGER t2tr1 before insert on SESSION.t2 for each statement insert into t1 values(1)");
0969:
0970: con1.rollback();
0971: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0972: System.out.println("TEST22A FAILED");
0973: } catch (Throwable e)
0974: {
0975: System.out.println("Expected message: "+ e.getMessage());
0976: s.executeUpdate("DROP TABLE SESSION.t2");
0977: s.executeUpdate("DROP TABLE t1");
0978: con1.commit();
0979: System.out.println("TEST22A PASSED");
0980: }
0981:
0982: try
0983: {
0984: System.out.println("TEST23A : CREATE TRIGGER not allowed on physical table in SESSION schema");
0985:
0986: s.executeUpdate("CREATE schema SESSION");
0987: s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
0988: s.executeUpdate("CREATE TABLE SESSION.t4 (c41 int)");
0989: s.executeUpdate("CREATE TRIGGER t3tr1 before insert on SESSION.t3 for each statement insert into SESSION.t4 values(1)");
0990:
0991: con1.rollback();
0992: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
0993: System.out.println("TEST23A FAILED");
0994: } catch (Throwable e)
0995: {
0996: System.out.println("Expected message: "+ e.getMessage());
0997: s.executeUpdate("DROP TABLE SESSION.t3");
0998: s.executeUpdate("DROP TABLE SESSION.t4");
0999: s.executeUpdate("drop schema SESSION restrict");
1000: con1.commit();
1001: System.out.println("TEST23A PASSED");
1002: }
1003:
1004: try
1005: {
1006: System.out.println("TEST24A : Temporary tables can not be referenced in trigger action");
1007:
1008: s.executeUpdate("CREATE TABLE t3 (c31 int)");
1009: s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4 (c41 int) not logged");
1010: s.executeUpdate("CREATE TRIGGER t3tr1 before insert on t3 for each statement insert into SESSION.t4 values(1)");
1011:
1012: con1.rollback();
1013: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1014: System.out.println("TEST24A FAILED");
1015: } catch (Throwable e)
1016: {
1017: System.out.println("Expected message: "+ e.getMessage());
1018: s.executeUpdate("DROP TABLE t3");
1019: s.executeUpdate("DROP TABLE SESSION.t4");
1020: con1.commit();
1021: System.out.println("TEST24A PASSED");
1022: }
1023:
1024: try
1025: {
1026: System.out.println("TEST24B : SESSION schema persistent tables can not be referenced in trigger action");
1027:
1028: s.executeUpdate("CREATE TABLE t3 (c31 int)"); //not a SESSION schema table
1029: s.executeUpdate("CREATE SCHEMA SESSION");
1030: s.executeUpdate("CREATE TABLE SESSION.t4 (c41 int)");
1031: s.executeUpdate("CREATE TRIGGER t3tr1 before insert on t3 for each statement delete from SESSION.t4");
1032:
1033: con1.rollback();
1034: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1035: System.out.println("TEST24B FAILED");
1036: } catch (Throwable e)
1037: {
1038: System.out.println("Expected message: "+ e.getMessage());
1039: s.executeUpdate("DROP TABLE t3");
1040: s.executeUpdate("DROP TABLE SESSION.t4");
1041: s.executeUpdate("drop schema SESSION restrict");
1042: con1.commit();
1043: System.out.println("TEST24B PASSED");
1044: }
1045: */
1046: try {
1047: System.out
1048: .println("TEST26A : CREATE VIEW not allowed on global temporary table.");
1049:
1050: s
1051: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1052: s
1053: .executeUpdate("CREATE VIEW t2v1 as select * from SESSION.t2");
1054:
1055: con1.rollback();
1056: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1057: System.out.println("TEST26A FAILED");
1058: } catch (Throwable e) {
1059: System.out.println("Expected message: " + e.getMessage());
1060: s.executeUpdate("DROP TABLE SESSION.t2");
1061: con1.commit();
1062: System.out.println("TEST26A PASSED");
1063: }
1064:
1065: try {
1066: System.out
1067: .println("TEST27A : CREATE VIEW not allowed on physical table in SESSION schema");
1068:
1069: s.executeUpdate("CREATE schema SESSION");
1070: s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
1071: s
1072: .executeUpdate("CREATE VIEW t3v1 as select * from SESSION.t3");
1073:
1074: con1.rollback();
1075: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1076: System.out.println("TEST27A FAILED");
1077: } catch (Throwable e) {
1078: System.out.println("Expected message: " + e.getMessage());
1079: s.executeUpdate("DROP TABLE SESSION.t3");
1080: s.executeUpdate("drop schema SESSION restrict");
1081: con1.commit();
1082: System.out.println("TEST27A PASSED");
1083: }
1084:
1085: //Derby424 - Queryplan for a query using SESSION schema view is incorrectly put in statement cache. This
1086: //could cause incorrect plan getting executed later if a temp. table is created with that name.
1087: System.out
1088: .println("TEST28A : CREATE VIEW in SESSION schema referencing a table outside of SESSION schema");
1089: s.executeUpdate("CREATE TABLE t28A (c28 int)");
1090: s.executeUpdate("INSERT INTO t28A VALUES (280),(281)");
1091: s
1092: .executeUpdate("CREATE VIEW SESSION.t28v1 as select * from t28A");
1093: System.out
1094: .println("SELECT * from SESSION.t28v1 should show contents of view");
1095: dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
1096: System.out
1097: .println("Now declare a global temporary table with same name as the view in SESSION schema");
1098: s
1099: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t28v1(c21 int, c22 int) not logged");
1100: s
1101: .executeUpdate("INSERT INTO SESSION.t28v1 VALUES (280,1),(281,2)");
1102: System.out
1103: .println("SELECT * from SESSION.t28v1 should show contents of global temporary table");
1104: dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
1105: s.executeUpdate("DROP TABLE SESSION.t28v1");
1106: System.out
1107: .println("We have dropped global temporary table hence SESSION.t28v1 should point to view at this point");
1108: dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
1109: s.executeUpdate("DROP VIEW SESSION.t28v1");
1110: con1.rollback();
1111: con1.commit();
1112: System.out.println("TEST28A PASSED");
1113:
1114: try {
1115: System.out
1116: .println("TEST29A : DELETE FROM global temporary table allowed.");
1117:
1118: s
1119: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 decimal) not logged");
1120: s.executeUpdate("insert into SESSION.t2 values(1, 1.1)");
1121: s.executeUpdate("insert into SESSION.t2 values(2, 2.2)");
1122:
1123: ResultSet rs2 = s
1124: .executeQuery("select count(*) from SESSION.t2");
1125: dumpRS(rs2);
1126:
1127: s.executeUpdate("DELETE FROM SESSION.t2 where c21 > 0");
1128:
1129: rs2 = s.executeQuery("select count(*) from SESSION.t2");
1130: dumpRS(rs2);
1131:
1132: s.executeUpdate("DROP TABLE SESSION.t2");
1133: con1.commit();
1134: System.out.println("TEST29A PASSED");
1135: } catch (Throwable e) {
1136: System.out.println("Unexpected message: " + e.getMessage());
1137: con1.rollback();
1138: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1139: System.out.println("TEST29A FAILED");
1140: }
1141:
1142: try {
1143: System.out
1144: .println("TEST31A : UPDATE on global temporary table allowed.");
1145:
1146: s
1147: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1148: s.executeUpdate("insert into SESSION.t2 values(1, 1)");
1149: s.executeUpdate("insert into SESSION.t2 values(2, 1)");
1150:
1151: ResultSet rs2 = s
1152: .executeQuery("select count(*) from SESSION.t2 where c22 = 1");
1153: rs2.next();
1154: if (rs2.getInt(1) != 2)
1155: System.out
1156: .println("TEST31A FAILED: count should have been 2.");
1157:
1158: s
1159: .executeUpdate("UPDATE SESSION.t2 SET c22 = 2 where c21>0");
1160:
1161: rs2 = s
1162: .executeQuery("select count(*) from SESSION.t2 where c22 = 1");
1163: rs2.next();
1164: if (rs2.getInt(1) != 0)
1165: System.out
1166: .println("TEST31A FAILED: count should have been 0.");
1167:
1168: rs2 = s
1169: .executeQuery("select count(*) from SESSION.t2 where c22 = 2");
1170: rs2.next();
1171: if (rs2.getInt(1) != 2)
1172: System.out
1173: .println("TEST31A FAILED: count should have been 2.");
1174:
1175: s.executeUpdate("DROP TABLE SESSION.t2");
1176: con1.commit();
1177: System.out.println("TEST31A PASSED");
1178: } catch (Throwable e) {
1179: System.out.println("Unexpected message: " + e.getMessage());
1180: con1.rollback();
1181: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1182: System.out.println("TEST31A FAILED");
1183: }
1184: /*
1185: try
1186: {
1187: System.out.println("TEST32A : SET TRIGGERS not allowed on global temporary tables");
1188:
1189: s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1190: s.executeUpdate("SET TRIGGERS FOR SESSION.t2 ENABLED");
1191:
1192: con1.rollback();
1193: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1194: System.out.println("TEST32A FAILED");
1195: } catch (Throwable e)
1196: {
1197: System.out.println("Expected message: "+ e.getMessage());
1198: s.executeUpdate("DROP TABLE SESSION.t2");
1199: con1.commit();
1200: System.out.println("TEST32A PASSED");
1201: }
1202: try
1203: {
1204: System.out.println("TEST32C : SET TRIGGERS on physical table in SESSION schema should work");
1205:
1206: s.executeUpdate("CREATE schema SESSION");
1207: s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
1208: s.executeUpdate("SET TRIGGERS FOR SESSION.t2 ENABLED");
1209: s.executeUpdate("DROP TABLE SESSION.t2");
1210: s.executeUpdate("drop schema SESSION restrict");
1211:
1212: con1.commit();
1213: System.out.println("TEST32C PASSED");
1214: } catch (Throwable e)
1215: {
1216: System.out.println("Unexpected message: "+ e.getMessage());
1217: con1.rollback();
1218: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1219: System.out.println("TEST32C FAILED");
1220: } */
1221:
1222: System.out
1223: .println("Multiple tests to make sure we do not do statement caching for statement referencing SESSION schema tables");
1224: try {
1225: System.out
1226: .println("TEST34A : CREATE physical table and then DECLARE GLOBAL TEMPORARY TABLE with the same name in session schema.");
1227:
1228: con1.setAutoCommit(true);
1229: //Need to do following 3 in autocommit mode otherwise the data dictionary will be in write mode and statements won't get
1230: //cached. I need to have statement caching enabled here to make sure that tables with same names do not conflict
1231: s.executeUpdate("CREATE schema SESSION");
1232: s.executeUpdate("CREATE TABLE SESSION.t2 (c21 int)");
1233: s.executeUpdate("INSERT into SESSION.t2 values(21)");
1234:
1235: con1.setAutoCommit(false);
1236: //select will return data from physical table t2
1237: s.execute("select * from SESSION.t2");
1238: dumpRS(s.getResultSet());
1239:
1240: //declare temporary table with same name as a physical table in SESSION schema
1241: s
1242: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
1243: s.executeUpdate("INSERT into SESSION.t2 values(22, 22)");
1244: s.executeUpdate("INSERT into SESSION.t2 values(23, 23)");
1245: //select will return data from temp table t2
1246: s.execute("select c21,c22 from SESSION.t2");
1247: dumpRS(s.getResultSet());
1248: //select will return data from temp table t2
1249: s.execute("select * from SESSION.t2");
1250: dumpRS(s.getResultSet());
1251:
1252: //drop the temp table t2
1253: s.executeUpdate("DROP TABLE SESSION.t2");
1254: //select will return data from physical table t2 because temp table has been deleted
1255: s.execute("select * from SESSION.t2");
1256: dumpRS(s.getResultSet());
1257:
1258: //cleanup
1259: s.executeUpdate("DROP TABLE SESSION.t2");
1260: s.executeUpdate("drop schema SESSION restrict");
1261: con1.commit();
1262: System.out.println("TEST34A PASSED");
1263: } catch (Throwable e) {
1264: System.out.println("Unexpected message: " + e.getMessage());
1265: con1.rollback();
1266: passed = false; //we shouldn't have reached here. Return false to indicate failure
1267: System.out.println("TEST34A FAILED");
1268: }
1269: try {
1270: System.out
1271: .println("TEST34B : Physical table & TEMPORARY TABLE with the same name in session schema, try insert.");
1272:
1273: con1.setAutoCommit(true);
1274: //Need to do following 3 in autocommit mode otherwise the data dictionary will be in write mode and statements won't get
1275: //cached. I need to have statement caching enabled here to make sure that tables with same names do not conflict
1276: s.executeUpdate("CREATE schema SESSION");
1277: s.executeUpdate("CREATE TABLE SESSION.t2 (c21 int)");
1278: s.executeUpdate("INSERT into SESSION.t2 values(21)");
1279:
1280: con1.setAutoCommit(false);
1281: //select will return data from physical table t2
1282: s.execute("select * from SESSION.t2");
1283: dumpRS(s.getResultSet());
1284:
1285: //declare temporary table with same name as a physical table in SESSION schema
1286: s
1287: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1288: //select will return data from temp table t2
1289: s.execute("select * from SESSION.t2");
1290: dumpRS(s.getResultSet());
1291: s.executeUpdate("INSERT into SESSION.t2 values(99)");
1292: s.execute("select * from SESSION.t2");
1293: dumpRS(s.getResultSet());
1294:
1295: //drop the temp table t2
1296: s.executeUpdate("DROP TABLE SESSION.t2");
1297: //select will return data from physical table t2 because temp table has been deleted
1298: s.execute("select * from SESSION.t2");
1299: dumpRS(s.getResultSet());
1300:
1301: //cleanup
1302: s.executeUpdate("DROP TABLE SESSION.t2");
1303: s.executeUpdate("drop schema SESSION restrict");
1304: con1.commit();
1305: System.out.println("TEST34B PASSED");
1306: } catch (Throwable e) {
1307: System.out.println("Unexpected message: " + e.getMessage());
1308: con1.rollback();
1309: passed = false; //we shouldn't have reached here. Return false to indicate failure
1310: System.out.println("TEST34B FAILED");
1311: }
1312:
1313: try {
1314: System.out
1315: .println("TEST35A : Temporary table created in one connection should not be available in another connection");
1316: s
1317: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1318: s.executeUpdate("insert into SESSION.t2 values(22, 22)");
1319:
1320: ResultSet rs1 = s
1321: .executeQuery("select count(*) from SESSION.t2");
1322: dumpRS(rs1);
1323:
1324: Statement s2 = con2.createStatement();
1325: ResultSet rs2 = s2
1326: .executeQuery("select count(*) from SESSION.t2"); //con2 should not find temp table declared in con1
1327:
1328: dumpRS(rs2);
1329: con1.rollback();
1330: con2.rollback();
1331: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1332: System.out.println("TEST35A FAILED");
1333: } catch (Throwable e) {
1334: System.out.println("Expected message: " + e.getMessage());
1335: s.executeUpdate("DROP TABLE SESSION.t2");
1336: con1.commit();
1337: con2.commit();
1338: System.out.println("TEST35A PASSED");
1339: }
1340:
1341: try {
1342: System.out
1343: .println("TEST35B : Temp table in one connection should not conflict with temp table with same name in another connection");
1344: s
1345: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1346: s.executeUpdate("insert into SESSION.t2 values(22, 22)");
1347:
1348: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1349: dumpRS(rs1); //should return 22, 22
1350:
1351: Statement s2 = con2.createStatement();
1352: s2
1353: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged");
1354: s2.executeUpdate("insert into SESSION.t2 values(99)");
1355: ResultSet rs2 = s2.executeQuery("select * from SESSION.t2");
1356: dumpRS(rs2); //should return 99
1357:
1358: rs1 = s.executeQuery("select * from SESSION.t2");
1359: dumpRS(rs1); //should return 22, 22
1360:
1361: s.executeUpdate("DROP TABLE SESSION.t2"); //dropping temp table t2 defined for con1
1362: s2.executeUpdate("DROP TABLE SESSION.t2"); //dropping temp table t2 defined for con2
1363: con1.commit();
1364: con2.commit();
1365: System.out.println("TEST35B PASSED");
1366: } catch (Throwable e) {
1367: System.out.println("Unexpected message: " + e.getMessage());
1368: con1.rollback();
1369: con2.rollback();
1370: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1371: System.out.println("TEST35B FAILED");
1372: }
1373:
1374: try {
1375: System.out
1376: .println("TEST36 : After creating SESSION schema and making it current schema, temporary tables should not require SESSION qualification");
1377:
1378: s
1379: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1380: s.executeUpdate("insert into SESSION.t2 values(21, 21)");
1381: s.executeUpdate("insert into SESSION.t2 values(22, 22)");
1382:
1383: ResultSet rs1 = s
1384: .executeQuery("select count(*) from SESSION.t2");
1385: rs1.next();
1386: if (rs1.getInt(1) != 2)
1387: System.out
1388: .println("TEST36 FAILED: count should have been 2.");
1389:
1390: s.executeUpdate("CREATE SCHEMA SESSION");
1391: s.executeUpdate("SET SCHEMA SESSION");
1392:
1393: rs1 = s.executeQuery("select count(*) from t2"); //no need to qualify temp table here because we are in SESSION schema
1394: rs1.next();
1395: if (rs1.getInt(1) != 2)
1396: System.out
1397: .println("TEST36 FAILED: count should have been 2.");
1398:
1399: s.executeUpdate("DROP TABLE t2");
1400: s.executeUpdate("SET SCHEMA APP");
1401: s.executeUpdate("drop schema SESSION restrict");
1402: con1.commit();
1403: System.out.println("TEST36 PASSED");
1404: } catch (Throwable e) {
1405: System.out.println("Unexpected message: " + e.getMessage());
1406: con1.rollback();
1407: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1408: System.out.println("TEST36 FAILED");
1409: }
1410:
1411: try {
1412: System.out
1413: .println("TEST37A : Prepared statement test - drop the temp table underneath");
1414: s
1415: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1416: PreparedStatement pStmt = con1
1417: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1418: pStmt.setInt(1, 21);
1419: pStmt.setInt(2, 1);
1420: pStmt.execute();
1421:
1422: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1423: dumpRS(rs1);
1424:
1425: s.executeUpdate("DROP TABLE SESSION.t2");
1426: pStmt.setInt(1, 22);
1427: pStmt.setInt(2, 2);
1428: pStmt.execute();
1429: System.out
1430: .println("TEST37A : Should not reach here because SESSION.t2 has been dropped underneath the prepared statement");
1431:
1432: con1.rollback();
1433: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1434: System.out.println("TEST37A FAILED");
1435: } catch (Throwable e) {
1436: System.out.println("Expected message: " + e.getMessage());
1437: con1.commit();
1438: System.out.println("TEST37A PASSED");
1439: }
1440:
1441: try {
1442: System.out
1443: .println("TEST37B : Prepared statement test - drop and recreate the temp table with different definition underneath");
1444: s
1445: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1446: PreparedStatement pStmt = con1
1447: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1448: pStmt.setInt(1, 21);
1449: pStmt.setInt(2, 1);
1450: pStmt.execute();
1451:
1452: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1453: dumpRS(rs1);
1454:
1455: s.executeUpdate("DROP TABLE SESSION.t2");
1456: s
1457: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) not logged");
1458: pStmt.setInt(1, 22);
1459: pStmt.setInt(2, 2);
1460: pStmt.execute();
1461:
1462: rs1 = s.executeQuery("select * from SESSION.t2");
1463: dumpRS(rs1);
1464:
1465: s.executeUpdate("DROP TABLE SESSION.t2");
1466: s
1467: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int, c24 int not null) not logged");
1468: pStmt.setInt(1, 22);
1469: pStmt.setInt(2, 2);
1470: pStmt.execute();
1471: System.out
1472: .println("TEST37B : Should not reach here because SESSION.t2 has been recreated with not null column");
1473:
1474: con1.rollback();
1475: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1476: System.out.println("TEST37B FAILED");
1477: } catch (Throwable e) {
1478: System.out.println("Expected message: " + e.getMessage());
1479: s.executeUpdate("DROP TABLE SESSION.t2");
1480: con1.commit();
1481: System.out.println("TEST37B PASSED");
1482: }
1483:
1484: try {
1485: System.out
1486: .println("TEST38A : Rollback behavior - declare temp table, rollback, select should fail");
1487: s
1488: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1489: PreparedStatement pStmt = con1
1490: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1491: pStmt.setInt(1, 21);
1492: pStmt.setInt(2, 1);
1493: pStmt.execute();
1494:
1495: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1496: dumpRS(rs1);
1497:
1498: con1.rollback();
1499:
1500: System.out
1501: .println("TEST38A : select should fail since temp table got dropped as part of rollback");
1502: rs1 = s.executeQuery("select * from SESSION.t2"); //no temp table t2, should fail
1503:
1504: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1505: System.out.println("TEST38A FAILED");
1506: } catch (Throwable e) {
1507: System.out.println("Expected message: " + e.getMessage());
1508: con1.commit();
1509: System.out.println("TEST38A PASSED");
1510: }
1511:
1512: try {
1513: System.out
1514: .println("TEST38B : Rollback behavior - declare temp table, commit, drop temp table, rollback, select should pass");
1515: s
1516: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
1517: PreparedStatement pStmt = con1
1518: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1519: pStmt.setInt(1, 21);
1520: pStmt.setInt(2, 1);
1521: pStmt.execute();
1522:
1523: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1524: dumpRS(rs1);
1525:
1526: con1.commit();
1527:
1528: rs1 = s.executeQuery("select * from SESSION.t2");
1529: dumpRS(rs1);
1530:
1531: s.executeUpdate("DROP TABLE SESSION.t2");
1532:
1533: con1.rollback();
1534: System.out
1535: .println("TEST38B : select should pass since temp table drop was rolled back");
1536: rs1 = s.executeQuery("select * from SESSION.t2"); //no temp table t2, should fail
1537: dumpRS(rs1);
1538:
1539: s.executeUpdate("DROP TABLE SESSION.t2");
1540: con1.commit();
1541: System.out.println("TEST38B PASSED");
1542: } catch (Throwable e) {
1543: System.out.println("Unexpected message: " + e.getMessage());
1544: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1545: System.out.println("TEST38B FAILED");
1546: }
1547:
1548: try {
1549: System.out.println("TEST38C : Rollback behavior");
1550: System.out.println(" In the transaction:");
1551: System.out
1552: .println(" Declare temp table t2 with 3 columns");
1553: s
1554: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) on commit preserve rows not logged");
1555: s.executeUpdate("insert into session.t2 values(1,1,1)");
1556: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1557: dumpRS(rs1);
1558: System.out.println(" Drop temp table t2 (with 3 columns)");
1559: s.executeUpdate("DROP TABLE SESSION.t2");
1560: try {
1561: rs1 = s.executeQuery("select * from SESSION.t2");
1562: } catch (Throwable e) {
1563: System.out
1564: .println(" Attempted to select from temp table t2 but it failed as expected with exception "
1565: + e.getMessage());
1566: }
1567: System.out
1568: .println(" Declare temp table t2 again but this time with 2 columns");
1569: s
1570: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
1571: rs1 = s.executeQuery("select * from SESSION.t2");
1572: dumpRS(rs1);
1573: System.out
1574: .println(" Commit the transaction. Should have temp table t2 with 2 columns");
1575: con1.commit();
1576:
1577: System.out.println(" In the next transaction:");
1578: rs1 = s.executeQuery("select * from SESSION.t2");
1579: dumpRS(rs1);
1580: System.out.println(" Drop temp table t2 (with 2 columns)");
1581: s.executeUpdate("DROP TABLE SESSION.t2");
1582: System.out
1583: .println(" Declare temp table t2 again but this time with 1 column");
1584: s
1585: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1586: rs1 = s.executeQuery("select * from SESSION.t2");
1587: dumpRS(rs1);
1588: System.out
1589: .println(" Rollback this transaction. Should have temp table t2 with 2 columns");
1590: con1.rollback();
1591:
1592: rs1 = s.executeQuery("select * from SESSION.t2");
1593: dumpRS(rs1);
1594: s.executeUpdate("DROP TABLE SESSION.t2");
1595:
1596: con1.commit();
1597: System.out.println("TEST38C PASSED");
1598: } catch (Throwable e) {
1599: System.out.println("Unexpected message: " + e.getMessage());
1600: con1.rollback();
1601: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1602: System.out.println("TEST38C FAILED");
1603: }
1604:
1605: try {
1606: System.out
1607: .println("TEST38D : Rollback behavior for tables touched with DML");
1608: System.out.println(" In the transaction:");
1609: System.out
1610: .println(" Declare temp table t2 & t3 & t4 & t5 with preserve rows, insert data and commit");
1611: s
1612: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
1613: s
1614: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) not logged on commit preserve rows on rollback delete rows");
1615: s
1616: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) not logged on rollback delete rows on commit preserve rows");
1617: s
1618: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t5(c51 int, c52 int) on commit preserve rows not logged");
1619: s.executeUpdate("insert into session.t2 values(21,1)");
1620: s.executeUpdate("insert into session.t2 values(22,2)");
1621: s.executeUpdate("insert into session.t2 values(23,3)");
1622: s.executeUpdate("insert into session.t3 values(31,1)");
1623: s.executeUpdate("insert into session.t3 values(32,2)");
1624: s.executeUpdate("insert into session.t3 values(33,3)");
1625: s.executeUpdate("insert into session.t4 values(41,1)");
1626: s.executeUpdate("insert into session.t4 values(42,2)");
1627: s.executeUpdate("insert into session.t4 values(43,3)");
1628: s.executeUpdate("insert into session.t5 values(51,1)");
1629: s.executeUpdate("insert into session.t5 values(52,2)");
1630: s.executeUpdate("insert into session.t5 values(53,3)");
1631: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1632: dumpRS(rs1);
1633: rs1 = s.executeQuery("select * from SESSION.t3");
1634: dumpRS(rs1);
1635: rs1 = s.executeQuery("select * from SESSION.t4");
1636: dumpRS(rs1);
1637: rs1 = s.executeQuery("select * from SESSION.t5");
1638: dumpRS(rs1);
1639: con1.commit();
1640:
1641: System.out.println(" In the next transaction:");
1642: System.out
1643: .println(" Declare temp table t6 with preserve rows, insert data and inspect data in all the tables");
1644: s
1645: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t6(c61 int, c62 int) on commit preserve rows not logged on rollback delete rows");
1646: s.executeUpdate("insert into session.t6 values(61,1)");
1647: s.executeUpdate("insert into session.t6 values(62,2)");
1648: s.executeUpdate("insert into session.t6 values(63,3)");
1649: rs1 = s.executeQuery("select * from SESSION.t2");
1650: dumpRS(rs1);
1651: rs1 = s.executeQuery("select * from SESSION.t3");
1652: dumpRS(rs1);
1653: rs1 = s.executeQuery("select * from SESSION.t4");
1654: dumpRS(rs1);
1655: rs1 = s.executeQuery("select * from SESSION.t5");
1656: dumpRS(rs1);
1657: rs1 = s.executeQuery("select * from SESSION.t6");
1658: dumpRS(rs1);
1659:
1660: System.out
1661: .println(" delete from t2 with t5 in it's where clause, look at t2");
1662: s
1663: .executeUpdate("DELETE FROM session.t2 WHERE c22> (select c52 from session.t5 where c52=2)");
1664: rs1 = s.executeQuery("select * from SESSION.t2");
1665: dumpRS(rs1);
1666:
1667: System.out
1668: .println(" delete with where clause from t3 so that no rows get deleted, look at the rows");
1669: s.executeUpdate("DELETE FROM session.t3 WHERE c32>3");
1670: rs1 = s.executeQuery("select * from SESSION.t3");
1671: dumpRS(rs1);
1672:
1673: System.out.println(" do not touch t4");
1674:
1675: System.out
1676: .println(" rollback this transaction, should not see any rows in temp table t2 after rollback");
1677: con1.rollback();
1678: rs1 = s.executeQuery("select * from SESSION.t2");
1679: dumpRS(rs1);
1680:
1681: System.out
1682: .println(" temp table t3 should have no rows because attempt was made to delete from it (even though nothing actually got deleted from it in the transaction)");
1683: rs1 = s.executeQuery("select * from SESSION.t3");
1684: dumpRS(rs1);
1685:
1686: System.out
1687: .println(" temp table t4 should have its data intact because it was not touched in the transaction that got rolled back");
1688: rs1 = s.executeQuery("select * from SESSION.t4");
1689: dumpRS(rs1);
1690:
1691: System.out
1692: .println(" temp table t5 should have its data intact because it was only used in where clause and not touched in the transaction that got rolled back");
1693: rs1 = s.executeQuery("select * from SESSION.t5");
1694: dumpRS(rs1);
1695:
1696: System.out
1697: .println(" temp table t6 got dropped as part of rollback of this transaction since it was declared in this same transaction");
1698: try {
1699: rs1 = s.executeQuery("select * from SESSION.t6");
1700: } catch (Throwable e) {
1701: System.out
1702: .println(" Attempted to select from temp table t6 but it failed as expected with exception "
1703: + e.getMessage());
1704: }
1705:
1706: s.executeUpdate("DROP TABLE SESSION.t2");
1707: s.executeUpdate("DROP TABLE SESSION.t3");
1708: s.executeUpdate("DROP TABLE SESSION.t4");
1709: s.executeUpdate("DROP TABLE SESSION.t5");
1710: con1.commit();
1711: System.out.println("TEST38D PASSED");
1712: } catch (Throwable e) {
1713: System.out.println("Unexpected message: " + e.getMessage());
1714: con1.rollback();
1715: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1716: System.out.println("TEST38D FAILED");
1717: }
1718:
1719: try {
1720: System.out
1721: .println("TEST39A : Verify that there is no entry in system catalogs for temporary tables");
1722: System.out
1723: .println(" Declare a temp table T2 and check system catalogs. Shouldn't find anything. Then drop the temp table");
1724: s
1725: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1726: ResultSet rs1 = s
1727: .executeQuery("select * from sys.systables where tablename like 'T2'");
1728: dumpRS(rs1);
1729: rs1 = s
1730: .executeQuery("select tablename, schemaname from sys.systables t, sys.sysschemas s where t.tablename like 'T2' and t.schemaid=s.schemaid");
1731: dumpRS(rs1);
1732: s.executeUpdate("DROP TABLE SESSION.t2");
1733: System.out
1734: .println(" Create physical schema SESSION, create a physical table T2 in SESSION schema and check system catalogs. Should be there");
1735: s.executeUpdate("CREATE SCHEMA SESSION");
1736: s
1737: .executeUpdate("CREATE TABLE SESSION.t2(c21 int, c22 int)");
1738: rs1 = s
1739: .executeQuery("select * from sys.systables where tablename like 'T2'");
1740: dumpRS(rs1);
1741: s.executeUpdate("DROP TABLE SESSION.t2");
1742: s.executeUpdate("drop schema SESSION restrict");
1743:
1744: con1.commit();
1745: System.out.println("TEST39A PASSED");
1746: } catch (Throwable e) {
1747: System.out.println("Unexpected message: " + e.getMessage());
1748: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1749: System.out.println("TEST39A FAILED");
1750: }
1751:
1752: try {
1753: System.out
1754: .println("TEST39B : Verify that there is no entry in system catalogs for SESSION schmea after declare table");
1755: System.out
1756: .println(" Declare a temp table T2 and check system catalogs for SESSION schmea. Shouldn't find anything. Then drop the temp table");
1757: s
1758: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1759: ResultSet rs1 = s
1760: .executeQuery("select schemaname from sys.sysschemas where schemaname like 'SESSION'");
1761: dumpRS(rs1);
1762: s.executeUpdate("DROP TABLE SESSION.t2");
1763:
1764: con1.commit();
1765: System.out.println("TEST39B PASSED");
1766: } catch (Throwable e) {
1767: System.out.println("Unexpected message: " + e.getMessage());
1768: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1769: System.out.println("TEST39B FAILED");
1770: }
1771:
1772: try {
1773: System.out
1774: .println("TEST40 : DatabaseMetaData.getTables() should not return temporary tables");
1775: DatabaseMetaData databaseMetaData;
1776: databaseMetaData = con1.getMetaData();
1777: s.executeUpdate("CREATE SCHEMA SESSION");
1778: s
1779: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1780: s
1781: .executeUpdate("CREATE TABLE SESSION.t3(c31 int, c32 int)");
1782: System.out.println("getTables() with no types:");
1783: dumpRS(databaseMetaData.getTables("", null, "%", null));
1784:
1785: s.executeUpdate("DROP TABLE SESSION.t2");
1786: s.executeUpdate("DROP TABLE SESSION.t3");
1787: s.executeUpdate("drop schema SESSION restrict");
1788: con1.commit();
1789: System.out.println("TEST40 PASSED");
1790: } catch (Throwable e) {
1791: System.out.println("Unexpected message: " + e.getMessage());
1792: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1793: System.out.println("TEST40 FAILED");
1794: }
1795:
1796: try {
1797: System.out
1798: .println("TEST41 : delete where current of on temporary tables");
1799: s
1800: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
1801: s.executeUpdate("insert into SESSION.t2 values(21, 1)");
1802: s.executeUpdate("insert into SESSION.t2 values(22, 1)");
1803: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1804: dumpRS(rs1);
1805: PreparedStatement pStmt1 = con1
1806: .prepareStatement("select c21 from session.t2 for update");
1807: ResultSet rs2 = pStmt1.executeQuery();
1808: rs2.next();
1809: PreparedStatement pStmt2 = con1
1810: .prepareStatement("delete from session.t2 where current of "
1811: + rs2.getCursorName());
1812: pStmt2.executeUpdate();
1813: rs1 = s.executeQuery("select * from SESSION.t2");
1814: dumpRS(rs1);
1815: rs2.next();
1816: pStmt2.executeUpdate();
1817: rs1 = s.executeQuery("select * from SESSION.t2");
1818: dumpRS(rs1);
1819:
1820: rs2.close();
1821: s.executeUpdate("DROP TABLE SESSION.t2");
1822: con1.commit();
1823: System.out.println("TEST41 PASSED");
1824: } catch (Throwable e) {
1825: System.out.println("Unexpected message: " + e.getMessage());
1826: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1827: System.out.println("TEST41 FAILED");
1828: }
1829:
1830: try {
1831: System.out
1832: .println("TEST42 : update where current of on temporary tables");
1833: s
1834: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
1835: s.executeUpdate("insert into SESSION.t2 values(21, 1)");
1836: s.executeUpdate("insert into SESSION.t2 values(22, 1)");
1837: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1838: dumpRS(rs1);
1839: PreparedStatement pStmt1 = con1
1840: .prepareStatement("select c21 from session.t2 for update");
1841: ResultSet rs2 = pStmt1.executeQuery();
1842: rs2.next();
1843: PreparedStatement pStmt2 = con1
1844: .prepareStatement("update session.t2 set c22 = 2 where current of "
1845: + rs2.getCursorName());
1846: pStmt2.executeUpdate();
1847: rs1 = s.executeQuery("select * from SESSION.t2");
1848: dumpRS(rs1);
1849: rs2.next();
1850: pStmt2.executeUpdate();
1851: rs1 = s.executeQuery("select * from SESSION.t2");
1852: dumpRS(rs1);
1853:
1854: rs2.close();
1855: s.executeUpdate("DROP TABLE SESSION.t2");
1856: con1.commit();
1857: System.out.println("TEST42 PASSED");
1858: } catch (Throwable e) {
1859: System.out.println("Unexpected message: " + e.getMessage());
1860: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1861: System.out.println("TEST42 FAILED");
1862: }
1863: /*
1864: try
1865: {
1866: System.out.println("TEST43A : SET CONSTRAINTS not allowed on global temporary tables");
1867:
1868: s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1869: s.executeUpdate("SET CONSTRAINTS FOR SESSION.t2 DISABLED");
1870:
1871: con1.rollback();
1872: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1873: System.out.println("TEST43A FAILED");
1874: } catch (Throwable e)
1875: {
1876: System.out.println("Expected message: "+ e.getMessage());
1877: s.executeUpdate("DROP TABLE SESSION.t2");
1878: con1.commit();
1879: System.out.println("TEST43A PASSED");
1880: }
1881:
1882: try
1883: {
1884: System.out.println("TEST43C : SET CONSTRAINTS FOR on physical table in SESSION schema should work");
1885:
1886: s.executeUpdate("CREATE schema SESSION");
1887: s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
1888: s.executeUpdate("SET CONSTRAINTS FOR SESSION.t2 ENABLED");
1889: s.executeUpdate("DROP TABLE SESSION.t2");
1890: s.executeUpdate("drop schema SESSION restrict");
1891:
1892: con1.commit();
1893: System.out.println("TEST43C PASSED");
1894: } catch (Throwable e)
1895: {
1896: System.out.println("Unexpected message: "+ e.getMessage());
1897: con1.rollback();
1898: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1899: System.out.println("TEST43C FAILED");
1900: }
1901: */
1902: try {
1903: System.out
1904: .println("TEST44A : Prepared statement test - DML and rollback behavior");
1905: System.out.println(" In the transaction:");
1906: System.out
1907: .println(" Declare temp table t2, insert data using prepared statement and commit");
1908: s
1909: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
1910: PreparedStatement pStmt = con1
1911: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1912: pStmt.setInt(1, 21);
1913: pStmt.setInt(2, 1);
1914: pStmt.execute();
1915:
1916: con1.commit();
1917: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1918: dumpRS(rs1);
1919:
1920: System.out.println(" In the next transaction:");
1921: System.out
1922: .println(" insert more data using same prepared statement and rollback. Should loose all the data in t2");
1923: pStmt.setInt(1, 22);
1924: pStmt.setInt(2, 2);
1925: pStmt.execute();
1926: con1.rollback();
1927: rs1 = s.executeQuery("select * from SESSION.t2");
1928: dumpRS(rs1);
1929:
1930: s.executeUpdate("DROP TABLE SESSION.t2");
1931: con1.commit();
1932: System.out.println("TEST44A PASSED");
1933: } catch (Throwable e) {
1934: System.out.println("Expected message: " + e.getMessage());
1935: con1.rollback();
1936: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1937: System.out.println("TEST44A FAILED");
1938: }
1939:
1940: try {
1941: System.out
1942: .println("TEST44B : Prepared statement test - DML and rollback behavior");
1943: System.out.println(" In the transaction:");
1944: System.out
1945: .println(" Declare temp table t2, insert data and commit");
1946: s
1947: .executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
1948: s.executeUpdate("INSERT INTO SESSION.t2 VALUES(21, 1)");
1949:
1950: con1.commit();
1951: ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1952: dumpRS(rs1);
1953:
1954: System.out.println(" In the next transaction:");
1955: System.out
1956: .println(" prepare a statement for insert into table but do not execute it and rollback");
1957: PreparedStatement pStmt = con1
1958: .prepareStatement("insert into SESSION.t2 values (?, ?)");
1959: con1.rollback();
1960: System.out.println(" Should not loose the data from t2");
1961: rs1 = s.executeQuery("select * from SESSION.t2");
1962: dumpRS(rs1);
1963:
1964: s.executeUpdate("DROP TABLE SESSION.t2");
1965: con1.commit();
1966: System.out.println("TEST44B PASSED");
1967: } catch (Throwable e) {
1968: System.out.println("Expected message: " + e.getMessage());
1969: con1.rollback();
1970: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1971: System.out.println("TEST44B FAILED");
1972: }
1973:
1974: /* try
1975: {
1976: System.out.println("TEST33A : CREATE STATEMENT attempting to reference physical SESSION table in USING clause should work??");
1977:
1978: s.executeUpdate("CREATE SCHEMA SESSION");
1979: s.executeUpdate("CREATE TABLE t1(c11 int)");
1980: s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
1981: s.executeUpdate("INSERT INTO SESSION.t2(c21) VALUES(1)");
1982: s.executeUpdate("CREATE STATEMENT s2 as select * from t1 where c11 = ? using select c21 from SESSION.t2");
1983:
1984: s.executeUpdate("DROP STATEMENT s2");
1985: s.executeUpdate("DROP TABLE t1");
1986: s.executeUpdate("DROP TABLE SESSION.t2");
1987: s.executeUpdate("drop schema SESSION restrict");
1988: con1.commit();
1989: System.out.println("TEST33A PASSED");
1990: } catch (Throwable e)
1991: {
1992: System.out.println("Unxpected message: "+ e.getMessage());
1993: con1.rollback();
1994: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1995: System.out.println("TEST33A FAILED");
1996: }
1997:
1998: try
1999: {
2000: System.out.println("TEST33B : CREATE STATEMENT attempting to global temp table in USING clause should work??");
2001:
2002: s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged");
2003: s.executeUpdate("CREATE TABLE t1(c11 int)");
2004: s.executeUpdate("INSERT INTO SESSION.t2(c21) VALUES(1)");
2005: s.executeUpdate("CREATE STATEMENT s2 as select * from t1 where c11 = ? using select c21 from SESSION.t2");
2006:
2007: s.executeUpdate("DROP STATEMENT s2");
2008: s.executeUpdate("DROP TABLE t1");
2009: s.executeUpdate("DROP TABLE SESSION.t2");
2010: con1.commit();
2011: System.out.println("TEST33B PASSED");
2012: } catch (Throwable e)
2013: {
2014: System.out.println("Unxpected message: "+ e.getMessage());
2015: con1.rollback();
2016: passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
2017: System.out.println("TEST33B FAILED");
2018: } */
2019:
2020: return passed;
2021: }
2022:
2023: static private void dumpExpectedSQLExceptions(SQLException se) {
2024: System.out.println("PASS -- expected exception");
2025: while (se != null) {
2026: System.out.println("SQLSTATE(" + se.getSQLState() + "): "
2027: + se);
2028: se = se.getNextException();
2029: }
2030: }
2031:
2032: static private void dumpSQLExceptions(SQLException se) {
2033: System.out.println("FAIL -- unexpected exception");
2034: while (se != null) {
2035: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
2036: se.printStackTrace(System.out);
2037: se = se.getNextException();
2038: }
2039: }
2040:
2041: // lifted from the metadata test
2042: private static void dumpRS(ResultSet s) throws SQLException {
2043: if (s == null) {
2044: System.out.println("<NULL>");
2045: return;
2046: }
2047:
2048: ResultSetMetaData rsmd = s.getMetaData();
2049:
2050: // Get the number of columns in the result set
2051: int numCols = rsmd.getColumnCount();
2052:
2053: if (numCols <= 0) {
2054: System.out.println("(no columns!)");
2055: return;
2056: }
2057:
2058: StringBuffer heading = new StringBuffer("\t ");
2059: StringBuffer underline = new StringBuffer("\t ");
2060:
2061: int len;
2062: // Display column headings
2063: for (int i = 1; i <= numCols; i++) {
2064: if (i > 1) {
2065: heading.append(",");
2066: underline.append(" ");
2067: }
2068: len = heading.length();
2069: heading.append(rsmd.getColumnLabel(i));
2070: len = heading.length() - len;
2071: for (int j = len; j > 0; j--) {
2072: underline.append("-");
2073: }
2074: }
2075: System.out.println(heading.toString());
2076: System.out.println(underline.toString());
2077:
2078: StringBuffer row = new StringBuffer();
2079: // Display data, fetching until end of the result set
2080: while (s.next()) {
2081: row.append("\t{");
2082: // Loop through each column, getting the
2083: // column data and displaying
2084: for (int i = 1; i <= numCols; i++) {
2085: if (i > 1)
2086: row.append(",");
2087: row.append(s.getString(i));
2088: }
2089: row.append("}\n");
2090: }
2091: System.out.println(row.toString());
2092: s.close();
2093: }
2094: }
|