Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > declareGlobalTempTableJava


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJava
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.Connection ;
25 import java.sql.DatabaseMetaData ;
26 import java.sql.PreparedStatement ;
27 import java.sql.ResultSet ;
28 import java.sql.ResultSetMetaData ;
29 import java.sql.Statement ;
30 import java.sql.SQLException ;
31
32 import org.apache.derby.tools.ij;
33 import org.apache.derby.tools.JDBCDisplayUtil;
34
35 /**
36  * Test for declared global temporary tables introduced in Cloudscape 5.2
37  * The temp table tests with holdable cursor and savepoints are in declareGlobalTempTableJavaJDBC30 class.
38  * The reason for a different test class is that the holdability and savepoint support is under jdk14 and higher.
39  * But we want to be able to run the non-jdk14 specific tests under all the jdks we support and hence splitting
40  * the tests into 2 different classes
41  */
42
43
44 public class declareGlobalTempTableJava {
45
46     /*
47     ** There is a small description prior to each sub-test describing what is being tested.
48     */
49     public static void main(String [] args) {
50         boolean passed = true;
51
52         Connection con1 = null, con2 = null;
53         Statement s = null;
54
55         /* Run all parts of this test, and catch any exceptions */
56         try {
57             System.out.println("Test declaredGlobalTempTableJava starting");
58
59             /* Load the JDBC Driver class */
60             // use the ij utility to read the property file and
61 // make the initial connection.
62
63             ij.getPropertyArg(args);
64             con1 = ij.startJBMS();
65             con2 = ij.startJBMS();
66
67             s = con1.createStatement();
68             con1.setAutoCommit(false);
69             con2.setAutoCommit(false);
70
71             /* Test this before other tests because this test requires
72              that session schema has not been created yet */
73             passed = testDERBY1706(con1, s) && passed;
74
75             /* Test various schema and grammar related cases */
76             passed = testSchemaNameAndGrammar(con1, s) && passed;
77
78             /* Test various unallowed operations */
79             passed = testOtherOperations(con1, s, con2) && passed;
80
81             con1.close();
82             con2.close();
83
84         } catch (Throwable e) {
85             System.out.println("FAIL -- unexpected exception "+e);
86             JDBCDisplayUtil.ShowException(System.out, e);
87             e.printStackTrace();
88             passed = false;
89         }
90
91         if (passed)
92             System.out.println("PASS");
93
94         System.out.println("Test declaredGlobalTempTable finished");
95     }
96
97     /**
98      * Test switching to session schema (it doesn't yet exist because
99      * no create schema session has been issued yet) & then try to create
100      * first persistent object in it. This used to cause null pointer
101      * exception (DERBY-1706).
102      *
103      * @param conn The Connection
104      * @param s A Statement on the Connection
105      *
106      * @return true if it succeeds, false if it doesn't
107      *
108      * @exception SQLException Thrown if some unexpected error happens
109      */
110
111     static boolean testDERBY1706(Connection con1, Statement s)
112                     throws SQLException {
113         boolean passed = true;
114
115         try
116         {
117             System.out.print("TEST-DERBY1706 : Create a persistent object");
118             System.out.print(" in SESSION schema w/o first creating the");
119             System.out.println(" schema");
120
121             s.executeUpdate("set schema SESSION");
122             s.executeUpdate("create table DERBY1706(c11 int)");
123             s.executeUpdate("drop table DERBY1706");
124             s.executeUpdate("set schema APP");
125             s.executeUpdate("drop schema SESSION restrict");
126
127             con1.commit();
128             System.out.println("TEST-DERBY1706 PASSED");
129         } catch (Throwable e)
130         {
131             System.out.println("Unexpected message: " + e.getMessage());
132             con1.rollback();
133             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
134 System.out.println("TEST-DERBY1706 FAILED");
135         }
136
137         return passed;
138     }
139
140     /**
141      * Test various schema and grammar related cases
142      *
143      * @param conn The Connection
144      * @param s A Statement on the Connection
145      *
146      * @return true if it succeeds, false if it doesn't
147      *
148      * @exception SQLException Thrown if some unexpected error happens
149      */
150
151     static boolean testSchemaNameAndGrammar(Connection con1, Statement s)
152                     throws SQLException {
153         boolean passed = true;
154
155         try
156         {
157             System.out.println("TEST1 : global temporary tables can only be in SESSION schema");
158
159             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE APP.t2(c21 int) on commit delete rows not logged");
160
161             con1.rollback();
162             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
163 System.out.println("TEST1 FAILED");
164         } catch (Throwable e)
165         {
166             System.out.println("Expected message: "+ e.getMessage());
167             con1.commit();
168             System.out.println("TEST1 PASSED");
169         }
170
171         try
172         {
173             System.out.print("TEST2A : Declaring a global temporary table while in SYS schema will pass ");
174             System.out.println("because temp tables always go in SESSION schema and never in default schema");
175
176             s.executeUpdate("set schema SYS");
177             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t2(c21 int) on commit delete rows not logged");
178
179             con1.commit();
180             System.out.println("TEST2A PASSED");
181         } catch (Throwable e)
182         {
183             System.out.println("Unexpected message: " + e.getMessage());
184             con1.rollback();
185             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
186 System.out.println("TEST2A FAILED");
187         }
188
189         try
190         {
191             System.out.println("TEST2B : Drop the declared global temporary table declared in TEST2A while in schema SYS");
192
193             s.executeUpdate("DROP TABLE SESSION.t2");
194             s.executeUpdate("set schema APP");
195
196             con1.commit();
197             System.out.println("TEST2B PASSED");
198         } catch (Throwable e)
199         {
200             System.out.println("Unexpected message: " + e.getMessage());
201             con1.rollback();
202             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
203 System.out.println("TEST2B FAILED");
204         }
205
206         try
207         {
208             System.out.println("TEST3A : positive grammar tests for DECLARE command");
209             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tA(c1 int) not logged");
210             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tB(c1 int) on commit delete rows not logged");
211             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tC(c1 int) not logged on commit delete rows");
212             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tD(c1 int) on commit preserve rows not logged");
213             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tE(c1 int) not logged on commit preserve rows");
214             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tF(c1 int) on rollback delete rows not logged");
215             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tG(c1 int) not logged on rollback delete rows");
216             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tH(c1 int) on commit preserve rows not logged on rollback delete rows");
217             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tI(c1 int) not logged on commit preserve rows on rollback delete rows");
218             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tJ(c1 int) not logged on rollback delete rows on commit preserve rows");
219             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tK(c1 int) on commit delete rows not logged on rollback delete rows");
220             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tL(c1 int) not logged on commit delete rows on rollback delete rows");
221             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tM(c1 int) not logged on rollback delete rows on commit delete rows");
222
223             s.executeUpdate("DROP TABLE SESSION.tA");
224             s.executeUpdate("DROP TABLE SESSION.tB");
225             s.executeUpdate("DROP TABLE SESSION.tC");
226             s.executeUpdate("DROP TABLE SESSION.tD");
227             s.executeUpdate("DROP TABLE SESSION.tE");
228             s.executeUpdate("DROP TABLE SESSION.tF");
229             s.executeUpdate("DROP TABLE SESSION.tG");
230             s.executeUpdate("DROP TABLE SESSION.tH");
231             s.executeUpdate("DROP TABLE SESSION.tI");
232             s.executeUpdate("DROP TABLE SESSION.tJ");
233             s.executeUpdate("DROP TABLE SESSION.tK");
234             s.executeUpdate("DROP TABLE SESSION.tL");
235             s.executeUpdate("DROP TABLE SESSION.tM");
236             con1.commit();
237             System.out.println("TEST3A PASSED");
238         } catch (Throwable e)
239         {
240             System.out.println("Unexpected message: "+ e.getMessage());
241             con1.rollback();
242             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
243 System.out.println("TEST3A FAILED");
244         }
245
246         try
247         {
248             System.out.println("TEST3B : negative grammar tests for DECLARE command");
249
250             try {
251                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int)");
252             } catch (Throwable e)
253             {
254                 System.out.println(" Expected exception. Attempted to declare a temp table without NOT LOGGED. " + e.getMessage());
255             }
256
257             try {
258                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED NOT LOGGED");
259             } catch (Throwable e)
260             {
261                 System.out.println(" Expected exception. Attempted to declare a temp table with multiple NOT LOGGED. " + e.getMessage());
262             }
263
264             try {
265                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON COMMIT PRESERVE ROWS ON COMMIT DELETE ROWS");
266             } catch (Throwable e)
267             {
268                 System.out.println(" Expected exception. Attempted to declare a temp table with multiple ON COMMIT. " + e.getMessage());
269             }
270
271             try {
272                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK DELETE ROWS ON ROLLBACK DELETE ROWS");
273             } catch (Throwable e)
274             {
275                 System.out.println(" Expected exception. Attempted to declare a temp table with multiple ON ROLLBACK. " + e.getMessage());
276             }
277
278             try {
279                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK PRESERVE ROWS");
280             } catch (Throwable e)
281             {
282                 System.out.println(" Expected exception. Attempted to declare a temp table with syntax error ON ROLLBACK PRESERVE ROWS. " + e.getMessage());
283             }
284
285             try {
286                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) ON ROLLBACK DELETE ROWS ON COMMIT PRESERVE ROWS");
287             } catch (Throwable e)
288             {
289                 System.out.println(" Expected exception. Attempted to declare a temp table without NOT LOGGED. " + e.getMessage());
290             }
291
292             con1.commit();
293             System.out.println("TEST3B PASSED");
294         } catch (Throwable e)
295         {
296             System.out.println("Unexpected message: "+ e.getMessage());
297             con1.rollback();
298             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
299 System.out.println("TEST3B FAILED");
300         }
301
302         return passed;
303     }
304
305     /**
306      * Test various other operations on declared global temporary tables
307      *
308      * @param con1 Connection to the database
309      * @param s A Statement on the Connection
310      * @param con2 Another Connection to the database
311      *
312      * @return true if it succeeds, false if it doesn't
313      *
314      * @exception SQLException Thrown if some unexpected error happens
315      */
316
317     static boolean testOtherOperations(Connection con1, Statement s, Connection con2)
318                     throws SQLException {
319         boolean passed = true;
320
321         try
322         {
323             System.out.println("TEST4A : ALTER TABLE not allowed on global temporary tables");
324
325             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit delete rows");
326             s.executeUpdate("ALTER TABLE SESSION.t2 add column c22 int");
327
328             con1.rollback();
329             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
330 System.out.println("TEST4A FAILED");
331         } catch (Throwable e)
332         {
333             System.out.println("Expected message: "+ e.getMessage());
334             s.executeUpdate("DROP TABLE SESSION.t2");
335             con1.commit();
336             System.out.println("TEST4A PASSED");
337         }
338
339         try
340         {
341             System.out.println("TEST4B : ALTER TABLE on physical table in SESSION schema should work");
342
343             s.executeUpdate("CREATE schema SESSION");
344             s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
345             s.executeUpdate("ALTER TABLE SESSION.t2 add column c22 int");
346             s.executeUpdate("DROP TABLE SESSION.t2");
347             s.executeUpdate("drop schema SESSION restrict");
348
349             con1.commit();
350             System.out.println("TEST4B PASSED");
351         } catch (Throwable e)
352         {
353             System.out.println("Unexpected message: "+ e.getMessage());
354             con1.rollback();
355             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
356 System.out.println("TEST4B FAILED");
357         }
358
359         try
360         {
361             System.out.println("TEST5A : LOCK TABLE not allowed on global temporary tables");
362
363             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
364             s.executeUpdate("LOCK TABLE SESSION.t2 IN SHARE MODE");
365
366             con1.rollback();
367             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
368 System.out.println("TEST5A FAILED");
369         } catch (Throwable e)
370         {
371             System.out.println("Expected message: "+ e.getMessage());
372             s.executeUpdate("DROP TABLE SESSION.t2");
373             con1.commit();
374             System.out.println("TEST5A PASSED");
375         }
376
377         try
378         {
379             System.out.println("TEST5B : LOCK TABLE on physical table in SESSION schema should work");
380
381             s.executeUpdate("CREATE schema SESSION");
382             s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
383             s.executeUpdate("LOCK TABLE SESSION.t2 IN EXCLUSIVE MODE");
384             s.executeUpdate("DROP TABLE SESSION.t2");
385             s.executeUpdate("DROP schema SESSION restrict");
386
387             con1.commit();
388             System.out.println("TEST5B PASSED");
389         } catch (Throwable e)
390         {
391             System.out.println("Unexpected message: "+ e.getMessage());
392             con1.rollback();
393             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
394 System.out.println("TEST5B FAILED");
395         }
396
397         try
398         {
399             System.out.println("TEST6A : RENAME TABLE not allowed on global temporary tables");
400
401             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
402             s.executeUpdate("RENAME TABLE SESSION.t2 TO t3");
403
404             con1.rollback();
405             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
406 System.out.println("TEST6A FAILED");
407         } catch (Throwable e)
408         {
409             System.out.println("Expected message: "+ e.getMessage());
410             s.executeUpdate("DROP TABLE SESSION.t2");
411             con1.commit();
412             System.out.println("TEST6A PASSED");
413         }
414
415         try
416         {
417             System.out.println("TEST6B : RENAME TABLE on physical table in SESSION schema should work");
418
419             s.executeUpdate("CREATE schema SESSION");
420             s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
421             s.executeUpdate("RENAME TABLE SESSION.t2 TO t3");
422             s.executeUpdate("DROP TABLE SESSION.t3");
423             s.executeUpdate("drop schema SESSION restrict");
424
425             con1.commit();
426             System.out.println("TEST6B PASSED");
427         } catch (Throwable e)
428         {
429             System.out.println("Unexpected message: "+ e.getMessage());
430             con1.rollback();
431             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
432 System.out.println("TEST6B FAILED");
433         }
434
435         try
436         {
437             System.out.println("TEST6C : RENAME COLUMN on physical table in SESSION schema should work");
438
439             s.executeUpdate("CREATE schema SESSION");
440             s.executeUpdate("SET schema SESSION");
441             s.executeUpdate("CREATE TABLE t2(c21 int)");
442             //s.executeUpdate("RENAME COLUMN t2.c21 TO c22");
443 s.executeUpdate("SET schema APP");
444             s.executeUpdate("DROP TABLE SESSION.t2");
445             s.executeUpdate("drop schema SESSION restrict");
446
447             con1.commit();
448             System.out.println("TEST6C PASSED");
449         } catch (Throwable e)
450         {
451             System.out.println("Unexpected message: "+ e.getMessage());
452             con1.rollback();
453             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
454 System.out.println("TEST6C FAILED");
455         }
456
457         try
458         {
459             System.out.println("TEST8 : generated always as identity not supported for declared global temporary tables");
460
461             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int generated always as identity) on commit delete rows not logged");
462
463             con1.rollback();
464             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
465 System.out.println("TEST8 FAILED");
466         } catch (Throwable e)
467         {
468             System.out.println("Expected message: "+ e.getMessage());
469             con1.commit();
470             System.out.println("TEST8 PASSED");
471         }
472
473         try
474         {
475             System.out.println("TEST9 : long datatypes not supported for declared global temporary tables");
476
477             try {
478                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 blob(3k)) on commit delete rows not logged");
479             } catch (Throwable e)
480             {
481                 System.out.println(" Expected exception. Attempted to declare a temp table with blob. " + e.getMessage());
482             }
483
484             try {
485                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 clob(3k)) on commit delete rows not logged");
486             } catch (Throwable e)
487             {
488                 System.out.println(" Expected exception. Attempted to declare a temp table with clob. " + e.getMessage());
489             }
490
491             try {
492                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 long varchar) on commit delete rows not logged");
493             } catch (Throwable e)
494             {
495                 System.out.println(" Expected exception. Attempted to declare a temp table with long varchar. " + e.getMessage());
496             }
497
498             try {
499                 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 \"org.apache.derbyTesting.functionTests.util.ShortHolder\") on commit delete rows not logged");
500             } catch (Throwable e)
501             {
502                 System.out.println(" Expected exception. Attempted to declare a temp table with user defined type. " + e.getMessage());
503             }
504
505             con1.commit();
506             System.out.println("TEST9 PASSED");
507         } catch (Throwable e)
508         {
509             System.out.println("Unexpected message: "+ e.getMessage());
510             con1.rollback();
511             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
512 System.out.println("TEST9 FAILED");
513         }
514
515         try
516         {
517             System.out.println("TEST10A : Primary key constraint not allowed on a declared global temporary table.");
518
519             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21)) on commit delete rows not logged");
520
521             con1.rollback();
522             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
523 System.out.println("TEST10A FAILED");
524         } catch (Throwable e)
525         {
526             System.out.println("Expected message: "+ e.getMessage());
527             con1.commit();
528             System.out.println("TEST10A PASSED");
529         }
530
531         try
532         {
533             System.out.println("TEST10B : Primary key constraint allowed on a physical table in SESSION schema.");
534
535             s.executeUpdate("CREATE SCHEMA SESSION");
536             s.executeUpdate("CREATE TABLE SESSION.t2(c21 int not null, constraint pk primary key (c21))");
537             s.executeUpdate("DROP TABLE SESSION.t2");
538             s.executeUpdate("drop schema SESSION restrict");
539
540             con1.commit();
541             System.out.println("TEST10B PASSED");
542         } catch (Throwable e)
543         {
544             System.out.println("Unexpected message: "+ e.getMessage());
545             con1.rollback();
546             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
547 System.out.println("TEST10B FAILED");
548         }
549
550         try
551         {
552             System.out.println("TEST10C : Unique key constraint not allowed on a declared global temporary table.");
553
554             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int not null unique) on commit delete rows not logged");
555
556             con1.rollback();
557             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
558 System.out.println("TEST10C FAILED");
559         } catch (Throwable e)
560         {
561             System.out.println("Expected message: "+ e.getMessage());
562             con1.commit();
563             System.out.println("TEST10C PASSED");
564         }
565
566         try
567         {
568             System.out.println("TEST10D : Foreign key constraint not allowed on a declared global temporary table.");
569
570             s.executeUpdate("CREATE TABLE t1(c11 int not null unique)");
571             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int references t1(c11)) on commit delete rows not logged");
572
573             con1.rollback();
574             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
575 System.out.println("TEST10D FAILED");
576         } catch (Throwable e)
577         {
578             System.out.println("Expected message: "+ e.getMessage());
579             s.executeUpdate("DROP TABLE t1");
580             con1.commit();
581             System.out.println("TEST10D PASSED");
582         }
583
584         try
585         {
586             System.out.println("TEST11 : Attempt to declare the same global temporary table twice will fail. Plan to support WITH REPLACE in future");
587
588             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
589             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit preserve rows");
590
591             con1.rollback();
592             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
593 System.out.println("TEST11 FAILED");
594         } catch (Throwable e)
595         {
596             System.out.println("Expected message: "+ e.getMessage());
597             s.executeUpdate("DROP TABLE SESSION.t2");
598             con1.commit();
599             System.out.println("TEST11 PASSED");
600         }
601
602         try
603         {
604             System.out.println("TEST12 : Try to drop a declared global temporary table that doesn't exist.");
605
606             s.executeUpdate("DROP TABLE SESSION.t2");
607
608             con1.rollback();
609             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
610 System.out.println("TEST12 FAILED");
611         } catch (Throwable e)
612         {
613             System.out.println("Expected message: "+ e.getMessage());
614             con1.commit();
615             System.out.println("TEST12 PASSED");
616         }
617
618         try
619         {
620             System.out.println("TEST13A : insert into declared global temporary table will pass.");
621
622             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2)) on commit delete rows not logged");
623             s.executeUpdate("insert into SESSION.t2 values (1, 'aa')");
624             s.executeUpdate("insert into SESSION.t2 values (2, 'bb'),(3, 'cc'),(4, null)");
625             s.executeUpdate("CREATE TABLE t1(c11 int, c22 char(2))");
626             s.executeUpdate("insert into t1 values (5, null),(6, null),(7, 'gg')");
627             s.executeUpdate("insert into SESSION.t2 (select * from t1 where c11>4)");
628             s.executeUpdate("insert into SESSION.t2 select * from SESSION.t2");
629             ResultSet rs1 = s.executeQuery("select sum(c21) from SESSION.t2");
630       dumpRS(rs1);
631             s.executeUpdate("DROP TABLE SESSION.t2");
632             s.executeUpdate("DROP TABLE t1");
633
634             con1.commit();
635             System.out.println("TEST13A PASSED");
636         } catch (Throwable e)
637         {
638             System.out.println("Unexpected message: "+ e.getMessage());
639             con1.rollback();
640             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
641 System.out.println("TEST13A FAILED");
642         }
643
644         try
645         {
646             System.out.println("TEST13B : attempt to insert null into non-null column in declared global temporary table will fail.");
647             System.out.println("Declare the table with non-null column, insert a row and commit");
648
649             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 char(2) not null) on commit delete rows not logged");
650             s.executeUpdate("insert into SESSION.t2 values (1, 'aa')");
651             con1.commit();
652             System.out.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");
653             s.executeUpdate("insert into SESSION.t2 values (2, null)");
654
655             con1.rollback();
656             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
657 System.out.println("TEST13B FAILED");
658         } catch (Throwable e)
659         {
660             System.out.println("Expected message: "+ e.getMessage());
661             System.out.println("should see no data in t2");
662
663             ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
664             dumpRS(rs1);
665             s.executeUpdate("DROP TABLE SESSION.t2");
666             con1.commit();
667             System.out.println("TEST13B PASSED");
668         }
669
670         try
671         {
672             System.out.println("TEST13C : declare a temporary table with default and then insert into it.");
673
674             s.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");
675             s.executeUpdate("insert into SESSION.t2 values (1, 'aa', null)");
676             s.executeUpdate("insert into SESSION.t2(c21) values (2)");
677             ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
678       dumpRS(rs1);
679
680             s.executeUpdate("DROP TABLE SESSION.t2");
681             con1.commit();
682             System.out.println("TEST13C PASSED");
683         } catch (Throwable e)
684         {
685             System.out.println("Unexpected message: "+ e.getMessage());
686             con1.rollback();
687             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
688 System.out.println("TEST13C FAILED");
689         }
690
691         try
692         {
693             System.out.println("TEST14 : Should be able to create Session schema manually.");
694
695             s.executeUpdate("CREATE schema SESSION");
696
697             con1.commit();
698             System.out.println("TEST14 PASSED");
699         } catch (Throwable e)
700         {
701             System.out.println("Unexpected message: "+ e.getMessage());
702             con1.rollback();
703             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
704 System.out.println("TEST14 FAILED");
705         }
706
707         try
708         {
709             System.out.println("TEST15 : Session schema can be dropped like any other user-defined schema.");
710
711             s.executeUpdate("drop schema SESSION restrict");
712
713             con1.commit();
714             System.out.println("TEST15 PASSED");
715         } catch (Throwable e)
716         {
717             System.out.println("Unexpected message: "+ e.getMessage());
718             con1.rollback();
719             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
720 System.out.println("TEST15 FAILED");
721         }
722
723         try
724         {
725             System.out.print("TEST16 : Create a physical SESSION schema, drop it. Next attempt to drop SESSION schema will throw ");
726             System.out.println("an exception because now we are dealing with in-memory SESSION schema and it can not be dropped by drop schema.");
727
728             s.executeUpdate("CREATE schema SESSION");
729             s.executeUpdate("drop schema SESSION restrict");
730
731             System.out.println("In TEST16, now attempting to drop in-memory SESSION schema");
732             s.executeUpdate("drop schema SESSION restrict"); //this should fail
733
734             con1.rollback();
735             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
736 System.out.println("TEST16 FAILED");
737         } catch (Throwable e)
738         {
739             System.out.println("Expected message: "+ e.getMessage());
740             con1.commit();
741             System.out.println("TEST16 PASSED");
742         }
743
744         try
745         {
746             System.out.println("TEST17A : Check constraint not allowed on global temporary table");
747
748             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int check (c21 > 0)) on commit delete rows not logged");
749
750             con1.rollback();
751             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
752 System.out.println("TEST17A FAILED");
753         } catch (Throwable e)
754         {
755             System.out.println("Expected message: "+ e.getMessage());
756             con1.commit();
757             System.out.println("TEST17A PASSED");
758         }
759
760         try
761         {
762             System.out.println("TEST17B : Check constraint allowed on physical SESSION schema table");
763
764             s.executeUpdate("CREATE schema SESSION");
765             s.executeUpdate("CREATE TABLE SESSION.t2(c21 int check (c21 > 0))");
766             s.executeUpdate("DROP TABLE SESSION.t2");
767             s.executeUpdate("drop schema SESSION restrict");
768
769             con1.commit();
770             System.out.println("TEST17B PASSED");
771         } catch (Throwable e)
772         {
773             System.out.println("Unexpected message: "+ e.getMessage());
774             con1.rollback();
775             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
776 System.out.println("TEST17B FAILED");
777         }
778
779         try
780         {
781             System.out.println("TEST18 : Test declared temporary table with ON COMMIT DELETE ROWS with and without open cursors");
782             System.out.println("Tests with holdable cursor are in a different class since holdability support is only under jdk14 and higher");
783
784             System.out.println("Temp table t2 with not holdable cursor open on it. Data should get deleted from t2 at commit time");
785             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
786             s.executeUpdate("insert into SESSION.t2 values(22, 22)");
787             s.executeUpdate("insert into SESSION.t2 values(23, 23)");
788
789             ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2");
790       dumpRS(rs2);
791
792             rs2 = s.executeQuery("select * from SESSION.t2"); //eventhough this cursor is open, it is not a hold cursor. Commit should delete the rows
793 rs2.next();
794
795             System.out.println("Temp table t3 with no open cursors of any kind on it. Data should get deleted from t3 at commit time");
796             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
797             s.executeUpdate("insert into SESSION.t3 values(32, 32)");
798             s.executeUpdate("insert into SESSION.t3 values(33, 33)");
799
800             ResultSet rs3 = s.executeQuery("select count(*) from SESSION.t3");
801       dumpRS(rs3);
802
803             con1.commit();
804
805             System.out.println("After commit, verify the 2 tables");
806             System.out.println("Temp table t2 will have no data after commit");
807             rs2 = s.executeQuery("select count(*) from SESSION.t2");
808       dumpRS(rs2);
809
810             System.out.println("Temp table t3 will have no data after commit");
811             rs3 = s.executeQuery("select count(*) from SESSION.t3");
812       dumpRS(rs3);
813
814             s.executeUpdate("DROP TABLE SESSION.t2");
815             s.executeUpdate("DROP TABLE SESSION.t3");
816
817             con1.commit();
818             System.out.println("TEST18 PASSED");
819         } catch (Throwable e)
820         {
821             System.out.println("Unexpected message: "+ e.getMessage());
822             con1.rollback();
823             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
824 System.out.println("TEST18 FAILED");
825         }
826
827         try
828         {
829             System.out.println("TEST19 : Declare a temporary table with ON COMMIT PRESERVE ROWS with and without open cursors");
830             System.out.println("Tests with holdable cursor are in a different class since holdability support is only under jdk14 and higher");
831
832             System.out.println("Temp table t2 with not holdable cursor open on it. Data should be preserved, holdability shouldn't matter");
833             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
834             s.executeUpdate("insert into SESSION.t2 values(22, 22)");
835             s.executeUpdate("insert into SESSION.t2 values(23, 23)");
836
837             ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2");
838       dumpRS(rs2);
839
840             rs2 = s.executeQuery("select * from SESSION.t2"); //eventhough this cursor is open, it is not a hold cursor.
841 rs2.next();
842
843             System.out.println("Temp table t3 with no open cursors of any kind on it. Data should be preserved, holdability shouldn't matter");
844             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
845             s.executeUpdate("insert into SESSION.t3 values(32, 32)");
846             s.executeUpdate("insert into SESSION.t3 values(33, 33)");
847
848             ResultSet rs3 = s.executeQuery("select count(*) from SESSION.t3");
849       dumpRS(rs3);
850
851             con1.commit();
852
853             System.out.println("After commit, verify the 2 tables");
854             System.out.println("Temp table t2 will have data after commit");
855             rs2 = s.executeQuery("select count(*) from SESSION.t2");
856       dumpRS(rs2);
857
858             System.out.println("Temp table t3 will have data after commit");
859             rs3 = s.executeQuery("select count(*) from SESSION.t3");
860       dumpRS(rs3);
861
862             s.executeUpdate("DROP TABLE SESSION.t2");
863             s.executeUpdate("DROP TABLE SESSION.t3");
864
865             con1.commit();
866             System.out.println("TEST19 PASSED");
867         } catch (Throwable e)
868         {
869             System.out.println("Unexpected message: "+ e.getMessage());
870             con1.rollback();
871             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
872 System.out.println("TEST19 FAILED");
873         }
874
875         try
876         {
877             System.out.println("TEST20A : CREATE INDEX not allowed on global temporary table.");
878
879             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
880             s.executeUpdate("CREATE index t2i1 on SESSION.t2 (c21)");
881
882             con1.rollback();
883             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
884 System.out.println("TEST20A FAILED");
885         } catch (Throwable e)
886         {
887             System.out.println("Expected message: "+ e.getMessage());
888             s.executeUpdate("DROP TABLE SESSION.t2");
889             con1.commit();
890             System.out.println("TEST20A PASSED");
891         }
892
893         try
894         {
895             System.out.println("TEST21A : CREATE INDEX on physical table in SESSION schema should work");
896
897             s.executeUpdate("CREATE schema SESSION");
898             s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
899             s.executeUpdate("CREATE index t3i1 on SESSION.t3 (c31)");
900             s.executeUpdate("DROP TABLE SESSION.t3");
901             s.executeUpdate("drop schema SESSION restrict");
902
903             con1.commit();
904             System.out.println("TEST21A PASSED");
905         } catch (Throwable e)
906         {
907             System.out.println("Unexpected message: "+ e.getMessage());
908             con1.rollback();
909             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
910 System.out.println("TEST21A FAILED");
911         }
912 /*
913         try
914         {
915             System.out.println("TEST22A : CREATE TRIGGER not allowed on global temporary table.");
916
917             s.executeUpdate("CREATE TABLE t1(c11 int)");
918             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
919             s.executeUpdate("CREATE TRIGGER t2tr1 before insert on SESSION.t2 for each statement insert into t1 values(1)");
920
921             con1.rollback();
922             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
923             System.out.println("TEST22A FAILED");
924         } catch (Throwable e)
925         {
926             System.out.println("Expected message: "+ e.getMessage());
927             s.executeUpdate("DROP TABLE SESSION.t2");
928             s.executeUpdate("DROP TABLE t1");
929             con1.commit();
930             System.out.println("TEST22A PASSED");
931         }
932
933         try
934         {
935             System.out.println("TEST23A : CREATE TRIGGER not allowed on physical table in SESSION schema");
936
937             s.executeUpdate("CREATE schema SESSION");
938             s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
939             s.executeUpdate("CREATE TABLE SESSION.t4 (c41 int)");
940             s.executeUpdate("CREATE TRIGGER t3tr1 before insert on SESSION.t3 for each statement insert into SESSION.t4 values(1)");
941
942             con1.rollback();
943             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
944             System.out.println("TEST23A FAILED");
945         } catch (Throwable e)
946         {
947             System.out.println("Expected message: "+ e.getMessage());
948             s.executeUpdate("DROP TABLE SESSION.t3");
949             s.executeUpdate("DROP TABLE SESSION.t4");
950             s.executeUpdate("drop schema SESSION restrict");
951             con1.commit();
952             System.out.println("TEST23A PASSED");
953         }
954
955         try
956         {
957             System.out.println("TEST24A : Temporary tables can not be referenced in trigger action");
958
959             s.executeUpdate("CREATE TABLE t3 (c31 int)");
960             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4 (c41 int) not logged");
961             s.executeUpdate("CREATE TRIGGER t3tr1 before insert on t3 for each statement insert into SESSION.t4 values(1)");
962
963             con1.rollback();
964             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
965             System.out.println("TEST24A FAILED");
966         } catch (Throwable e)
967         {
968             System.out.println("Expected message: "+ e.getMessage());
969             s.executeUpdate("DROP TABLE t3");
970             s.executeUpdate("DROP TABLE SESSION.t4");
971             con1.commit();
972             System.out.println("TEST24A PASSED");
973         }
974
975         try
976         {
977             System.out.println("TEST24B : SESSION schema persistent tables can not be referenced in trigger action");
978
979             s.executeUpdate("CREATE TABLE t3 (c31 int)"); //not a SESSION schema table
980             s.executeUpdate("CREATE SCHEMA SESSION");
981             s.executeUpdate("CREATE TABLE SESSION.t4 (c41 int)");
982             s.executeUpdate("CREATE TRIGGER t3tr1 before insert on t3 for each statement delete from SESSION.t4");
983
984             con1.rollback();
985             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
986             System.out.println("TEST24B FAILED");
987         } catch (Throwable e)
988         {
989             System.out.println("Expected message: "+ e.getMessage());
990             s.executeUpdate("DROP TABLE t3");
991             s.executeUpdate("DROP TABLE SESSION.t4");
992             s.executeUpdate("drop schema SESSION restrict");
993             con1.commit();
994             System.out.println("TEST24B PASSED");
995         }
996 */
997         try
998         {
999             System.out.println("TEST26A : CREATE VIEW not allowed on global temporary table.");
1000
1001            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1002            s.executeUpdate("CREATE VIEW t2v1 as select * from SESSION.t2");
1003
1004            con1.rollback();
1005            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1006 System.out.println("TEST26A FAILED");
1007        } catch (Throwable e)
1008        {
1009            System.out.println("Expected message: "+ e.getMessage());
1010            s.executeUpdate("DROP TABLE SESSION.t2");
1011            con1.commit();
1012            System.out.println("TEST26A PASSED");
1013        }
1014
1015        try
1016        {
1017            System.out.println("TEST27A : CREATE VIEW not allowed on physical table in SESSION schema");
1018
1019            s.executeUpdate("CREATE schema SESSION");
1020            s.executeUpdate("CREATE TABLE SESSION.t3 (c31 int)");
1021            s.executeUpdate("CREATE VIEW t3v1 as select * from SESSION.t3");
1022
1023            con1.rollback();
1024            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1025 System.out.println("TEST27A FAILED");
1026        } catch (Throwable e)
1027        {
1028            System.out.println("Expected message: "+ e.getMessage());
1029            s.executeUpdate("DROP TABLE SESSION.t3");
1030            s.executeUpdate("drop schema SESSION restrict");
1031            con1.commit();
1032            System.out.println("TEST27A PASSED");
1033        }
1034
1035        //Derby424 - Queryplan for a query using SESSION schema view is incorrectly put in statement cache. This
1036 //could cause incorrect plan getting executed later if a temp. table is created with that name.
1037 System.out.println("TEST28A : CREATE VIEW in SESSION schema referencing a table outside of SESSION schema");
1038        s.executeUpdate("CREATE TABLE t28A (c28 int)");
1039        s.executeUpdate("INSERT INTO t28A VALUES (280),(281)");
1040        s.executeUpdate("CREATE VIEW SESSION.t28v1 as select * from t28A");
1041        System.out.println("SELECT * from SESSION.t28v1 should show contents of view");
1042        dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
1043        System.out.println("Now declare a global temporary table with same name as the view in SESSION schema");
1044        s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t28v1(c21 int, c22 int) not logged");
1045        s.executeUpdate("INSERT INTO SESSION.t28v1 VALUES (280,1),(281,2)");
1046        System.out.println("SELECT * from SESSION.t28v1 should show contents of global temporary table");
1047        dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
1048        s.executeUpdate("DROP TABLE SESSION.t28v1");
1049        System.out.println("We have dropped global temporary table hence SESSION.t28v1 should point to view at this point");
1050        dumpRS(s.executeQuery("SELECT * from SESSION.t28v1"));
1051        s.executeUpdate("DROP VIEW SESSION.t28v1");
1052        con1.rollback();
1053        con1.commit();
1054        System.out.println("TEST28A PASSED");
1055
1056        try
1057        {
1058            System.out.println("TEST29A : DELETE FROM global temporary table allowed.");
1059
1060            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 decimal) not logged");
1061            s.executeUpdate("insert into SESSION.t2 values(1, 1.1)");
1062            s.executeUpdate("insert into SESSION.t2 values(2, 2.2)");
1063
1064            ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2");
1065            dumpRS(rs2);
1066
1067            s.executeUpdate("DELETE FROM SESSION.t2 where c21 > 0");
1068
1069            rs2 = s.executeQuery("select count(*) from SESSION.t2");
1070            dumpRS(rs2);
1071
1072            s.executeUpdate("DROP TABLE SESSION.t2");
1073            con1.commit();
1074            System.out.println("TEST29A PASSED");
1075        } catch (Throwable e)
1076        {
1077            System.out.println("Unexpected message: "+ e.getMessage());
1078            con1.rollback();
1079            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1080 System.out.println("TEST29A FAILED");
1081        }
1082
1083        try
1084        {
1085            System.out.println("TEST31A : UPDATE on global temporary table allowed.");
1086
1087            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1088            s.executeUpdate("insert into SESSION.t2 values(1, 1)");
1089            s.executeUpdate("insert into SESSION.t2 values(2, 1)");
1090
1091            ResultSet rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 1");
1092            rs2.next();
1093            if (rs2.getInt(1) != 2)
1094                System.out.println("TEST31A FAILED: count should have been 2.");
1095
1096            s.executeUpdate("UPDATE SESSION.t2 SET c22 = 2 where c21>0");
1097
1098            rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 1");
1099            rs2.next();
1100            if (rs2.getInt(1) != 0)
1101                System.out.println("TEST31A FAILED: count should have been 0.");
1102
1103            rs2 = s.executeQuery("select count(*) from SESSION.t2 where c22 = 2");
1104            rs2.next();
1105            if (rs2.getInt(1) != 2)
1106                System.out.println("TEST31A FAILED: count should have been 2.");
1107
1108            s.executeUpdate("DROP TABLE SESSION.t2");
1109            con1.commit();
1110            System.out.println("TEST31A PASSED");
1111        } catch (Throwable e)
1112        {
1113            System.out.println("Unexpected message: "+ e.getMessage());
1114            con1.rollback();
1115            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1116 System.out.println("TEST31A FAILED");
1117        }
1118/*
1119        try
1120        {
1121            System.out.println("TEST32A : SET TRIGGERS not allowed on global temporary tables");
1122
1123            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1124            s.executeUpdate("SET TRIGGERS FOR SESSION.t2 ENABLED");
1125
1126            con1.rollback();
1127            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1128            System.out.println("TEST32A FAILED");
1129        } catch (Throwable e)
1130        {
1131            System.out.println("Expected message: "+ e.getMessage());
1132            s.executeUpdate("DROP TABLE SESSION.t2");
1133            con1.commit();
1134            System.out.println("TEST32A PASSED");
1135        }
1136        try
1137        {
1138            System.out.println("TEST32C : SET TRIGGERS on physical table in SESSION schema should work");
1139
1140            s.executeUpdate("CREATE schema SESSION");
1141            s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
1142            s.executeUpdate("SET TRIGGERS FOR SESSION.t2 ENABLED");
1143            s.executeUpdate("DROP TABLE SESSION.t2");
1144            s.executeUpdate("drop schema SESSION restrict");
1145
1146            con1.commit();
1147            System.out.println("TEST32C PASSED");
1148        } catch (Throwable e)
1149        {
1150            System.out.println("Unexpected message: "+ e.getMessage());
1151            con1.rollback();
1152            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1153            System.out.println("TEST32C FAILED");
1154        } */
1155
1156        System.out.println("Multiple tests to make sure we do not do statement caching for statement referencing SESSION schema tables");
1157        try
1158        {
1159            System.out.println("TEST34A : CREATE physical table and then DECLARE GLOBAL TEMPORARY TABLE with the same name in session schema.");
1160
1161            con1.setAutoCommit(true);
1162            //Need to do following 3 in autocommit mode otherwise the data dictionary will be in write mode and statements won't get
1163 //cached. I need to have statement caching enabled here to make sure that tables with same names do not conflict
1164 s.executeUpdate("CREATE schema SESSION");
1165            s.executeUpdate("CREATE TABLE SESSION.t2 (c21 int)");
1166            s.executeUpdate("INSERT into SESSION.t2 values(21)");
1167
1168            con1.setAutoCommit(false);
1169            //select will return data from physical table t2
1170 s.execute("select * from SESSION.t2");
1171            dumpRS(s.getResultSet());
1172
1173            //declare temporary table with same name as a physical table in SESSION schema
1174 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
1175            s.executeUpdate("INSERT into SESSION.t2 values(22, 22)");
1176            s.executeUpdate("INSERT into SESSION.t2 values(23, 23)");
1177            //select will return data from temp table t2
1178 s.execute("select c21,c22 from SESSION.t2");
1179            dumpRS(s.getResultSet());
1180            //select will return data from temp table t2
1181 s.execute("select * from SESSION.t2");
1182            dumpRS(s.getResultSet());
1183
1184            //drop the temp table t2
1185 s.executeUpdate("DROP TABLE SESSION.t2");
1186            //select will return data from physical table t2 because temp table has been deleted
1187 s.execute("select * from SESSION.t2");
1188            dumpRS(s.getResultSet());
1189
1190            //cleanup
1191 s.executeUpdate("DROP TABLE SESSION.t2");
1192            s.executeUpdate("drop schema SESSION restrict");
1193            con1.commit();
1194            System.out.println("TEST34A PASSED");
1195        } catch (Throwable e)
1196        {
1197            System.out.println("Unexpected message: " + e.getMessage());
1198            con1.rollback();
1199            passed = false; //we shouldn't have reached here. Return false to indicate failure
1200 System.out.println("TEST34A FAILED");
1201        }
1202        try
1203        {
1204            System.out.println("TEST34B : Physical table & TEMPORARY TABLE with the same name in session schema, try insert.");
1205
1206            con1.setAutoCommit(true);
1207            //Need to do following 3 in autocommit mode otherwise the data dictionary will be in write mode and statements won't get
1208 //cached. I need to have statement caching enabled here to make sure that tables with same names do not conflict
1209 s.executeUpdate("CREATE schema SESSION");
1210            s.executeUpdate("CREATE TABLE SESSION.t2 (c21 int)");
1211            s.executeUpdate("INSERT into SESSION.t2 values(21)");
1212
1213            con1.setAutoCommit(false);
1214            //select will return data from physical table t2
1215 s.execute("select * from SESSION.t2");
1216            dumpRS(s.getResultSet());
1217
1218            //declare temporary table with same name as a physical table in SESSION schema
1219 s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1220            //select will return data from temp table t2
1221 s.execute("select * from SESSION.t2");
1222            dumpRS(s.getResultSet());
1223            s.executeUpdate("INSERT into SESSION.t2 values(99)");
1224            s.execute("select * from SESSION.t2");
1225            dumpRS(s.getResultSet());
1226
1227            //drop the temp table t2
1228 s.executeUpdate("DROP TABLE SESSION.t2");
1229            //select will return data from physical table t2 because temp table has been deleted
1230 s.execute("select * from SESSION.t2");
1231            dumpRS(s.getResultSet());
1232
1233            //cleanup
1234 s.executeUpdate("DROP TABLE SESSION.t2");
1235            s.executeUpdate("drop schema SESSION restrict");
1236            con1.commit();
1237            System.out.println("TEST34B PASSED");
1238        } catch (Throwable e)
1239        {
1240            System.out.println("Unexpected message: " + e.getMessage());
1241            con1.rollback();
1242            passed = false; //we shouldn't have reached here. Return false to indicate failure
1243 System.out.println("TEST34B FAILED");
1244        }
1245
1246        try
1247        {
1248            System.out.println("TEST35A : Temporary table created in one connection should not be available in another connection");
1249            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1250            s.executeUpdate("insert into SESSION.t2 values(22, 22)");
1251
1252            ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
1253      dumpRS(rs1);
1254
1255            Statement s2 = con2.createStatement();
1256            ResultSet rs2 = s2.executeQuery("select count(*) from SESSION.t2"); //con2 should not find temp table declared in con1
1257
1258      dumpRS(rs2);
1259            con1.rollback();
1260            con2.rollback();
1261            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1262 System.out.println("TEST35A FAILED");
1263        } catch (Throwable e)
1264        {
1265            System.out.println("Expected message: "+ e.getMessage());
1266            s.executeUpdate("DROP TABLE SESSION.t2");
1267            con1.commit();
1268            con2.commit();
1269            System.out.println("TEST35A PASSED");
1270        }
1271
1272        try
1273        {
1274            System.out.println("TEST35B : Temp table in one connection should not conflict with temp table with same name in another connection");
1275            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1276            s.executeUpdate("insert into SESSION.t2 values(22, 22)");
1277
1278            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1279      dumpRS(rs1); //should return 22, 22
1280
1281            Statement s2 = con2.createStatement();
1282            s2.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged");
1283            s2.executeUpdate("insert into SESSION.t2 values(99)");
1284            ResultSet rs2 = s2.executeQuery("select * from SESSION.t2");
1285      dumpRS(rs2); //should return 99
1286
1287            rs1 = s.executeQuery("select * from SESSION.t2");
1288      dumpRS(rs1); //should return 22, 22
1289
1290            s.executeUpdate("DROP TABLE SESSION.t2"); //dropping temp table t2 defined for con1
1291 s2.executeUpdate("DROP TABLE SESSION.t2"); //dropping temp table t2 defined for con2
1292 con1.commit();
1293            con2.commit();
1294            System.out.println("TEST35B PASSED");
1295        } catch (Throwable e)
1296        {
1297            System.out.println("Unexpected message: "+ e.getMessage());
1298            con1.rollback();
1299            con2.rollback();
1300            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1301 System.out.println("TEST35B FAILED");
1302        }
1303
1304        try
1305        {
1306            System.out.println("TEST36 : After creating SESSION schema and making it current schema, temporary tables should not require SESSION qualification");
1307
1308            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1309            s.executeUpdate("insert into SESSION.t2 values(21, 21)");
1310            s.executeUpdate("insert into SESSION.t2 values(22, 22)");
1311
1312            ResultSet rs1 = s.executeQuery("select count(*) from SESSION.t2");
1313            rs1.next();
1314            if (rs1.getInt(1) != 2)
1315                System.out.println("TEST36 FAILED: count should have been 2.");
1316
1317            s.executeUpdate("CREATE SCHEMA SESSION");
1318            s.executeUpdate("SET SCHEMA SESSION");
1319
1320            rs1 = s.executeQuery("select count(*) from t2"); //no need to qualify temp table here because we are in SESSION schema
1321 rs1.next();
1322            if (rs1.getInt(1) != 2)
1323                System.out.println("TEST36 FAILED: count should have been 2.");
1324
1325            s.executeUpdate("DROP TABLE t2");
1326            s.executeUpdate("SET SCHEMA APP");
1327            s.executeUpdate("drop schema SESSION restrict");
1328            con1.commit();
1329            System.out.println("TEST36 PASSED");
1330        } catch (Throwable e)
1331        {
1332            System.out.println("Unexpected message: "+ e.getMessage());
1333            con1.rollback();
1334            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1335 System.out.println("TEST36 FAILED");
1336        }
1337
1338        try
1339        {
1340            System.out.println("TEST37A : Prepared statement test - drop the temp table underneath");
1341            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1342            PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
1343      pStmt.setInt(1, 21);
1344      pStmt.setInt(2, 1);
1345      pStmt.execute();
1346
1347            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1348            dumpRS(rs1);
1349
1350            s.executeUpdate("DROP TABLE SESSION.t2");
1351      pStmt.setInt(1, 22);
1352      pStmt.setInt(2, 2);
1353      pStmt.execute();
1354            System.out.println("TEST37A : Should not reach here because SESSION.t2 has been dropped underneath the prepared statement");
1355
1356            con1.rollback();
1357            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1358 System.out.println("TEST37A FAILED");
1359        } catch (Throwable e)
1360        {
1361            System.out.println("Expected message: "+ e.getMessage());
1362            con1.commit();
1363            System.out.println("TEST37A PASSED");
1364        }
1365
1366        try
1367        {
1368            System.out.println("TEST37B : Prepared statement test - drop and recreate the temp table with different definition underneath");
1369            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1370            PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
1371      pStmt.setInt(1, 21);
1372      pStmt.setInt(2, 1);
1373      pStmt.execute();
1374
1375            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1376            dumpRS(rs1);
1377
1378            s.executeUpdate("DROP TABLE SESSION.t2");
1379            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) not logged");
1380      pStmt.setInt(1, 22);
1381      pStmt.setInt(2, 2);
1382      pStmt.execute();
1383
1384            rs1 = s.executeQuery("select * from SESSION.t2");
1385            dumpRS(rs1);
1386
1387            s.executeUpdate("DROP TABLE SESSION.t2");
1388            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int, c24 int not null) not logged");
1389      pStmt.setInt(1, 22);
1390      pStmt.setInt(2, 2);
1391      pStmt.execute();
1392            System.out.println("TEST37B : Should not reach here because SESSION.t2 has been recreated with not null column");
1393
1394            con1.rollback();
1395            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1396 System.out.println("TEST37B FAILED");
1397        } catch (Throwable e)
1398        {
1399            System.out.println("Expected message: "+ e.getMessage());
1400            s.executeUpdate("DROP TABLE SESSION.t2");
1401            con1.commit();
1402            System.out.println("TEST37B PASSED");
1403        }
1404
1405        try
1406        {
1407            System.out.println("TEST38A : Rollback behavior - declare temp table, rollback, select should fail");
1408            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged");
1409            PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
1410      pStmt.setInt(1, 21);
1411      pStmt.setInt(2, 1);
1412      pStmt.execute();
1413
1414            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1415            dumpRS(rs1);
1416
1417            con1.rollback();
1418
1419            System.out.println("TEST38A : select should fail since temp table got dropped as part of rollback");
1420            rs1 = s.executeQuery("select * from SESSION.t2"); //no temp table t2, should fail
1421
1422            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1423 System.out.println("TEST38A FAILED");
1424        } catch (Throwable e)
1425        {
1426            System.out.println("Expected message: "+ e.getMessage());
1427            con1.commit();
1428            System.out.println("TEST38A PASSED");
1429        }
1430
1431        try
1432        {
1433            System.out.println("TEST38B : Rollback behavior - declare temp table, commit, drop temp table, rollback, select should pass");
1434            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
1435            PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
1436      pStmt.setInt(1, 21);
1437      pStmt.setInt(2, 1);
1438      pStmt.execute();
1439
1440            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1441            dumpRS(rs1);
1442
1443            con1.commit();
1444
1445            rs1 = s.executeQuery("select * from SESSION.t2");
1446            dumpRS(rs1);
1447
1448            s.executeUpdate("DROP TABLE SESSION.t2");
1449
1450            con1.rollback();
1451            System.out.println("TEST38B : select should pass since temp table drop was rolled back");
1452            rs1 = s.executeQuery("select * from SESSION.t2"); //no temp table t2, should fail
1453 dumpRS(rs1);
1454
1455            s.executeUpdate("DROP TABLE SESSION.t2");
1456            con1.commit();
1457            System.out.println("TEST38B PASSED");
1458        } catch (Throwable e)
1459        {
1460            System.out.println("Unexpected message: "+ e.getMessage());
1461            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1462 System.out.println("TEST38B FAILED");
1463        }
1464
1465        try
1466        {
1467            System.out.println("TEST38C : Rollback behavior");
1468            System.out.println(" In the transaction:");
1469            System.out.println(" Declare temp table t2 with 3 columns");
1470            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int, c23 int) on commit preserve rows not logged");
1471            s.executeUpdate("insert into session.t2 values(1,1,1)");
1472            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1473            dumpRS(rs1);
1474            System.out.println(" Drop temp table t2 (with 3 columns)");
1475            s.executeUpdate("DROP TABLE SESSION.t2");
1476            try {
1477                rs1 = s.executeQuery("select * from SESSION.t2");
1478            } catch (Throwable e)
1479            {
1480                System.out.println(" Attempted to select from temp table t2 but it failed as expected with exception " + e.getMessage());
1481            }
1482            System.out.println(" Declare temp table t2 again but this time with 2 columns");
1483            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
1484            rs1 = s.executeQuery("select * from SESSION.t2");
1485            dumpRS(rs1);
1486            System.out.println(" Commit the transaction. Should have temp table t2 with 2 columns");
1487            con1.commit();
1488
1489            System.out.println(" In the next transaction:");
1490            rs1 = s.executeQuery("select * from SESSION.t2");
1491            dumpRS(rs1);
1492            System.out.println(" Drop temp table t2 (with 2 columns)");
1493            s.executeUpdate("DROP TABLE SESSION.t2");
1494            System.out.println(" Declare temp table t2 again but this time with 1 column");
1495            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1496            rs1 = s.executeQuery("select * from SESSION.t2");
1497            dumpRS(rs1);
1498            System.out.println(" Rollback this transaction. Should have temp table t2 with 2 columns");
1499            con1.rollback();
1500
1501            rs1 = s.executeQuery("select * from SESSION.t2");
1502            dumpRS(rs1);
1503            s.executeUpdate("DROP TABLE SESSION.t2");
1504
1505            con1.commit();
1506            System.out.println("TEST38C PASSED");
1507        } catch (Throwable e)
1508        {
1509            System.out.println("Unexpected message: "+ e.getMessage());
1510            con1.rollback();
1511            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1512 System.out.println("TEST38C FAILED");
1513        }
1514
1515        try
1516        {
1517            System.out.println("TEST38D : Rollback behavior for tables touched with DML");
1518            System.out.println(" In the transaction:");
1519            System.out.println(" Declare temp table t2 & t3 & t4 & t5 with preserve rows, insert data and commit");
1520            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
1521            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) not logged on commit preserve rows on rollback delete rows");
1522            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) not logged on rollback delete rows on commit preserve rows");
1523            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t5(c51 int, c52 int) on commit preserve rows not logged");
1524            s.executeUpdate("insert into session.t2 values(21,1)");
1525            s.executeUpdate("insert into session.t2 values(22,2)");
1526            s.executeUpdate("insert into session.t2 values(23,3)");
1527            s.executeUpdate("insert into session.t3 values(31,1)");
1528            s.executeUpdate("insert into session.t3 values(32,2)");
1529            s.executeUpdate("insert into session.t3 values(33,3)");
1530            s.executeUpdate("insert into session.t4 values(41,1)");
1531            s.executeUpdate("insert into session.t4 values(42,2)");
1532            s.executeUpdate("insert into session.t4 values(43,3)");
1533            s.executeUpdate("insert into session.t5 values(51,1)");
1534            s.executeUpdate("insert into session.t5 values(52,2)");
1535            s.executeUpdate("insert into session.t5 values(53,3)");
1536            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1537            dumpRS(rs1);
1538            rs1 = s.executeQuery("select * from SESSION.t3");
1539            dumpRS(rs1);
1540            rs1 = s.executeQuery("select * from SESSION.t4");
1541            dumpRS(rs1);
1542            rs1 = s.executeQuery("select * from SESSION.t5");
1543            dumpRS(rs1);
1544            con1.commit();
1545
1546            System.out.println(" In the next transaction:");
1547            System.out.println(" Declare temp table t6 with preserve rows, insert data and inspect data in all the tables");
1548            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t6(c61 int, c62 int) on commit preserve rows not logged on rollback delete rows");
1549            s.executeUpdate("insert into session.t6 values(61,1)");
1550            s.executeUpdate("insert into session.t6 values(62,2)");
1551            s.executeUpdate("insert into session.t6 values(63,3)");
1552            rs1 = s.executeQuery("select * from SESSION.t2");
1553            dumpRS(rs1);
1554            rs1 = s.executeQuery("select * from SESSION.t3");
1555            dumpRS(rs1);
1556            rs1 = s.executeQuery("select * from SESSION.t4");
1557            dumpRS(rs1);
1558            rs1 = s.executeQuery("select * from SESSION.t5");
1559            dumpRS(rs1);
1560            rs1 = s.executeQuery("select * from SESSION.t6");
1561            dumpRS(rs1);
1562
1563            System.out.println(" delete from t2 with t5 in it's where clause, look at t2");
1564            s.executeUpdate("DELETE FROM session.t2 WHERE c22> (select c52 from session.t5 where c52=2)");
1565            rs1 = s.executeQuery("select * from SESSION.t2");
1566            dumpRS(rs1);
1567
1568            System.out.println(" delete with where clause from t3 so that no rows get deleted, look at the rows");
1569            s.executeUpdate("DELETE FROM session.t3 WHERE c32>3");
1570            rs1 = s.executeQuery("select * from SESSION.t3");
1571            dumpRS(rs1);
1572
1573            System.out.println(" do not touch t4");
1574
1575            System.out.println(" rollback this transaction, should not see any rows in temp table t2 after rollback");
1576            con1.rollback();
1577            rs1 = s.executeQuery("select * from SESSION.t2");
1578            dumpRS(rs1);
1579
1580            System.out.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)");
1581            rs1 = s.executeQuery("select * from SESSION.t3");
1582            dumpRS(rs1);
1583
1584            System.out.println(" temp table t4 should have its data intact because it was not touched in the transaction that got rolled back");
1585            rs1 = s.executeQuery("select * from SESSION.t4");
1586            dumpRS(rs1);
1587
1588            System.out.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");
1589            rs1 = s.executeQuery("select * from SESSION.t5");
1590            dumpRS(rs1);
1591
1592            System.out.println(" temp table t6 got dropped as part of rollback of this transaction since it was declared in this same transaction");
1593            try {
1594                rs1 = s.executeQuery("select * from SESSION.t6");
1595            } catch (Throwable e)
1596            {
1597                System.out.println(" Attempted to select from temp table t6 but it failed as expected with exception " + e.getMessage());
1598            }
1599
1600            s.executeUpdate("DROP TABLE SESSION.t2");
1601            s.executeUpdate("DROP TABLE SESSION.t3");
1602            s.executeUpdate("DROP TABLE SESSION.t4");
1603            s.executeUpdate("DROP TABLE SESSION.t5");
1604            con1.commit();
1605            System.out.println("TEST38D PASSED");
1606        } catch (Throwable e)
1607        {
1608            System.out.println("Unexpected message: "+ e.getMessage());
1609            con1.rollback();
1610            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1611 System.out.println("TEST38D FAILED");
1612        }
1613
1614        try
1615        {
1616            System.out.println("TEST39A : Verify that there is no entry in system catalogs for temporary tables");
1617            System.out.println(" Declare a temp table T2 and check system catalogs. Shouldn't find anything. Then drop the temp table");
1618            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1619            ResultSet rs1 = s.executeQuery("select * from sys.systables where tablename like 'T2'");
1620            dumpRS(rs1);
1621            rs1 = s.executeQuery("select tablename, schemaname from sys.systables t, sys.sysschemas s where t.tablename like 'T2' and t.schemaid=s.schemaid");
1622            dumpRS(rs1);
1623            s.executeUpdate("DROP TABLE SESSION.t2");
1624            System.out.println(" Create physical schema SESSION, create a physical table T2 in SESSION schema and check system catalogs. Should be there");
1625            s.executeUpdate("CREATE SCHEMA SESSION");
1626            s.executeUpdate("CREATE TABLE SESSION.t2(c21 int, c22 int)");
1627            rs1 = s.executeQuery("select * from sys.systables where tablename like 'T2'");
1628            dumpRS(rs1);
1629            s.executeUpdate("DROP TABLE SESSION.t2");
1630            s.executeUpdate("drop schema SESSION restrict");
1631
1632            con1.commit();
1633            System.out.println("TEST39A PASSED");
1634        } catch (Throwable e)
1635        {
1636            System.out.println("Unexpected message: "+ e.getMessage());
1637            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1638 System.out.println("TEST39A FAILED");
1639        }
1640
1641        try
1642        {
1643            System.out.println("TEST39B : Verify that there is no entry in system catalogs for SESSION schmea after declare table");
1644            System.out.println(" Declare a temp table T2 and check system catalogs for SESSION schmea. Shouldn't find anything. Then drop the temp table");
1645            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1646            ResultSet rs1 = s.executeQuery("select schemaname from sys.sysschemas where schemaname like 'SESSION'");
1647            dumpRS(rs1);
1648            s.executeUpdate("DROP TABLE SESSION.t2");
1649
1650            con1.commit();
1651            System.out.println("TEST39B PASSED");
1652        } catch (Throwable e)
1653        {
1654            System.out.println("Unexpected message: "+ e.getMessage());
1655            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1656 System.out.println("TEST39B FAILED");
1657        }
1658
1659        try
1660        {
1661            System.out.println("TEST40 : DatabaseMetaData.getTables() should not return temporary tables");
1662            DatabaseMetaData databaseMetaData;
1663            databaseMetaData = con1.getMetaData();
1664            s.executeUpdate("CREATE SCHEMA SESSION");
1665            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1666            s.executeUpdate("CREATE TABLE SESSION.t3(c31 int, c32 int)");
1667            System.out.println("getTables() with no types:");
1668            dumpRS(databaseMetaData.getTables("", null, "%", null));
1669
1670            s.executeUpdate("DROP TABLE SESSION.t2");
1671            s.executeUpdate("DROP TABLE SESSION.t3");
1672            s.executeUpdate("drop schema SESSION restrict");
1673            con1.commit();
1674            System.out.println("TEST40 PASSED");
1675        } catch (Throwable e)
1676        {
1677            System.out.println("Unexpected message: "+ e.getMessage());
1678            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1679 System.out.println("TEST40 FAILED");
1680        }
1681
1682        try
1683        {
1684            System.out.println("TEST41 : delete where current of on temporary tables");
1685            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
1686            s.executeUpdate("insert into SESSION.t2 values(21, 1)");
1687            s.executeUpdate("insert into SESSION.t2 values(22, 1)");
1688            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1689            dumpRS(rs1);
1690            PreparedStatement pStmt1 = con1.prepareStatement("select c21 from session.t2 for update");
1691            ResultSet rs2 = pStmt1.executeQuery();
1692            rs2.next();
1693            PreparedStatement pStmt2 = con1.prepareStatement("delete from session.t2 where current of "+
1694                                       rs2.getCursorName());
1695            pStmt2.executeUpdate();
1696            rs1 = s.executeQuery("select * from SESSION.t2");
1697            dumpRS(rs1);
1698            rs2.next();
1699            pStmt2.executeUpdate();
1700            rs1 = s.executeQuery("select * from SESSION.t2");
1701            dumpRS(rs1);
1702
1703            rs2.close();
1704            s.executeUpdate("DROP TABLE SESSION.t2");
1705            con1.commit();
1706            System.out.println("TEST41 PASSED");
1707        } catch (Throwable e)
1708        {
1709            System.out.println("Unexpected message: "+ e.getMessage());
1710            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1711 System.out.println("TEST41 FAILED");
1712        }
1713
1714        try
1715        {
1716            System.out.println("TEST42 : update where current of on temporary tables");
1717            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
1718            s.executeUpdate("insert into SESSION.t2 values(21, 1)");
1719            s.executeUpdate("insert into SESSION.t2 values(22, 1)");
1720            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1721            dumpRS(rs1);
1722            PreparedStatement pStmt1 = con1.prepareStatement("select c21 from session.t2 for update");
1723            ResultSet rs2 = pStmt1.executeQuery();
1724            rs2.next();
1725            PreparedStatement pStmt2 = con1.prepareStatement("update session.t2 set c22 = 2 where current of "+
1726                                       rs2.getCursorName());
1727            pStmt2.executeUpdate();
1728            rs1 = s.executeQuery("select * from SESSION.t2");
1729            dumpRS(rs1);
1730            rs2.next();
1731            pStmt2.executeUpdate();
1732            rs1 = s.executeQuery("select * from SESSION.t2");
1733            dumpRS(rs1);
1734
1735            rs2.close();
1736            s.executeUpdate("DROP TABLE SESSION.t2");
1737            con1.commit();
1738            System.out.println("TEST42 PASSED");
1739        } catch (Throwable e)
1740        {
1741            System.out.println("Unexpected message: "+ e.getMessage());
1742            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1743 System.out.println("TEST42 FAILED");
1744        }
1745/*
1746        try
1747        {
1748            System.out.println("TEST43A : SET CONSTRAINTS not allowed on global temporary tables");
1749
1750            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
1751            s.executeUpdate("SET CONSTRAINTS FOR SESSION.t2 DISABLED");
1752
1753            con1.rollback();
1754            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1755            System.out.println("TEST43A FAILED");
1756        } catch (Throwable e)
1757        {
1758            System.out.println("Expected message: "+ e.getMessage());
1759            s.executeUpdate("DROP TABLE SESSION.t2");
1760            con1.commit();
1761            System.out.println("TEST43A PASSED");
1762        }
1763
1764        try
1765        {
1766            System.out.println("TEST43C : SET CONSTRAINTS FOR on physical table in SESSION schema should work");
1767
1768            s.executeUpdate("CREATE schema SESSION");
1769            s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
1770            s.executeUpdate("SET CONSTRAINTS FOR SESSION.t2 ENABLED");
1771            s.executeUpdate("DROP TABLE SESSION.t2");
1772            s.executeUpdate("drop schema SESSION restrict");
1773
1774            con1.commit();
1775            System.out.println("TEST43C PASSED");
1776        } catch (Throwable e)
1777        {
1778            System.out.println("Unexpected message: "+ e.getMessage());
1779            con1.rollback();
1780            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1781            System.out.println("TEST43C FAILED");
1782        }
1783*/
1784        try
1785        {
1786            System.out.println("TEST44A : Prepared statement test - DML and rollback behavior");
1787            System.out.println(" In the transaction:");
1788            System.out.println(" Declare temp table t2, insert data using prepared statement and commit");
1789            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
1790            PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
1791            pStmt.setInt(1, 21);
1792            pStmt.setInt(2, 1);
1793            pStmt.execute();
1794
1795            con1.commit();
1796            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1797            dumpRS(rs1);
1798
1799            System.out.println(" In the next transaction:");
1800            System.out.println(" insert more data using same prepared statement and rollback. Should loose all the data in t2");
1801            pStmt.setInt(1, 22);
1802            pStmt.setInt(2, 2);
1803            pStmt.execute();
1804            con1.rollback();
1805            rs1 = s.executeQuery("select * from SESSION.t2");
1806            dumpRS(rs1);
1807
1808            s.executeUpdate("DROP TABLE SESSION.t2");
1809            con1.commit();
1810            System.out.println("TEST44A PASSED");
1811        } catch (Throwable e)
1812        {
1813            System.out.println("Expected message: "+ e.getMessage());
1814            con1.rollback();
1815            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1816 System.out.println("TEST44A FAILED");
1817        }
1818
1819        try
1820        {
1821            System.out.println("TEST44B : Prepared statement test - DML and rollback behavior");
1822            System.out.println(" In the transaction:");
1823            System.out.println(" Declare temp table t2, insert data and commit");
1824            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
1825            s.executeUpdate("INSERT INTO SESSION.t2 VALUES(21, 1)");
1826
1827            con1.commit();
1828            ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
1829            dumpRS(rs1);
1830
1831            System.out.println(" In the next transaction:");
1832            System.out.println(" prepare a statement for insert into table but do not execute it and rollback");
1833            PreparedStatement pStmt = con1.prepareStatement("insert into SESSION.t2 values (?, ?)");
1834            con1.rollback();
1835            System.out.println(" Should not loose the data from t2");
1836            rs1 = s.executeQuery("select * from SESSION.t2");
1837            dumpRS(rs1);
1838
1839            s.executeUpdate("DROP TABLE SESSION.t2");
1840            con1.commit();
1841            System.out.println("TEST44B PASSED");
1842        } catch (Throwable e)
1843        {
1844            System.out.println("Expected message: "+ e.getMessage());
1845            con1.rollback();
1846            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1847 System.out.println("TEST44B FAILED");
1848        }
1849
1850/* try
1851        {
1852            System.out.println("TEST33A : CREATE STATEMENT attempting to reference physical SESSION table in USING clause should work??");
1853
1854            s.executeUpdate("CREATE SCHEMA SESSION");
1855            s.executeUpdate("CREATE TABLE t1(c11 int)");
1856            s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
1857            s.executeUpdate("INSERT INTO SESSION.t2(c21) VALUES(1)");
1858            s.executeUpdate("CREATE STATEMENT s2 as select * from t1 where c11 = ? using select c21 from SESSION.t2");
1859
1860            s.executeUpdate("DROP STATEMENT s2");
1861            s.executeUpdate("DROP TABLE t1");
1862            s.executeUpdate("DROP TABLE SESSION.t2");
1863            s.executeUpdate("drop schema SESSION restrict");
1864            con1.commit();
1865            System.out.println("TEST33A PASSED");
1866        } catch (Throwable e)
1867        {
1868            System.out.println("Unxpected message: "+ e.getMessage());
1869            con1.rollback();
1870            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1871            System.out.println("TEST33A FAILED");
1872        }
1873
1874        try
1875        {
1876            System.out.println("TEST33B : CREATE STATEMENT attempting to global temp table in USING clause should work??");
1877
1878            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged");
1879            s.executeUpdate("CREATE TABLE t1(c11 int)");
1880            s.executeUpdate("INSERT INTO SESSION.t2(c21) VALUES(1)");
1881            s.executeUpdate("CREATE STATEMENT s2 as select * from t1 where c11 = ? using select c21 from SESSION.t2");
1882
1883            s.executeUpdate("DROP STATEMENT s2");
1884            s.executeUpdate("DROP TABLE t1");
1885            s.executeUpdate("DROP TABLE SESSION.t2");
1886            con1.commit();
1887            System.out.println("TEST33B PASSED");
1888        } catch (Throwable e)
1889        {
1890            System.out.println("Unxpected message: "+ e.getMessage());
1891            con1.rollback();
1892            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1893            System.out.println("TEST33B FAILED");
1894        } */
1895
1896        return passed;
1897    }
1898
1899    static private void dumpExpectedSQLExceptions (SQLException se) {
1900        System.out.println("PASS -- expected exception");
1901        while (se != null)
1902        {
1903            System.out.println("SQLSTATE("+se.getSQLState()+"): "+se);
1904            se = se.getNextException();
1905        }
1906    }
1907
1908    static private void dumpSQLExceptions (SQLException se) {
1909        System.out.println("FAIL -- unexpected exception");
1910        while (se != null) {
1911            System.out.print("SQLSTATE("+se.getSQLState()+"):");
1912            se.printStackTrace(System.out);
1913            se = se.getNextException();
1914        }
1915    }
1916
1917    // lifted from the metadata test
1918 private static void dumpRS(ResultSet s) throws SQLException
1919    {
1920        if (s == null)
1921        {
1922            System.out.println("<NULL>");
1923            return;
1924        }
1925
1926        ResultSetMetaData rsmd = s.getMetaData();
1927
1928        // Get the number of columns in the result set
1929 int numCols = rsmd.getColumnCount();
1930
1931        if (numCols <= 0)
1932        {
1933            System.out.println("(no columns!)");
1934            return;
1935        }
1936
1937        StringBuffer heading = new StringBuffer ("\t ");
1938        StringBuffer underline = new StringBuffer ("\t ");
1939
1940        int len;
1941        // Display column headings
1942 for (int i=1; i<=numCols; i++)
1943        {
1944            if (i > 1)
1945            {
1946                heading.append(",");
1947                underline.append(" ");
1948            }
1949            len = heading.length();
1950            heading.append(rsmd.getColumnLabel(i));
1951            len = heading.length() - len;
1952            for (int j = len; j > 0; j--)
1953            {
1954                underline.append("-");
1955            }
1956        }
1957        System.out.println(heading.toString());
1958        System.out.println(underline.toString());
1959        
1960    
1961        StringBuffer row = new StringBuffer ();
1962        // Display data, fetching until end of the result set
1963 while (s.next())
1964        {
1965            row.append("\t{");
1966            // Loop through each column, getting the
1967 // column data and displaying
1968 for (int i=1; i<=numCols; i++)
1969            {
1970                if (i > 1) row.append(",");
1971                row.append(s.getString(i));
1972            }
1973            row.append("}\n");
1974        }
1975        System.out.println(row.toString());
1976        s.close();
1977    }
1978}
1979

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags