Examples with XAConnection javax.sql.XAConnection used on opensource projects

Example 41 with XAConnection

use of javax.sql.XAConnection in project derby by apache.

the class XATest method doXATempTableD4731Work.

/**
 * The two cases for DERBY-4371 do essentially the same thing. Except doing
 * logged work causes the RawStore error and doing only temp table
 * operations causes the assert.
 *
 * @param doLoggedWorkInXact
 * @throws SQLException
 * @throws XAException
 */
private void doXATempTableD4731Work(boolean doLoggedWorkInXact, boolean access_temp_table_after_xaendandcommit, Xid xid) throws SQLException, XAException {
    XADataSource xads = J2EEDataSource.getXADataSource();
    XAConnection xaconn = xads.getXAConnection();
    XAResource xar = xaconn.getXAResource();
    xar.start(xid, XAResource.TMNOFLAGS);
    Connection conn = xaconn.getConnection();
    Statement s = conn.createStatement();
    if (doLoggedWorkInXact) {
        // need to do some real work in our transaction
        // so make a table
        makeARealTable(s);
    }
    // make the temp table
    s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.T1 ( XWSID INT, XCTID INT, XIID CHAR(26), XVID SMALLINT, XLID CHAR(8) FOR BIT DATA) ON COMMIT DELETE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS");
    // insert a row
    PreparedStatement ps = conn.prepareStatement("INSERT INTO SESSION.T1 VALUES (?,?,?,?,?)");
    ps.setInt(1, 1);
    ps.setInt(2, 1);
    ps.setString(3, "hello");
    ps.setShort(4, (short) 1);
    ps.setBytes(5, new byte[] { 0x0, 0x1 });
    ps.executeUpdate();
    ResultSet rs = s.executeQuery("SELECT count(*) FROM SESSION.t1");
    JDBC.assertFullResultSet(rs, new String[][] { { "1" } });
    rs.close();
    // You could work around the issue by dropping the TEMP table
    // s.executeUpdate("DROP TABLE SESSION.T1");
    xar.end(xid, XAResource.TMSUCCESS);
    assertEquals((doLoggedWorkInXact ? XAResource.XA_OK : XAResource.XA_RDONLY), xar.prepare(xid));
    if (doLoggedWorkInXact) {
        // if you don't do logged work in the transaction, then the
        // prepare with be done with XAResource.XA_RDONLY.  If you
        // try to commit a prepared read only transaction you will get
        // an error, so only commit prepared transactions here that did
        // some "real" logged work.
        xar.commit(xid, false);
    }
    if (access_temp_table_after_xaendandcommit) {
        // temp table should not be available after commit of an XA
        // transaction.
        assertStatementError("42X05", s, "SELECT count(*) FROM SESSION.t1");
        conn.commit();
    }
    s.close();
    conn.close();
    xaconn.close();
}

Example 42 with XAConnection

use of javax.sql.XAConnection in project derby by apache.

the class XATest method testDerby966.

/**
 * Derby-966 holdability and global/location transactions. (work in
 * progress)
 */
public void testDerby966() throws SQLException, XAException {
    Statement preStatement = getConnection().createStatement();
    preStatement.execute("create table foo966 (a int)");
    preStatement.executeUpdate("insert into APP.foo966 values (0)");
    preStatement.executeUpdate("insert into APP.foo966 values (1)");
    preStatement.executeUpdate("insert into APP.foo966 values (2)");
    preStatement.executeUpdate("insert into APP.foo966 values (3)");
    preStatement.executeUpdate("insert into APP.foo966 values (4)");
    preStatement.executeUpdate("insert into APP.foo966 values (2001)");
    preStatement.executeUpdate("insert into APP.foo966 values (2002)");
    preStatement.executeUpdate("insert into APP.foo966 values (2003)");
    preStatement.executeUpdate("insert into APP.foo966 values (2005)");
    preStatement.executeUpdate("insert into APP.foo966 values (2007)");
    preStatement.close();
    XADataSource xads = J2EEDataSource.getXADataSource();
    XAConnection xac = xads.getXAConnection();
    XAResource xar = xac.getXAResource();
    Xid xid = XATestUtil.getXid(996, 9, 48);
    Connection conn = xac.getConnection();
    // Obtain Statements and PreparedStatements
    // with all the holdability options.
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability());
    Statement sdh = conn.createStatement();
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, sdh.getResultSetHoldability());
    checkHeldRS(conn, sdh, sdh.executeQuery("select * from app.foo966"));
    PreparedStatement psdh = conn.prepareStatement("SELECT * FROM APP.foo966");
    PreparedStatement psdh_d = conn.prepareStatement("DELETE FROM APP.foo966 WHERE A < -99");
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, psdh.getResultSetHoldability());
    checkHeldRS(conn, psdh, psdh.executeQuery());
    Statement shh = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, shh.getResultSetHoldability());
    checkHeldRS(conn, shh, shh.executeQuery("select * from app.foo966"));
    PreparedStatement pshh = conn.prepareStatement("SELECT * FROM APP.foo966", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    PreparedStatement pshh_d = conn.prepareStatement("DELETE FROM APP.foo966 WHERE A < -99", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, shh.getResultSetHoldability());
    checkHeldRS(conn, pshh, pshh.executeQuery());
    Statement sch = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sch.getResultSetHoldability());
    checkHeldRS(conn, sch, sch.executeQuery("select * from app.foo966"));
    PreparedStatement psch = conn.prepareStatement("SELECT * FROM APP.foo966", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
    PreparedStatement psch_d = conn.prepareStatement("DELETE FROM APP.foo966 WHERE A < -99", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psch_d.getResultSetHoldability());
    checkHeldRS(conn, psch, psch.executeQuery());
    // set the connection's holdabilty to false
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    Statement sdc = conn.createStatement();
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sdc.getResultSetHoldability());
    checkHeldRS(conn, sdc, sdc.executeQuery("select * from app.foo966"));
    PreparedStatement psdc = conn.prepareStatement("SELECT * FROM APP.foo966");
    PreparedStatement psdc_d = conn.prepareStatement("DELETE FROM APP.foo966 WHERE A < -99");
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psdc.getResultSetHoldability());
    checkHeldRS(conn, psdc, psdc.executeQuery());
    Statement shc = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psdc.getResultSetHoldability());
    checkHeldRS(conn, shc, shc.executeQuery("select * from app.foo966"));
    PreparedStatement pshc = conn.prepareStatement("SELECT * FROM APP.foo966", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    PreparedStatement pshc_d = conn.prepareStatement("DELETE FROM APP.foo966 WHERE A < -99", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, pshc.getResultSetHoldability());
    checkHeldRS(conn, pshc, pshc.executeQuery());
    Statement scc = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, scc.getResultSetHoldability());
    checkHeldRS(conn, scc, scc.executeQuery("select * from app.foo966"));
    PreparedStatement pscc = conn.prepareStatement("SELECT * FROM APP.foo966", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
    PreparedStatement pscc_d = conn.prepareStatement("DELETE FROM APP.foo966 WHERE A < -99", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, pscc.getResultSetHoldability());
    checkHeldRS(conn, pscc, pscc.executeQuery());
    // Revert back to holdable
    conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
    ResultSet rs = sdh.executeQuery("SELECT * FROM APP.foo966");
    rs.next();
    // before commit
    assertEquals(0, +rs.getInt(1));
    conn.commit();
    // aftercommit
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs.close();
    // ensure a transaction is active to test DERBY-1025
    rs = sdh.executeQuery("SELECT * FROM APP.foo966");
    // This switch to global is ok because conn
    // is in auto-commit mode, thus the start performs
    // an implicit commit to complete the local transaction.
    // start a global xact and test those statements.
    xar.start(xid, XAResource.TMNOFLAGS);
    // Statements not returning ResultSet's should be ok
    sdh.executeUpdate("DELETE FROM APP.foo966 where A < -99");
    shh.executeUpdate("DELETE FROM APP.foo966 where A < -99");
    sch.executeUpdate("DELETE FROM APP.foo966 where A < -99");
    ArrayList<ResultSet> openRS = new ArrayList<ResultSet>();
    // Statements obtained while default was hold.
    // All should work, holability will be downgraded
    // to close on commit for those Statements with hold set.
    openRS.add(sdh.executeQuery("SELECT * FROM APP.foo966"));
    openRS.add(shh.executeQuery("SELECT * FROM APP.foo966"));
    openRS.add(sch.executeQuery("SELECT * FROM APP.foo966"));
    // PreparedStatements obtained while default was hold.
    // Holdability should be downgraded.
    openRS.add(psdh.executeQuery());
    openRS.add(pshh.executeQuery());
    openRS.add(psch.executeQuery());
    // Statements not returning ResultSet's should be ok
    psdh_d.executeUpdate();
    pshh_d.executeUpdate();
    psch_d.executeUpdate();
    // Statements not returning ResultSet's should be ok
    sdc.executeUpdate("DELETE FROM APP.foo966 where A < -99");
    shc.executeUpdate("DELETE FROM APP.foo966 where A < -99");
    scc.executeUpdate("DELETE FROM APP.foo966 where A < -99");
    // Statements obtained while default was close.
    // all should return close on commit ResultSets
    openRS.add(sdc.executeQuery("SELECT * FROM APP.foo966"));
    openRS.add(shc.executeQuery("SELECT * FROM APP.foo966"));
    openRS.add(scc.executeQuery("SELECT * FROM APP.foo966"));
    // PreparedStatements obtained while default was close.
    openRS.add(psdc.executeQuery());
    openRS.add(pshc.executeQuery());
    openRS.add(pscc.executeQuery());
    // Statements not returning ResultSet's should be ok
    psdc_d.executeUpdate();
    pshc_d.executeUpdate();
    pscc_d.executeUpdate();
    // All the ResultSets should be open. Run a simple
    // test, clearWarnings throws an error if the ResultSet
    // is closed. Also would be nice here to use the new
    // JDBC 4.0 method getHoldabilty to ensure the
    // holdability is reported correctly.
    int orsCount = 0;
    for (ResultSet ors : openRS) {
        ors.clearWarnings();
        orsCount++;
    }
    assertEquals("Incorrect number of open result sets", 12, orsCount);
    // Test we cannot switch the connection to holdable
    try {
        conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
        fail("FAIL - set holdability in global xact.");
    } catch (SQLException sqle) {
        assertSQLState("XJ05C", sqle);
    }
    // JDBC 4.0 (proposed final draft) section allows
    // drivers to change the holdability when creating
    // a Statement object and attach a warning to the Connection.
    Statement sglobalhold = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sglobalhold.getResultSetHoldability());
    sglobalhold.close();
    // DERBY2481 Client does not downgrade PreparedStatement holdability
    if (!usingDerbyNetClient()) {
        PreparedStatement psglobalhold = conn.prepareStatement("SELECT * FROM APP.foo966", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psglobalhold.getResultSetHoldability());
        psglobalhold.close();
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sdh.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sch.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psdh.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, pshh.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psch.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sdc.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, shc.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, scc.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psdh_d.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, pshh_d.getResultSetHoldability());
        assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psch_d.getResultSetHoldability());
    }
    xar.end(xid, XAResource.TMSUCCESS);
    if (xar.prepare(xid) != XAResource.XA_RDONLY)
        fail("FAIL prepare didn't indicate r/o");
    // All the ResultSets should be closed. Run a simple
    // test, clearWarnings throws an error if the ResultSet
    // is closed.
    int crsCount = 0;
    for (ResultSet crs : openRS) {
        try {
            crs.clearWarnings();
        } catch (SQLException sqle) {
        }
        crsCount++;
    }
    assertEquals("After global transaction closed ResultSets ", 12, crsCount);
    // Check the statements revert to holdable as required.
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, sdh.getResultSetHoldability());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, shh.getResultSetHoldability());
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sch.getResultSetHoldability());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, psdh.getResultSetHoldability());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, pshh.getResultSetHoldability());
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psch.getResultSetHoldability());
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, sdc.getResultSetHoldability());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, shc.getResultSetHoldability());
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, scc.getResultSetHoldability());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, psdh_d.getResultSetHoldability());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, pshh_d.getResultSetHoldability());
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, psch_d.getResultSetHoldability());
    // intermittently we get a failure in this test if we do not
    // rollback, see DERBY-6248.
    conn.rollback();
    conn.close();
}

Example 43 with XAConnection

use of javax.sql.XAConnection in project derby by apache.

the class XATransactionTest method testForgetExceptionDerby1016PROTO.

/**
 * This fixture triggers DERBY-1016. It creates an XA transaction, executes
 * an update over it and then prepares the transaction. Trying to forget
 * after preparing should throw XAER_PROTO and not XAER_NOTA.
 */
public void testForgetExceptionDerby1016PROTO() throws XAException, SQLException {
    XADataSource xads = J2EEDataSource.getXADataSource();
    J2EEDataSource.setBeanProperty(xads, "databaseName", "wombat");
    XAConnection xaconn = xads.getXAConnection();
    XAResource xar = xaconn.getXAResource();
    Xid xid = createXid(93, 18);
    xar.start(xid, XAResource.TMNOFLAGS);
    Connection conn = xaconn.getConnection();
    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE Derby1016 (I INT)");
    xar.end(xid, XAResource.TMSUCCESS);
    xar.prepare(xid);
    try {
        xar.forget(xid);
        fail("FAIL: prepared XA-Transaction forgotten");
    } catch (XAException XAeForget) {
        assertEquals("FAIL: Got unexpected exception " + XAeForget.getMessage() + " errorCode: " + XAeForget.errorCode + "  calling forget on a prepared transaction", XAException.XAER_PROTO, XAeForget.errorCode);
    } finally {
        s.close();
        xar.rollback(xid);
        conn.close();
        xaconn.close();
    }
}

Example 44 with XAConnection

use of javax.sql.XAConnection in project derby by apache.

the class XATransactionTest method testTransactionTimeoutAndSuspendResume.

/**
 * DERBY-4232: Test that an XA transaction can be suspended and resumed
 * when a timeout is in effect.
 */
public void testTransactionTimeoutAndSuspendResume() throws Exception {
    XADataSource xads = J2EEDataSource.getXADataSource();
    XAConnection xac = xads.getXAConnection();
    XAResource xar = xac.getXAResource();
    Xid xid = XATestUtil.getXid(1, 2, 3);
    // Start work in a new transaction with a timeout
    xar.setTransactionTimeout(500);
    xar.start(xid, XAResource.TMNOFLAGS);
    // Suspend the transaction
    xar.end(xid, XAResource.TMSUSPEND);
    // Resume the transaction (used to fail with a XAER_PROTO on the
    // network client)
    xar.start(xid, XAResource.TMRESUME);
    // End the transaction and free up the resources
    xar.end(xid, XAResource.TMSUCCESS);
    xar.rollback(xid);
    xac.close();
}

Example 45 with XAConnection

use of javax.sql.XAConnection in project derby by apache.

the class XATransactionTest method testForgetExceptionDerby1016NOTA.

/**
 * Further test case prompted by DERBY-1016. Tests that XAER_NOTA is thrown
 * if no transaction exists.
 */
public void testForgetExceptionDerby1016NOTA() throws XAException, SQLException {
    XADataSource xads = J2EEDataSource.getXADataSource();
    J2EEDataSource.setBeanProperty(xads, "databaseName", "wombat");
    XAConnection xaconn = xads.getXAConnection();
    XAResource xar = xaconn.getXAResource();
    Xid xid = createXid(93, 18);
    xar.start(xid, XAResource.TMNOFLAGS);
    Connection conn = xaconn.getConnection();
    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE Derby1016 (I INT)");
    xar.end(xid, XAResource.TMSUCCESS);
    xar.prepare(xid);
    xar.commit(xid, false);
    try {
        // since the transaction was committed, it should no longer exist
        // thus, forget should now throw an XAER_NOTA
        xar.forget(xid);
        fail("FAIL: able to forget committed XA-Transaction");
    } catch (XAException XAeForget) {
        assertEquals("FAIL: Got unexpected exception " + XAeForget.getMessage() + " errorCode: " + XAeForget.errorCode + "  calling forget on a committed transaction", XAException.XAER_NOTA, XAeForget.errorCode);
    } finally {
        s.executeUpdate("DROP TABLE Derby1016");
        conn.commit();
        s.close();
        conn.close();
        xaconn.close();
    }
}