KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > savepointJdbc30_JSR169


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30_JSR169
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.jdbcapi;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.DriverManager JavaDoc;
26 import java.sql.ResultSet JavaDoc;
27 import java.sql.SQLException JavaDoc;
28 import java.sql.Savepoint JavaDoc;
29 import java.sql.Statement JavaDoc;
30
31 import org.apache.derby.tools.ij;
32 import org.apache.derbyTesting.functionTests.util.TestUtil;
33 /**
34  * Test the new class Savepoint in jdbc 30.
35  * Also, test some mix and match of defining savepoints through JDBC and sql
36  * Testing both callable and prepared statements meta data
37  *
38  * Do not put tests in this file that are not compatible with JSR169
39  * @author mamta
40  */

41
42
43 public class savepointJdbc30_JSR169 {
44
45     static private boolean isDerbyNet = false;
46
47     static private String JavaDoc[] testObjects = { "table t1", "table t2", "table savepoint"};
48
49
50
51     public static void main(String JavaDoc[] args) {
52         Connection JavaDoc con = null, con2 = null;
53         Statement JavaDoc s;
54         System.out.println("Test savepointJdbc30 starting");
55
56         try
57         {
58             // use the ij utility to read the property file and
59
// make the initial connection.
60
ij.getPropertyArg(args);
61             con = ij.startJBMS();
62             con2 = ij.startJBMS();
63             runTests("regular connections", con,con2);
64             
65             con.close();
66             con2.close();
67
68         }
69         catch (SQLException JavaDoc e) {
70             dumpSQLExceptions(e);
71         }
72         catch (Throwable JavaDoc e) {
73             System.out.println("FAIL -- unexpected exception:");
74             e.printStackTrace(System.out);
75         }
76
77     
78     }
79
80 public static void runTests(String JavaDoc tag, Connection JavaDoc con, Connection JavaDoc con2)
81 throws SQLException JavaDoc {
82     
83     Statement JavaDoc s;
84     System.out.println("Test savepointJdbc30 starting for " + tag);
85     isDerbyNet = TestUtil.isNetFramework();
86     con.setAutoCommit(true); // make sure it is true
87
con2.setAutoCommit(false);
88     s = con.createStatement();
89
90     /* Create the table and do any other set-up */
91     setUpTest(s);
92
93     //JCC translates the JDBC savepoint calls into equivalent SQL statements.
94
//In addition, we do not allow nested savepoints when
95
//coming through SQL statements. Because of this restriction, we can't run most of the
96
//JDBC savepoint tests under DRDA framework. The JDBC tests have nested JDBC savepoint
97
//calls and they fail when run under JCC(because they get translated into nested SQL savepoints).
98
//Hence, splitting the test cases into non-DRDA and more generic tests.
99
System.out.println("Tests common to DRDA and embedded Cloudscape");
100     genericTests(con, con2, s);
101
102     System.out.println("Next try non-DRDA tests");
103     if (!isDerbyNet)
104         nonDRDATests(con, s);
105
106     
107     con.setAutoCommit(true);
108     TestUtil.cleanUpTest(s, testObjects);
109     
110     s.close();
111     
112     }
113
114
115     //The following tests have nested savepoints through JDBC calls. When coming through JCC,
116
//these nested JDBC savepoint calls are translated into equivalent SQL savepoint statements.
117
//But we do not allow nested savepoints coming through SQL statments
118
//and hence these tests can't be run under DRDA framework.
119
static void nonDRDATests(Connection JavaDoc con, Statement JavaDoc s)
120                     throws SQLException JavaDoc {
121         ResultSet JavaDoc rs1, rs2, rs1WithHold, rs2WithHold;
122         Savepoint JavaDoc savepoint1, savepoint2, savepoint3, savepoint4;
123
124         //Setting autocommit to false will allow savepoints
125
con.setAutoCommit(false); // make sure it is false
126

127         //Test40 - We internally generate a unique name for unnamed savepoints. If a
128
//named savepoint uses the currently used internal savepoint name, we won't
129
//get an exception thrown for it because we prepend external saves with "e."
130
//to avoid name conflicts.
131
System.out.println("Test40 - named savepoint can't conflict with internally generated name for unnamed savepoints");
132         savepoint1 = con.setSavepoint();
133         savepoint2 = con.setSavepoint("i.SAVEPT0");
134         con.rollback();
135
136         //Test41 - Rolling back to a savepoint will release all the savepoints created after that savepoint.
137
System.out.println("Test41a - Rollback to a savepoint, then try to release savepoint created after that savepoint");
138
139         savepoint1 = con.setSavepoint();
140         s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
141
142         savepoint2 = con.setSavepoint("s1");
143         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
144
145         savepoint3 = con.setSavepoint("s2");
146         s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
147
148         //Rollback to first named savepoint s1. This will internally release the second named savepoint s2.
149
con.rollback(savepoint2);
150         rs1 = s.executeQuery("select count(*) from t1");
151         rs1.next();
152         if(rs1.getInt(1) != 1) {
153             System.out.println("ERROR: There should have been 1 row in the table, but found " + rs1.getInt(1) + " rows");
154             return;
155         }
156
157         //Trying to release second named savepoint s2 should throw exception.
158
try
159         {
160             con.releaseSavepoint(savepoint3);
161             System.out.println("FAIL 41a release of rolled back savepoint");
162         }
163         catch (SQLException JavaDoc se) {
164             System.out.println("Expected Exception is " + se.getMessage());
165         }
166
167         //Trying to rollback second named savepoint s2 should throw exception.
168
System.out.println("Test41b - Rollback to a savepoint, then try to rollback savepoint created after that savepoint");
169         try
170         {
171             con.rollback(savepoint3);
172             System.out.println("FAIL 41b release of rolled back savepoint");
173         }
174         catch (SQLException JavaDoc se) {
175             System.out.println("Expected Exception is " + se.getMessage());
176         }
177
178         //Release the unnamed named savepoint.
179
con.rollback(savepoint1);
180         rs1 = s.executeQuery("select count(*) from t1");
181         rs1.next();
182         if(rs1.getInt(1) != 0) {
183             System.out.println("ERROR: There should have been no rows in the table, but found " + rs1.getInt(1) + " rows");
184             return;
185         }
186         con.rollback();
187
188         //Test42 - Rollback/commit on a connection will release all the savepoints created for that transaction
189
System.out.println("Test42 - Rollback/commit the transaction, then try to use savepoint from that transaction");
190         savepoint1 = con.setSavepoint();
191         savepoint2 = con.setSavepoint("s1");
192         con.rollback();
193         try {
194             con.rollback(savepoint1);
195             System.out.println("FAIL 42 release of rolled back savepoint");
196         } catch (SQLException JavaDoc se) {
197             System.out.println("Expected Exception is " + se.getMessage());
198         }
199         //Testing commit next
200
savepoint1 = con.setSavepoint();
201         savepoint2 = con.setSavepoint("s1");
202         con.commit();
203         try {
204             con.rollback(savepoint1);
205             System.out.println("FAIL 42 rollback of rolled back savepoint");
206         } catch (SQLException JavaDoc se) {
207             System.out.println("Expected Exception is " + se.getMessage());
208         }
209
210         //Test43 - After releasing a savepoint, should be able to reuse it.
211
System.out.println("Test43 - Release and reuse a savepoint name");
212         savepoint1 = con.setSavepoint("s1");
213         try {
214             savepoint2 = con.setSavepoint("s1");
215             System.out.println("FAIL 43");
216         } catch (SQLException JavaDoc se) {
217             System.out.println("Expected Exception is " + se.getMessage());
218         }
219         con.releaseSavepoint(savepoint1);
220         savepoint2 = con.setSavepoint("s1");
221         con.rollback();
222
223         // Test 45 reuse savepoint name after rollback - should not work
224
System.out.println("Test 45 reuse savepoint name after rollback - should not work");
225         savepoint1 = con.setSavepoint("MyName");
226         con.rollback(savepoint1);
227         try {
228             savepoint2 = con.setSavepoint("MyName");
229             System.out.println("FAIL 45 reuse of savepoint name after rollback should fail");
230         } catch (SQLException JavaDoc se) {
231             System.out.println("Expected Exception is " + se.getMessage());
232         }
233         con.rollback();
234
235         // Test 46 bug 5145 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint
236
System.out.println("Test 46 Cursors declared before and within the savepoint unit will be closed when rolling back the savepoint");
237         Statement JavaDoc sWithHold = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT );
238         con.setAutoCommit(false);
239         s.executeUpdate("DELETE FROM T1");
240         s.executeUpdate("INSERT INTO T1 VALUES(19,1)");
241         s.executeUpdate("INSERT INTO T1 VALUES(19,2)");
242         s.executeUpdate("INSERT INTO T1 VALUES(19,3)");
243         rs1 = s.executeQuery("select * from t1");
244         rs1.next();
245         rs1WithHold = sWithHold.executeQuery("select * from t1");
246         rs1WithHold.next();
247         savepoint1 = con.setSavepoint();
248         rs2 = s.executeQuery("select * from t1");
249         rs2.next();
250         rs2WithHold = sWithHold.executeQuery("select * from t1");
251         rs2WithHold.next();
252         con.rollback(savepoint1);
253         try {//resultset declared outside the savepoint unit should be closed at this point after the rollback to savepoint
254
rs1.next();
255             System.out.println("FAIL 46 shouldn't be able to use a resultset (declared before the savepoint unit) after the rollback to savepoint");
256         } catch (SQLException JavaDoc se) {
257             System.out.println("Expected Exception is " + se.getMessage());
258         }
259         try {//holdable resultset declared outside the savepoint unit should be closed at this point after the rollback to savepoint
260
rs1WithHold.next();
261             System.out.println("FAIL 46 shouldn't be able to use a holdable resultset (declared before the savepoint unit) after the rollback to savepoint");
262         } catch (SQLException JavaDoc se) {
263             System.out.println("Expected Exception is " + se.getMessage());
264         }
265         try {//resultset declared within the savepoint unit should be closed at this point after the rollback to savepoint
266
rs2.next();
267             System.out.println("FAIL 46 shouldn't be able to use a resultset (declared within the savepoint unit) after the rollback to savepoint");
268         } catch (SQLException JavaDoc se) {
269             System.out.println("Expected Exception is " + se.getMessage());
270         }
271         try {//holdable resultset declared within the savepoint unit should be closed at this point after the rollback to savepoint
272
rs2WithHold.next();
273             System.out.println("FAIL 46 shouldn't be able to use a holdable resultset (declared within the savepoint unit) after the rollback to savepoint");
274         } catch (SQLException JavaDoc se) {
275             System.out.println("Expected Exception is " + se.getMessage());
276         }
277         con.rollback();
278
279         // Test 47 multiple tests for getSavepointId()
280
System.out.println("Test 47 multiple tests for getSavepointId()");
281         savepoint1 = con.setSavepoint();
282         savepoint2 = con.setSavepoint();
283         System.out.println(savepoint1.getSavepointId());
284         System.out.println(savepoint2.getSavepointId());
285         con.releaseSavepoint(savepoint2);
286         savepoint2 = con.setSavepoint();
287         System.out.println(savepoint2.getSavepointId());
288         con.commit();
289         savepoint2 = con.setSavepoint();
290         System.out.println(savepoint2.getSavepointId());
291         con.rollback();
292         savepoint2 = con.setSavepoint();
293         System.out.println(savepoint2.getSavepointId());
294         con.rollback();
295
296         // Test 48
297
System.out.println("Test 48 No nested SQL savepoints allowed.");
298         savepoint1 = con.setSavepoint();
299         savepoint2 = con.setSavepoint();
300         System.out.println("Following SQL savepoint will fail because we are trying to nest it inside JDBC savepoint");
301         try {
302             s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
303             System.out.println("FAIL 48 shouldn't be able set SQL savepoint nested inside JDBC/SQL savepoints");
304         } catch (SQLException JavaDoc se) {
305             System.out.println("Expected Exception is " + se.getMessage());
306         }
307         //rollback JDBC savepoint but still can't have SQL savepoint because there is still one JDBC savepoint
308
con.releaseSavepoint(savepoint2);
309         try {
310             s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
311             System.out.println("FAIL 48 Should have gotten exception for nested SQL savepoint");
312         } catch (SQLException JavaDoc se) {
313             System.out.println("Expected Exception is " + se.getMessage());
314         }
315         con.releaseSavepoint(savepoint1); //rollback last JDBC savepoint and now try SQL savepoint again
316
s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
317         con.rollback();
318     }
319
320     //These tests do not allow savepoint nesting and hence can be run under DRDA too
321
static void genericTests(Connection JavaDoc con, Connection JavaDoc con2, Statement JavaDoc s)
322                     throws SQLException JavaDoc {
323
324         ResultSet JavaDoc rs1, rs2, rs1WithHold, rs2WithHold;
325         Savepoint JavaDoc savepoint1, savepoint2, savepoint3, savepoint4;
326
327         //Test1 and Test1a fail under DRDA (bug 5384).
328
//Test1 - No savepoint allowed when auto commit is true
329
con.setAutoCommit(true); // make sure it is true
330
try
331         {
332             System.out.println("Test1 - no unnamed savepoints allowed if autocommit is true");
333             con.setSavepoint(); // will throw exception because auto commit is true
334
System.out.println("FAIL 1 - auto commit on");
335         }
336         catch (SQLException JavaDoc se) {
337             System.out.println("Expected Exception is " + se.getMessage());
338         }
339         //Test1a - No savepoint allowed when auto commit is true
340
try {
341             System.out.println("Test1a - no named savepoints allowed if autocommit is true");
342             con.setSavepoint("notallowed"); // will throw exception because auto commit is true
343
System.out.println("FAIL 1a - auto commit on");
344         } catch (SQLException JavaDoc se) {
345             System.out.println("Expected Exception is " + se.getMessage());
346         }
347
348         con.setAutoCommit(false); // make sure it is false
349

350         //Test2 - After releasing a savepoint, should be able to reuse it.
351
System.out.println("Test2 - Release and reuse a savepoint name");
352         savepoint1 = con.setSavepoint("s1");
353         con.releaseSavepoint(savepoint1);
354         savepoint2 = con.setSavepoint("s1");
355         con.rollback();
356
357         //Test3 - Named savepoints can't pass null for name
358
try {
359             System.out.println("Test3 - null name not allowed for named savepoints");
360             con.setSavepoint(null);
361             System.out.println("FAIL 3 null savepoint ");
362         } catch (SQLException JavaDoc se) {
363             System.out.println("Expected Exception is " + se.getMessage());
364         }
365         con.rollback();
366
367         //Test4 - Verify names/ids of named/unnamed savepoints
368
//named savepoints don't have an id.
369
//unnamed savepoints don't have a name (internally, all our savepoints have names,
370
//but for unnamed savepoint, that is not exposed thro jdbc api)
371
System.out.println("Test4 - Verify names/ids of named/unnamed savepoints");
372         try {
373             savepoint1 = con.setSavepoint();
374             savepoint1.getSavepointId();
375             //following should throw exception for un-named savepoint
376
savepoint1.getSavepointName();
377             System.out.println("FAIL 4 getSavepointName on id savepoint ");
378         } catch (SQLException JavaDoc se) {
379             System.out.println("Expected Exception is " + se.getMessage());
380         }
381         con.rollback();
382         try {
383             savepoint1 = con.setSavepoint("s1");
384             savepoint1.getSavepointName();
385             //following should throw exception for named savepoint
386
savepoint1.getSavepointId();
387             System.out.println("FAIL 4 getSavepointId on named savepoint ");
388         } catch (SQLException JavaDoc se) {
389             System.out.println("Expected Exception is " + se.getMessage());
390         }
391         con.rollback();
392
393         // TEST 5a and 5b for bug 4465
394
// test 5a - create two savepoints in two different transactions
395
// and release the first one in the subsequent transaction
396
System.out.println("Test5a - create two savepoints in two different transactions" +
397             " and release the first one in the subsequent transaction");
398         savepoint1 = con.setSavepoint("s1");
399         con.commit();
400         //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
401
//for savepoints. The reason for that is as follows
402
//JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
403
//different connections are named the same, then the release savepoint below will get converted to
404
//RELEASE TO SAVEPOINT s1 and that succeeds because the 2nd connection does have a savepoint named s1.
405
//Hence we don't really check what we intended to check which is trying to release a savepoint created
406
//in a different transaction
407
savepoint2 = con.setSavepoint("s2");
408         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
409         try {
410             con.releaseSavepoint(savepoint1);
411             System.out.println("FAIL 5a - release savepoint from a different transaction did not raise error");
412         } catch (SQLException JavaDoc se) {
413             System.out.println("Expected Exception is " + se.getMessage());
414         }
415         con.commit();
416
417         // test 5b - create two savepoints in two different transactions
418
// and rollback the first one in the subsequent transaction
419
System.out.println("Test5b - create two savepoints in two different transactions" +
420             " and rollback the first one in the subsequent transaction");
421         savepoint1 = con.setSavepoint("s1");
422         con.commit();
423         //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
424
//for savepoints. The reason for that is as follows
425
//JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
426
//different connections are named the same, then the rollback savepoint below will get converted to
427
//ROLLBACK TO SAVEPOINT s1 and that succeeds because the 2nd connection does have a savepoint named s1.
428
//Hence we don't really check what we intended to check which is trying to rollback a savepoint created
429
//in a different transaction
430
savepoint2 = con.setSavepoint("s2");
431         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
432         try {
433             con.rollback(savepoint1);
434             System.out.println("FAIL 5b - rollback savepoint from a different transaction did not raise error");
435         } catch (SQLException JavaDoc se) {
436             System.out.println("Expected Exception is " + se.getMessage());
437         }
438         con.commit();
439
440         // test 6a - create a savepoint release it and then create another with the same name.
441
// and release the first one
442
System.out.println("Test6a - create a savepoint, release it, create another with" +
443             " same name and release the first one");
444         savepoint1 = con.setSavepoint("s1");
445         con.releaseSavepoint(savepoint1);
446         //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
447
//for savepoints. The reason for that is as follows
448
//JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
449
//a transaction are named the same, then the release savepoint below will get converted to
450
//RELEASE TO SAVEPOINT s1 and that succeeds because there is a valid savepoint named s1.
451
savepoint2 = con.setSavepoint("s2");
452         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
453         try {
454             con.releaseSavepoint(savepoint1);
455             System.out.println("FAIL 6a - releasing a released savepoint did not raise error");
456         } catch (SQLException JavaDoc se) {
457             System.out.println("Expected Exception is " + se.getMessage());
458         }
459         con.commit();
460
461         // test 6b - create a savepoints release it and then create another with the same name.
462
// and rollback the first one
463
System.out.println("Test6b - create a savepoint, release it, create another with" +
464             " same name and rollback the first one");
465         savepoint1 = con.setSavepoint("s1");
466         con.releaseSavepoint(savepoint1);
467         //The following savepoint was earlier named s1. Changed it to s2 while working on DRDA support
468
//for savepoints. The reason for that is as follows
469
//JCC translates all savepoint jdbc calls to equivalent sql and hence if the 2 savepoints in
470
//a transaction are named the same, then the rollback savepoint below will get converted to
471
//ROLLBACK TO SAVEPOINT s1 and that succeeds because there is a valid savepoint named s1.
472
savepoint2 = con.setSavepoint("s2");
473         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
474         try {
475             con.rollback(savepoint1);
476             System.out.println("FAIL 6b - rollback a released savepoint did not raise error");
477         } catch (SQLException JavaDoc se) {
478             System.out.println("Expected Exception is " + se.getMessage());
479         }
480         con.commit();
481
482 /* TEST case just for bug 4467
483             // Test 10 - create a named savepoint with the a generated name
484             savepoint1 = con2.setSavepoint("SAVEPT0");
485
486             // what exactly is the correct behaviour here?
487             try {
488                 savepoint2 = con2.setSavepoint();
489             }
490             catch (SQLException se) {
491                 System.out.println("Expected Exception is " + se.getMessage());
492             }
493             con2.commit();
494 */

495
496         System.out.println("Test6c - Try to use a savepoint from another connection for release");
497         savepoint1 = con.setSavepoint("s1");
498         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
499         try {
500             con2.releaseSavepoint(savepoint1);
501             System.out.println("FAIL 6c - releasing another transaction's savepoint did not raise error");
502         } catch (SQLException JavaDoc se) {
503             System.out.println("Expected Exception is " + se.getMessage());
504         }
505         con.commit();
506         con2.commit();
507
508         /* BUG 4468 - should not be able to pass a savepoint from a different transaction for release/rollback */
509         // Test 7a - swap savepoints across connections
510
System.out.println("Test7a - swap savepoints across connections with release");
511         savepoint1 = con2.setSavepoint("s1");
512         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
513         savepoint2 = con.setSavepoint("s1");
514         try {
515             con.releaseSavepoint(savepoint1);
516             System.out.println("FAIL 7a - releasing a another transaction's savepoint did not raise error");
517         } catch (SQLException JavaDoc se) {
518             System.out.println("Expected Exception is " + se.getMessage());
519         }
520         con.commit();
521         con2.commit();
522
523         // Test 7b - swap savepoints across connections
524
System.out.println("Test7b - swap savepoints across connections with rollback");
525         savepoint1 = con2.setSavepoint("s1");
526         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
527         savepoint2 = con.setSavepoint("s1");
528         try {
529             con.rollback(savepoint1);
530             System.out.println("FAIL 7b - rolling back a another transaction's savepoint did not raise error");
531         } catch (SQLException JavaDoc se) {
532             System.out.println("Expected Exception is " + se.getMessage());
533         }
534         con.commit();
535         con2.commit();
536
537         /*
538          * following section attempts to call statement in a method to do a negative test
539          * because savepoints are not supported in a trigger
540          * however, this cannot be done because a call is not supported in a trigger.
541          * leaving the test here for later reference for when we support the SQL version
542                  *
543         // bug 4507 - Test 8 test all 4 savepoint commands inside the trigger code
544         System.out.println("Test 8a set savepoint(unnamed) command inside the trigger code");
545         s.executeUpdate("create trigger trig1 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionSetSavepointUnnamed()");
546         try {
547     
548             s.executeUpdate("insert into t1 values(1,1)");
549             System.out.println("FAIL 8a set savepoint(unnamed) command inside the trigger code");
550         } catch (SQLException se) {
551             System.out.println("Expected Exception is " + se.getMessage());
552         }
553         s.executeUpdate("drop trigger trig1");
554
555         System.out.println("Test 8b set savepoint(named) command inside the trigger code");
556         s.executeUpdate("create trigger trig2 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionSetSavepointNamed()");
557         try {
558             s.executeUpdate("insert into t1 values(1,1)");
559             System.out.println("FAIL 8b set savepoint(named) command inside the trigger code");
560         } catch (SQLException se) {
561             System.out.println("Expected Exception is " + se.getMessage());
562         }
563         s.executeUpdate("drop trigger trig2");
564
565         System.out.println("Test 8c release savepoint command inside the trigger code");
566         s.executeUpdate("create trigger trig3 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionReleaseSavepoint()");
567         try {
568             s.executeUpdate("insert into t1 values(1,1)");
569             System.out.println("FAIL 8c release savepoint command inside the trigger code");
570         } catch (SQLException se) {
571             System.out.println("Expected Exception is " + se.getMessage());
572         }
573         s.executeUpdate("drop trigger trig3");
574
575         System.out.println("Test 8d rollback savepoint command inside the trigger code");
576         s.executeUpdate("create trigger trig4 before insert on t1 for each statement call org.apache.derbyTesting.functionTests.tests.jdbcapi.savepointJdbc30::doConnectionRollbackSavepoint()");
577         try {
578             s.executeUpdate("insert into t1 values(1,1)");
579             System.out.println("FAIL 8d rollback savepoint command inside the trigger code");
580         } catch (SQLException se) {
581             System.out.println("Expected Exception is " + se.getMessage());
582         }
583         s.executeUpdate("drop trigger trig4");
584         con.rollback();
585         */
//end commented out test 8
586

587         // Test 9 test savepoint name and verify case sensitivity
588
System.out.println("Test 9 test savepoint name");
589         savepoint1 = con.setSavepoint("myname");
590         String JavaDoc savepointName = savepoint1.getSavepointName();
591         if (!savepointName.equals("myname"))
592             System.out.println("fail - savepoint name mismatch");
593         con.rollback();
594
595         // Test 10 test savepoint name case sensitivity
596
System.out.println("Test 10 test savepoint name case sensitivity");
597         savepoint1 = con.setSavepoint("MyName");
598         savepointName = savepoint1.getSavepointName();
599         if (!savepointName.equals("MyName"))
600             System.out.println("fail - savepoint name mismatch");
601         con.rollback();
602
603         // Test 11 rolling back a savepoint multiple times - should work
604
System.out.println("Test 11 rolling back a savepoint multiple times - should work");
605         savepoint1 = con.setSavepoint("MyName");
606         con.rollback(savepoint1);
607         try {
608             con.rollback(savepoint1);
609         } catch (SQLException JavaDoc se) {
610             System.out.println("FAIL 11 second rollback failed");
611             System.out.println("Exception is " + se.getMessage());
612         }
613         con.rollback();
614
615         // Test 12 releasing a savepoint multiple times - should not work
616
System.out.println("Test 12 releasing a savepoint multiple times - should not work");
617         savepoint1 = con.setSavepoint("MyName");
618         con.releaseSavepoint(savepoint1);
619         try {
620             con.releaseSavepoint(savepoint1);
621             System.out.println("FAIL 12 releasing a savepoint multiple times should fail");
622         } catch (SQLException JavaDoc se) {
623             System.out.println("Expected Exception is " + se.getMessage());
624         }
625         con.rollback();
626
627         // Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off
628
System.out.println("Test 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off");
629         savepoint1 = con.setSavepoint("MyName");
630         con.setAutoCommit(true);
631         con.setAutoCommit(false);
632         savepoint2 = con.setSavepoint("MyName1");
633         try {//shouldn't be able to use savepoint from earlier tranasaction after setting autocommit on and off
634
con.releaseSavepoint(savepoint1);
635             System.out.println("FAIL 13 shouldn't be able to use a savepoint from earlier transaction after setting autocommit on and off");
636         } catch (SQLException JavaDoc se) {
637             System.out.println("Expected Exception is " + se.getMessage());
638         }
639         con.releaseSavepoint(savepoint2);
640         con.rollback();
641
642         // Test 14 cause a transaction rollback and that should release the internal savepoint array
643
System.out.println("Test 14 A non-user initiated transaction rollback should release the internal savepoint array");
644         Statement JavaDoc s1, s2;
645         s1 = con.createStatement();
646         s1.executeUpdate("insert into t1 values(1,1)");
647         s1.executeUpdate("insert into t1 values(2,0)");
648         con.commit();
649         s1.executeUpdate("update t1 set c11=c11+1 where c12 > 0");
650         s2 = con2.createStatement();
651         savepoint1 = con2.setSavepoint("MyName");
652         try {//following will get lock timeout which will rollback transaction on c2
653
s2.executeUpdate("update t1 set c11=c11+1 where c12 < 1");
654             System.out.println("FAIL 14 should have gotten lock time out");
655         } catch (SQLException JavaDoc se) {
656             System.out.println("Expected Exception is " + se.getMessage());
657         }
658         try {//the transaction rollback above should have removed the savepoint MyName
659
con2.releaseSavepoint(savepoint1);
660             System.out.println("FAIL 14 A non-user initiated transaction rollback should release the internal savepoint array");
661         } catch (SQLException JavaDoc se) {
662             System.out.println("Expected Exception is " + se.getMessage());
663         }
664         con.rollback();
665         con2.rollback();
666         s.execute("delete from t1");
667         con.commit();
668
669         // Test 15 check savepoints in batch
670
System.out.println("Test 15 check savepoints in batch");
671         s.execute("delete from t1");
672         s.addBatch("insert into t1 values(1,1)");
673         s.addBatch("insert into t1 values(1,1)");
674         savepoint1 = con.setSavepoint();
675         s.addBatch("insert into t1 values(1,1)");
676         s.executeBatch();
677         con.rollback(savepoint1);
678         int val = count(con,s);
679         if (val != 0)
680             System.out.println("FAIL 15 savepoint should have been set before batch");
681         con.rollback();
682
683         // Test 16 grammar check for savepoint sq1
684
System.out.println("Test 16 grammar check for savepoint sq1");
685         try {
686             s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS");
687             System.out.println("FAIL 16 Should have gotten exception for missing ON ROLLBACK RETAIN CURSORS");
688         } catch (SQLException JavaDoc se) {
689             System.out.println("Expected Exception is " + se.getMessage());
690         }
691         try {
692             s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN CURSORS");
693             System.out.println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN CURSORS");
694         } catch (SQLException JavaDoc se) {
695             System.out.println("Expected Exception is " + se.getMessage());
696         }
697         try {
698             s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN LOCKS");
699             System.out.println("FAIL 16 Should have gotten exception for multiple ON ROLLBACK RETAIN LOCKS");
700         } catch (SQLException JavaDoc se) {
701             System.out.println("Expected Exception is " + se.getMessage());
702         }
703         try {
704             s.executeUpdate("SAVEPOINT s1 UNIQUE UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
705             System.out.println("FAIL 16 Should have gotten exception for multiple UNIQUE keywords");
706         } catch (SQLException JavaDoc se) {
707             System.out.println("Expected Exception is " + se.getMessage());
708         }
709         s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS ON ROLLBACK RETAIN LOCKS");
710         s.executeUpdate("RELEASE TO SAVEPOINT s1");
711         con.rollback();
712
713         // Test 17
714
System.out.println("Test 17 No nested savepoints allowed when using SQL to set savepoints.");
715         System.out.println("Test 17a Test with UNIQUE clause.");
716         s.executeUpdate("SAVEPOINT s1 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
717         try {
718             s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
719             System.out.println("FAIL 17a Should have gotten exception for nested savepoints");
720         } catch (SQLException JavaDoc se) {
721             System.out.println("Expected Exception is " + se.getMessage());
722         }
723         s.executeUpdate("RELEASE TO SAVEPOINT s1");
724         s.executeUpdate("SAVEPOINT s2 UNIQUE ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
725         con.rollback();
726
727         System.out.println("Test 17b Test without UNIQUE clause.");
728         System.out.println("Since no nesting is allowed, skipping UNIQUE still gives error for trying to define another savepoint");
729         s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
730         try {
731             s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
732             System.out.println("FAIL 17b Should have gotten exception for nested savepoints");
733         } catch (SQLException JavaDoc se) {
734             System.out.println("Expected Exception is " + se.getMessage());
735         }
736         con.rollback();
737
738         // Test 18
739
System.out.println("Test 18 No nested SQL savepoints allowed inside JDBC savepoint.");
740         savepoint1 = con.setSavepoint();
741         System.out.println("Following SQL savepoint will fail because we are trying to nest it inside JDBC savepoint");
742         try {
743             s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
744             System.out.println("FAIL 18 shouldn't be able set SQL savepoint nested inside JDBC savepoints");
745         } catch (SQLException JavaDoc se) {
746             System.out.println("Expected Exception is " + se.getMessage());
747         }
748         //rollback the JDBC savepoint. Now since there are no user defined savepoints, we can define SQL savepoint
749
con.releaseSavepoint(savepoint1);
750         s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
751         con.rollback();
752
753         // Test 19
754
System.out.println("Test 19 No nested SQL savepoints allowed inside SQL savepoint.");
755         s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
756         System.out.println("Following SQL savepoint will fail because we are trying to nest it inside SQL savepoint");
757         try {
758             s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
759             System.out.println("FAIL 19 shouldn't be able set SQL savepoint nested inside SQL savepoint");
760         } catch (SQLException JavaDoc se) {
761             System.out.println("Expected Exception is " + se.getMessage());
762         }
763         //rollback the SQL savepoint. Now since there are no user defined savepoints, we can define SQL savepoint
764
s.executeUpdate("RELEASE TO SAVEPOINT s1");
765         s.executeUpdate("SAVEPOINT s2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
766         con.rollback();
767
768         // Test 20
769
System.out.println("Test 20 Rollback of SQL savepoint works same as rollback of JDBC savepoint.");
770         s.executeUpdate("DELETE FROM T1");
771         con.commit();
772         s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
773         s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
774         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
775         s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
776         //Rollback to SQL savepoint and should see changes rolledback
777
s.executeUpdate("ROLLBACK TO SAVEPOINT s1");
778         rs1 = s.executeQuery("select count(*) from t1");
779         rs1.next();
780         if(rs1.getInt(1) != 0) {
781             System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows");
782             return;
783         }
784         con.rollback();
785
786         // Test 21
787
System.out.println("Test 21 After releasing the SQL savepoint, rollback the transaction and should see everything undone.");
788         s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
789         s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
790         s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
791         s.executeUpdate("INSERT INTO T1 VALUES(3,1)");
792         //Release the SQL savepoint and then rollback the transaction and should see changes rolledback
793
s.executeUpdate("RELEASE TO SAVEPOINT s1");
794         con.rollback();
795         rs1 = s.executeQuery("select count(*) from t1");
796         rs1.next();
797         if(rs1.getInt(1) != 0) {
798             System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows");
799             return;
800         }
801         con.rollback();
802
803         // Test 22
804
System.out.println("Test 22 Should not be able to create a SQL savepoint starting with name SYS");
805         try {
806             s.executeUpdate("SAVEPOINT SYSs2 ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
807             System.out.println("FAIL 22 shouldn't be able to create a SQL savepoint starting with name SYS");
808         } catch (SQLException JavaDoc se) {
809             System.out.println("Expected Exception is " + se.getMessage());
810         }
811         con.rollback();
812
813         // Test 23 - bug 5817 - make savepoint and release non-reserved keywords
814
System.out.println("Test 23 Should be able to use non-reserved keywords savepoint and release as identifiers");
815         System.out.println("Create table with savepoint and release as identifiers");
816         s.execute("create table savepoint (savepoint int, release int)");
817         rs1 = s.executeQuery("select count(*) from savepoint");
818         rs1.next();
819         if(rs1.getInt(1) != 0) {
820             System.out.println("ERROR: There should have been 0 rows in the table, but found " + rs1.getInt(1) + " rows");
821             return;
822         }
823         System.out.println("Create a savepoint with name savepoint");
824         s.execute("SAVEPOINT savepoint ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
825         s.executeUpdate("INSERT INTO savepoint VALUES(1,1)");
826         System.out.println("Release the savepoint with name savepoint");
827         s.execute("RELEASE SAVEPOINT savepoint");
828         rs1 = s.executeQuery("select count(*) from savepoint");
829         rs1.next();
830         if(rs1.getInt(1) != 1) {
831             System.out.println("ERROR: There should have been 1 rows in the table, but found " + rs1.getInt(1) + " rows");
832             return;
833         }
834         System.out.println("Create a savepoint with name release");
835         s.execute("SAVEPOINT release ON ROLLBACK RETAIN LOCKS ON ROLLBACK RETAIN CURSORS");
836         s.executeUpdate("INSERT INTO savepoint VALUES(2,1)");
837         System.out.println("Rollback to the savepoint with name release");
838         s.execute("ROLLBACK TO SAVEPOINT release");
839         rs1 = s.executeQuery("select count(*) from savepoint");
840         rs1.next();
841         if(rs1.getInt(1) != 1) {
842             System.out.println("ERROR: There should have been 1 rows in the table, but found " + rs1.getInt(1) + " rows");
843             return;
844         }
845         System.out.println("Release the savepoint with name release");
846         s.execute("RELEASE SAVEPOINT release");
847         con.rollback();
848
849         // Test 24
850
System.out.println("Test 24 Savepoint name can't exceed 128 characters");
851         try {
852             savepoint1 = con.setSavepoint("MyName1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890");
853             System.out.println("FAIL 24 shouldn't be able to create a SQL savepoint with name exceeding 128 characters");
854         } catch (SQLException JavaDoc se) {
855             System.out.println("Expected Exception is " + se.getMessage());
856         }
857         con.rollback();
858
859         // Test 25
860
System.out.println("Test 25 Should not be able to create a SQL savepoint starting with name SYS through jdbc");
861         try {
862             savepoint1 = con.setSavepoint("SYSs2");
863             System.out.println("FAIL 25 shouldn't be able to create a SQL savepoint starting with name SYS through jdbc");
864         } catch (SQLException JavaDoc se) {
865             System.out.println("Expected Exception is " + se.getMessage());
866         }
867         con.rollback();
868
869         s1.close();
870         s2.close();
871
872         // bug 4451 - Test 26a pass Null value to rollback
873
// bug 5374 - Passing a null savepoint to rollback or release method
874
// used to give a npe in JCC
875
// it should give a SQLException aying "Cannot rollback to a null savepoint"
876
System.out.println("Test 26a rollback of null savepoint");
877         try {
878             con.rollback((Savepoint JavaDoc) null);
879             System.out.println("FAIL 26a rollback of null savepoint did not raise error ");
880         } catch (SQLException JavaDoc se) {
881             System.out.println("Expected Exception is " + se.getMessage());
882         }
883         // Test 26b pass Null value to releaseSavepoint
884
System.out.println("Test 26b release of null savepoint");
885         try {
886             con.releaseSavepoint((Savepoint JavaDoc) null);
887             System.out.println("FAIL 26b release of null savepoint did not raise error ");
888         } catch (SQLException JavaDoc se) {
889             System.out.println("Expected Exception is " + se.getMessage());
890         }
891     }
892
893     //Set up the test by creating the table used by the rest of the test.
894
static void setUpTest(Statement JavaDoc s)
895                     throws SQLException JavaDoc {
896
897         try {
898             /* Drop the tables, just in case they're there from another test */
899             s.execute("drop table t1");
900             s.execute("drop table t2");
901             s.execute("drop table savepoint");
902         } catch (SQLException JavaDoc se) {
903             //System.out.println("Expected Exception is " + se.getMessage());
904
}
905
906         /* Create a table */
907         s.execute("create table t1 (c11 int, c12 smallint)");
908         s.execute("create table t2 (c11 int)");
909
910     }
911
912     static private int count(Connection JavaDoc con, Statement JavaDoc s) throws SQLException JavaDoc {
913         int count = 0;
914         ResultSet JavaDoc rs = s.executeQuery("select count(*) from t1");
915         rs.next();
916         count = rs.getInt(1);
917         rs.close();
918         return count;
919     }
920   
921     public static void doConnectionSetSavepointUnnamed() throws Throwable JavaDoc
922     {
923         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
924         Savepoint JavaDoc s1 = conn.setSavepoint();
925         Statement JavaDoc s = conn.createStatement();
926         s.executeUpdate("insert into t2 values(1)");
927         conn.rollback(s1);
928     }
929
930     public static void doConnectionSetSavepointNamed() throws Throwable JavaDoc
931     {
932         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
933         Savepoint JavaDoc s1 = conn.setSavepoint("s1");
934         Statement JavaDoc s = conn.createStatement();
935         s.executeUpdate("insert into t2 values(1)");
936         conn.rollback(s1);
937     }
938
939     public static void doConnectionRollbackSavepoint() throws Throwable JavaDoc
940     {
941         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
942         conn.rollback((Savepoint JavaDoc) null);
943         Statement JavaDoc s = conn.createStatement();
944         s.executeUpdate("insert into t2 values(1)");
945     }
946
947     public static void doConnectionReleaseSavepoint() throws Throwable JavaDoc
948     {
949         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
950         conn.releaseSavepoint((Savepoint JavaDoc) null);
951         Statement JavaDoc s = conn.createStatement();
952         s.executeUpdate("insert into t2 values(1)");
953     }
954
955      public static void dumpSQLExceptions (SQLException JavaDoc se) {
956         System.out.println("FAIL -- unexpected exception");
957         while (se != null) {
958             System.out.print("SQLSTATE("+se.getSQLState()+"):");
959             se.printStackTrace(System.out);
960             se = se.getNextException();
961         }
962     }
963 }
964
Popular Tags