KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJavaJDBC30
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 JavaDoc;
25 import java.sql.PreparedStatement JavaDoc;
26 import java.sql.ResultSet JavaDoc;
27 import java.sql.ResultSetMetaData JavaDoc;
28 import java.sql.Savepoint JavaDoc;
29 import java.sql.Statement JavaDoc;
30 import java.sql.SQLException JavaDoc;
31
32 import javax.sql.ConnectionPoolDataSource JavaDoc;
33 import javax.sql.PooledConnection JavaDoc;
34
35 import org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource;
36
37 import org.apache.derby.tools.ij;
38 import org.apache.derby.tools.JDBCDisplayUtil;
39 import org.apache.derbyTesting.functionTests.util.TestUtil;
40 /**
41  * Test for declared global temporary tables (introduced in Cloudscape 5.2) and pooled connection close and jdbc 3.0 specific features
42  * The jdbc3.0 specific featuers are holdable cursors, savepoints.
43  * The rest of the temp table test are in declareGlobalTempTableJava class. The reason for a different test
44  * class is that the holdability and savepoint support is under jdk14 and higher. But we want to be able to run the non-holdable
45  * tests under all the jdks we support and hence splitting the tests into two separate tests. Also, the reason for pooled connection close
46  * is because DRDA doesn't yet have support for pooled connection and hence can't pull this test into other temp table test which runs under
47  * both DRDA and plain Cloudscape.
48  */
49
50
51 public class declareGlobalTempTableJavaJDBC30 {
52
53     static private boolean isDerbyNet = false;
54
55     /*
56     ** There is a small description prior to each sub-test describing what is being tested.
57     */
58     public static void main(String JavaDoc[] args) {
59         boolean passed = true;
60
61         Connection JavaDoc con = null;
62         Statement JavaDoc s = null;
63
64         /* Run all parts of this test, and catch any exceptions */
65         try {
66             System.out.println("Test declaredGlobalTempTableJava starting");
67
68             /* Load the JDBC Driver class */
69             // use the ij utility to read the property file and
70 // make the initial connection.
71
72             ij.getPropertyArg(args);
73             con = ij.startJBMS();
74             isDerbyNet = TestUtil.isNetFramework();
75
76             con.setAutoCommit(false);
77             s = con.createStatement();
78
79             /* Test temp tables with holdable cursors and with ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS */
80             /* Test temp tables rollback behavior in combination with savepoints */
81             passed = testHoldableCursorsAndSavepoints(con, s) && passed;
82
83             /* Test pooled connection close behavior */
84             passed = testPooledConnectionClose() && passed;
85
86             con.close();
87
88         } catch (Throwable JavaDoc e) {
89             System.out.println("FAIL -- unexpected exception "+e);
90             JDBCDisplayUtil.ShowException(System.out, e);
91             e.printStackTrace();
92             passed = false;
93         }
94
95         if (passed)
96             System.out.println("PASS");
97
98         System.out.println("Test declaredGlobalTempTable finished");
99     }
100
101     /**
102      * Test temp tables with holdable cursors and with ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS
103      * Test temp tables rollback behavior in combination with savepoints
104      *
105      * @param conn The Connection
106      * @param s A Statement on the Connection
107      *
108      * @return true if it succeeds, false if it doesn't
109      *
110      * @exception SQLException Thrown if some unexpected error happens
111      */
112
113     static boolean testHoldableCursorsAndSavepoints(Connection JavaDoc con, Statement JavaDoc s)
114                     throws SQLException JavaDoc {
115         boolean passed = true;
116
117         try
118         {
119             System.out.println("TEST1 : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors");
120                      
121             System.out.println("Temp table t1 with held open cursors on it. Data should be preserved in t1 at commit time");
122       //create a statement with hold cursors over commit
123 Statement JavaDoc s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
124       ResultSet.HOLD_CURSORS_OVER_COMMIT );
125             s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
126             s1.executeUpdate("insert into session.t1 values(11, 1)");
127             s1.executeUpdate("insert into session.t1 values(12, 2)");
128             ResultSet JavaDoc rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
129 dumpRS(rs1);
130
131             rs1 = s1.executeQuery("select * from SESSION.t1"); //hold cursor open on t1. Commit should preserve the rows
132 rs1.next();
133
134             System.out.println("Temp tables t2 & t3 with one held open cursor on them together. Data should be preserved in t2 & t3 at commit time");
135       Statement JavaDoc s2 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
136       ResultSet.HOLD_CURSORS_OVER_COMMIT );
137             s2.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
138             s2.executeUpdate("insert into session.t2 values(21, 1)");
139             s2.executeUpdate("insert into session.t2 values(22, 2)");
140             ResultSet JavaDoc rs23 = s2.executeQuery("select count(*) from SESSION.t2"); //should return count of 2
141 dumpRS(rs23);
142
143             s2.executeUpdate("declare global temporary table SESSION.t3(c31 int, c32 int) on commit delete rows not logged");
144             s2.executeUpdate("insert into session.t3 values(31, 1)");
145             s2.executeUpdate("insert into session.t3 values(32, 2)");
146             rs23 = s2.executeQuery("select count(*) from SESSION.t3"); //should return count of 2
147 dumpRS(rs23);
148
149             rs23 = s2.executeQuery("select * from SESSION.t2, SESSION.t3 where c22=c32"); //hold cursor open on t2 & t3. Commit should preseve the rows
150 rs23.next();
151
152             System.out.println("Temp table t4 with one held cursor but it is closed before commit. Data should be deleted from t4 at commit time");
153       Statement JavaDoc s3 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
154       ResultSet.HOLD_CURSORS_OVER_COMMIT );
155             s3.executeUpdate("declare global temporary table SESSION.t4(c41 int, c42 int) on commit delete rows not logged");
156             s3.executeUpdate("insert into session.t4 values(41, 1)");
157             s3.executeUpdate("insert into session.t4 values(42, 2)");
158             ResultSet JavaDoc rs4 = s3.executeQuery("select count(*) from SESSION.t4"); //should return count of 2
159 dumpRS(rs4);
160
161             rs4 = s3.executeQuery("select * from SESSION.t4"); //hold cursor open on t4 but close it before commit.
162 rs4.next();
163             rs4.close();
164
165             con.commit();
166
167             System.out.println("After commit, verify all the 4 tables");
168
169             System.out.println("Temp table t1 will have the data intact after commit");
170             rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
171 dumpRS(rs1);
172
173             System.out.println("Temp table t2 will have the data intact after commit");
174             rs23 = s2.executeQuery("select count(*) from SESSION.t2"); //should return count of 2
175 dumpRS(rs23);
176
177             System.out.println("Temp table t3 will have the data intact after commit");
178             rs23 = s2.executeQuery("select count(*) from SESSION.t3"); //should return count of 2
179 dumpRS(rs23);
180
181             System.out.println("Temp table t4 will have no data after commit");
182             rs4 = s3.executeQuery("select count(*) from SESSION.t4"); //should return count of 0
183 dumpRS(rs4);
184
185             s.executeUpdate("drop table SESSION.t1");
186             s.executeUpdate("drop table SESSION.t2");
187             s.executeUpdate("drop table SESSION.t3");
188             s.executeUpdate("drop table SESSION.t4");
189
190             con.commit();
191             System.out.println("TEST1 PASSED");
192         } catch (Throwable JavaDoc e)
193         {
194             System.out.println("Unexpected message: "+ e.getMessage());
195             e.printStackTrace(System.out);
196             con.rollback();
197             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
198 System.out.println("TEST1 FAILED");
199         }
200
201         try
202         {
203             System.out.println("TEST1a : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors on prepared statement");
204
205             System.out.println("Temp table t1 with held open cursors on it. Data should be preserved in t1 at commit time");
206             Statement JavaDoc s1 = con.createStatement();
207             s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
208             s1.executeUpdate("insert into session.t1 values(11, 1)");
209             s1.executeUpdate("insert into session.t1 values(12, 2)");
210       
211             //create a prepared statement with hold cursors over commit
212 PreparedStatement JavaDoc ps1 = con.prepareStatement("select count(*) from SESSION.t1",
213             ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
214             ResultSet JavaDoc rs1 = ps1.executeQuery(); //should return count of 2
215 dumpRS(rs1);
216
217             PreparedStatement JavaDoc ps2 = con.prepareStatement("select * from SESSION.t1",
218             ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
219             ResultSet JavaDoc rs11 = ps2.executeQuery(); //hold cursor open on t1. Commit should preserve the rows
220 rs11.next(); //notice that we didn't close rs11 with hold cursor on commit
221
222             System.out.println("Temp table t2 with one held cursor but it is closed before commit. Data should be deleted from t2 at commit time");
223             s1.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit delete rows not logged");
224             s1.executeUpdate("insert into session.t2 values(21, 1)");
225             s1.executeUpdate("insert into session.t2 values(22, 2)");
226       
227             //create a prepared statement with hold cursors over commit
228 PreparedStatement JavaDoc ps3 = con.prepareStatement("select count(*) from SESSION.t2",
229             ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
230             ResultSet JavaDoc rs2 = ps3.executeQuery(); //should return count of 2
231 dumpRS(rs2);
232
233             PreparedStatement JavaDoc ps4 = con.prepareStatement("select * from SESSION.t2",
234             ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
235             rs2 = ps4.executeQuery(); //hold cursor open on t2 but close it before commit.
236 rs2.next();
237             rs2.close();
238
239             con.commit();
240
241             System.out.println("After commit, verify both the tables");
242
243             System.out.println("Temp table t1 will have the data intact after commit");
244             rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
245 dumpRS(rs1);
246             //Need to close the held cursor on t1 before t1 can be dropped
247 rs11.close();
248
249             System.out.println("Temp table t2 will have no data after commit");
250             rs2 = s1.executeQuery("select count(*) from SESSION.t2"); //should return count of 0
251 dumpRS(rs2);
252
253             s.executeUpdate("drop table SESSION.t1");
254             s.executeUpdate("drop table SESSION.t2");
255
256             con.commit();
257             System.out.println("TEST1a PASSED");
258         } catch (Throwable JavaDoc e)
259         {
260             System.out.println("Unexpected message: "+ e.getMessage());
261             con.rollback();
262             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
263 System.out.println("TEST1a FAILED");
264         }
265
266         try
267         {
268             System.out.println("TEST2 : Declare a temporary table with ON COMMIT PRESERVE ROWS and various combinations of holdability");
269
270             System.out.println("Temp table t1 with held open cursors on it. Data should be preserved, holdability shouldn't matter");
271       //create a statement with hold cursors over commit
272 Statement JavaDoc s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
273       ResultSet.HOLD_CURSORS_OVER_COMMIT );
274             s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
275             s1.executeUpdate("insert into session.t1 values(11, 1)");
276             s1.executeUpdate("insert into session.t1 values(12, 2)");
277             ResultSet JavaDoc rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
278 dumpRS(rs1);
279
280             rs1 = s1.executeQuery("select * from SESSION.t1"); //hold cursor open on t1.
281 rs1.next();
282
283             con.commit();
284
285             System.out.println("After commit, verify the table");
286
287             System.out.println("Temp table t1 will have data after commit");
288             rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
289 dumpRS(rs1);
290
291             s.executeUpdate("drop table SESSION.t1");
292             con.commit();
293             System.out.println("TEST2 PASSED");
294         } catch (Throwable JavaDoc e)
295         {
296             System.out.println("Unexpected message: "+ e.getMessage());
297             con.rollback();
298             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
299 System.out.println("TEST2 FAILED");
300         }
301
302         try
303         {
304             System.out.println("TEST3A : Savepoint and Rollback behavior");
305
306             System.out.println(" In the transaction:");
307             System.out.println(" Create savepoint1 and declare temp table t1");
308             Savepoint JavaDoc savepoint1 = con.setSavepoint();
309             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
310             PreparedStatement JavaDoc pStmt = con.prepareStatement("insert into SESSION.t1 values (?, ?)");
311       pStmt.setInt(1, 11);
312       pStmt.setInt(2, 1);
313       pStmt.execute();
314             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
315             dumpRS(rs1);
316
317             System.out.println(" Create savepoint 2, drop temp table t1, rollback savepoint 2");
318             Savepoint JavaDoc savepoint2 = con.setSavepoint();
319             s.executeUpdate("drop table SESSION.t1");
320             try {
321                 rs1 = s.executeQuery("select * from SESSION.t1");
322             } catch (Throwable JavaDoc e)
323             {
324                 System.out.println("Expected message: "+ e.getMessage());
325             }
326       con.rollback(savepoint2);
327
328             System.out.println(" select should pass, rollback savepoint 1, select should fail");
329             rs1 = s.executeQuery("select * from SESSION.t1");
330             dumpRS(rs1);
331       con.rollback(savepoint1);
332             rs1 = s.executeQuery("select * from SESSION.t1");
333
334             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
335 System.out.println("TEST3A FAILED");
336         } catch (Throwable JavaDoc e)
337         {
338             System.out.println("Expected message: "+ e.getMessage());
339             con.commit();
340             System.out.println("TEST3A PASSED");
341         }
342
343         try
344         {
345             System.out.println("TEST3B : Savepoint and Rollback behavior");
346
347             System.out.println(" In the transaction:");
348             System.out.println(" Create savepoint1 and declare temp table t1");
349             Savepoint JavaDoc savepoint1 = con.setSavepoint();
350             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
351
352             System.out.println(" Create savepoint2 and declare temp table t2");
353             Savepoint JavaDoc savepoint2 = con.setSavepoint();
354             s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
355
356             System.out.println(" Release savepoint 1 and select from temp table t1 & t2");
357       con.releaseSavepoint(savepoint1);
358             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
359             dumpRS(rs1);
360             rs1 = s.executeQuery("select * from SESSION.t2");
361             dumpRS(rs1);
362
363             System.out.println(" Drop temp table t2(explicit drop), rollback transaction(implicit drop of t1)");
364             s.executeUpdate("drop table SESSION.t2");
365       con.rollback();
366
367             System.out.println(" Select from temp table t1 and t2 will fail");
368             try {
369                 rs1 = s.executeQuery("select * from SESSION.t1");
370             } catch (Throwable JavaDoc e)
371             {
372                 System.out.println("Expected message: "+ e.getMessage());
373             }
374             try {
375                 rs1 = s.executeQuery("select * from SESSION.t2");
376             } catch (Throwable JavaDoc e)
377             {
378                 System.out.println("Expected message: "+ e.getMessage());
379             }
380             con.commit();
381             System.out.println("TEST3B PASSED");
382         } catch (Throwable JavaDoc e)
383         {
384             System.out.println("Unexpected message: "+ e.getMessage());
385       con.rollback();
386             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
387 System.out.println("TEST3B FAILED");
388         }
389
390         try
391         {
392             System.out.println("TEST3C : Savepoint and Rollback behavior");
393
394             System.out.println(" In the transaction:");
395             System.out.println(" Create savepoint1 and declare temp table t1");
396             Savepoint JavaDoc savepoint1 = con.setSavepoint();
397             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
398
399             System.out.println(" Create savepoint2 and declare temp table t2");
400             Savepoint JavaDoc savepoint2 = con.setSavepoint();
401             s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
402
403             System.out.println(" Release savepoint 1 and select from temp table t1 and t2");
404       con.releaseSavepoint(savepoint1);
405             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
406             dumpRS(rs1);
407             rs1 = s.executeQuery("select * from SESSION.t2");
408             dumpRS(rs1);
409
410             System.out.println(" Create savepoint3 and rollback savepoint3(should not touch t1 and t2)");
411             Savepoint JavaDoc savepoint3 = con.setSavepoint();
412       con.rollback(savepoint3);
413
414             System.out.println(" select from temp tables t1 and t2 should pass");
415             rs1 = s.executeQuery("select * from SESSION.t1");
416             dumpRS(rs1);
417             rs1 = s.executeQuery("select * from SESSION.t2");
418             dumpRS(rs1);
419
420             System.out.println(" Rollback transaction and select from temp tables t1 and t2 should fail");
421       con.rollback();
422             try {
423                 rs1 = s.executeQuery("select * from SESSION.t1");
424             } catch (Throwable JavaDoc e)
425             {
426                 System.out.println("Expected message: "+ e.getMessage());
427             }
428             try {
429                 rs1 = s.executeQuery("select * from SESSION.t2");
430             } catch (Throwable JavaDoc e)
431             {
432                 System.out.println("Expected message: "+ e.getMessage());
433             }
434
435             con.commit();
436             System.out.println("TEST3C PASSED");
437         } catch (Throwable JavaDoc e)
438         {
439             System.out.println("Unexpected message: "+ e.getMessage());
440       con.rollback();
441             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
442 System.out.println("TEST3C FAILED");
443         }
444
445         try
446         {
447             System.out.println("TEST3D : Savepoint and Rollback behavior");
448
449             System.out.println(" In the transaction:");
450             System.out.println(" Create savepoint1 and declare temp table t1");
451             Savepoint JavaDoc savepoint1 = con.setSavepoint();
452             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
453
454             System.out.println(" Create savepoint2 and drop temp table t1");
455             Savepoint JavaDoc savepoint2 = con.setSavepoint();
456             s.executeUpdate("drop table SESSION.t1");
457
458             System.out.println(" Rollback savepoint2 and select temp table t1");
459       con.rollback(savepoint2);
460             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
461             dumpRS(rs1);
462
463             System.out.println(" Commit transaction and select temp table t1");
464             con.commit();
465             rs1 = s.executeQuery("select * from SESSION.t1");
466             dumpRS(rs1);
467
468             s.executeUpdate("drop table SESSION.t1");
469             con.commit();
470             System.out.println("TEST3D PASSED");
471         } catch (Throwable JavaDoc e)
472         {
473             System.out.println("Unexpected message: "+ e.getMessage());
474             con.rollback();
475             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
476 System.out.println("TEST3D FAILED");
477         }
478
479         try
480         {
481             System.out.println("TEST3E : Savepoint and Rollback behavior");
482
483             System.out.println(" In the transaction:");
484             System.out.println(" Create savepoint1 and declare temp table t1");
485             Savepoint JavaDoc savepoint1 = con.setSavepoint();
486             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
487
488             System.out.println(" Create savepoint2 and drop temp table t1");
489             Savepoint JavaDoc savepoint2 = con.setSavepoint();
490             s.executeUpdate("drop table SESSION.t1");
491
492             System.out.println(" Rollback savepoint 1 and select from temp table t1 should fail");
493       con.rollback(savepoint1);
494             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
495
496             con.rollback();
497             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
498 System.out.println("TEST3E FAILED");
499         } catch (Throwable JavaDoc e)
500         {
501             System.out.println("Expected message: "+ e.getMessage());
502             con.commit();
503             System.out.println("TEST3E PASSED");
504         }
505
506         try
507         {
508             System.out.println("TEST3F : Savepoint and Rollback behavior");
509
510             System.out.println(" In the transaction:");
511             System.out.println(" declare temp table t1 and drop temp table t1");
512             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
513             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
514             dumpRS(rs1);
515             s.executeUpdate("drop table SESSION.t1");
516             System.out.println(" rollback, select on t1 should fail");
517             con.rollback();
518             rs1 = s.executeQuery("select * from SESSION.t1");
519
520             con.rollback();
521             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
522 System.out.println("TEST3F FAILED");
523         } catch (Throwable JavaDoc e)
524         {
525             System.out.println("Expected message: "+ e.getMessage());
526             con.commit();
527             System.out.println("TEST3F PASSED");
528         }
529
530         try
531         {
532             System.out.println("TEST3G : Savepoint and Rollback behavior");
533
534             System.out.println(" In the transaction:");
535             System.out.println(" declare temp table t1 and commit");
536             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
537             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
538             dumpRS(rs1);
539             con.commit();
540             System.out.println(" In the transaction:");
541             System.out.println(" drop temp table t1 and rollback, select on t1 should pass");
542             s.executeUpdate("drop table SESSION.t1");
543             con.rollback();
544             rs1 = s.executeQuery("select * from SESSION.t1");
545             dumpRS(rs1);
546
547             s.executeUpdate("drop table SESSION.t1");
548             con.commit();
549             System.out.println("TEST3G PASSED");
550         } catch (Throwable JavaDoc e)
551         {
552             System.out.println("Unexpected message: "+ e.getMessage());
553             con.rollback();
554             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
555 System.out.println("TEST3G FAILED");
556         }
557
558         try
559         {
560             System.out.println("TEST3H : Savepoint and commit behavior");
561
562             System.out.println(" In the transaction:");
563             System.out.println(" declare temp table t1 and commit");
564             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
565             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
566             dumpRS(rs1);
567             con.commit();
568             System.out.println(" In the transaction:");
569             System.out.println(" drop temp table t1 and commit, select on t1 should fail");
570             s.executeUpdate("drop table SESSION.t1");
571             con.commit();
572             rs1 = s.executeQuery("select * from SESSION.t1");
573
574             con.rollback();
575             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
576 System.out.println("TEST3H FAILED");
577         } catch (Throwable JavaDoc e)
578         {
579             System.out.println("Expected message: "+ e.getMessage());
580             con.commit();
581             System.out.println("TEST3H PASSED");
582         }
583
584         try
585         {
586             System.out.println("TEST3I : Savepoint and Rollback behavior");
587
588             System.out.println(" In the transaction:");
589             System.out.println(" declare temp table t1 and rollback");
590             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
591             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
592             dumpRS(rs1);
593             con.rollback();
594             rs1 = s.executeQuery("select * from SESSION.t1");
595
596             con.rollback();
597             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
598 System.out.println("TEST3I FAILED");
599         } catch (Throwable JavaDoc e)
600         {
601             System.out.println("Expected message: "+ e.getMessage());
602             con.commit();
603             System.out.println("TEST3I PASSED");
604         }
605
606         try
607         {
608             System.out.println("TEST3J : Savepoint and Rollback behavior");
609
610             System.out.println(" In the transaction:");
611             System.out.println(" declare temp table t1 with 2 columns and commit");
612             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
613             s.executeUpdate("insert into SESSION.t1 values(11, 11)");
614             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
615             dumpRS(rs1);
616             con.commit();
617             System.out.println(" Create savepoint1 and drop temp table t1 with 2 columns");
618             Savepoint JavaDoc savepoint1 = con.setSavepoint();
619             s.executeUpdate("drop table SESSION.t1");
620             System.out.println(" declare temp table t1 but this time with 3 columns");
621             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int, c13 int not null) on commit preserve rows not logged");
622             s.executeUpdate("insert into SESSION.t1 values(22, 22, 22)");
623             rs1 = s.executeQuery("select * from SESSION.t1");
624             dumpRS(rs1);
625             System.out.println(" Create savepoint2 and drop temp table t1 with 3 columns");
626             Savepoint JavaDoc savepoint2 = con.setSavepoint();
627             s.executeUpdate("drop table SESSION.t1");
628             con.rollback();
629             System.out.println(" select from temp table t1 here should have 2 columns");
630             rs1 = s.executeQuery("select * from SESSION.t1");
631             dumpRS(rs1);
632             s.executeUpdate("drop table SESSION.t1");
633
634             con.commit();
635             System.out.println("TEST3J PASSED");
636         } catch (Throwable JavaDoc e)
637         {
638             System.out.println("Unexpected message: "+ e.getMessage());
639             con.rollback();
640             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
641 System.out.println("TEST3J FAILED");
642         }
643
644         try
645         {
646             System.out.println("TEST3K : Savepoint and Rollback behavior");
647
648             System.out.println(" In the transaction:");
649             System.out.println(" declare temp table t1 & t2, insert few rows and commit");
650             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
651             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
652             s.executeUpdate("insert into SESSION.t1 values(11, 1)");
653             s.executeUpdate("insert into session.t2 values(21, 1)");
654             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
655             dumpRS(rs1);
656             rs1 = s.executeQuery("select * from SESSION.t2");
657             dumpRS(rs1);
658             con.commit();
659
660             System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
661             s.executeUpdate("insert into SESSION.t1 values(12, 2)");
662             s.executeUpdate("insert into SESSION.t2 values(22, 2)");
663             rs1 = s.executeQuery("select * from SESSION.t1");
664             dumpRS(rs1);
665             rs1 = s.executeQuery("select * from SESSION.t2");
666             dumpRS(rs1);
667
668             System.out.println(" Create savepoint1 and update some rows in t1 and inspect the data");
669             Savepoint JavaDoc savepoint1 = con.setSavepoint();
670             s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
671             rs1 = s.executeQuery("select * from SESSION.t1");
672             dumpRS(rs1);
673
674             System.out.println(" update t2 with where clause such that no rows get modified in t2 and inspect the data");
675             s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>2");
676             rs1 = s.executeQuery("select * from SESSION.t2");
677             dumpRS(rs1);
678
679             System.out.println(" Rollback to savepoint1 and we should loose all the rows in t1");
680             con.rollback(savepoint1);
681             rs1 = s.executeQuery("select * from SESSION.t1");
682             dumpRS(rs1);
683             System.out.println(" temp table t2 should also have no rows because attempt was made to modify it (even though nothing actually got modified in t2 in the savepoint)");
684             rs1 = s.executeQuery("select * from SESSION.t2");
685             dumpRS(rs1);
686
687             System.out.println(" Commit the transaction and should see no data in t1 and t2");
688             con.commit();
689             rs1 = s.executeQuery("select * from SESSION.t1");
690             dumpRS(rs1);
691             rs1 = s.executeQuery("select * from SESSION.t2");
692             dumpRS(rs1);
693
694             s.executeUpdate("drop table SESSION.t1");
695             s.executeUpdate("drop table SESSION.t2");
696             con.commit();
697             System.out.println("TEST3K PASSED");
698         } catch (Throwable JavaDoc e)
699         {
700             System.out.println("Unexpected message: "+ e.getMessage());
701             con.rollback();
702             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
703 System.out.println("TEST3K FAILED");
704         }
705
706         try
707         {
708             System.out.println("TEST3L : Savepoint and Rollback behavior");
709
710             System.out.println(" In the transaction:");
711             System.out.println(" declare temp table t1 & t2, insert few rows and commit");
712             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
713             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
714             s.executeUpdate("insert into SESSION.t1 values(11, 1)");
715             s.executeUpdate("insert into session.t2 values(21, 1)");
716             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
717             dumpRS(rs1);
718             rs1 = s.executeQuery("select * from SESSION.t2");
719             dumpRS(rs1);
720             con.commit();
721
722             System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
723             s.executeUpdate("insert into SESSION.t1 values(12, 2)");
724             s.executeUpdate("insert into session.t2 values(22, 2)");
725             rs1 = s.executeQuery("select * from SESSION.t1");
726             dumpRS(rs1);
727             rs1 = s.executeQuery("select * from SESSION.t2");
728             dumpRS(rs1);
729
730             System.out.println(" Create savepoint1 and update some rows in t1 and inspect the data");
731             Savepoint JavaDoc savepoint1 = con.setSavepoint();
732             s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
733             rs1 = s.executeQuery("select * from SESSION.t1");
734             dumpRS(rs1);
735
736             System.out.println(" update t2 with where clause such that no rows get modified in t2 and inspect the data");
737             s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>3");
738             rs1 = s.executeQuery("select * from SESSION.t2");
739             dumpRS(rs1);
740
741             System.out.println(" Rollback to savepoint1 and we should loose all the rows in t1");
742             con.rollback(savepoint1);
743             rs1 = s.executeQuery("select * from SESSION.t1");
744             dumpRS(rs1);
745             System.out.println(" temp table t2 should also have no rows because attempt was made to modfiy it (even though nothing actually got modified in t2 in the savepoint)");
746             rs1 = s.executeQuery("select * from SESSION.t2");
747             dumpRS(rs1);
748
749             System.out.println(" Rollback the transaction and should see no data in t1 and t2");
750             con.rollback();
751             rs1 = s.executeQuery("select * from SESSION.t1");
752             dumpRS(rs1);
753             rs1 = s.executeQuery("select * from SESSION.t2");
754             dumpRS(rs1);
755
756             s.executeUpdate("drop table SESSION.t1");
757             s.executeUpdate("drop table SESSION.t2");
758             con.commit();
759             System.out.println("TEST3L PASSED");
760         } catch (Throwable JavaDoc e)
761         {
762             System.out.println("Unexpected message: "+ e.getMessage());
763             con.rollback();
764             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
765 System.out.println("TEST3L FAILED");
766         }
767
768         try
769         {
770             System.out.println("TEST3M : Savepoint and Rollback behavior");
771
772             System.out.println(" In the transaction:");
773             System.out.println(" declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
774             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
775             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
776             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
777             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
778             s.executeUpdate("insert into SESSION.t1 values(11, 1)");
779             s.executeUpdate("insert into SESSION.t2 values(21, 1)");
780             s.executeUpdate("insert into SESSION.t3 values(31, 1)");
781             s.executeUpdate("insert into SESSION.t4 values(41, 1)");
782             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
783             dumpRS(rs1);
784             rs1 = s.executeQuery("select * from SESSION.t2");
785             dumpRS(rs1);
786             rs1 = s.executeQuery("select * from SESSION.t3");
787             dumpRS(rs1);
788             rs1 = s.executeQuery("select * from SESSION.t4");
789             dumpRS(rs1);
790             con.commit();
791
792             System.out.println(" In the next transaction, insert couple more rows in t1 & t2 & t3 and ");
793             s.executeUpdate("insert into SESSION.t1 values(12, 2)");
794             s.executeUpdate("insert into session.t2 values(22, 2)");
795             s.executeUpdate("insert into session.t3 values(32, 2)");
796             rs1 = s.executeQuery("select * from SESSION.t1");
797             dumpRS(rs1);
798             rs1 = s.executeQuery("select * from SESSION.t2");
799             dumpRS(rs1);
800             rs1 = s.executeQuery("select * from SESSION.t3");
801             dumpRS(rs1);
802
803             System.out.println(" Create savepoint1 and delete some rows from t1 and inspect the data in t1");
804             Savepoint JavaDoc savepoint1 = con.setSavepoint();
805             s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
806             rs1 = s.executeQuery("select * from SESSION.t1");
807             dumpRS(rs1);
808
809             System.out.println(" Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
810             Savepoint JavaDoc savepoint2 = con.setSavepoint();
811             s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
812             rs1 = s.executeQuery("select * from SESSION.t2");
813             dumpRS(rs1);
814
815             System.out.println(" Release savepoint2 and now savepoint1 should keep track of changes made to t1 and t2, inspect the data in t1 & t2");
816             con.releaseSavepoint(savepoint2);
817             rs1 = s.executeQuery("select * from SESSION.t1");
818             dumpRS(rs1);
819             rs1 = s.executeQuery("select * from SESSION.t2");
820             dumpRS(rs1);
821
822             System.out.println(" Rollback savepoint1 and should see no data in t1 and t2, inspect the data");
823             con.rollback(savepoint1);
824             rs1 = s.executeQuery("select * from SESSION.t1");
825             dumpRS(rs1);
826             rs1 = s.executeQuery("select * from SESSION.t2");
827             dumpRS(rs1);
828
829             System.out.println(" Should see data in t3 since it was not touched in the savepoint that was rolled back");
830             rs1 = s.executeQuery("select * from SESSION.t3");
831             dumpRS(rs1);
832
833             System.out.println(" Rollback the transaction and should see no data in t1 and t2 and t3");
834             con.rollback();
835             rs1 = s.executeQuery("select * from SESSION.t1");
836             dumpRS(rs1);
837             rs1 = s.executeQuery("select * from SESSION.t2");
838             dumpRS(rs1);
839             rs1 = s.executeQuery("select * from SESSION.t3");
840             dumpRS(rs1);
841
842             System.out.println(" Should see data in t4 since it was not touched in the transaction that was rolled back");
843             rs1 = s.executeQuery("select * from SESSION.t4");
844             dumpRS(rs1);
845
846             s.executeUpdate("drop table SESSION.t1");
847             s.executeUpdate("drop table SESSION.t2");
848             s.executeUpdate("drop table SESSION.t3");
849             s.executeUpdate("drop table SESSION.t4");
850             con.commit();
851             System.out.println("TEST3M PASSED");
852         } catch (Throwable JavaDoc e)
853         {
854             System.out.println("Unexpected message: "+ e.getMessage());
855             con.rollback();
856             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
857 System.out.println("TEST3M FAILED");
858         }
859
860         try
861         {
862             System.out.println("TEST3N : Savepoint and Rollback behavior");
863
864             System.out.println(" In the transaction:");
865             System.out.println(" declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
866             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
867             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
868             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
869             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
870             s.executeUpdate("insert into SESSION.t1 values(11, 1)");
871             s.executeUpdate("insert into SESSION.t1 values(12, 2)");
872             s.executeUpdate("insert into SESSION.t2 values(21, 1)");
873             s.executeUpdate("insert into SESSION.t2 values(22, 2)");
874             s.executeUpdate("insert into SESSION.t3 values(31, 1)");
875             s.executeUpdate("insert into SESSION.t4 values(41, 1)");
876             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
877             dumpRS(rs1);
878             rs1 = s.executeQuery("select * from SESSION.t2");
879             dumpRS(rs1);
880             rs1 = s.executeQuery("select * from SESSION.t3");
881             dumpRS(rs1);
882             rs1 = s.executeQuery("select * from SESSION.t4");
883             dumpRS(rs1);
884             con.commit();
885
886             System.out.println(" In the next transaction, insert couple more rows in t3 ");
887             s.executeUpdate("insert into SESSION.t3 values(31, 2)");
888             rs1 = s.executeQuery("select * from SESSION.t3");
889             dumpRS(rs1);
890
891             System.out.println(" Create savepoint1 and delete some rows from t1 and inspect the data in t1");
892             Savepoint JavaDoc savepoint1 = con.setSavepoint();
893             s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
894             rs1 = s.executeQuery("select * from SESSION.t1");
895             dumpRS(rs1);
896
897             System.out.println(" delete from t2 with where clause such that no rows are deleted from t2 and inspect the data in t2");
898             s.executeUpdate("DELETE FROM SESSION.t2 where c22>3");
899             rs1 = s.executeQuery("select * from SESSION.t2");
900             dumpRS(rs1);
901
902             System.out.println(" Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
903             Savepoint JavaDoc savepoint2 = con.setSavepoint();
904             s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
905             rs1 = s.executeQuery("select * from SESSION.t2");
906             dumpRS(rs1);
907
908             System.out.println(" Rollback the transaction and should see no data in t1 and t2 and t3");
909             con.rollback();
910             rs1 = s.executeQuery("select * from SESSION.t1");
911             dumpRS(rs1);
912             rs1 = s.executeQuery("select * from SESSION.t2");
913             dumpRS(rs1);
914             rs1 = s.executeQuery("select * from SESSION.t3");
915             dumpRS(rs1);
916
917             System.out.println(" Should see data in t4 since it was not touched in the transaction that was rolled back");
918             rs1 = s.executeQuery("select * from SESSION.t4");
919             dumpRS(rs1);
920
921             s.executeUpdate("drop table SESSION.t1");
922             s.executeUpdate("drop table SESSION.t2");
923             s.executeUpdate("drop table SESSION.t3");
924             s.executeUpdate("drop table SESSION.t4");
925             con.commit();
926             System.out.println("TEST3N PASSED");
927         } catch (Throwable JavaDoc e)
928         {
929             System.out.println("Unexpected message: "+ e.getMessage());
930             con.rollback();
931             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
932 System.out.println("TEST3N FAILED");
933         }
934
935         try
936         {
937             System.out.println("TEST3O : Savepoint and Rollback behavior");
938
939             System.out.println(" In the transaction:");
940             System.out.println(" declare temp table t1 & t2, insert few rows and commit");
941             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
942             s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
943             s.executeUpdate("insert into SESSION.t1 values(11, 1)");
944             s.executeUpdate("insert into SESSION.t2 values(21, 1)");
945             ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
946             dumpRS(rs1);
947             rs1 = s.executeQuery("select * from SESSION.t2");
948             dumpRS(rs1);
949             con.commit();
950
951             System.out.println(" In the next transaction, insert couple more rows in t1 ");
952             s.executeUpdate("insert into SESSION.t1 values(12, 2)");
953             rs1 = s.executeQuery("select * from SESSION.t1");
954             dumpRS(rs1);
955
956             System.out.println(" Create savepoint1 and insert one row in t2 and inspect the data in t2");
957             Savepoint JavaDoc savepoint1 = con.setSavepoint();
958             s.executeUpdate("insert into SESSION.t2 values(22, 2)");
959             rs1 = s.executeQuery("select * from SESSION.t2");
960             dumpRS(rs1);
961
962             System.out.println(" Rollback savepoint1 and should see no data in t2 but t1 should have data, inspect the data");
963             con.rollback(savepoint1);
964             rs1 = s.executeQuery("select * from SESSION.t1");
965             dumpRS(rs1);
966             rs1 = s.executeQuery("select * from SESSION.t2");
967             dumpRS(rs1);
968
969             System.out.println(" Commit the transaction and should see no data in t2 but t1 should have data");
970             con.commit();
971             rs1 = s.executeQuery("select * from SESSION.t1");
972             dumpRS(rs1);
973             rs1 = s.executeQuery("select * from SESSION.t2");
974             dumpRS(rs1);
975
976             s.executeUpdate("drop table SESSION.t1");
977             s.executeUpdate("drop table SESSION.t2");
978             con.commit();
979             System.out.println("TEST3O PASSED");
980         } catch (Throwable JavaDoc e)
981         {
982             System.out.println("Unexpected message: "+ e.getMessage());
983             con.rollback();
984             passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
985 System.out.println("TEST3O FAILED");
986         }
987
988         try
989         {
990             System.out.println("TEST3P : Savepoint and Rollback behavior");
991
992             System.out.println(" In the transaction:");
993             System.out.println(" declare temp table t1, insert few rows and commit");
994             s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
995             s.executeUpdate("insert into SESSION.t1 values(11, 1)");
996             s.executeUpdate("insert into SESSION.t1 values(12, 2)");
997             con.commit();
998
999             System.out.println(" In the transaction:");
1000            System.out.println(" Create savepoint1 and insert some rows into t1 and inspect the data in t1");
1001            Savepoint JavaDoc savepoint1 = con.setSavepoint();
1002            s.executeUpdate("insert into SESSION.t1 values(13, 3)");
1003
1004            System.out.println(" Release savepoint1 and now transaction should keep track of changes made to t1, inspect the data in t1");
1005            con.releaseSavepoint(savepoint1);
1006            ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1");
1007            dumpRS(rs1);
1008
1009            System.out.println(" Rollback the transaction and should still see no data in t1");
1010            con.rollback();
1011            rs1 = s.executeQuery("select * from SESSION.t1");
1012            dumpRS(rs1);
1013
1014            s.executeUpdate("drop table SESSION.t1");
1015            con.commit();
1016            System.out.println("TEST3P PASSED");
1017        } catch (Throwable JavaDoc e)
1018        {
1019            System.out.println("Unexpected message: "+ e.getMessage());
1020            con.rollback();
1021            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1022 System.out.println("TEST3P FAILED");
1023        }
1024
1025        try
1026        {
1027            System.out.println("TEST3Q : Prepared statement test - DML and rollback behavior");
1028            System.out.println(" In the transaction:");
1029            System.out.println(" Declare temp table t2, insert / update / delete data using various prepared statements and commit");
1030            s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
1031            PreparedStatement JavaDoc pStmtInsert = con.prepareStatement("insert into SESSION.t2 values (?, ?)");
1032            pStmtInsert.setInt(1, 21);
1033            pStmtInsert.setInt(2, 1);
1034            pStmtInsert.execute();
1035            pStmtInsert.setInt(1, 22);
1036            pStmtInsert.setInt(2, 2);
1037            pStmtInsert.execute();
1038            pStmtInsert.setInt(1, 23);
1039            pStmtInsert.setInt(2, 2);
1040            pStmtInsert.execute();
1041            PreparedStatement JavaDoc pStmtUpdate = con.prepareStatement("UPDATE SESSION.t2 SET c22 = 3 where c21=?");
1042            pStmtUpdate.setInt(1, 23);
1043            pStmtUpdate.execute();
1044            PreparedStatement JavaDoc pStmtDelete = con.prepareStatement("DELETE FROM SESSION.t2 where c21 = ?");
1045            pStmtDelete.setInt(1, 23);
1046            pStmtDelete.execute();
1047
1048            con.commit();
1049            ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t2");
1050            dumpRS(rs1);
1051
1052            System.out.println(" In the next transaction:");
1053            System.out.println(" Create savepoint1 and insert some rows into t2 using prepared statement and inspect the data in t2");
1054            Savepoint JavaDoc savepoint1 = con.setSavepoint();
1055            pStmtInsert.setInt(1, 23);
1056            pStmtInsert.setInt(2, 2);
1057            pStmtInsert.execute();
1058            rs1 = s.executeQuery("select * from SESSION.t2");
1059            dumpRS(rs1);
1060
1061            System.out.println(" Create savepoint2 and update row inserted in savepoint1 using prepared statement and inspect the data in t2");
1062            Savepoint JavaDoc savepoint2 = con.setSavepoint();
1063            pStmtUpdate.setInt(1, 23);
1064            pStmtUpdate.execute();
1065            rs1 = s.executeQuery("select * from SESSION.t2");
1066            dumpRS(rs1);
1067
1068            System.out.println(" rollback savepoint2 and should loose all the data from t2");
1069            con.rollback(savepoint2);
1070            rs1 = s.executeQuery("select * from SESSION.t2");
1071            dumpRS(rs1);
1072
1073            System.out.println(" Create savepoint3 and insert some rows into t2 using prepared statement and inspect the data in t2");
1074            Savepoint JavaDoc savepoint3 = con.setSavepoint();
1075            pStmtInsert.setInt(1, 21);
1076            pStmtInsert.setInt(2, 1);
1077            pStmtInsert.execute();
1078            pStmtInsert.setInt(1, 22);
1079            pStmtInsert.setInt(2, 2);
1080            pStmtInsert.execute();
1081            pStmtInsert.setInt(1, 23);
1082            pStmtInsert.setInt(2, 333);
1083            pStmtInsert.execute();
1084            rs1 = s.executeQuery("select * from SESSION.t2");
1085            dumpRS(rs1);
1086
1087            System.out.println(" Create savepoint4 and update row inserted in savepoint3 using prepared statement and inspect the data in t2");
1088            Savepoint JavaDoc savepoint4 = con.setSavepoint();
1089            pStmtUpdate.setInt(1, 23);
1090            pStmtUpdate.execute();
1091            rs1 = s.executeQuery("select * from SESSION.t2");
1092            dumpRS(rs1);
1093
1094            System.out.println(" Release savepoint4 and inspect the data in t2, then delete a row from t2");
1095            con.releaseSavepoint(savepoint4);
1096            rs1 = s.executeQuery("select * from SESSION.t2");
1097            dumpRS(rs1);
1098            pStmtDelete.setInt(1, 23);
1099            pStmtDelete.execute();
1100
1101            System.out.println(" Commit transaction and should see data data in t2");
1102            con.commit();
1103            rs1 = s.executeQuery("select * from SESSION.t2");
1104            dumpRS(rs1);
1105
1106            s.executeUpdate("drop table SESSION.t2");
1107            con.commit();
1108            System.out.println("TEST3Q PASSED");
1109        } catch (Throwable JavaDoc e)
1110        {
1111            System.out.println("FAIL " + e.getMessage());
1112            e.printStackTrace(System.out);
1113            con.rollback();
1114            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1115 System.out.println("TEST3Q FAILED");
1116        }
1117
1118        try
1119        {
1120            System.out.println("TEST4 : Test declared temporary table with ON COMMIT DELETE ROWS and holdable cursors and temp table as part of subquery");
1121
1122            System.out.println("Temp table t1 with no direct held cursor open on it. Data should be deleted from t1 at commit time");
1123            Statement JavaDoc s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
1124            ResultSet.HOLD_CURSORS_OVER_COMMIT );
1125            s1.executeUpdate("create table t1(c11 int, c12 int)");
1126            s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
1127            s1.executeUpdate("insert into session.t1 values(11, 1)");
1128            s1.executeUpdate("insert into session.t1 values(12, 2)");
1129            ResultSet JavaDoc rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 2
1130 dumpRS(rs1);
1131            rs1 = s1.executeQuery("select count(*) from t1"); //should return count of 0
1132 dumpRS(rs1);
1133            System.out.println("Insert into real table using temporary table data on a statement with holdability set to true");
1134            s1.executeUpdate("INSERT INTO T1 SELECT * FROM SESSION.T1");
1135            con.commit();
1136
1137            System.out.println("After commit, verify both the tables");
1138
1139            System.out.println("Temp table t1 will have no data after commit");
1140            rs1 = s1.executeQuery("select count(*) from SESSION.t1"); //should return count of 0
1141 dumpRS(rs1);
1142
1143            System.out.println("Physical table t1 will have 2 rows after commit");
1144            rs1 = s1.executeQuery("select count(*) from t1"); //should return count of 2
1145 dumpRS(rs1);
1146
1147            s.executeUpdate("drop table SESSION.t1");
1148            s.executeUpdate("drop table t1");
1149
1150            con.commit();
1151            System.out.println("TEST4 PASSED");
1152        } catch (Throwable JavaDoc e)
1153        {
1154            System.out.println("Unexpected message: "+ e.getMessage());
1155            con.rollback();
1156            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1157 System.out.println("TEST4 FAILED");
1158        }
1159
1160        return passed;
1161    }
1162
1163    /**
1164     * Test that global temporary tables declared in a connection handle to pooled connection are dropped at connection handle close time
1165     * and are not available to next connection handle to the same pooled connection
1166     *
1167     * @param conn The Connection
1168     * @param s A Statement on the Connection
1169     *
1170     * @return true if it succeeds, false if it doesn't
1171     *
1172     * @exception SQLException Thrown if some unexpected error happens
1173     */
1174    static boolean testPooledConnectionClose()
1175                    throws SQLException JavaDoc {
1176        boolean passed = true;
1177        Connection JavaDoc con1 = null, con2 = null;
1178
1179        try
1180        {
1181            System.out.println("TEST5 : Temporary tables declared in a pooled connection should get dropped when that pooled connection is closed");
1182            ConnectionPoolDataSource JavaDoc dsp;
1183            if (isDerbyNet) {
1184            /* following would require the IBM universal jdbc driver to be available during build...This section needs to be reworked for networkserver
1185                com.ibm.db2.jcc.DB2ConnectionPoolDataSource ds = new com.ibm.db2.jcc.DB2ConnectionPoolDataSource();
1186                ds.setDatabaseName("wombat");
1187                ds.setUser("cs");
1188                ds.setPassword("cs");
1189                hostName = TestUtil.getHostName();
1190                ds.setServerName(hostName);
1191                ds.setPortNumber(1527);
1192                ds.setDriverType(4);
1193                dsp = ds;
1194            */
1195                System.out.println("test will not build without universal driver");
1196                return passed;
1197            
1198            } else {
1199                EmbeddedConnectionPoolDataSource dscsp = new EmbeddedConnectionPoolDataSource();
1200                dscsp.setDatabaseName("wombat");
1201                //dscsp.setConnectionAttributes("unicode=true");
1202 dsp = dscsp;
1203            }
1204
1205            PooledConnection JavaDoc pc = dsp.getPooledConnection();
1206            con1 = pc.getConnection();
1207            con1.setAutoCommit(false);
1208            Statement JavaDoc s = con1.createStatement();
1209
1210            System.out.println(" In the first connection handle to the pooled connection, create physical session schema, create table t1 in it");
1211            System.out.println(" Insert some rows in physical SESSION.t1 table. Inspect the data.");
1212            s.executeUpdate("CREATE schema SESSION");
1213            s.executeUpdate("CREATE TABLE SESSION.t1(c21 int)");
1214            s.executeUpdate("insert into SESSION.t1 values(11)");
1215            s.executeUpdate("insert into SESSION.t1 values(12)");
1216            s.executeUpdate("insert into SESSION.t1 values(13)");
1217            ResultSet JavaDoc rs1 = s.executeQuery("select * from SESSION.t1"); //should return 3 rows for the physical table
1218 dumpRS(rs1);
1219
1220            System.out.println(" Next declare a temp table with same name as physical table in SESSION schema.");
1221            System.out.println(" Insert some rows in temporary table SESSION.t1. Inspect the data");
1222            s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
1223            s.executeUpdate("insert into SESSION.t1 values(11,1)");
1224            rs1 = s.executeQuery("select * from SESSION.t1"); //should return 1 row for the temporary table
1225 dumpRS(rs1);
1226            System.out.println(" Now close the connection handle to the pooled connection");
1227            con1.commit();
1228            con1.close();
1229            con1=null;
1230
1231            System.out.println(" Do another getConnection() to get a new connection handle to the pooled connection");
1232            con2 = pc.getConnection();
1233            s = con2.createStatement();
1234            System.out.println(" In this new handle, a select * from SESSION.t1 should be looking at the physical session table");
1235            rs1 = s.executeQuery("select * from SESSION.t1");
1236            dumpRS(rs1);
1237
1238            s.executeUpdate("DROP TABLE SESSION.t1");
1239            if (isDerbyNet)
1240                s.executeUpdate("DROP TABLE SESSION.t1");
1241
1242            s.executeUpdate("DROP schema SESSION restrict");
1243            con2.commit();
1244            con2.close();
1245            System.out.println("TEST5 PASSED");
1246        } catch (Throwable JavaDoc e)
1247        {
1248            System.out.println("Unexpected message: "+ e.getMessage());
1249            if (con1 != null) con1.rollback();
1250            if (con2 != null) con2.rollback();
1251            passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
1252 System.out.println("TEST5 FAILED");
1253        }
1254
1255        return passed;
1256    }
1257
1258    static private void dumpExpectedSQLExceptions (SQLException JavaDoc se) {
1259        System.out.println("PASS -- expected exception");
1260        while (se != null)
1261        {
1262            System.out.println("SQLSTATE("+se.getSQLState()+"): "+se);
1263            se = se.getNextException();
1264        }
1265    }
1266
1267    static private void dumpSQLExceptions (SQLException JavaDoc se) {
1268        System.out.println("FAIL -- unexpected exception");
1269        while (se != null) {
1270            System.out.print("SQLSTATE("+se.getSQLState()+"):");
1271            se.printStackTrace(System.out);
1272            se = se.getNextException();
1273        }
1274    }
1275
1276    // lifted from the metadata test
1277 private static void dumpRS(ResultSet JavaDoc s) throws SQLException JavaDoc
1278    {
1279        if (s == null)
1280        {
1281            System.out.println("<NULL>");
1282            return;
1283        }
1284
1285        ResultSetMetaData JavaDoc rsmd = s.getMetaData();
1286
1287        // Get the number of columns in the result set
1288 int numCols = rsmd.getColumnCount();
1289
1290        if (numCols <= 0)
1291        {
1292            System.out.println("(no columns!)");
1293            return;
1294        }
1295
1296        StringBuffer JavaDoc heading = new StringBuffer JavaDoc("\t ");
1297        StringBuffer JavaDoc underline = new StringBuffer JavaDoc("\t ");
1298
1299        int len;
1300        // Display column headings
1301 for (int i=1; i<=numCols; i++)
1302        {
1303            if (i > 1)
1304            {
1305                heading.append(",");
1306                underline.append(" ");
1307            }
1308            len = heading.length();
1309            heading.append(rsmd.getColumnLabel(i));
1310            len = heading.length() - len;
1311            for (int j = len; j > 0; j--)
1312            {
1313                underline.append("-");
1314            }
1315        }
1316        System.out.println(heading.toString());
1317        System.out.println(underline.toString());
1318        
1319    
1320        StringBuffer JavaDoc row = new StringBuffer JavaDoc();
1321        // Display data, fetching until end of the result set
1322 while (s.next())
1323        {
1324            row.append("\t{");
1325            // Loop through each column, getting the
1326 // column data and displaying
1327 for (int i=1; i<=numCols; i++)
1328            {
1329                if (i > 1) row.append(",");
1330                row.append(s.getString(i));
1331            }
1332            row.append("}\n");
1333        }
1334        System.out.println(row.toString());
1335        s.close();
1336    }
1337}
1338
Popular Tags