xatest.java

derby database source code.good for you.

            PreparedStatement psch =
                conn.prepareStatement("SELECT * FROM APP.FOO",
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY,
                        ResultSet.CLOSE_CURSORS_AT_COMMIT);
            PreparedStatement psch_d =
                conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY,
                        ResultSet.CLOSE_CURSORS_AT_COMMIT);
             showHoldStatus("Local(held) close Prepared", psch);
            checkHeldRS(conn, psch, psch.executeQuery());
         
            // set the connection's holdabilty to false
            conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
            
            Statement sdc = conn.createStatement();
            showHoldStatus("Local(close) default ", sdc);
            checkHeldRS(conn, sdc, sdc.executeQuery("select * from app.foo"));
            PreparedStatement psdc = conn.prepareStatement("SELECT * FROM APP.FOO");
            PreparedStatement psdc_d = conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99");
            showHoldStatus("Local(close) default Prepared", psdc);
            checkHeldRS(conn, psdc, psdc.executeQuery());
 
            Statement shc = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY,
                    ResultSet.HOLD_CURSORS_OVER_COMMIT);
            showHoldStatus("Local(close) held ", shc);
            checkHeldRS(conn, shc, shc.executeQuery("select * from app.foo"));
            PreparedStatement pshc =
                conn.prepareStatement("SELECT * FROM APP.FOO",
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY,
                        ResultSet.HOLD_CURSORS_OVER_COMMIT);
            PreparedStatement pshc_d =
                conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY,
                        ResultSet.HOLD_CURSORS_OVER_COMMIT);
            showHoldStatus("Local(close) held Prepared", pshc);
            checkHeldRS(conn, pshc, pshc.executeQuery());
            
            Statement scc = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY,
                    ResultSet.CLOSE_CURSORS_AT_COMMIT);
            showHoldStatus("Local(close) close ", scc);
            checkHeldRS(conn, scc, scc.executeQuery("select * from app.foo"));
            PreparedStatement pscc =
                conn.prepareStatement("SELECT * FROM APP.FOO",
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY,
                        ResultSet.CLOSE_CURSORS_AT_COMMIT);
            PreparedStatement pscc_d =
                conn.prepareStatement("DELETE FROM APP.FOO WHERE A < -99",
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY,
                        ResultSet.CLOSE_CURSORS_AT_COMMIT);
             showHoldStatus("Local(close) close Prepared", pscc);
            checkHeldRS(conn, pscc, pscc.executeQuery());
            
            // Revert back to holdable
            conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
            
            ResultSet rs = sdh.executeQuery("SELECT * FROM APP.FOO");
            rs.next(); System.out.println("BGBC " + rs.getInt(1));
            conn.commit();
            rs.next(); System.out.println("BGAC " + rs.getInt(1));
            rs.close();
           
            // ensure a transaction is active to test DERBY-1025
            rs = sdh.executeQuery("SELECT * FROM APP.FOO");
           
            // 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.
            
            System.out.println("START GLOBAL 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.FOO where A < -99");
            shh.executeUpdate("DELETE FROM APP.FOO where A < -99");
            sch.executeUpdate("DELETE FROM APP.FOO where A < -99");
           
            ArrayList openRS = new ArrayList();
            
            // 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.FOO"));
            openRS.add(shh.executeQuery("SELECT * FROM APP.FOO"));
            openRS.add(sch.executeQuery("SELECT * FROM APP.FOO"));

            
            // 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.FOO where A < -99");
            shc.executeUpdate("DELETE FROM APP.FOO where A < -99");
            scc.executeUpdate("DELETE FROM APP.FOO where A < -99");
 
            // Statements obtained while default was close.
            // all should return close on commit ResultSets
            openRS.add(sdc.executeQuery("SELECT * FROM APP.FOO"));
            openRS.add(shc.executeQuery("SELECT * FROM APP.FOO"));
            openRS.add(scc.executeQuery("SELECT * FROM APP.FOO"));
            
            // 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 (Iterator i = openRS.iterator(); i.hasNext();) {
                ResultSet ors = (ResultSet) i.next();
                ors.clearWarnings();
                orsCount++;
            }
            System.out.println("Global transaction open ResultSets " + orsCount);

                   
            // Test we cannot switch the connection to holdable
            try {
                conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
                System.out.println("FAIL - set holdability in global xact.");
            } catch (SQLException sqle)
            {
                TestUtil.dumpSQLExceptions(sqle, true);
            }
            
            // 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);
            showHoldStatus("Global createStatement(hold)", sglobalhold);
            sglobalhold.close();

            PreparedStatement psglobalhold = conn.prepareStatement(
                "SELECT * FROM APP.FOO",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY,
                ResultSet.HOLD_CURSORS_OVER_COMMIT);
            showHoldStatus("Global prepareStatement(hold)", psglobalhold);
            psglobalhold.close();
            
            if (!TestUtil.isDerbyNetClientFramework()) { //DERBY-1158 in progress
            // Show the holdability for all the Statements while
            // in the global transaction, all should be close on commit.
            showHoldStatus("Global xact Statement sdh ", sdh);
            showHoldStatus("Global xact Statement shh ", shh);
            showHoldStatus("Global xact Statement sch ", sch);
            
            showHoldStatus("Global xact Statement psdh ", psdh);
            showHoldStatus("Global xact Statement pshh ", pshh);
            showHoldStatus("Global xact Statement psch ", psch);
            
            showHoldStatus("Global xact Statement sdc ", sdc);
            showHoldStatus("Global xact Statement shc ", shc);
            showHoldStatus("Global xact Statement scc ", scc);
 
            showHoldStatus("Global xact Statement psdh_d ", psdh_d);
            showHoldStatus("Global xact Statement pshh_d ", pshh_d);
            showHoldStatus("Global xact Statement psch_d ", psch_d);
            }
 
        
            xar.end(xid, XAResource.TMSUCCESS);
            if (xar.prepare(xid) != XAResource.XA_RDONLY)
                System.out.println("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 (Iterator i = openRS.iterator(); i.hasNext();) {
                ResultSet crs = (ResultSet) i.next();
                try {
                    crs.clearWarnings();
                } catch (SQLException sqle) {
                }
                crsCount++;
            }
            System.out.println("After global transaction closed ResultSets " + crsCount);

            
            // Check the statements revert to holdable as required.
            showHoldStatus("Global xact Statement sdh ", sdh);
            showHoldStatus("Global xact Statement shh ", shh);
            showHoldStatus("Global xact Statement sch ", sch);
            
            showHoldStatus("Global xact Statement psdh ", psdh);
            showHoldStatus("Global xact Statement pshh ", pshh);
            showHoldStatus("Global xact Statement psch ", psch);
 
            showHoldStatus("Global xact Statement sdc ", sdc);
            showHoldStatus("Global xact Statement shc ", shc);
            showHoldStatus("Global xact Statement scc ", scc);
            
            showHoldStatus("Global xact Statement psdh_d ", psdh_d);
            showHoldStatus("Global xact Statement pshh_d ", pshh_d);
            showHoldStatus("Global xact Statement psch_d ", psch_d);
            
            conn.close();
            
            System.out.println("derby966 complete");
                
        } catch (SQLException e) {
            TestUtil.dumpSQLExceptions(e);
            e.printStackTrace(System.out);
        } catch (XAException e) {
            XATestUtil.dumpXAException("derby966", e);
        }
    }
    
    /**
     * Check the held state of a ResultSet by fetching
     * one row, executing a commit and then fetching the
     * next. Checks the held state matches the behaviour.
    */
    private static void checkHeldRS(Connection conn,
            Statement s, ResultSet rs) throws SQLException
    {
        if (s.getConnection() != conn)
            System.out.println("FAIL - mismatched statement & Connection");
        if (rs.getStatement() != s)
        {
            // DERBY-1009
            System.out.println("FAIL - mismatched statement & ResultSet");
            System.out.println("Statement class " + s.getClass());
            System.out.println("ResultSet' Statements class " + rs.getStatement().getClass());
         }

        boolean held = s.getResultSetHoldability() ==
            ResultSet.HOLD_CURSORS_OVER_COMMIT;
        
        System.out.println("ResultSet " + holdStatus(s.getResultSetHoldability()));
        
        rs.next();
        System.out.println("  BC A=" + rs.getInt(1));
        conn.commit();
       
        try {
            while (rs.next())
            {
                rs.getInt(1);
                System.out.println("  AC A=" + rs.getInt(1));
            }
           if (!held)
               System.out.println("FAIL: non-held cursor not closed by commit");
        } catch (SQLException sqle)
        {
            boolean ok = !held;
            boolean showError = true;
            if (ok) {
                if (TestUtil.isEmbeddedFramework()) {
                    if ("XCL16".equals(sqle.getSQLState()))
                        showError = false;
                } else if (TestUtil.isDerbyNetClientFramework()) {
                    // No SQL state yet from client error.
                    showError = false;
                }
            }
            if (showError)
                TestUtil.dumpSQLExceptions(sqle, ok);
            else if (ok)
                System.out.println("Non-held ResultSet correctly closed after commit");
        }
        
        rs.close();
        conn.commit();
    }
    
    /**
     * Show the held status of the Statement.
    */
    private static void showHoldStatus(String tag, Statement s) throws SQLException
    {
        System.out.println(tag + "Statement holdable " +
                holdStatus(s.getResultSetHoldability()));
        SQLWarning w = s.getConnection().getWarnings();
        while (w != null)
        {
            System.out.println(w.getSQLState() + " :" + w.toString());
            w = w.getNextWarning();
        }
        s.getConnection().clearWarnings();
        
    }
    /**
     * Show the held status of the Connection.
    */
    private static void showHoldStatus(String tag, Connection conn) throws SQLException
    {
        System.out.println(tag + "Connection holdable " +
                holdStatus(conn.getHoldability()));
    }
    
    private static String holdStatus(int holdability)
    {
        String s;
        switch (holdability)
        {
        case ResultSet.CLOSE_CURSORS_AT_COMMIT:
            s = "CLOSE_CURSORS_AT_COMMIT ";
            break;
        case ResultSet.HOLD_CURSORS_OVER_COMMIT:
            s = "HOLD_CURSORS_OVER_COMMIT ";
            break;
        default:
            s = "UNKNOWN HOLDABILITY ";
            break;
        }
        
        return s + Integer.toString(holdability);
    }
    
    /*
     * 5 interleaving transactions.
     * Taken from the SQL test xaANotherTest.
     * <code>
xa_connect user 'mamta' password 'mamta' ;

-- global connection 1
xa_start xa_noflags 1;
xa_getconnection;
insert into APP.foo values (1);
xa_end xa_suspend 1;

-- global connection 2
xa_start xa_noflags 2;
insert into APP.foo values (2);
xa_end xa_suspend 2;

-- global connection 3
xa_start xa_noflags 3;
insert into APP.foo values (3);
xa_end xa_suspend 3;

-- global connection 4
xa_start xa_noflags 4;
insert into APP.foo values (4);
xa_end xa_suspend 4;

-- global connection 5
xa_start xa_noflags 5;
insert into APP.foo values (5);
xa_end xa_suspend 5;

xa_start xa_resume 1;
insert into APP.foo values (11);
xa_end xa_suspend 1;

xa_start xa_resume 5;
insert into APP.foo values (55);
xa_end xa_suspend 5;

xa_start xa_resume 2;
insert into APP.foo values (22);
xa_end xa_suspend 2;

xa_start xa_resume 4;
insert into APP.foo values (44);
xa_end xa_suspend 4;

xa_start xa_resume 3;
insert into APP.foo values (33);
xa_end xa_suspend 3;

-- prepare all the global connections except the first one. This way, we will see all
-- the global transactions prepared so far after the database shutdown and restart.
xa_end xa_success 2;
xa_prepare 2;
xa_end xa_success 3;
xa_prepare 3;
xa_end xa_success 4;
xa_prepare 4;
xa_end xa_success 5;
xa_prepare 5;

     * </code>
     */
    private static void interleavingTransactions5(XADataSource xads) throws SQLException
    {
        System.out.println("interleavingTransactions5");
        
        XAConnection xac = xads.getXAConnection("mamta", "mamtapwd");
        
    }
 
}