0001: /*
0002:
0003: Derby - Class org.apache.derby.impl.services.bytecode.CodeChunk
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
0023:
0024: import java.sql.Connection;
0025: import java.sql.PreparedStatement;
0026: import java.sql.ResultSet;
0027: import java.sql.SQLException;
0028: import java.sql.SQLWarning;
0029: import java.sql.Statement;
0030: import java.util.ArrayList;
0031: import java.util.Iterator;
0032: import java.util.Properties;
0033:
0034: import javax.sql.XAConnection;
0035: import javax.sql.XADataSource;
0036: import javax.transaction.xa.XAException;
0037: import javax.transaction.xa.XAResource;
0038: import javax.transaction.xa.Xid;
0039:
0040: import org.apache.derby.tools.JDBCDisplayUtil;
0041: import org.apache.derby.tools.ij;
0042: import org.apache.derbyTesting.functionTests.util.TestUtil;
0043: import org.apache.derbyTesting.functionTests.util.XATestUtil;
0044:
0045: /**
0046: * XATests harvested from SQL XA tests.
0047: * Modified so that they can be run with NetworkServer.
0048: */
0049: public class XATest {
0050:
0051: /**
0052: * Run all the tests.
0053: */
0054: public static void main(String[] args) throws Exception {
0055: ij.getPropertyArg(args);
0056: Connection dmc = ij.startJBMS();
0057:
0058: showHoldStatus("initial ", dmc);
0059:
0060: XATestUtil.createXATransactionView(dmc);
0061: dmc.close();
0062:
0063: XADataSource dsx = TestUtil.getXADataSource(cleanProperties());
0064:
0065: // tests originally from xaSimplePositive.sql
0066: singleConnectionOnePhaseCommit(dsx);
0067: xaShutdown();
0068: interleavingTransactions(dsx);
0069:
0070: xaShutdown();
0071:
0072: // tests originally from xaStateTran.sql
0073: noTransaction(dsx);
0074:
0075: // test originally from xaMorph.sql
0076: morph(dsx);
0077:
0078: // DERBY-966 holdability testing
0079: derby966(dsx);
0080:
0081: // for cleaning up, make a clean new connection
0082: Connection dmc2 = ij.startJBMS();
0083: cleanUp(dmc2);
0084:
0085: System.out.println("XATest complete");
0086: }
0087:
0088: /**
0089: * Get the basic set of properties for an XADataSource.
0090: * Only sets databaseName to wombat.
0091: * @return
0092: */
0093: private static Properties cleanProperties() {
0094: Properties dsAttrs = new Properties();
0095: dsAttrs.setProperty("databaseName", "wombat");
0096: return dsAttrs;
0097: }
0098:
0099: /**
0100: * Shutdown the database through an XADataSource.
0101: */
0102: private static void xaShutdown() {
0103:
0104: Properties dsAttrs = cleanProperties();
0105:
0106: if (TestUtil.isEmbeddedFramework())
0107: dsAttrs.put("shutdownDatabase", "shutdown");
0108: else
0109: dsAttrs.put("connectionAttributes", "shutdown=true");
0110:
0111: XADataSource dsx = TestUtil.getXADataSource(dsAttrs);
0112:
0113: try {
0114: dsx.getXAConnection().getConnection();
0115: } catch (SQLException sqle) {
0116: if ("08006".equals(sqle.getSQLState()))
0117: return;
0118: TestUtil.dumpSQLExceptions(sqle);
0119: }
0120: System.out.println("FAIL: no exception on shutdown");
0121: }
0122:
0123: /*
0124: ** Test cases
0125: */
0126:
0127: /**
0128: * A single connection and 1 phase commit.
0129: *
0130:
0131: Original "SQL" from xaSimplePositive.sql
0132: <code>
0133: xa_connect ;
0134: xa_start xa_noflags 0;
0135: xa_getconnection;
0136: drop table foo;
0137: create table foo (a int);
0138: insert into foo values (0);
0139: select * from foo;
0140: run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
0141: select * from global_xactTable where gxid is not null order by gxid;
0142: xa_end xa_success 0;
0143: xa_commit xa_1phase 0;
0144:
0145: xa_datasource 'wombat' shutdown;
0146: </code>
0147: * @throws SQLException
0148: * @throws XAException
0149: */
0150: private static void singleConnectionOnePhaseCommit(XADataSource xads) {
0151: System.out.println("singleConnectionOnePhaseCommit");
0152: try {
0153: XAConnection xac = xads.getXAConnection();
0154:
0155: XAResource xar = xac.getXAResource();
0156:
0157: Xid xid = XATestUtil.getXid(0, 32, 46);
0158:
0159: xar.start(xid, XAResource.TMNOFLAGS);
0160:
0161: Connection conn = xac.getConnection();
0162:
0163: showHoldStatus("XA ", conn);
0164:
0165: Statement s = conn.createStatement();
0166: showHoldStatus("XA ", s);
0167:
0168: s.execute("create table foo (a int)");
0169: s.executeUpdate("insert into foo values (0)");
0170:
0171: ResultSet rs = s.executeQuery("select * from foo");
0172: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0173: rs.close();
0174:
0175: XATestUtil.showXATransactionView(conn);
0176:
0177: s.close();
0178: xar.end(xid, XAResource.TMSUCCESS);
0179:
0180: // 1 phase commit
0181: xar.commit(xid, true);
0182:
0183: conn.close();
0184: xac.close();
0185:
0186: } catch (SQLException sqle) {
0187: TestUtil.dumpSQLExceptions(sqle);
0188: sqle.printStackTrace(System.out);
0189: } catch (XAException e) {
0190: XATestUtil.dumpXAException(
0191: "singleConnectionOnePhaseCommit", e);
0192: }
0193: }
0194:
0195: /*
0196: * Two interleaving transaction and prepare/commit prepare/rollback.
0197: *
0198: * (original test said two connections but only one connection was opened)
0199:
0200: <code>
0201: xa_datasource 'wombat';
0202: xa_connect user 'sku' password 'testxa' ;
0203:
0204: xa_start xa_noflags 1;
0205: xa_getconnection;
0206: insert into APP.foo values (1);
0207: xa_end xa_suspend 1;
0208:
0209: xa_start xa_noflags 2;
0210: insert into APP.foo values (2);
0211: xa_end xa_suspend 2;
0212:
0213: xa_start xa_resume 1;
0214: insert into APP.foo values (3);
0215: xa_end xa_suspend 1;
0216:
0217: xa_start xa_resume 2;
0218: insert into APP.foo values (4);
0219: select * from APP.global_xactTable where gxid is not null order by gxid;
0220: -- this prepare won't work since transaction 1 has been suspended - XA_PROTO
0221: xa_prepare 1;
0222:
0223: select * from APP.global_xactTable where gxid is not null order by gxid;
0224: xa_end xa_success 2;
0225:
0226: -- this assumes a resume
0227: xa_end xa_success 1;
0228: xa_prepare 1;
0229: xa_prepare 2;
0230:
0231: -- both transactions should be prepared
0232: select * from APP.global_xactTable where gxid is not null order by gxid;
0233:
0234: -- NOTE: The following call to "xa_recover xa_startrscan" is apt to
0235: -- return the result set rows in reverse order when changes to
0236: -- the Derby engine affect the number of transactions that it takes
0237: -- to create a database. The transactions are stored in a hash table
0238: -- based on a global and local id, and when the number of transactions
0239: -- changes, the (internal) local id can change, which may lead to a
0240: -- change in the result set order. This order is determined by the
0241: -- JVM's hashing algorithm. Examples of changes to the engine that
0242: -- can affect this include ones that cause more commits or that
0243: -- change the amount of data being stored, such as changes to the
0244: -- metadata statements (which is what prompted this explanation in
0245: -- the first place). Ultimately, the problem is that there is no
0246: -- way to order the return values from "xa_recover" since it is an
0247: -- ij internal statement, not SQL...
0248: xa_recover xa_startrscan;
0249: xa_recover xa_noflags;
0250:
0251: xa_commit xa_2Phase 1;
0252: xa_rollback 2;
0253:
0254: -- check results
0255: xa_start xa_noflags 3;
0256: select * from APP.global_xactTable where gxid is not null order by gxid;
0257: select * from APP.foo;
0258: xa_end xa_success 3;
0259:
0260: xa_prepare 3;
0261:
0262: -- should fail with XA_NOTA because we prepared a read only transaction
0263: xa_commit xa_1Phase 3;
0264: disconnect;
0265: </code>
0266: */
0267: private static void interleavingTransactions(XADataSource xads) {
0268: System.out.println("interleavingTransactions");
0269: try {
0270: XAConnection xac = xads.getXAConnection("sku", "testxa");
0271: XAResource xar = xac.getXAResource();
0272:
0273: Xid xid1 = XATestUtil.getXid(1, 93, 18);
0274: Xid xid2 = XATestUtil.getXid(2, 45, 77);
0275:
0276: xar.start(xid1, XAResource.TMNOFLAGS);
0277:
0278: Connection conn = xac.getConnection();
0279:
0280: Statement s = conn.createStatement();
0281: s.executeUpdate("insert into APP.foo values (1)");
0282: xar.end(xid1, XAResource.TMSUSPEND);
0283:
0284: xar.start(xid2, XAResource.TMNOFLAGS);
0285: s.executeUpdate("insert into APP.foo values (2)");
0286: xar.end(xid2, XAResource.TMSUSPEND);
0287:
0288: xar.start(xid1, XAResource.TMRESUME);
0289: s.executeUpdate("insert into APP.foo values (3)");
0290: xar.end(xid1, XAResource.TMSUSPEND);
0291:
0292: xar.start(xid2, XAResource.TMRESUME);
0293: s.executeUpdate("insert into APP.foo values (4)");
0294:
0295: XATestUtil.showXATransactionView(conn);
0296:
0297: // this prepare won't work since
0298: // transaction 1 has been suspended - XA_PROTO
0299: try {
0300: xar.prepare(xid1);
0301: System.out
0302: .println("FAIL - prepare on suspended transaction");
0303: } catch (XAException e) {
0304: if (e.errorCode != XAException.XAER_PROTO)
0305: XATestUtil.dumpXAException(
0306: "FAIL - prepare on suspended transaction",
0307: e);
0308:
0309: }
0310:
0311: // check it was not prepared
0312: XATestUtil.showXATransactionView(conn);
0313:
0314: xar.end(xid2, XAResource.TMSUCCESS);
0315:
0316: xar.end(xid1, XAResource.TMSUCCESS);
0317:
0318: xar.prepare(xid1);
0319: xar.prepare(xid2);
0320:
0321: // both should be prepared.
0322: XATestUtil.showXATransactionView(conn);
0323:
0324: Xid[] recoveredStart = xar.recover(XAResource.TMSTARTRSCAN);
0325: System.out.println("recovered start "
0326: + recoveredStart.length);
0327: Xid[] recovered = xar.recover(XAResource.TMNOFLAGS);
0328: System.out.println("recovered " + recovered.length);
0329: Xid[] recoveredEnd = xar.recover(XAResource.TMENDRSCAN);
0330: System.out.println("recovered end " + recoveredEnd.length);
0331:
0332: for (int i = 0; i < recoveredStart.length; i++) {
0333: Xid xid = recoveredStart[i];
0334: if (xid.getFormatId() == 1) {
0335: // commit 1 with 2pc
0336: xar.commit(xid, false);
0337: } else if (xid.getFormatId() == 2) {
0338: xar.rollback(xid);
0339: } else {
0340: System.out.println("FAIL: unknown xact");
0341: }
0342: }
0343:
0344: // check the results
0345: Xid xid3 = XATestUtil.getXid(3, 2, 101);
0346: xar.start(xid3, XAResource.TMNOFLAGS);
0347: XATestUtil.showXATransactionView(conn);
0348: ResultSet rs = s.executeQuery("select * from APP.foo");
0349: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0350: rs.close();
0351: xar.end(xid3, XAResource.TMSUCCESS);
0352:
0353: int pr = xar.prepare(xid3);
0354: if (pr != XAResource.XA_RDONLY)
0355: System.out
0356: .println("FAIL - prepare on read only xact returned "
0357: + pr);
0358:
0359: try {
0360: xar.commit(xid3, true);
0361: System.out
0362: .println("FAIL - 2pc commit on read-only xact");
0363: } catch (XAException e) {
0364: if (e.errorCode != XAException.XAER_NOTA)
0365: throw e;
0366: }
0367:
0368: s.close();
0369: conn.close();
0370: xac.close();
0371: } catch (SQLException sqle) {
0372: TestUtil.dumpSQLExceptions(sqle);
0373: } catch (XAException e) {
0374: XATestUtil.dumpXAException("interleavingTransactions", e);
0375: }
0376: }
0377:
0378: /**
0379: Tests on INIT STATE (no tr
0380: Original SQL from xaStateTran.sql.
0381: <code>
0382:
0383: -- the following should error XAER_NOTA
0384: xa_start xa_join 11;
0385: -- the following should error XAER_NOTA
0386: xa_start xa_resume 11;
0387: -- the following should error XAER_NOTA
0388: xa_end xa_success 11;
0389: -- the following should error XAER_NOTA
0390: xa_end xa_fail 11;
0391: -- the following should error XAER_NOTA
0392: xa_end xa_suspend 11;
0393: -- the following should error XAER_NOTA
0394: xa_prepare 11;
0395: -- the following should error XAER_NOTA
0396: xa_commit xa_1phase 11;
0397: -- the following should error XAER_NOTA
0398: xa_commit xa_2phase 11;
0399: -- the following should error XAER_NOTA
0400: xa_rollback 11;
0401: -- the following should error XAER_NOTA
0402: xa_forget 11;
0403: </code>
0404: */
0405: private static void noTransaction(XADataSource xads) {
0406: System.out.println("noTransaction");
0407: try {
0408: XAConnection xac = xads.getXAConnection();
0409: XAResource xar = xac.getXAResource();
0410:
0411: Xid xid11 = XATestUtil.getXid(11, 3, 128);
0412:
0413: try {
0414: xar.start(xid11, XAResource.TMJOIN);
0415: } catch (XAException e) {
0416: if (e.errorCode != XAException.XAER_NOTA)
0417: throw e;
0418: }
0419:
0420: try {
0421: xar.start(xid11, XAResource.TMRESUME);
0422: } catch (XAException e) {
0423: if (e.errorCode != XAException.XAER_NOTA)
0424: throw e;
0425: }
0426:
0427: try {
0428: xar.end(xid11, XAResource.TMSUCCESS);
0429: } catch (XAException e) {
0430: if (e.errorCode != XAException.XAER_NOTA)
0431: throw e;
0432: }
0433: try {
0434: xar.end(xid11, XAResource.TMFAIL);
0435: } catch (XAException e) {
0436: if (e.errorCode != XAException.XAER_NOTA)
0437: throw e;
0438: }
0439:
0440: try {
0441: xar.end(xid11, XAResource.TMSUSPEND);
0442: } catch (XAException e) {
0443: if (e.errorCode != XAException.XAER_NOTA)
0444: throw e;
0445: }
0446:
0447: try {
0448: xar.prepare(xid11);
0449: } catch (XAException e) {
0450: if (e.errorCode != XAException.XAER_NOTA)
0451: throw e;
0452: }
0453: try {
0454: xar.commit(xid11, false);
0455: } catch (XAException e) {
0456: if (e.errorCode != XAException.XAER_NOTA)
0457: throw e;
0458: }
0459: try {
0460: xar.commit(xid11, true);
0461: } catch (XAException e) {
0462: if (e.errorCode != XAException.XAER_NOTA)
0463: throw e;
0464: }
0465: try {
0466: xar.rollback(xid11);
0467: } catch (XAException e) {
0468: if (e.errorCode != XAException.XAER_NOTA)
0469: throw e;
0470: }
0471: try {
0472: xar.forget(xid11);
0473: } catch (XAException e) {
0474: if (e.errorCode != XAException.XAER_NOTA)
0475: throw e;
0476: }
0477: } catch (SQLException e) {
0478: TestUtil.dumpSQLExceptions(e);
0479: } catch (XAException e) {
0480: XATestUtil.dumpXAException("noTransaction", e);
0481: }
0482: }
0483:
0484: /**
0485: * Morph a connection between local anf global transactions.
0486: */
0487: private static void morph(XADataSource xads) {
0488: System.out.println("morph");
0489:
0490: try {
0491: XAConnection xac = xads.getXAConnection();
0492:
0493: XAResource xar = xac.getXAResource();
0494:
0495: Connection conn = xac.getConnection();
0496:
0497: /*
0498: autocommit off;
0499: insert into foo values (1);
0500: select * from global_xactTable where gxid is not null order by gxid,username;
0501: commit;
0502: */
0503: conn.setAutoCommit(false);
0504: Statement s = conn.createStatement();
0505: s.executeUpdate("insert into APP.foo values (2001)");
0506: XATestUtil.showXATransactionView(conn);
0507: conn.commit();
0508:
0509: /*
0510: autocommit on;
0511: insert into foo values (2);
0512: select * from global_xactTable where gxid is not null order by gxid,username;
0513:
0514: */
0515:
0516: conn.setAutoCommit(true);
0517: s.executeUpdate("insert into APP.foo values (2002)");
0518: XATestUtil.showXATransactionView(conn);
0519:
0520: /*
0521: -- morph the connection to a global transaction
0522: xa_start xa_noflags 1;
0523: select * from global_xactTable where gxid is not null order by gxid,username;
0524: insert into foo values (3);
0525: */
0526:
0527: Xid xid = XATestUtil.getXid(1001, 66, 13);
0528: xar.start(xid, XAResource.TMNOFLAGS);
0529: XATestUtil.showXATransactionView(conn);
0530: s.executeUpdate("insert into APP.foo values (2003)");
0531:
0532: /*
0533: -- disallowed
0534: commit;
0535: -- disallowed
0536: rollback;
0537: -- disallowed
0538: autocommit on;
0539: -- OK
0540: autocommit off;
0541: */
0542: try {
0543: conn.commit();
0544: System.out
0545: .println("FAIL: commit allowed in global xact");
0546: } catch (SQLException e) {
0547: }
0548:
0549: try {
0550: conn.rollback();
0551: System.out
0552: .println("FAIL: roll back allowed in global xact");
0553: } catch (SQLException e) {
0554: }
0555: try {
0556: conn.setAutoCommit(true);
0557: System.out.println("FAIL: setAutoCommit(true) allowed "
0558: + "in global xact");
0559: } catch (SQLException e) {
0560: }
0561: try {
0562: conn.setSavepoint();
0563: System.out
0564: .println("FAIL: setSavepoint() allowed in global xact");
0565: } catch (SQLException e) {
0566: }
0567: try {
0568: conn.setSavepoint("badsavepoint");
0569: System.out
0570: .println("FAIL: setAutoCommit(String) allowed in "
0571: + "global xact");
0572: } catch (SQLException e) {
0573: }
0574:
0575: conn.setAutoCommit(false);
0576:
0577: // s was created in local mode so it has holdibilty
0578: // set, will execute but ResultSet will have close on commit
0579:
0580: if (TestUtil.isDerbyNetClientFramework()) { // DERBY-1158
0581: s.executeQuery("select * from APP.foo where A >= 2000")
0582: .close();
0583: System.out.println("OK: query with holdable statement");
0584: }
0585: s.close();
0586:
0587: s = conn.createStatement();
0588: boolean holdable = s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT;
0589: System.out
0590: .println("Statement created in global has holdabilty: "
0591: + holdable);
0592:
0593: /*
0594: select * from foo;
0595: xa_end xa_success 1;
0596: xa_prepare 1;
0597: */
0598: ResultSet rs = s
0599: .executeQuery("select * from APP.foo where A >= 2000");
0600: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0601: rs.close();
0602:
0603: xar.end(xid, XAResource.TMSUCCESS);
0604: xar.prepare(xid);
0605:
0606: /*
0607: -- dup id
0608: xa_start xa_noflags 1;
0609: */
0610: try {
0611: xar.start(xid, XAResource.TMNOFLAGS);
0612: System.out.println("FAIL - start with duplicate XID");
0613: } catch (XAException e) {
0614: if (e.errorCode != XAException.XAER_DUPID)
0615: throw e;
0616: }
0617:
0618: /*
0619: xa_start xa_noflags 2;
0620: -- still should disallow autommit;
0621: autocommit on;
0622: -- still should disallow commit and rollback
0623: commit;
0624: rollback;
0625: select * from global_xactTable where gxid is not null order by gxid,username;
0626: xa_end xa_suspend 2;
0627: */
0628:
0629: Xid xid2 = XATestUtil.getXid(1002, 23, 3);
0630: xar.start(xid2, XAResource.TMNOFLAGS);
0631: try {
0632: conn.commit();
0633: System.out
0634: .println("FAIL: commit allowed in global xact");
0635: } catch (SQLException e) {
0636: }
0637: try {
0638: conn.rollback();
0639: System.out
0640: .println("FAIL: roll back allowed in global xact");
0641: } catch (SQLException e) {
0642: }
0643: try {
0644: conn.setAutoCommit(true);
0645: System.out
0646: .println("FAIL: setAutoCommit(true) allowed in global xact");
0647: } catch (SQLException e) {
0648: }
0649: conn.setAutoCommit(false);
0650:
0651: xar.end(xid2, XAResource.TMSUSPEND);
0652:
0653: /*
0654: -- get local connection again
0655: xa_getconnection;
0656:
0657: insert into foo values (5);
0658:
0659: -- autocommit should be on by default;
0660: commit;
0661:
0662: autocommit off;
0663: insert into foo values (6);
0664:
0665: -- commit and rollback is allowed on local connection
0666: rollback;
0667:
0668: insert into foo values (6);
0669: commit;
0670: */
0671: conn = xac.getConnection();
0672: s = conn.createStatement();
0673: s.executeUpdate("insert into APP.foo values (2005)");
0674: conn.commit();
0675: conn.setAutoCommit(false);
0676: s.executeUpdate("insert into APP.foo values (2006)");
0677: conn.rollback();
0678: s.executeUpdate("insert into APP.foo values (2007)");
0679: conn.commit();
0680:
0681: XATestUtil.showXATransactionView(conn);
0682: /*
0683: -- I am still able to commit other global transactions while I am attached to a
0684: -- local transaction.
0685: xa_commit xa_2phase 1;
0686: xa_end xa_success 2;
0687: xa_rollback 2;
0688: */
0689: xar.commit(xid, false);
0690: xar.end(xid2, XAResource.TMSUCCESS);
0691: xar.rollback(xid2);
0692:
0693: XATestUtil.showXATransactionView(conn);
0694: rs = s
0695: .executeQuery("select * from APP.foo where A >= 2000");
0696: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0697: rs.close();
0698:
0699: conn.close();
0700:
0701: /*
0702: xa_getconnection;
0703: select * from global_xactTable where gxid is not null order by gxid,username;
0704: select * from foo;
0705: autocommit off;
0706: delete from foo;
0707: */
0708: conn = xac.getConnection();
0709: conn.setAutoCommit(false);
0710: s = conn.createStatement();
0711: s.executeUpdate("delete from app.foo");
0712: rs = s.executeQuery("select * from APP.foo");
0713: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0714: rs.close();
0715:
0716: // DERBY-1004
0717: if (TestUtil.isDerbyNetClientFramework()) {
0718: System.out
0719: .println("DERBY-1004 Call conn.rollback to avoid exception with client");
0720: conn.rollback();
0721: }
0722: /*
0723: -- yanking a local connection away should rollback the changes
0724: */
0725: conn = xac.getConnection();
0726: conn.setAutoCommit(false);
0727: s = conn.createStatement();
0728: rs = s
0729: .executeQuery("select * from APP.foo where A >= 2000");
0730: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0731: rs.close();
0732:
0733: /*
0734: -- cannot morph it if the local transaction is not idle
0735: xa_start xa_noflags 3;
0736: commit;
0737: -- now morph it to a global transaction
0738: xa_start xa_noflags 3;
0739: */
0740: Xid xid3 = XATestUtil.getXid(1003, 27, 9);
0741: try {
0742: xar.start(xid3, XAResource.TMNOFLAGS);
0743: System.out
0744: .println("FAIL XAResource.start on a global transaction with an active local transaction (autocommit false)");
0745: } catch (XAException xae) {
0746: if (xae.errorCode != XAException.XAER_OUTSIDE)
0747: throw xae;
0748: System.out
0749: .println("Correct XAException on starting a global transaction with an active local transaction (autocommit false)");
0750: }
0751: conn.commit();
0752: xar.start(xid3, XAResource.TMNOFLAGS);
0753:
0754: /*
0755: -- now I shouldn't be able to yank it
0756: xa_getconnection;
0757: */
0758: if (TestUtil.isDerbyNetClientFramework()) {
0759: System.out
0760: .println("DERBY-341 - Client skipping XAConnection with active local transaction");
0761: } else {
0762: try {
0763: xac.getConnection();
0764: System.out
0765: .println("FAIL: getConnection with active global xact");
0766: } catch (SQLException sqle) {
0767: TestUtil.dumpSQLExceptions(sqle, true);
0768: }
0769: }
0770: /*
0771: select * from foo;
0772: delete from foo;
0773:
0774: xa_end xa_fail 3;
0775: xa_rollback 3;
0776:
0777: -- local connection again
0778: xa_getconnection;
0779: select * from global_xactTable where gxid is not null order by gxid,username;
0780: select * from foo;
0781: */
0782: s = conn.createStatement();
0783: s.executeUpdate("delete from APP.foo");
0784: rs = s
0785: .executeQuery("select * from APP.foo where A >= 2000");
0786: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0787: rs.close();
0788: try {
0789: xar.end(xid3, XAResource.TMFAIL);
0790: } catch (XAException e) {
0791: if (e.errorCode != XAException.XA_RBROLLBACK)
0792: throw e;
0793: }
0794: xar.rollback(xid3);
0795:
0796: conn = xac.getConnection();
0797: s = conn.createStatement();
0798: rs = s
0799: .executeQuery("select * from APP.foo where A >= 2000");
0800: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
0801: rs.close();
0802:
0803: s.close();
0804: conn.close();
0805:
0806: } catch (SQLException e) {
0807: TestUtil.dumpSQLExceptions(e);
0808: e.printStackTrace(System.out);
0809: } catch (XAException e) {
0810: XATestUtil.dumpXAException("morph", e);
0811: }
0812:
0813: }
0814:
0815: /**
0816: * Derby-966 holdability and global/location transactions.
0817: * (work in progress)
0818: */
0819: private static void derby966(XADataSource xads) {
0820: System.out.println("derby966");
0821:
0822: try {
0823: XAConnection xac = xads.getXAConnection();
0824: XAResource xar = xac.getXAResource();
0825:
0826: Xid xid = XATestUtil.getXid(996, 9, 48);
0827:
0828: Connection conn = xac.getConnection();
0829:
0830: // Obtain Statements and PreparedStatements
0831: // with all the holdability options.
0832:
0833: showHoldStatus("Local ", conn);
0834:
0835: Statement sdh = conn.createStatement();
0836: showHoldStatus("Local(held) default ", sdh);
0837: checkHeldRS(conn, sdh, sdh
0838: .executeQuery("select * from app.foo"));
0839: PreparedStatement psdh = conn
0840: .prepareStatement("SELECT * FROM APP.FOO");
0841: PreparedStatement psdh_d = conn
0842: .prepareStatement("DELETE FROM APP.FOO WHERE A < -99");
0843: showHoldStatus("Local(held) default Prepared", psdh);
0844: checkHeldRS(conn, psdh, psdh.executeQuery());
0845:
0846: Statement shh = conn.createStatement(
0847: ResultSet.TYPE_FORWARD_ONLY,
0848: ResultSet.CONCUR_READ_ONLY,
0849: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0850: showHoldStatus("Local(held) held ", shh);
0851: checkHeldRS(conn, shh, shh
0852: .executeQuery("select * from app.foo"));
0853: PreparedStatement pshh = conn.prepareStatement(
0854: "SELECT * FROM APP.FOO",
0855: ResultSet.TYPE_FORWARD_ONLY,
0856: ResultSet.CONCUR_READ_ONLY,
0857: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0858: PreparedStatement pshh_d = conn.prepareStatement(
0859: "DELETE FROM APP.FOO WHERE A < -99",
0860: ResultSet.TYPE_FORWARD_ONLY,
0861: ResultSet.CONCUR_READ_ONLY,
0862: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0863: showHoldStatus("Local(held) held Prepared", pshh);
0864: checkHeldRS(conn, pshh, pshh.executeQuery());
0865:
0866: Statement sch = conn.createStatement(
0867: ResultSet.TYPE_FORWARD_ONLY,
0868: ResultSet.CONCUR_READ_ONLY,
0869: ResultSet.CLOSE_CURSORS_AT_COMMIT);
0870: showHoldStatus("Local(held) close ", sch);
0871: checkHeldRS(conn, sch, sch
0872: .executeQuery("select * from app.foo"));
0873: PreparedStatement psch = conn.prepareStatement(
0874: "SELECT * FROM APP.FOO",
0875: ResultSet.TYPE_FORWARD_ONLY,
0876: ResultSet.CONCUR_READ_ONLY,
0877: ResultSet.CLOSE_CURSORS_AT_COMMIT);
0878: PreparedStatement psch_d = conn.prepareStatement(
0879: "DELETE FROM APP.FOO WHERE A < -99",
0880: ResultSet.TYPE_FORWARD_ONLY,
0881: ResultSet.CONCUR_READ_ONLY,
0882: ResultSet.CLOSE_CURSORS_AT_COMMIT);
0883: showHoldStatus("Local(held) close Prepared", psch);
0884: checkHeldRS(conn, psch, psch.executeQuery());
0885:
0886: // set the connection's holdabilty to false
0887: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
0888:
0889: Statement sdc = conn.createStatement();
0890: showHoldStatus("Local(close) default ", sdc);
0891: checkHeldRS(conn, sdc, sdc
0892: .executeQuery("select * from app.foo"));
0893: PreparedStatement psdc = conn
0894: .prepareStatement("SELECT * FROM APP.FOO");
0895: PreparedStatement psdc_d = conn
0896: .prepareStatement("DELETE FROM APP.FOO WHERE A < -99");
0897: showHoldStatus("Local(close) default Prepared", psdc);
0898: checkHeldRS(conn, psdc, psdc.executeQuery());
0899:
0900: Statement shc = conn.createStatement(
0901: ResultSet.TYPE_FORWARD_ONLY,
0902: ResultSet.CONCUR_READ_ONLY,
0903: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0904: showHoldStatus("Local(close) held ", shc);
0905: checkHeldRS(conn, shc, shc
0906: .executeQuery("select * from app.foo"));
0907: PreparedStatement pshc = conn.prepareStatement(
0908: "SELECT * FROM APP.FOO",
0909: ResultSet.TYPE_FORWARD_ONLY,
0910: ResultSet.CONCUR_READ_ONLY,
0911: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0912: PreparedStatement pshc_d = conn.prepareStatement(
0913: "DELETE FROM APP.FOO WHERE A < -99",
0914: ResultSet.TYPE_FORWARD_ONLY,
0915: ResultSet.CONCUR_READ_ONLY,
0916: ResultSet.HOLD_CURSORS_OVER_COMMIT);
0917: showHoldStatus("Local(close) held Prepared", pshc);
0918: checkHeldRS(conn, pshc, pshc.executeQuery());
0919:
0920: Statement scc = conn.createStatement(
0921: ResultSet.TYPE_FORWARD_ONLY,
0922: ResultSet.CONCUR_READ_ONLY,
0923: ResultSet.CLOSE_CURSORS_AT_COMMIT);
0924: showHoldStatus("Local(close) close ", scc);
0925: checkHeldRS(conn, scc, scc
0926: .executeQuery("select * from app.foo"));
0927: PreparedStatement pscc = conn.prepareStatement(
0928: "SELECT * FROM APP.FOO",
0929: ResultSet.TYPE_FORWARD_ONLY,
0930: ResultSet.CONCUR_READ_ONLY,
0931: ResultSet.CLOSE_CURSORS_AT_COMMIT);
0932: PreparedStatement pscc_d = conn.prepareStatement(
0933: "DELETE FROM APP.FOO WHERE A < -99",
0934: ResultSet.TYPE_FORWARD_ONLY,
0935: ResultSet.CONCUR_READ_ONLY,
0936: ResultSet.CLOSE_CURSORS_AT_COMMIT);
0937: showHoldStatus("Local(close) close Prepared", pscc);
0938: checkHeldRS(conn, pscc, pscc.executeQuery());
0939:
0940: // Revert back to holdable
0941: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
0942:
0943: ResultSet rs = sdh.executeQuery("SELECT * FROM APP.FOO");
0944: rs.next();
0945: System.out.println("BGBC " + rs.getInt(1));
0946: conn.commit();
0947: rs.next();
0948: System.out.println("BGAC " + rs.getInt(1));
0949: rs.close();
0950:
0951: // ensure a transaction is active to test DERBY-1025
0952: rs = sdh.executeQuery("SELECT * FROM APP.FOO");
0953:
0954: // This switch to global is ok because conn
0955: // is in auto-commit mode, thus the start performs
0956: // an implicit commit to complete the local transaction.
0957:
0958: System.out.println("START GLOBAL TRANSACTION");
0959: // start a global xact and test those statements.
0960: xar.start(xid, XAResource.TMNOFLAGS);
0961:
0962: // Statements not returning ResultSet's should be ok
0963: sdh.executeUpdate("DELETE FROM APP.FOO where A < -99");
0964: shh.executeUpdate("DELETE FROM APP.FOO where A < -99");
0965: sch.executeUpdate("DELETE FROM APP.FOO where A < -99");
0966:
0967: ArrayList openRS = new ArrayList();
0968:
0969: // Statements obtained while default was hold.
0970: // All should work, holability will be downgraded
0971: // to close on commit for those Statements with hold set.
0972: openRS.add(sdh.executeQuery("SELECT * FROM APP.FOO"));
0973: openRS.add(shh.executeQuery("SELECT * FROM APP.FOO"));
0974: openRS.add(sch.executeQuery("SELECT * FROM APP.FOO"));
0975:
0976: // PreparedStatements obtained while default was hold.
0977: // Holdability should be downgraded.
0978: openRS.add(psdh.executeQuery());
0979: openRS.add(pshh.executeQuery());
0980: openRS.add(psch.executeQuery());
0981:
0982: // Statements not returning ResultSet's should be ok
0983: psdh_d.executeUpdate();
0984: pshh_d.executeUpdate();
0985: psch_d.executeUpdate();
0986:
0987: // Statements not returning ResultSet's should be ok
0988: sdc.executeUpdate("DELETE FROM APP.FOO where A < -99");
0989: shc.executeUpdate("DELETE FROM APP.FOO where A < -99");
0990: scc.executeUpdate("DELETE FROM APP.FOO where A < -99");
0991:
0992: // Statements obtained while default was close.
0993: // all should return close on commit ResultSets
0994: openRS.add(sdc.executeQuery("SELECT * FROM APP.FOO"));
0995: openRS.add(shc.executeQuery("SELECT * FROM APP.FOO"));
0996: openRS.add(scc.executeQuery("SELECT * FROM APP.FOO"));
0997:
0998: // PreparedStatements obtained while default was close.
0999: openRS.add(psdc.executeQuery());
1000: openRS.add(pshc.executeQuery());
1001: openRS.add(pscc.executeQuery());
1002:
1003: // Statements not returning ResultSet's should be ok
1004: psdc_d.executeUpdate();
1005: pshc_d.executeUpdate();
1006: pscc_d.executeUpdate();
1007:
1008: // All the ResultSets should be open. Run a simple
1009: // test, clearWarnings throws an error if the ResultSet
1010: // is closed. Also would be nice here to use the new
1011: // JDBC 4.0 method getHoldabilty to ensure the
1012: // holdability is reported correctly.
1013: int orsCount = 0;
1014: for (Iterator i = openRS.iterator(); i.hasNext();) {
1015: ResultSet ors = (ResultSet) i.next();
1016: ors.clearWarnings();
1017: orsCount++;
1018: }
1019: System.out.println("Global transaction open ResultSets "
1020: + orsCount);
1021:
1022: // Test we cannot switch the connection to holdable
1023: try {
1024: conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
1025: System.out
1026: .println("FAIL - set holdability in global xact.");
1027: } catch (SQLException sqle) {
1028: TestUtil.dumpSQLExceptions(sqle, true);
1029: }
1030:
1031: // JDBC 4.0 (proposed final draft) section allows
1032: // drivers to change the holdability when creating
1033: // a Statement object and attach a warning to the Connection.
1034: Statement sglobalhold = conn.createStatement(
1035: ResultSet.TYPE_FORWARD_ONLY,
1036: ResultSet.CONCUR_READ_ONLY,
1037: ResultSet.HOLD_CURSORS_OVER_COMMIT);
1038: showHoldStatus("Global createStatement(hold)", sglobalhold);
1039: sglobalhold.close();
1040:
1041: PreparedStatement psglobalhold = conn.prepareStatement(
1042: "SELECT * FROM APP.FOO",
1043: ResultSet.TYPE_FORWARD_ONLY,
1044: ResultSet.CONCUR_READ_ONLY,
1045: ResultSet.HOLD_CURSORS_OVER_COMMIT);
1046: showHoldStatus("Global prepareStatement(hold)",
1047: psglobalhold);
1048: psglobalhold.close();
1049:
1050: if (!TestUtil.isDerbyNetClientFramework()) { //DERBY-1158 in progress
1051: // Show the holdability for all the Statements while
1052: // in the global transaction, all should be close on commit.
1053: showHoldStatus("Global xact Statement sdh ", sdh);
1054: showHoldStatus("Global xact Statement shh ", shh);
1055: showHoldStatus("Global xact Statement sch ", sch);
1056:
1057: showHoldStatus("Global xact Statement psdh ", psdh);
1058: showHoldStatus("Global xact Statement pshh ", pshh);
1059: showHoldStatus("Global xact Statement psch ", psch);
1060:
1061: showHoldStatus("Global xact Statement sdc ", sdc);
1062: showHoldStatus("Global xact Statement shc ", shc);
1063: showHoldStatus("Global xact Statement scc ", scc);
1064:
1065: showHoldStatus("Global xact Statement psdh_d ", psdh_d);
1066: showHoldStatus("Global xact Statement pshh_d ", pshh_d);
1067: showHoldStatus("Global xact Statement psch_d ", psch_d);
1068: }
1069:
1070: xar.end(xid, XAResource.TMSUCCESS);
1071: if (xar.prepare(xid) != XAResource.XA_RDONLY)
1072: System.out.println("FAIL prepare didn't indicate r/o");
1073:
1074: // All the ResultSets should be closed. Run a simple
1075: // test, clearWarnings throws an error if the ResultSet
1076: // is closed.
1077: int crsCount = 0;
1078: for (Iterator i = openRS.iterator(); i.hasNext();) {
1079: ResultSet crs = (ResultSet) i.next();
1080: try {
1081: crs.clearWarnings();
1082: } catch (SQLException sqle) {
1083: }
1084: crsCount++;
1085: }
1086: System.out
1087: .println("After global transaction closed ResultSets "
1088: + crsCount);
1089:
1090: // Check the statements revert to holdable as required.
1091: showHoldStatus("Global xact Statement sdh ", sdh);
1092: showHoldStatus("Global xact Statement shh ", shh);
1093: showHoldStatus("Global xact Statement sch ", sch);
1094:
1095: showHoldStatus("Global xact Statement psdh ", psdh);
1096: showHoldStatus("Global xact Statement pshh ", pshh);
1097: showHoldStatus("Global xact Statement psch ", psch);
1098:
1099: showHoldStatus("Global xact Statement sdc ", sdc);
1100: showHoldStatus("Global xact Statement shc ", shc);
1101: showHoldStatus("Global xact Statement scc ", scc);
1102:
1103: showHoldStatus("Global xact Statement psdh_d ", psdh_d);
1104: showHoldStatus("Global xact Statement pshh_d ", pshh_d);
1105: showHoldStatus("Global xact Statement psch_d ", psch_d);
1106:
1107: conn.close();
1108:
1109: System.out.println("derby966 complete");
1110:
1111: } catch (SQLException e) {
1112: TestUtil.dumpSQLExceptions(e);
1113: e.printStackTrace(System.out);
1114: } catch (XAException e) {
1115: XATestUtil.dumpXAException("derby966", e);
1116: }
1117: }
1118:
1119: /**
1120: * Check the held state of a ResultSet by fetching
1121: * one row, executing a commit and then fetching the
1122: * next. Checks the held state matches the behaviour.
1123: */
1124: private static void checkHeldRS(Connection conn, Statement s,
1125: ResultSet rs) throws SQLException {
1126: if (s.getConnection() != conn)
1127: System.out
1128: .println("FAIL - mismatched statement & Connection");
1129: if (rs.getStatement() != s) {
1130: // DERBY-1009
1131: System.out
1132: .println("FAIL - mismatched statement & ResultSet");
1133: System.out.println("Statement class " + s.getClass());
1134: System.out.println("ResultSet' Statements class "
1135: + rs.getStatement().getClass());
1136: }
1137:
1138: boolean held = s.getResultSetHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT;
1139:
1140: System.out.println("ResultSet "
1141: + holdStatus(s.getResultSetHoldability()));
1142:
1143: rs.next();
1144: System.out.println(" BC A=" + rs.getInt(1));
1145: conn.commit();
1146:
1147: try {
1148: while (rs.next()) {
1149: rs.getInt(1);
1150: System.out.println(" AC A=" + rs.getInt(1));
1151: }
1152: if (!held)
1153: System.out
1154: .println("FAIL: non-held cursor not closed by commit");
1155: } catch (SQLException sqle) {
1156: boolean ok = !held;
1157: boolean showError = true;
1158: if (ok) {
1159: if (TestUtil.isEmbeddedFramework()) {
1160: if ("XCL16".equals(sqle.getSQLState()))
1161: showError = false;
1162: } else if (TestUtil.isDerbyNetClientFramework()) {
1163: // No SQL state yet from client error.
1164: showError = false;
1165: }
1166: }
1167: if (showError)
1168: TestUtil.dumpSQLExceptions(sqle, ok);
1169: else if (ok)
1170: System.out
1171: .println("Non-held ResultSet correctly closed after commit");
1172: }
1173:
1174: rs.close();
1175: conn.commit();
1176: }
1177:
1178: /**
1179: * Show the held status of the Statement.
1180: */
1181: private static void showHoldStatus(String tag, Statement s)
1182: throws SQLException {
1183: System.out.println(tag + "Statement holdable "
1184: + holdStatus(s.getResultSetHoldability()));
1185: SQLWarning w = s.getConnection().getWarnings();
1186: while (w != null) {
1187: System.out.println(w.getSQLState() + " :" + w.toString());
1188: w = w.getNextWarning();
1189: }
1190: s.getConnection().clearWarnings();
1191:
1192: }
1193:
1194: /**
1195: * Show the held status of the Connection.
1196: */
1197: private static void showHoldStatus(String tag, Connection conn)
1198: throws SQLException {
1199: System.out.println(tag + "Connection holdable "
1200: + holdStatus(conn.getHoldability()));
1201: }
1202:
1203: private static String holdStatus(int holdability) {
1204: String s;
1205: switch (holdability) {
1206: case ResultSet.CLOSE_CURSORS_AT_COMMIT:
1207: s = "CLOSE_CURSORS_AT_COMMIT ";
1208: break;
1209: case ResultSet.HOLD_CURSORS_OVER_COMMIT:
1210: s = "HOLD_CURSORS_OVER_COMMIT ";
1211: break;
1212: default:
1213: s = "UNKNOWN HOLDABILITY ";
1214: break;
1215: }
1216:
1217: return s + Integer.toString(holdability);
1218: }
1219:
1220: /*
1221: * 5 interleaving transactions.
1222: * Taken from the SQL test xaANotherTest.
1223: * <code>
1224: xa_connect user 'mamta' password 'mamta' ;
1225:
1226: -- global connection 1
1227: xa_start xa_noflags 1;
1228: xa_getconnection;
1229: insert into APP.foo values (1);
1230: xa_end xa_suspend 1;
1231:
1232: -- global connection 2
1233: xa_start xa_noflags 2;
1234: insert into APP.foo values (2);
1235: xa_end xa_suspend 2;
1236:
1237: -- global connection 3
1238: xa_start xa_noflags 3;
1239: insert into APP.foo values (3);
1240: xa_end xa_suspend 3;
1241:
1242: -- global connection 4
1243: xa_start xa_noflags 4;
1244: insert into APP.foo values (4);
1245: xa_end xa_suspend 4;
1246:
1247: -- global connection 5
1248: xa_start xa_noflags 5;
1249: insert into APP.foo values (5);
1250: xa_end xa_suspend 5;
1251:
1252: xa_start xa_resume 1;
1253: insert into APP.foo values (11);
1254: xa_end xa_suspend 1;
1255:
1256: xa_start xa_resume 5;
1257: insert into APP.foo values (55);
1258: xa_end xa_suspend 5;
1259:
1260: xa_start xa_resume 2;
1261: insert into APP.foo values (22);
1262: xa_end xa_suspend 2;
1263:
1264: xa_start xa_resume 4;
1265: insert into APP.foo values (44);
1266: xa_end xa_suspend 4;
1267:
1268: xa_start xa_resume 3;
1269: insert into APP.foo values (33);
1270: xa_end xa_suspend 3;
1271:
1272: -- prepare all the global connections except the first one. This way, we will see all
1273: -- the global transactions prepared so far after the database shutdown and restart.
1274: xa_end xa_success 2;
1275: xa_prepare 2;
1276: xa_end xa_success 3;
1277: xa_prepare 3;
1278: xa_end xa_success 4;
1279: xa_prepare 4;
1280: xa_end xa_success 5;
1281: xa_prepare 5;
1282:
1283: * </code>
1284: */
1285: private static void interleavingTransactions5(XADataSource xads)
1286: throws SQLException {
1287: System.out.println("interleavingTransactions5");
1288:
1289: XAConnection xac = xads.getXAConnection("mamta", "mamtapwd");
1290:
1291: }
1292:
1293: private static void cleanUp(Connection conn) throws SQLException {
1294: String testObjects[] = { "view XATESTUTIL.global_xactTable",
1295: "schema XATESTUTIL restrict", "table app.foo",
1296: "table foo" };
1297: Statement stmt = conn.createStatement();
1298: TestUtil.cleanUpTest(stmt, testObjects);
1299: conn.commit();
1300: stmt.close();
1301: }
1302:
1303: }
|