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


1 /*
2
3  Derby - Class org.apache.derby.impl.services.bytecode.CodeChunk
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 ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.SQLWarning ;
29 import java.sql.Statement ;
30 import java.util.ArrayList ;
31 import java.util.Iterator ;
32 import java.util.Properties ;
33
34 import javax.sql.XAConnection ;
35 import javax.sql.XADataSource ;
36 import javax.transaction.xa.XAException ;
37 import javax.transaction.xa.XAResource ;
38 import javax.transaction.xa.Xid ;
39
40 import org.apache.derby.tools.JDBCDisplayUtil;
41 import org.apache.derby.tools.ij;
42 import org.apache.derbyTesting.functionTests.util.TestUtil;
43 import org.apache.derbyTesting.functionTests.util.XATestUtil;
44
45 /**
46  * XATests harvested from SQL XA tests.
47  * Modified so that they can be run with NetworkServer.
48  */
49 public class XATest {
50
51     /**
52      * Run all the tests.
53      */
54     public static void main(String [] args) throws Exception {
55         ij.getPropertyArg(args);
56         Connection dmc = ij.startJBMS();
57         
58         showHoldStatus("initial ", dmc);
59         
60         XATestUtil.createXATransactionView(dmc);
61         dmc.close();
62
63         XADataSource dsx = TestUtil.getXADataSource(cleanProperties());
64
65         // tests originally from xaSimplePositive.sql
66 singleConnectionOnePhaseCommit(dsx);
67         xaShutdown();
68         interleavingTransactions(dsx);
69
70         xaShutdown();
71
72         // tests originally from xaStateTran.sql
73 noTransaction(dsx);
74
75         // test originally from xaMorph.sql
76 morph(dsx);
77         
78         // DERBY-966 holdability testing
79 derby966(dsx);
80
81         // for cleaning up, make a clean new connection
82 Connection dmc2 = ij.startJBMS();
83         cleanUp(dmc2);
84
85         System.out.println("XATest complete");
86     }
87
88     /**
89      * Get the basic set of properties for an XADataSource.
90      * Only sets databaseName to wombat.
91      * @return
92      */
93     private static Properties cleanProperties() {
94         Properties dsAttrs = new Properties ();
95         dsAttrs.setProperty("databaseName", "wombat");
96         return dsAttrs;
97     }
98
99     /**
100      * Shutdown the database through an XADataSource.
101      */
102     private static void xaShutdown() {
103
104         Properties dsAttrs = cleanProperties();
105
106         if (TestUtil.isEmbeddedFramework())
107             dsAttrs.put("shutdownDatabase", "shutdown");
108         else
109             dsAttrs.put("connectionAttributes", "shutdown=true");
110
111         XADataSource dsx = TestUtil.getXADataSource(dsAttrs);
112
113         try {
114             dsx.getXAConnection().getConnection();
115         } catch (SQLException sqle) {
116             if ("08006".equals(sqle.getSQLState()))
117                 return;
118             TestUtil.dumpSQLExceptions(sqle);
119         }
120         System.out.println("FAIL: no exception on shutdown");
121     }
122
123     /*
124      ** Test cases
125      */
126
127     /**
128      * A single connection and 1 phase commit.
129      *
130      
131      Original "SQL" from xaSimplePositive.sql
132      <code>
133      xa_connect ;
134      xa_start xa_noflags 0;
135      xa_getconnection;
136      drop table foo;
137      create table foo (a int);
138      insert into foo values (0);
139      select * from foo;
140      run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
141      select * from global_xactTable where gxid is not null order by gxid;
142      xa_end xa_success 0;
143      xa_commit xa_1phase 0;
144      
145      xa_datasource 'wombat' shutdown;
146      </code>
147      * @throws SQLException
148      * @throws XAException
149      */
150     private static void singleConnectionOnePhaseCommit(XADataSource xads) {
151         System.out.println("singleConnectionOnePhaseCommit");
152         try {
153             XAConnection xac = xads.getXAConnection();
154
155             XAResource xar = xac.getXAResource();
156
157             Xid xid = XATestUtil.getXid(0, 32, 46);
158
159             xar.start(xid, XAResource.TMNOFLAGS);
160
161             Connection conn = xac.getConnection();
162             
163             showHoldStatus("XA ", conn);
164
165             Statement s = conn.createStatement();
166             showHoldStatus("XA ", s);
167
168             s.execute("create table foo (a int)");
169             s.executeUpdate("insert into foo values (0)");
170
171             ResultSet rs = s.executeQuery("select * from foo");
172             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
173             rs.close();
174
175             XATestUtil.showXATransactionView(conn);
176
177             s.close();
178             xar.end(xid, XAResource.TMSUCCESS);
179
180             // 1 phase commit
181 xar.commit(xid, true);
182
183             conn.close();
184             xac.close();
185
186         } catch (SQLException sqle) {
187             TestUtil.dumpSQLExceptions(sqle);
188             sqle.printStackTrace(System.out);
189         } catch (XAException e) {
190             XATestUtil.dumpXAException("singleConnectionOnePhaseCommit", e);
191         }
192     }
193
194     /*
195      * Two interleaving transaction and prepare/commit prepare/rollback.
196      *
197      * (original test said two connections but only one connection was opened)
198
199      <code>
200      xa_datasource 'wombat';
201      xa_connect user 'sku' password 'testxa' ;
202
203      xa_start xa_noflags 1;
204      xa_getconnection;
205      insert into APP.foo values (1);
206      xa_end xa_suspend 1;
207
208      xa_start xa_noflags 2;
209      insert into APP.foo values (2);
210      xa_end xa_suspend 2;
211
212      xa_start xa_resume 1;
213      insert into APP.foo values (3);
214      xa_end xa_suspend 1;
215
216      xa_start xa_resume 2;
217      insert into APP.foo values (4);
218      select * from APP.global_xactTable where gxid is not null order by gxid;
219      -- this prepare won't work since transaction 1 has been suspended - XA_PROTO
220      xa_prepare 1;
221
222      select * from APP.global_xactTable where gxid is not null order by gxid;
223      xa_end xa_success 2;
224
225      -- this assumes a resume
226      xa_end xa_success 1;
227      xa_prepare 1;
228      xa_prepare 2;
229
230      -- both transactions should be prepared
231      select * from APP.global_xactTable where gxid is not null order by gxid;
232
233      -- NOTE: The following call to "xa_recover xa_startrscan" is apt to
234      -- return the result set rows in reverse order when changes to
235      -- the Derby engine affect the number of transactions that it takes
236      -- to create a database. The transactions are stored in a hash table
237      -- based on a global and local id, and when the number of transactions
238      -- changes, the (internal) local id can change, which may lead to a
239      -- change in the result set order. This order is determined by the
240      -- JVM's hashing algorithm. Examples of changes to the engine that
241      -- can affect this include ones that cause more commits or that
242      -- change the amount of data being stored, such as changes to the
243      -- metadata statements (which is what prompted this explanation in
244      -- the first place). Ultimately, the problem is that there is no
245      -- way to order the return values from "xa_recover" since it is an
246      -- ij internal statement, not SQL...
247      xa_recover xa_startrscan;
248      xa_recover xa_noflags;
249
250      xa_commit xa_2Phase 1;
251      xa_rollback 2;
252
253      -- check results
254      xa_start xa_noflags 3;
255      select * from APP.global_xactTable where gxid is not null order by gxid;
256      select * from APP.foo;
257      xa_end xa_success 3;
258
259      xa_prepare 3;
260
261      -- should fail with XA_NOTA because we prepared a read only transaction
262      xa_commit xa_1Phase 3;
263      disconnect;
264      </code>
265      */
266     private static void interleavingTransactions(XADataSource xads) {
267         System.out.println("interleavingTransactions");
268         try {
269             XAConnection xac = xads.getXAConnection("sku", "testxa");
270             XAResource xar = xac.getXAResource();
271
272             Xid xid1 = XATestUtil.getXid(1, 93, 18);
273             Xid xid2 = XATestUtil.getXid(2, 45, 77);
274
275             xar.start(xid1, XAResource.TMNOFLAGS);
276
277             Connection conn = xac.getConnection();
278
279             Statement s = conn.createStatement();
280             s.executeUpdate("insert into APP.foo values (1)");
281             xar.end(xid1, XAResource.TMSUSPEND);
282
283             xar.start(xid2, XAResource.TMNOFLAGS);
284             s.executeUpdate("insert into APP.foo values (2)");
285             xar.end(xid2, XAResource.TMSUSPEND);
286
287             xar.start(xid1, XAResource.TMRESUME);
288             s.executeUpdate("insert into APP.foo values (3)");
289             xar.end(xid1, XAResource.TMSUSPEND);
290
291             xar.start(xid2, XAResource.TMRESUME);
292             s.executeUpdate("insert into APP.foo values (4)");
293
294             XATestUtil.showXATransactionView(conn);
295
296             // this prepare won't work since
297 // transaction 1 has been suspended - XA_PROTO
298 try {
299                 xar.prepare(xid1);
300                 System.out.println("FAIL - prepare on suspended transaction");
301             } catch (XAException e) {
302                 if (e.errorCode != XAException.XAER_PROTO)
303                     XATestUtil.dumpXAException(
304                             "FAIL - prepare on suspended transaction", e);
305
306             }
307
308             // check it was not prepared
309 XATestUtil.showXATransactionView(conn);
310
311             xar.end(xid2, XAResource.TMSUCCESS);
312
313             xar.end(xid1, XAResource.TMSUCCESS);
314
315             xar.prepare(xid1);
316             xar.prepare(xid2);
317
318             // both should be prepared.
319 XATestUtil.showXATransactionView(conn);
320
321             Xid [] recoveredStart = xar.recover(XAResource.TMSTARTRSCAN);
322             System.out.println("recovered start " + recoveredStart.length);
323             Xid [] recovered = xar.recover(XAResource.TMNOFLAGS);
324             System.out.println("recovered " + recovered.length);
325             Xid [] recoveredEnd = xar.recover(XAResource.TMENDRSCAN);
326             System.out.println("recovered end " + recoveredEnd.length);
327
328             for (int i = 0; i < recoveredStart.length; i++) {
329                 Xid xid = recoveredStart[i];
330                 if (xid.getFormatId() == 1) {
331                     // commit 1 with 2pc
332 xar.commit(xid, false);
333                 } else if (xid.getFormatId() == 2) {
334                     xar.rollback(xid);
335                 } else {
336                     System.out.println("FAIL: unknown xact");
337                 }
338             }
339
340             // check the results
341 Xid xid3 = XATestUtil.getXid(3, 2, 101);
342             xar.start(xid3, XAResource.TMNOFLAGS);
343             XATestUtil.showXATransactionView(conn);
344             ResultSet rs = s.executeQuery("select * from APP.foo");
345             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
346             rs.close();
347             xar.end(xid3, XAResource.TMSUCCESS);
348
349             int pr = xar.prepare(xid3);
350             if (pr != XAResource.XA_RDONLY)
351                 System.out.println("FAIL - prepare on read only xact returned "
352                         + pr);
353
354             try {
355                 xar.commit(xid3, true);
356                 System.out.println("FAIL - 2pc commit on read-only xact");
357             } catch (XAException e) {
358                 if (e.errorCode != XAException.XAER_NOTA)
359                     throw e;
360             }
361
362             s.close();
363             conn.close();
364             xac.close();
365         } catch (SQLException sqle) {
366             TestUtil.dumpSQLExceptions(sqle);
367         } catch (XAException e) {
368             XATestUtil.dumpXAException("interleavingTransactions", e);
369         }
370     }
371
372     /**
373      Tests on INIT STATE (no tr
374      Original SQL from xaStateTran.sql.
375      <code>
376
377      -- the following should error XAER_NOTA
378      xa_start xa_join 11;
379      -- the following should error XAER_NOTA
380      xa_start xa_resume 11;
381      -- the following should error XAER_NOTA
382      xa_end xa_success 11;
383      -- the following should error XAER_NOTA
384      xa_end xa_fail 11;
385      -- the following should error XAER_NOTA
386      xa_end xa_suspend 11;
387      -- the following should error XAER_NOTA
388      xa_prepare 11;
389      -- the following should error XAER_NOTA
390      xa_commit xa_1phase 11;
391      -- the following should error XAER_NOTA
392      xa_commit xa_2phase 11;
393      -- the following should error XAER_NOTA
394      xa_rollback 11;
395      -- the following should error XAER_NOTA
396      xa_forget 11;
397      </code>
398      */
399     private static void noTransaction(XADataSource xads) {
400         System.out.println("noTransaction");
401         try {
402             XAConnection xac = xads.getXAConnection();
403             XAResource xar = xac.getXAResource();
404
405             Xid xid11 = XATestUtil.getXid(11, 3, 128);
406
407             try {
408                 xar.start(xid11, XAResource.TMJOIN);
409             } catch (XAException e) {
410                 if (e.errorCode != XAException.XAER_NOTA)
411                     throw e;
412             }
413
414             try {
415                 xar.start(xid11, XAResource.TMRESUME);
416             } catch (XAException e) {
417                 if (e.errorCode != XAException.XAER_NOTA)
418                     throw e;
419             }
420
421             try {
422                 xar.end(xid11, XAResource.TMSUCCESS);
423             } catch (XAException e) {
424                 if (e.errorCode != XAException.XAER_NOTA)
425                     throw e;
426             }
427             try {
428                 xar.end(xid11, XAResource.TMFAIL);
429             } catch (XAException e) {
430                 if (e.errorCode != XAException.XAER_NOTA)
431                     throw e;
432             }
433
434             try {
435                 xar.end(xid11, XAResource.TMSUSPEND);
436             } catch (XAException e) {
437                 if (e.errorCode != XAException.XAER_NOTA)
438                     throw e;
439             }
440
441             try {
442                 xar.prepare(xid11);
443             } catch (XAException e) {
444                 if (e.errorCode != XAException.XAER_NOTA)
445                     throw e;
446             }
447             try {
448                 xar.commit(xid11, false);
449             } catch (XAException e) {
450                 if (e.errorCode != XAException.XAER_NOTA)
451                     throw e;
452             }
453             try {
454                 xar.commit(xid11, true);
455             } catch (XAException e) {
456                 if (e.errorCode != XAException.XAER_NOTA)
457                     throw e;
458             }
459             try {
460                 xar.rollback(xid11);
461             } catch (XAException e) {
462                 if (e.errorCode != XAException.XAER_NOTA)
463                     throw e;
464             }
465             try {
466                 xar.forget(xid11);
467             } catch (XAException e) {
468                 if (e.errorCode != XAException.XAER_NOTA)
469                     throw e;
470             }
471         } catch (SQLException e) {
472             TestUtil.dumpSQLExceptions(e);
473         } catch (XAException e) {
474             XATestUtil.dumpXAException("noTransaction", e);
475         }
476     }
477
478     /**
479      * Morph a connection between local anf global transactions.
480      */
481     private static void morph(XADataSource xads) {
482         System.out.println("morph");
483
484         try {
485             XAConnection xac = xads.getXAConnection();
486
487             XAResource xar = xac.getXAResource();
488
489             Connection conn = xac.getConnection();
490
491             /*
492              autocommit off;
493              insert into foo values (1);
494              select * from global_xactTable where gxid is not null order by gxid,username;
495              commit;
496              */
497             conn.setAutoCommit(false);
498             Statement s = conn.createStatement();
499             s.executeUpdate("insert into APP.foo values (2001)");
500             XATestUtil.showXATransactionView(conn);
501             conn.commit();
502
503             /*
504              autocommit on;
505              insert into foo values (2);
506              select * from global_xactTable where gxid is not null order by gxid,username;
507              
508              */
509
510             conn.setAutoCommit(true);
511             s.executeUpdate("insert into APP.foo values (2002)");
512             XATestUtil.showXATransactionView(conn);
513
514             /*
515              -- morph the connection to a global transaction
516              xa_start xa_noflags 1;
517              select * from global_xactTable where gxid is not null order by gxid,username;
518              insert into foo values (3);
519              */
520
521             Xid xid = XATestUtil.getXid(1001, 66, 13);
522             xar.start(xid, XAResource.TMNOFLAGS);
523             XATestUtil.showXATransactionView(conn);
524             s.executeUpdate("insert into APP.foo values (2003)");
525
526             /*
527              -- disallowed
528              commit;
529              -- disallowed
530              rollback;
531              -- disallowed
532              autocommit on;
533              -- OK
534              autocommit off;
535              */
536             try {
537                 conn.commit();
538                 System.out.println("FAIL: commit allowed in global xact");
539             } catch (SQLException e) {
540             }
541
542             try {
543                 conn.rollback();
544                 System.out.println("FAIL: roll back allowed in global xact");
545             } catch (SQLException e) {
546             }
547             try {
548                 conn.setAutoCommit(true);
549                 System.out
550                         .println("FAIL: setAutoCommit(true) allowed "+
551                  "in global xact");
552             } catch (SQLException e) {
553             }
554             try {
555                 conn.setSavepoint();
556                 System.out
557                     .println("FAIL: setSavepoint() allowed in global xact");
558             } catch (SQLException e) {}
559             try {
560                 conn.setSavepoint("badsavepoint");
561                 System.out
562                     .println("FAIL: setAutoCommit(String) allowed in "+
563                              "global xact");
564             } catch (SQLException e) {}
565
566             conn.setAutoCommit(false);
567
568             // s was created in local mode so it has holdibilty
569 // set, will execute but ResultSet will have close on commit
570
571             if (TestUtil.isDerbyNetClientFramework()) { // DERBY-1158
572 s.executeQuery("select * from APP.foo where A >= 2000").close();
573             System.out.println("OK: query with holdable statement");
574             }
575             s.close();
576             
577             
578             s = conn.createStatement();
579             boolean holdable = s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT;
580             System.out.println("Statement created in global has holdabilty: "
581                     + holdable);
582
583             /*
584              select * from foo;
585              xa_end xa_success 1;
586              xa_prepare 1;
587              */
588             ResultSet rs = s
589                     .executeQuery("select * from APP.foo where A >= 2000");
590             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
591             rs.close();
592
593             xar.end(xid, XAResource.TMSUCCESS);
594             xar.prepare(xid);
595
596             /*
597              -- dup id
598              xa_start xa_noflags 1;
599              */
600             try {
601                 xar.start(xid, XAResource.TMNOFLAGS);
602                 System.out.println("FAIL - start with duplicate XID");
603             } catch (XAException e) {
604                 if (e.errorCode != XAException.XAER_DUPID)
605                     throw e;
606             }
607
608             /*
609              xa_start xa_noflags 2;
610              -- still should disallow autommit;
611              autocommit on;
612              -- still should disallow commit and rollback
613              commit;
614              rollback;
615              select * from global_xactTable where gxid is not null order by gxid,username;
616              xa_end xa_suspend 2;
617              */
618
619             Xid xid2 = XATestUtil.getXid(1002, 23, 3);
620             xar.start(xid2, XAResource.TMNOFLAGS);
621             try {
622                 conn.commit();
623                 System.out.println("FAIL: commit allowed in global xact");
624             } catch (SQLException e) {
625             }
626             try {
627                 conn.rollback();
628                 System.out.println("FAIL: roll back allowed in global xact");
629             } catch (SQLException e) {
630             }
631             try {
632                 conn.setAutoCommit(true);
633                 System.out
634                         .println("FAIL: setAutoCommit(true) allowed in global xact");
635             } catch (SQLException e) {
636             }
637             conn.setAutoCommit(false);
638
639             xar.end(xid2, XAResource.TMSUSPEND);
640
641             /*
642              -- get local connection again
643              xa_getconnection;
644
645              insert into foo values (5);
646
647              -- autocommit should be on by default;
648              commit;
649
650              autocommit off;
651              insert into foo values (6);
652
653              -- commit and rollback is allowed on local connection
654              rollback;
655
656              insert into foo values (6);
657              commit;
658              */
659             conn = xac.getConnection();
660             s = conn.createStatement();
661             s.executeUpdate("insert into APP.foo values (2005)");
662             conn.commit();
663             conn.setAutoCommit(false);
664             s.executeUpdate("insert into APP.foo values (2006)");
665             conn.rollback();
666             s.executeUpdate("insert into APP.foo values (2007)");
667             conn.commit();
668
669             XATestUtil.showXATransactionView(conn);
670             /*
671              -- I am still able to commit other global transactions while I am attached to a
672              -- local transaction.
673              xa_commit xa_2phase 1;
674              xa_end xa_success 2;
675              xa_rollback 2;
676              */
677             xar.commit(xid, false);
678             xar.end(xid2, XAResource.TMSUCCESS);
679             xar.rollback(xid2);
680
681             XATestUtil.showXATransactionView(conn);
682             rs = s.executeQuery("select * from APP.foo where A >= 2000");
683             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
684             rs.close();
685
686             conn.close();
687
688             /*
689              xa_getconnection;
690              select * from global_xactTable where gxid is not null order by gxid,username;
691              select * from foo;
692              autocommit off;
693              delete from foo;
694              */
695             conn = xac.getConnection();
696             conn.setAutoCommit(false);
697             s = conn.createStatement();
698             s.executeUpdate("delete from app.foo");
699             rs = s.executeQuery("select * from APP.foo");
700             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
701             rs.close();
702
703             // DERBY-1004
704 if (TestUtil.isDerbyNetClientFramework()) {
705                 System.out.println("DERBY-1004 Call conn.rollback to avoid exception with client");
706                 conn.rollback();
707             }
708             /*
709              -- yanking a local connection away should rollback the changes
710              */
711             conn = xac.getConnection();
712             conn.setAutoCommit(false);
713             s = conn.createStatement();
714             rs = s.executeQuery("select * from APP.foo where A >= 2000");
715             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
716             rs.close();
717
718             /*
719              -- cannot morph it if the local transaction is not idle
720              xa_start xa_noflags 3;
721              commit;
722              -- now morph it to a global transaction
723              xa_start xa_noflags 3;
724              */
725             Xid xid3 = XATestUtil.getXid(1003, 27, 9);
726             try {
727                 xar.start(xid3, XAResource.TMNOFLAGS);
728                 System.out.println("FAIL XAResource.start on a global transaction with an active local transaction (autocommit false)");
729             } catch (XAException xae) {
730                 if (xae.errorCode != XAException.XAER_OUTSIDE)
731                     throw xae;
732                 System.out.println("Correct XAException on starting a global transaction with an active local transaction (autocommit false)");
733             }
734             conn.commit();
735             xar.start(xid3, XAResource.TMNOFLAGS);
736
737             /*
738              -- now I shouldn't be able to yank it
739              xa_getconnection;
740              */
741             if (TestUtil.isDerbyNetClientFramework()) {
742                 System.out.println("DERBY-341 - Client skipping XAConnection with active local transaction");
743             } else {
744             try {
745                 xac.getConnection();
746                 System.out
747                         .println("FAIL: getConnection with active global xact");
748             } catch (SQLException sqle) {
749                 TestUtil.dumpSQLExceptions(sqle, true);
750             }
751             }
752             /*
753              select * from foo;
754              delete from foo;
755
756              xa_end xa_fail 3;
757              xa_rollback 3;
758
759              -- local connection again
760              xa_getconnection;
761              select * from global_xactTable where gxid is not null order by gxid,username;
762              select * from foo;
763              */
764             s = conn.createStatement();
765             s.executeUpdate("delete from APP.foo");
766             rs = s.executeQuery("select * from APP.foo where A >= 2000");
767             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
768             rs.close();
769             try {
770                 xar.end(xid3, XAResource.TMFAIL);
771             } catch (XAException e) {
772                 if (e.errorCode != XAException.XA_RBROLLBACK)
773                     throw e;
774             }
775             xar.rollback(xid3);
776
777             conn = xac.getConnection();
778             s = conn.createStatement();
779             rs = s.executeQuery("select * from APP.foo where A >= 2000");
780             JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
781             rs.close();
782
783             s.close();
784             conn.close();
785
786         } catch (SQLException e) {
787             TestUtil.dumpSQLExceptions(e);
788             e.printStackTrace(System.out);
789         } catch (XAException e) {
790             XATestUtil.dumpXAException("morph", e);
791         }
792
793     }
794     
795     /**
796      * Derby-966 holdability and global/location transactions.
797      * (work in progress)
798      */
799     private static void derby966(XADataSource xads)
800     {
801         System.out.println("derby966");
802         
803         try {
804             XAConnection xac = xads.getXAConnection();
805             XAResource xar = xac.getXAResource();
806
807             Xid xid = XATestUtil.getXid(996, 9, 48);
808             
809             Connection conn = xac.getConnection();
810             
811             // Obtain Statements and PreparedStatements
812 // with all the holdability options.
813
814             showHoldStatus("Local ", conn);
815            
816             Statement sdh = conn.createStatement();
817             showHoldStatus("Local(held) default ", sdh);
818             checkHeldRS(conn, sdh, sdh.executeQuery("select * from app.foo"));
819             PreparedStatement psdh = conn.prepareStatement("SELECT * FROM APP.FOO");
820             PreparedStatement psdh_d = conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99");
821             showHoldStatus("Local(held) default Prepared", psdh);
822             checkHeldRS(conn, psdh, psdh.executeQuery());
823             
824             Statement shh = conn.createStatement(
825                     ResultSet.TYPE_FORWARD_ONLY,
826                     ResultSet.CONCUR_READ_ONLY,
827                     ResultSet.HOLD_CURSORS_OVER_COMMIT);
828             showHoldStatus("Local(held) held ", shh);
829             checkHeldRS(conn, shh, shh.executeQuery("select * from app.foo"));
830             PreparedStatement pshh =
831                 conn.prepareStatement("SELECT * FROM APP.FOO",
832                         ResultSet.TYPE_FORWARD_ONLY,
833                         ResultSet.CONCUR_READ_ONLY,
834                         ResultSet.HOLD_CURSORS_OVER_COMMIT);
835             PreparedStatement pshh_d =
836                 conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
837                         ResultSet.TYPE_FORWARD_ONLY,
838                         ResultSet.CONCUR_READ_ONLY,
839                         ResultSet.HOLD_CURSORS_OVER_COMMIT);
840             showHoldStatus("Local(held) held Prepared", pshh);
841             checkHeldRS(conn, pshh, pshh.executeQuery());
842                         
843             Statement sch = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
844                     ResultSet.CONCUR_READ_ONLY,
845                     ResultSet.CLOSE_CURSORS_AT_COMMIT);
846             showHoldStatus("Local(held) close ", sch);
847             checkHeldRS(conn, sch, sch.executeQuery("select * from app.foo"));
848             PreparedStatement psch =
849                 conn.prepareStatement("SELECT * FROM APP.FOO",
850                         ResultSet.TYPE_FORWARD_ONLY,
851                         ResultSet.CONCUR_READ_ONLY,
852                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
853             PreparedStatement psch_d =
854                 conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
855                         ResultSet.TYPE_FORWARD_ONLY,
856                         ResultSet.CONCUR_READ_ONLY,
857                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
858              showHoldStatus("Local(held) close Prepared", psch);
859             checkHeldRS(conn, psch, psch.executeQuery());
860          
861             // set the connection's holdabilty to false
862 conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
863             
864             Statement sdc = conn.createStatement();
865             showHoldStatus("Local(close) default ", sdc);
866             checkHeldRS(conn, sdc, sdc.executeQuery("select * from app.foo"));
867             PreparedStatement psdc = conn.prepareStatement("SELECT * FROM APP.FOO");
868             PreparedStatement psdc_d = conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99");
869             showHoldStatus("Local(close) default Prepared", psdc);
870             checkHeldRS(conn, psdc, psdc.executeQuery());
871  
872             Statement shc = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
873                     ResultSet.CONCUR_READ_ONLY,
874                     ResultSet.HOLD_CURSORS_OVER_COMMIT);
875             showHoldStatus("Local(close) held ", shc);
876             checkHeldRS(conn, shc, shc.executeQuery("select * from app.foo"));
877             PreparedStatement pshc =
878                 conn.prepareStatement("SELECT * FROM APP.FOO",
879                         ResultSet.TYPE_FORWARD_ONLY,
880                         ResultSet.CONCUR_READ_ONLY,
881                         ResultSet.HOLD_CURSORS_OVER_COMMIT);
882             PreparedStatement pshc_d =
883                 conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
884                         ResultSet.TYPE_FORWARD_ONLY,
885                         ResultSet.CONCUR_READ_ONLY,
886                         ResultSet.HOLD_CURSORS_OVER_COMMIT);
887             showHoldStatus("Local(close) held Prepared", pshc);
888             checkHeldRS(conn, pshc, pshc.executeQuery());
889             
890             Statement scc = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
891                     ResultSet.CONCUR_READ_ONLY,
892                     ResultSet.CLOSE_CURSORS_AT_COMMIT);
893             showHoldStatus("Local(close) close ", scc);
894             checkHeldRS(conn, scc, scc.executeQuery("select * from app.foo"));
895             PreparedStatement pscc =
896                 conn.prepareStatement("SELECT * FROM APP.FOO",
897                         ResultSet.TYPE_FORWARD_ONLY,
898                         ResultSet.CONCUR_READ_ONLY,
899                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
900             PreparedStatement pscc_d =
901                 conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
902                         ResultSet.TYPE_FORWARD_ONLY,
903                         ResultSet.CONCUR_READ_ONLY,
904                         ResultSet.CLOSE_CURSORS_AT_COMMIT);
905              showHoldStatus("Local(close) close Prepared", pscc);
906             checkHeldRS(conn, pscc, pscc.executeQuery());
907             
908             // Revert back to holdable
909 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
910             
911             ResultSet rs = sdh.executeQuery("SELECT * FROM APP.FOO");
912             rs.next(); System.out.println("BGBC " + rs.getInt(1));
913             conn.commit();
914             rs.next(); System.out.println("BGAC " + rs.getInt(1));
915             rs.close();
916            
917             // ensure a transaction is active to test DERBY-1025
918 rs = sdh.executeQuery("SELECT * FROM APP.FOO");
919            
920             // This switch to global is ok because conn
921 // is in auto-commit mode, thus the start performs
922 // an implicit commit to complete the local transaction.
923
924             System.out.println("START GLOBAL TRANSACTION");
925             // start a global xact and test those statements.
926 xar.start(xid, XAResource.TMNOFLAGS);
927             
928             // Statements not returning ResultSet's should be ok
929 sdh.executeUpdate("DELETE FROM APP.FOO where A < -99");
930             shh.executeUpdate("DELETE FROM APP.FOO where A < -99");
931             sch.executeUpdate("DELETE FROM APP.FOO where A < -99");
932            
933             ArrayList openRS = new ArrayList ();
934             
935             // Statements obtained while default was hold.
936 // All should work, holability will be downgraded
937 // to close on commit for those Statements with hold set.
938 openRS.add(sdh.executeQuery("SELECT * FROM APP.FOO"));
939             openRS.add(shh.executeQuery("SELECT * FROM APP.FOO"));
940             openRS.add(sch.executeQuery("SELECT * FROM APP.FOO"));
941
942             
943             // PreparedStatements obtained while default was hold.
944 // Holdability should be downgraded.
945 openRS.add(psdh.executeQuery());
946             openRS.add(pshh.executeQuery());
947             openRS.add(psch.executeQuery());
948             
949             // Statements not returning ResultSet's should be ok
950 psdh_d.executeUpdate();
951             pshh_d.executeUpdate();
952             psch_d.executeUpdate();
953  
954             // Statements not returning ResultSet's should be ok
955 sdc.executeUpdate("DELETE FROM APP.FOO where A < -99");
956             shc.executeUpdate("DELETE FROM APP.FOO where A < -99");
957             scc.executeUpdate("DELETE FROM APP.FOO where A < -99");
958  
959             // Statements obtained while default was close.
960 // all should return close on commit ResultSets
961 openRS.add(sdc.executeQuery("SELECT * FROM APP.FOO"));
962             openRS.add(shc.executeQuery("SELECT * FROM APP.FOO"));
963             openRS.add(scc.executeQuery("SELECT * FROM APP.FOO"));
964             
965             // PreparedStatements obtained while default was close.
966 openRS.add(psdc.executeQuery());
967             openRS.add(pshc.executeQuery());
968             openRS.add(pscc.executeQuery());
969             
970             // Statements not returning ResultSet's should be ok
971 psdc_d.executeUpdate();
972             pshc_d.executeUpdate();
973             pscc_d.executeUpdate();
974             
975             // All the ResultSets should be open. Run a simple
976 // test, clearWarnings throws an error if the ResultSet
977 // is closed. Also would be nice here to use the new
978 // JDBC 4.0 method getHoldabilty to ensure the
979 // holdability is reported correctly.
980 int orsCount = 0;
981             for (Iterator i = openRS.iterator(); i.hasNext();) {
982                 ResultSet ors = (ResultSet ) i.next();
983                 ors.clearWarnings();
984                 orsCount++;
985             }
986             System.out.println("Global transaction open ResultSets " + orsCount);
987
988                    
989             // Test we cannot switch the connection to holdable
990 try {
991                 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
992                 System.out.println("FAIL - set holdability in global xact.");
993             } catch (SQLException sqle)
994             {
995                 TestUtil.dumpSQLExceptions(sqle, true);
996             }
997             
998             // JDBC 4.0 (proposed final draft) section allows
999 // drivers to change the holdability when creating
1000 // a Statement object and attach a warning to the Connection.
1001 Statement sglobalhold = conn.createStatement(
1002                    ResultSet.TYPE_FORWARD_ONLY,
1003                    ResultSet.CONCUR_READ_ONLY,
1004                    ResultSet.HOLD_CURSORS_OVER_COMMIT);
1005            showHoldStatus("Global createStatement(hold)", sglobalhold);
1006            sglobalhold.close();
1007
1008            PreparedStatement psglobalhold = conn.prepareStatement(
1009                "SELECT * FROM APP.FOO",
1010                ResultSet.TYPE_FORWARD_ONLY,
1011                ResultSet.CONCUR_READ_ONLY,
1012                ResultSet.HOLD_CURSORS_OVER_COMMIT);
1013            showHoldStatus("Global prepareStatement(hold)", psglobalhold);
1014            psglobalhold.close();
1015            
1016            if (!TestUtil.isDerbyNetClientFramework()) { //DERBY-1158 in progress
1017 // Show the holdability for all the Statements while
1018 // in the global transaction, all should be close on commit.
1019 showHoldStatus("Global xact Statement sdh ", sdh);
1020            showHoldStatus("Global xact Statement shh ", shh);
1021            showHoldStatus("Global xact Statement sch ", sch);
1022            
1023            showHoldStatus("Global xact Statement psdh ", psdh);
1024            showHoldStatus("Global xact Statement pshh ", pshh);
1025            showHoldStatus("Global xact Statement psch ", psch);
1026            
1027            showHoldStatus("Global xact Statement sdc ", sdc);
1028            showHoldStatus("Global xact Statement shc ", shc);
1029            showHoldStatus("Global xact Statement scc ", scc);
1030 
1031            showHoldStatus("Global xact Statement psdh_d ", psdh_d);
1032            showHoldStatus("Global xact Statement pshh_d ", pshh_d);
1033            showHoldStatus("Global xact Statement psch_d ", psch_d);
1034            }
1035 
1036        
1037            xar.end(xid, XAResource.TMSUCCESS);
1038            if (xar.prepare(xid) != XAResource.XA_RDONLY)
1039                System.out.println("FAIL prepare didn't indicate r/o");
1040            
1041            // All the ResultSets should be closed. Run a simple
1042 // test, clearWarnings throws an error if the ResultSet
1043 // is closed.
1044 int crsCount = 0;
1045            for (Iterator i = openRS.iterator(); i.hasNext();) {
1046                ResultSet crs = (ResultSet ) i.next();
1047                try {
1048                    crs.clearWarnings();
1049                } catch (SQLException sqle) {
1050                }
1051                crsCount++;
1052            }
1053            System.out.println("After global transaction closed ResultSets " + crsCount);
1054
1055            
1056            // Check the statements revert to holdable as required.
1057 showHoldStatus("Global xact Statement sdh ", sdh);
1058            showHoldStatus("Global xact Statement shh ", shh);
1059            showHoldStatus("Global xact Statement sch ", sch);
1060            
1061            showHoldStatus("Global xact Statement psdh ", psdh);
1062            showHoldStatus("Global xact Statement pshh ", pshh);
1063            showHoldStatus("Global xact Statement psch ", psch);
1064 
1065            showHoldStatus("Global xact Statement sdc ", sdc);
1066            showHoldStatus("Global xact Statement shc ", shc);
1067            showHoldStatus("Global xact Statement scc ", scc);
1068            
1069            showHoldStatus("Global xact Statement psdh_d ", psdh_d);
1070            showHoldStatus("Global xact Statement pshh_d ", pshh_d);
1071            showHoldStatus("Global xact Statement psch_d ", psch_d);
1072            
1073            conn.close();
1074            
1075            System.out.println("derby966 complete");
1076                
1077        } catch (SQLException e) {
1078            TestUtil.dumpSQLExceptions(e);
1079            e.printStackTrace(System.out);
1080        } catch (XAException e) {
1081            XATestUtil.dumpXAException("derby966", e);
1082        }
1083    }
1084    
1085    /**
1086     * Check the held state of a ResultSet by fetching
1087     * one row, executing a commit and then fetching the
1088     * next. Checks the held state matches the behaviour.
1089    */
1090    private static void checkHeldRS(Connection conn,
1091            Statement s, ResultSet rs) throws SQLException
1092    {
1093        if (s.getConnection() != conn)
1094            System.out.println("FAIL - mismatched statement & Connection");
1095        if (rs.getStatement() != s)
1096        {
1097            // DERBY-1009
1098 System.out.println("FAIL - mismatched statement & ResultSet");
1099            System.out.println("Statement class " + s.getClass());
1100            System.out.println("ResultSet' Statements class " + rs.getStatement().getClass());
1101         }
1102
1103        boolean held = s.getResultSetHoldability() ==
1104            ResultSet.HOLD_CURSORS_OVER_COMMIT;
1105        
1106        System.out.println("ResultSet " + holdStatus(s.getResultSetHoldability()));
1107        
1108        rs.next();
1109        System.out.println(" BC A=" + rs.getInt(1));
1110        conn.commit();
1111       
1112        try {
1113            while (rs.next())
1114            {
1115                rs.getInt(1);
1116                System.out.println(" AC A=" + rs.getInt(1));
1117            }
1118           if (!held)
1119               System.out.println("FAIL: non-held cursor not closed by commit");
1120        } catch (SQLException sqle)
1121        {
1122            boolean ok = !held;
1123            boolean showError = true;
1124            if (ok) {
1125                if (TestUtil.isEmbeddedFramework()) {
1126                    if ("XCL16".equals(sqle.getSQLState()))
1127                        showError = false;
1128                } else if (TestUtil.isDerbyNetClientFramework()) {
1129                    // No SQL state yet from client error.
1130 showError = false;
1131                }
1132            }
1133            if (showError)
1134                TestUtil.dumpSQLExceptions(sqle, ok);
1135            else if (ok)
1136                System.out.println("Non-held ResultSet correctly closed after commit");
1137        }
1138        
1139        rs.close();
1140        conn.commit();
1141    }
1142    
1143    /**
1144     * Show the held status of the Statement.
1145    */
1146    private static void showHoldStatus(String tag, Statement s) throws SQLException
1147    {
1148        System.out.println(tag + "Statement holdable " +
1149                holdStatus(s.getResultSetHoldability()));
1150        SQLWarning w = s.getConnection().getWarnings();
1151        while (w != null)
1152        {
1153            System.out.println(w.getSQLState() + " :" + w.toString());
1154            w = w.getNextWarning();
1155        }
1156        s.getConnection().clearWarnings();
1157        
1158    }
1159    /**
1160     * Show the held status of the Connection.
1161    */
1162    private static void showHoldStatus(String tag, Connection conn) throws SQLException
1163    {
1164        System.out.println(tag + "Connection holdable " +
1165                holdStatus(conn.getHoldability()));
1166    }
1167    
1168    private static String holdStatus(int holdability)
1169    {
1170        String s;
1171        switch (holdability)
1172        {
1173        case ResultSet.CLOSE_CURSORS_AT_COMMIT:
1174            s = "CLOSE_CURSORS_AT_COMMIT ";
1175            break;
1176        case ResultSet.HOLD_CURSORS_OVER_COMMIT:
1177            s = "HOLD_CURSORS_OVER_COMMIT ";
1178            break;
1179        default:
1180            s = "UNKNOWN HOLDABILITY ";
1181            break;
1182        }
1183        
1184        return s + Integer.toString(holdability);
1185    }
1186    
1187    /*
1188     * 5 interleaving transactions.
1189     * Taken from the SQL test xaANotherTest.
1190     * <code>
1191xa_connect user 'mamta' password 'mamta' ;
1192
1193-- global connection 1
1194xa_start xa_noflags 1;
1195xa_getconnection;
1196insert into APP.foo values (1);
1197xa_end xa_suspend 1;
1198
1199-- global connection 2
1200xa_start xa_noflags 2;
1201insert into APP.foo values (2);
1202xa_end xa_suspend 2;
1203
1204-- global connection 3
1205xa_start xa_noflags 3;
1206insert into APP.foo values (3);
1207xa_end xa_suspend 3;
1208
1209-- global connection 4
1210xa_start xa_noflags 4;
1211insert into APP.foo values (4);
1212xa_end xa_suspend 4;
1213
1214-- global connection 5
1215xa_start xa_noflags 5;
1216insert into APP.foo values (5);
1217xa_end xa_suspend 5;
1218
1219xa_start xa_resume 1;
1220insert into APP.foo values (11);
1221xa_end xa_suspend 1;
1222
1223xa_start xa_resume 5;
1224insert into APP.foo values (55);
1225xa_end xa_suspend 5;
1226
1227xa_start xa_resume 2;
1228insert into APP.foo values (22);
1229xa_end xa_suspend 2;
1230
1231xa_start xa_resume 4;
1232insert into APP.foo values (44);
1233xa_end xa_suspend 4;
1234
1235xa_start xa_resume 3;
1236insert into APP.foo values (33);
1237xa_end xa_suspend 3;
1238
1239-- prepare all the global connections except the first one. This way, we will see all
1240-- the global transactions prepared so far after the database shutdown and restart.
1241xa_end xa_success 2;
1242xa_prepare 2;
1243xa_end xa_success 3;
1244xa_prepare 3;
1245xa_end xa_success 4;
1246xa_prepare 4;
1247xa_end xa_success 5;
1248xa_prepare 5;
1249
1250     * </code>
1251     */
1252    private static void interleavingTransactions5(XADataSource xads) throws SQLException
1253    {
1254        System.out.println("interleavingTransactions5");
1255        
1256        XAConnection xac = xads.getXAConnection("mamta", "mamtapwd");
1257        
1258    }
1259
1260    private static void cleanUp(Connection conn) throws SQLException
1261    {
1262        String testObjects[] = { "view XATESTUTIL.global_xactTable",
1263                                 "schema XATESTUTIL restrict", "table app.foo", "table foo" };
1264        Statement stmt = conn.createStatement();
1265        TestUtil.cleanUpTest(stmt, testObjects);
1266        conn.commit();
1267        stmt.close();
1268    }
1269 
1270}
1271

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags