Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.checkDataSource

   Copyright 2002, 2005 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");

   you may not use this file except in compliance with the License.

   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software

   distributed under the License is distributed on an "AS IS" BASIS,

   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

   See the License for the specific language governing permissions and

   limitations under the License.

*/

  // Only test connection toString values for embedded.

  // Client connection toString values are not correlated at this time and just

  // use default toString

  // These tests are exempted from other frameworks

  private boolean testConnectionToString = TestUtil.isEmbeddedFramework();

 

  // Only embedded supports SimpleDataSource (JSR169). 

  // These tests are exempted from other frameworks

  private boolean testSimpleDataSource = TestUtil.isEmbeddedFramework();

 

  // DERBY-1183 getCursorName not correct after first statement execution

  private static boolean hasGetCursorNameBug = TestUtil.isDerbyNetClientFramework();

 

    // DERBY-1326 - Network server may abandon sessions when Derby system is shutdown

    // and this causes intermittent hangs in the client

  private static boolean hangAfterSystemShutdown = TestUtil.isDerbyNetClientFramework();

  /**

     * A hashtable of opened connections.  This is used when checking to

     * make sure connection strings are unique; we need to make sure all

     * the connections are closed when we are done, so they are stored

     * in this hashtable

     */

  protected static Hashtable conns = new Hashtable();

    /** The expected format of a connection string. In English:

     * "<classname>@<hashcode> (XID=<xid>), (SESSION = <sessionid>),

     *  (DATABASE=<dbname>), (DRDAID = <drdaid>)"

     */

    private static final String CONNSTRING_FORMAT = "\\S+@[0-9]+ " +

        "\\(XID = .*\\), \\(SESSIONID = [0-9]+\\), " +

        "\\(DATABASE = [A-Za-z]+\\), \\(DRDAID = .+\\)";

   

  public static void main(String[] args) throws Exception {

        try

        {

      new checkDataSource().runTest(args);

        }

        catch ( Exception e )

        {

            e.printStackTrace();

            throw e;

        }

    System.out.println("Completed checkDataSource");

  }

  public checkDataSource() {

  }

  protected void runTest(String[] args) throws Exception {

    // Check the returned type of the JDBC Connections.

    ij.getPropertyArg(args);

    Connection dmc = ij.startJBMS();

    dmc.createStatement().executeUpdate("create table y(i int)");

    dmc.createStatement().executeUpdate(

                "create procedure checkConn2(in dsname varchar(20)) " +

                "parameter style java language java modifies SQL DATA " +

                "external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi." +

                this.getNestedMethodName() +

                "'");

    CallableStatement cs = dmc.prepareCall("call checkConn2(?)");

    cs.setString(1,"Nested");

    cs.execute();

   

    checkConnection("DriverManager ", dmc);

    if (testConnectionToString)

      checkJBMSToString();

    Properties attrs = new Properties();

    attrs.setProperty("databaseName", "wombat");

    DataSource dscs = TestUtil.getDataSource(attrs);

   

    if (testConnectionToString)

        checkToString(dscs);

    DataSource ds = dscs;

    checkConnection("DataSource", ds.getConnection());

    

    DataSource dssimple = null;

    if (testSimpleDataSource)

    {

      dssimple = TestUtil.getSimpleDataSource(attrs);

      ds = dssimple;

      checkConnection("SimpleDataSource", ds.getConnection());

    }

    ConnectionPoolDataSource dsp = TestUtil.getConnectionPoolDataSource(attrs);

   

   

    if (testConnectionToString)

      checkToString(dsp);

 

    PooledConnection pc = dsp.getPooledConnection();

    pc.addConnectionEventListener(new EventCatcher(1));

    checkConnection("ConnectionPoolDataSource", pc.getConnection());

    checkConnection("ConnectionPoolDataSource", pc.getConnection());

    // BUG 4471 - check outstanding updates are rolled back.

    Connection c1 = pc.getConnection();

    Statement s = c1.createStatement();

    s.executeUpdate("create table t (i int)");

    s.executeUpdate("insert into t values(1)");

    c1.setAutoCommit(false);

    // this update should be rolled back

    s.executeUpdate("insert into t values(2)");

   

    c1 = pc.getConnection();

    ResultSet rs = c1.createStatement().executeQuery("select count(*) from t");

    rs.next();

    int count = rs.getInt(1);

    System.out.println(count == 1 ? "Changes rolled back OK in auto closed pooled connection" :

        ("FAIL changes committed in in auto closed pooled connection - " + count));

    c1.close();

    // check connection objects are closed once connection is closed

    try {

      rs.next();

      System.out.println("FAIL - ResultSet is open for a closed connection obtained from PooledConnection");

    } catch (SQLException sqle) {

      System.out.println("expected " + sqle.toString());

    }

    try {

      s.executeUpdate("update t set i = 1");

      System.out.println("FAIL - Statement is open for a closed connection obtained from PooledConnection");

    } catch (SQLException sqle) {

      System.out.println("expected " + sqle.toString());

    }

    pc.close();

    pc = null;

    testPoolReset("ConnectionPoolDataSource", dsp.getPooledConnection());

    XADataSource dsx = TestUtil.getXADataSource(attrs);

    if (testConnectionToString)

      checkToString(dsx);

    XAConnection xac = dsx.getXAConnection();

    xac.addConnectionEventListener(new EventCatcher(3));

    checkConnection("XADataSource", xac.getConnection());

    // BUG 4471 - check outstanding updates are rolled back wi XAConnection.

    c1 = xac.getConnection();

    s = c1.createStatement();

    s.executeUpdate("insert into t values(1)");

    c1.setAutoCommit(false);

    // this update should be rolled back

    s.executeUpdate("insert into t values(2)");

   

    c1 = xac.getConnection();

    rs = c1.createStatement().executeQuery("select count(*) from t");

    rs.next();

    count = rs.getInt(1);

    rs.close();

    System.out.println(count == 2 ? "Changes rolled back OK in auto closed local XAConnection" :

        ("FAIL changes committed in in auto closed pooled connection - " + count));

    c1.close();

    xac.close();

    xac = null;

    testPoolReset("XADataSource", dsx.getXAConnection());

    // DERBY-1326 - hang in client after Derby system shutdown

    if(! hangAfterSystemShutdown) {

      try {

        TestUtil.getConnection("","shutdown=true");

      } catch (SQLException sqle) {

        JDBCDisplayUtil.ShowSQLException(System.out, sqle);

      }

    }

    dmc = ij.startJBMS();

    cs = dmc.prepareCall("call checkConn2(?)");

    cs.setString(1,"Nested");

    cs.execute();

   

    checkConnection("DriverManager ", dmc);

    // reset ds back to the Regular DataSource

    ds = dscs;

    checkConnection("DataSource", ds.getConnection());

   

    // and back to EmbeddedSimpleDataSource

    if(TestUtil.isEmbeddedFramework())

    {

      // JSR169 (SimpleDataSource) is only available on embedded.

      ds = dssimple;

      checkConnection("EmbeddedSimpleDataSource", dssimple.getConnection());

    }

   

    pc = dsp.getPooledConnection();

    pc.addConnectionEventListener(new EventCatcher(2));

    checkConnection("ConnectionPoolDataSource", pc.getConnection());

    checkConnection("ConnectionPoolDataSource", pc.getConnection());

    // test "local" XAConnections

    xac = dsx.getXAConnection();

    xac.addConnectionEventListener(new EventCatcher(4));

    checkConnection("XADataSource", xac.getConnection());

    checkConnection("XADataSource", xac.getConnection());

    xac.close();

    // test "global" XAConnections

    xac = dsx.getXAConnection();

    xac.addConnectionEventListener(new EventCatcher(5));

    XAResource xar = xac.getXAResource();

    Xid xid = new cdsXid(1, (byte) 35, (byte) 47);

    xar.start(xid, XAResource.TMNOFLAGS);

    Connection xacc = xac.getConnection();

    xacc.close();

    checkConnection("Global XADataSource", xac.getConnection());

    checkConnection("Global XADataSource", xac.getConnection());

    xar.end(xid, XAResource.TMSUCCESS);

    checkConnection("Switch to local XADataSource", xac.getConnection());

    checkConnection("Switch to local XADataSource", xac.getConnection());

    Connection backtoGlobal = xac.getConnection();

    xar.start(xid, XAResource.TMJOIN);

    checkConnection("Switch to global XADataSource", backtoGlobal);

    checkConnection("Switch to global XADataSource", xac.getConnection());

    xar.end(xid, XAResource.TMSUCCESS);

    xar.commit(xid, true);

    xac.close();

    // now some explicit tests for how connection state behaves

    // when switching between global transactions and local

    // and setting connection state.

    // some of this is already tested in simpleDataSource and checkDataSource

    // but I want to make sure I cover all situations. (djd)

    xac = dsx.getXAConnection();

    xac.addConnectionEventListener(new EventCatcher(6));

    xar = xac.getXAResource();

    xid = new cdsXid(1, (byte) 93, (byte) 103);

    // series 1 - Single connection object

    Connection cs1 = xac.getConnection();

    printState("initial local", cs1);

    xar.start(xid, XAResource.TMNOFLAGS);

    printState("initial  X1", cs1);

    cs1.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

    cs1.setReadOnly(true);

    setHoldability(cs1, false);

    printState("modified X1", cs1);

    xar.end(xid, XAResource.TMSUCCESS);

    // the underlying local transaction/connection must pick up the

    // state of the Connection handle cs1

    printState("modified local", cs1);

    cs1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

    cs1.setReadOnly(false);

    setHoldability(cs1, false);

    printState("reset local", cs1);

    // now re-join the transaction, should pick up the read-only

    // and isolation level from the transaction,

    // holdability remains that of this handle.

    xar.start(xid, XAResource.TMJOIN);

    printState("re-join X1", cs1);

    xar.end(xid, XAResource.TMSUCCESS);

    // should be the same as the reset local

    printState("back to local (same as reset)", cs1);

   

    // test suspend/resume

    // now re-join the transaction, should pick up the read-only

    // and isolation level from the transaction,

    // holdability remains that of this handle.

    xar.start(xid, XAResource.TMJOIN);

    printState("re-join X1 second time", cs1);

   

    xar.end(xid, XAResource.TMSUSPEND);

    printState("local after suspend", cs1);

   

    xar.start(xid, XAResource.TMRESUME);

    printState("resume X1", cs1);

   

    xar.end(xid, XAResource.TMSUCCESS);

    printState("back to local (second time)", cs1);

   

    cs1.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

    cs1.setReadOnly(true);

    setHoldability(cs1, true);

    cs1.close();

    cs1 = xac.getConnection();

    printState("new handle - local ", cs1);

    cs1.close();

    xar.start(xid, XAResource.TMJOIN);

    cs1 = xac.getConnection();

    printState("re-join with new handle X1", cs1);

    cs1.close();

    xar.end(xid, XAResource.TMSUCCESS);

    // now get a connection (attached to a local)

    // attach to the global and commit it.

    // state should be that of the local after the commit.

    cs1 = xac.getConnection();

    cs1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

    printState("pre-X1 commit - local", cs1);

    xar.start(xid, XAResource.TMJOIN);

    printState("pre-X1 commit - X1", cs1);

    xar.end(xid, XAResource.TMSUCCESS);

    printState("post-X1 end - local", cs1);

    xar.commit(xid, true);

    printState("post-X1 commit - local", cs1);

    cs1.close();

    //Derby-421 Setting isolation level with SQL was not getting handled correctly

    System.out.println("Some more isolation testing using SQL and JDBC api");

    cs1 = xac.getConnection();

    s = cs1.createStatement();

    printState("initial local", cs1);

    System.out.println("Issue setTransactionIsolation in local transaction");

    cs1.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

    printState("setTransactionIsolation in local", cs1);

    testSetIsolationWithStatement(s, xar, cs1);

    // now check re-use of *Statement objects across local/global connections.

    System.out.println("TESTING RE_USE OF STATEMENT OBJECTS");

    cs1 = xac.getConnection();

   

    // ensure read locsk stay around until end-of transaction

    cs1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

    cs1.setAutoCommit(false);

    checkLocks(cs1);

    Statement sru1 = cs1.createStatement();

    sru1.setCursorName("SN1");

    sru1.executeUpdate("create table ru(i int)");

    sru1.executeUpdate("insert into ru values 1,2,3");

    Statement sruBatch = cs1.createStatement();

    Statement sruState = createFloatStatementForStateChecking(cs1);

    PreparedStatement psruState = createFloatStatementForStateChecking(cs1, "select i from ru where i = ?");

    CallableStatement csruState = createFloatCallForStateChecking(cs1, "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)");

    PreparedStatement psParams = cs1.prepareStatement("select * from ru where i > ?");

    psParams.setCursorName("params");

    psParams.setInt(1, 2);

    resultSetQuery("Params-local-1", psParams.executeQuery());

    sruBatch.addBatch("insert into ru values 4");

    queryOnStatement("sru1-local-1", cs1, sru1);

    cs1.commit(); // need to commit to switch to an global connection;

    xid = new cdsXid(1, (byte) 103, (byte) 119);

    xar.start(xid, XAResource.TMNOFLAGS); // simple case - underlying connection is re-used for global.

    System.out.println("Expecting downgrade because global transaction sru1-global-2 is using a statement with holdability true");

    queryOnStatement("sru1-global-2", cs1, sru1);

    sruBatch.addBatch("insert into ru values 5");

    Statement sru2 = cs1.createStatement();

    sru2.setCursorName("OAK2");

    queryOnStatement("sru2-global-3", cs1, sru2);

    System.out.println("Expecting downgrade because global transaction sru1-global-4 is using a statement with holdability true");

    queryOnStatement("sru1-global-4", cs1, sru1);

    showStatementState("GLOBAL ", sruState);

    showStatementState("PS GLOBAL ", psruState);

    showStatementState("CS GLOBAL ", csruState);

    resultSetQuery("Params-global-1", psParams.executeQuery());

    xar.end(xid, XAResource.TMSUCCESS);

    // now a new underlying connection is created

    queryOnStatement("sru1-local-5", cs1, sru1);

    queryOnStatement("sru2-local-6", cs1, sru2);

    sruBatch.addBatch("insert into ru values 6,7");

    Statement sru3 = cs1.createStatement();

    sru3.setCursorName("SF3");

    queryOnStatement("sru3-local-7", cs1, sru3);

    // Two transactions should hold locks (global and the current XA);

    showStatementState("LOCAL ", sruState);

    showStatementState("PS LOCAL ", psruState);

    showStatementState("CS LOCAL ", csruState);

    resultSetQuery("Params-local-2", psParams.executeQuery());

    checkLocks(cs1);

    cs1.commit();

    // attach the XA transaction to another connection and see what happens

    XAConnection xac2 = dsx.getXAConnection();

    xac2.addConnectionEventListener(new EventCatcher(5));

    XAResource xar2 = xac2.getXAResource();

    xar2.start(xid, XAResource.TMJOIN);

    Connection cs2 = xac2.getConnection();

    // these statements were generated by cs1 and thus are still

    // in a local connection.

    queryOnStatement("sru1-local-8", cs1, sru1);

    queryOnStatement("sru2-local-9", cs1, sru2);

    queryOnStatement("sru3-local-10", cs1, sru3);

    sruBatch.addBatch("insert into ru values 8");

    showStatementState("LOCAL 2 ", sruState);

    showStatementState("PS LOCAL 2 ", psruState);

    showStatementState("CS LOCAL 2", csruState);

    checkLocks(cs1);

    int[] updateCounts = sruBatch.executeBatch();

    System.out.print("sruBatch update counts :");

    for (int i = 0; i < updateCounts.length; i++) {

      System.out.print(" " + updateCounts[i] + " ");

    }

    System.out.println(":");

    queryOnStatement("sruBatch", cs1, sruBatch);

    xar2.end(xid, XAResource.TMSUCCESS);

    xac2.close();

    // allow close on already closed XAConnection

    xac2.close();

    xac2.addConnectionEventListener(null);

    xac2.removeConnectionEventListener(null);

    // test methods against a closed XAConnection and its resource

    try {

      xac2.getXAResource();

    } catch (SQLException sqle) {

      System.out.println("XAConnection.getXAResource : " + sqle.getMessage());

    }

    try {

      xac2.getConnection();

    } catch (SQLException sqle) {

      System.out.println("XAConnection.getConnection : " + sqle.getMessage());

    }

    try {

      xar2.start(xid, XAResource.TMJOIN);

    } catch (XAException xae) {

      showXAException("XAResource.start", xae);

    }

    try {

      xar2.end(xid, XAResource.TMJOIN);

    } catch (XAException xae) {

      showXAException("XAResource.end", xae);

    }

    try {

      xar2.commit(xid, true);

    } catch (XAException xae) {

      showXAException("XAResource.commit", xae);

    }

    try {

      xar2.prepare(xid);

    } catch (XAException xae) {

      showXAException("XAResource.prepare", xae);

    }

    try {

      xar2.recover(0);

    } catch (XAException xae) {

      showXAException("XAResource.recover", xae);

    }

    try {

      xar2.prepare(xid);

    } catch (XAException xae) {

      showXAException("XAResource.prepare", xae);

    }

    try {

      xar2.isSameRM(xar2);

    } catch (XAException xae) {

      showXAException("XAResource.isSameRM", xae);

    }

    // Patricio (on the forum) one was having an issue with set schema not working in an XA connection.

    dmc = ij.startJBMS();

    dmc.createStatement().executeUpdate("create schema SCHEMA_Patricio");

    dmc.createStatement().executeUpdate("create table SCHEMA_Patricio.Patricio (id VARCHAR(255), value INTEGER)");

    dmc.commit();

    dmc.close();

    XAConnection xac3 = dsx.getXAConnection();

    Connection conn3 = xac3.getConnection();

    Statement st3 = conn3.createStatement();

    st3.execute("SET SCHEMA SCHEMA_Patricio");

    st3.close();

    PreparedStatement ps3 = conn3.prepareStatement("INSERT INTO Patricio VALUES (? , ?)");

    ps3.setString(1, "Patricio");

    ps3.setInt(2, 3);

    ps3.executeUpdate();

    System.out.println("Patricio update count " + ps3.getUpdateCount());

    ps3.close();

    conn3.close();

    xac3.close();

    // test that an xastart in auto commit mode commits the existing work.(beetle 5178)

    XAConnection xac4 = dsx.getXAConnection();

    Xid xid4a = new cdsXid(4, (byte) 23, (byte) 76);

    Connection conn4 = xac4.getConnection();

    System.out.println("conn4 autcommit " + conn4.getAutoCommit());

    Statement s4 = conn4.createStatement();

    s4.executeUpdate("create table autocommitxastart(i int)");

    s4.executeUpdate("insert into autocommitxastart values 1,2,3,4,5");

    ResultSet rs4 = s4.executeQuery("select i from autocommitxastart");

    rs4.next(); System.out.println("acxs " + rs4.getInt(1));

    rs4.next(); System.out.println("acxs " + rs4.getInt(1));

    xac4.getXAResource().start(xid4a, XAResource.TMNOFLAGS);

    xac4.getXAResource().end(xid4a, XAResource.TMSUCCESS);

    try {

      rs4.next(); System.out.println("acxs " + rs.getInt(1));

    } catch (SQLException sqle) {

      System.out.println("autocommitxastart expected " + sqle.getMessage());

    }

    conn4.setAutoCommit(false);

    rs4 = s4.executeQuery("select i from autocommitxastart");

    rs4.next(); System.out.println("acxs " + rs4.getInt(1));

    rs4.next(); System.out.println("acxs " + rs4.getInt(1));

   

     // Get a new xid to begin another transaction.

     // This should give XAER_OUTSIDE exception because

     // the resource manager is busy in the local transaction

     xid4a = new cdsXid(4, (byte) 93, (byte) 103);

    try {

      xac4.getXAResource().start(xid4a, XAResource.TMNOFLAGS);

    } catch (XAException xae) {

      showXAException("autocommitxastart expected ", xae);

      System.out.println("Expected XA error code: " + xae.errorCode);

    }

    rs4.next(); System.out.println("acxs " + rs4.getInt(1));

    rs4.close();

    conn4.rollback();

    conn4.close();

    xac4.close();

   

    // test jira-derby 95 - a NullPointerException was returned when passing

    // an incorrect database name (a url in this case) - should now give error XCY00

    Connection dmc95 = ij.startJBMS();

    String sqls;

    try {

      testJira95ds( dmc95, "jdbc:derby:mydb" );

    } catch (SQLException sqle) {

      sqls = sqle.getSQLState();

      if (sqls.equals("XCY00"))

        System.out.println("; ok - expected exception: " + sqls);

      else

        System.out.println("; wrong, unexpected exception: " + sqls + " - " + sqle.toString());

    } catch (Exception e) {

        System.out.println("; wrong, unexpected exception: " + e.toString());

    }

     

    try {

      testJira95xads( dmc95, "jdbc:derby:wombat" );

    } catch (SQLException sqle) {

      sqls = sqle.getSQLState();

      if (sqls.equals("XCY00"))

        System.out.println("; ok - expected exception: " + sqls + "\n");

      else

        System.out.println("; wrong - unexpected exception: " + sqls + " - " + sqle.toString());

    } catch (Exception e) {

        System.out.println("; wrong, unexpected exception: " + e.toString());

    }

    // skip testDSRequestAuthentication for  client because of these

    // two issues:

    // DERBY-1130 : Client should not allow databaseName to be set with

    // setConnectionAttributes

    // DERBY-1131 : Deprecate  Derby DataSource property attributesAsPassword

    if (TestUtil.isDerbyNetClientFramework())

      return;

    testDSRequestAuthentication();

   

  }

  /**

   * @param s

   * @param xar

   * @param conn

   * @throws SQLException

   * @throws XAException

   */

  private void testSetIsolationWithStatement(Statement s, XAResource xar, Connection conn) throws SQLException, XAException {

    Xid xid;

    System.out.println("Issue SQL to change isolation in local transaction");

      s.executeUpdate("set current isolation = RR");

      printState("SQL to change isolation in local", conn);

      xid = new cdsXid(1, (byte) 35, (byte) 47);

      xar.start(xid, XAResource.TMNOFLAGS);

      printState("1st global(new)", conn);

      xar.end(xid, XAResource.TMSUCCESS);

      printState("local", conn);

    System.out.println("Issue SQL to change isolation in local transaction");

      s.executeUpdate("set current isolation = RS");

      printState("SQL to change isolation in local", conn);

      // DERBY-1325 - Isolation level of local connection does not get reset after ending

      // a global transaction that was joined/resumed if the isolation level was changed

      // using SQL

      xar.start(xid, XAResource.TMJOIN);

      printState("1st global(existing)", conn);

      xar.end(xid, XAResource.TMSUCCESS);

      printState("local", conn);

      // DERBY-1325 end test

     

      Xid xid2 = new cdsXid(1, (byte) 93, (byte) 103);

      xar.start(xid2, XAResource.TMNOFLAGS);

      printState("2nd global(new)", conn);

      xar.end(xid2, XAResource.TMSUCCESS);

      xar.start(xid, XAResource.TMJOIN);

      printState("1st global(existing)", conn);

      xar.end(xid, XAResource.TMSUCCESS);

      printState("local", conn);

      xar.start(xid, XAResource.TMJOIN);

      printState("1st global(existing)", conn);

    System.out.println("Issue SQL to change isolation in 1st global transaction");

      s.executeUpdate("set current isolation = UR");

      printState("change isolation of existing 1st global transaction", conn);

      xar.end(xid, XAResource.TMSUCCESS);

      printState("local", conn);

      xar.start(xid2, XAResource.TMJOIN);

      printState("2nd global(existing)", conn);

      xar.end(xid2, XAResource.TMSUCCESS);

      xar.rollback(xid2);

      printState("(After 2nd global rollback) local", conn);

      xar.rollback(xid);

      printState("(After 1st global rollback) local", conn);

  }

  protected void showXAException(String tag, XAException xae) {

    System.out.println(tag + " : XAException - " + xae.getMessage());

  }

  /**

    Create a statement with modified State.

  */

  protected Statement createFloatStatementForStateChecking(Connection conn) throws SQLException {

    Statement s = internalCreateFloatStatementForStateChecking(conn);

    s.setCursorName("StokeNewington");

    s.setFetchDirection(ResultSet.FETCH_REVERSE);

    s.setFetchSize(444);

    s.setMaxFieldSize(713);

    s.setMaxRows(19);

    showStatementState("Create ", s);

    return s;

  }

  protected Statement internalCreateFloatStatementForStateChecking(Connection conn) throws SQLException {

    return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

  }

  protected void showStatementState(String when, Statement s) throws SQLException {

    System.out.println("Statement State @ " + when);

    System.out.println("  getResultSetType() " + rsType(s.getResultSetType()));

    System.out.println("  getResultSetConcurrency() " + rsConcurrency(s.getResultSetConcurrency()));

    System.out.println("  getFetchDirection() " + rsFetchDirection(s.getFetchDirection()));

    System.out.println("  getFetchSize() " + s.getFetchSize());

    System.out.println("  getMaxFieldSize() " + s.getMaxFieldSize());

    System.out.println("  getMaxRows() " + s.getMaxRows());

  }

  protected PreparedStatement createFloatStatementForStateChecking(Connection conn, String sql) throws SQLException {

    PreparedStatement s = internalCreateFloatStatementForStateChecking(conn, sql);

    // Need to make a different cursor name here because of DERBY-1036

    // client won't allow duplicate name.

    //s.setCursorName("StokeNewington");

    s.setCursorName("LondonNW17");

    s.setFetchDirection(ResultSet.FETCH_REVERSE);

    s.setFetchSize(888);

    s.setMaxFieldSize(317);

    s.setMaxRows(91);

    showStatementState("PS Create ", s);

    return s;

  }

  protected CallableStatement createFloatCallForStateChecking(Connection conn, String sql) throws SQLException {

    CallableStatement s = internalCreateFloatCallForStateChecking(conn, sql);

    //DERBY-1036 - need a new name

    //s.setCursorName("StokeNewington");

    s.setCursorName("districtInLondon");

    s.setFetchDirection(ResultSet.FETCH_REVERSE);

    s.setFetchSize(999);

    s.setMaxFieldSize(137);

    s.setMaxRows(85);

    showStatementState("CS Create ", s);

    return s;

  }

  protected PreparedStatement internalCreateFloatStatementForStateChecking(Connection conn, String sql) throws SQLException {

    return conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

  }

  protected CallableStatement internalCreateFloatCallForStateChecking(Connection conn, String sql) throws SQLException {

    return conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

  }

   

    /**

     * Return the Java class and method for the procedure

     * for the nested connection test.

     * checkDataSource 30 will override.

     */

    protected String getNestedMethodName()

    {

        return "checkDataSource.checkNesConn";

    }

  static String rsType(int type) {

    switch (type) {

    case ResultSet.TYPE_FORWARD_ONLY:

      return "FORWARD_ONLY";

    case ResultSet.TYPE_SCROLL_SENSITIVE:

      return "SCROLL_SENSITIVE";

    case ResultSet.TYPE_SCROLL_INSENSITIVE:

      return "SCROLL_INSENSITIVE";

    default:

      return "?? TYPE UNKNOWN ??";

    }

  }

  static String rsConcurrency(int type) {

    switch (type) {

    case ResultSet.CONCUR_READ_ONLY:

      return "READ_ONLY";

    case ResultSet.CONCUR_UPDATABLE:

      return "UPDATEABLE";

    default:

      return "?? CONCURRENCY UNKNOWN ??";

    }

  }

  static String rsFetchDirection(int type) {

    switch (type) {

    case ResultSet.FETCH_FORWARD:

      return "FORWARD";

    case ResultSet.FETCH_REVERSE:

      return "REVERSE";

    case ResultSet.FETCH_UNKNOWN:

      return "UNKNOWN";

    default:

      return "?? FETCH DIRECTION REALLY UNKNOWN ??";

    }

  }

  private static void checkLocks(Connection conn) throws SQLException {

    Statement s = conn.createStatement();

    ResultSet rs = s.executeQuery("SELECT XID, sum(cast (LOCKCOUNT AS INT)) FROM new org.apache.derby.diag.LockTable() AS L GROUP BY XID");

    System.out.println("LOCK TABLE");

        // Don't output actual XID's as they tend for every catalog change

        // to the system.

        int xact_index = 0;

    while (rs.next()) {

      // System.out.println("  xid " + rs.getString(1) + " lock count " + rs.getInt(2));

      System.out.println("  xid row " + xact_index + " lock count " + rs.getInt(2));

            xact_index++;

    }

    s.close();

    System.out.println("END LOCK TABLE");

  }

  private static void queryOnStatement(String tag, Connection conn, Statement s) throws SQLException {

    try {

      if (s.getConnection() != conn)

        System.out.println(tag + ": mismatched Statement connection");

      resultSetQuery(tag, s.executeQuery("select * from ru"));

    } catch (SQLException sqle) {

      System.out.println(tag + ": " + sqle.toString());

    }

  }

  private static void resultSetQuery(String tag, ResultSet rs) throws SQLException {

    String cursorName = rs.getCursorName();

    //  DERBY-1183 client cursor name is not correct.

    // need to truncate the cursor number of the generated name as it might

    // not be consistent.

    if (hasGetCursorNameBug && cursorName.startsWith("SQL_CUR"))

    {

      cursorName = cursorName.substring(0,13);

    }

    System.out.print(tag + ": ru(" + cursorName + ") contents");

    while (rs.next()) {

      System.out.print(" {" + rs.getInt(1) + "}");

    }

    System.out.println("");

    rs.close();

  }

  private void printState(String header, Connection conn) throws SQLException {

    System.out.println(header);

    getHoldability(conn);

    System.out.println("  isolation level " + translateIso(conn.getTransactionIsolation()));

    System.out.println("  auto commit     " + conn.getAutoCommit());

    System.out.println("  read only       " + conn.isReadOnly());

  }

  protected void setHoldability(Connection conn, boolean hold) throws SQLException {

  }

  protected void getHoldability(Connection conn) throws SQLException {

  }

  //calling checkConnection - for use in a procedure to get a nested connection.

  public static void checkNesConn (String dsName) throws SQLException {

        Connection conn = DriverManager.getConnection("jdbc:default:connection");

        new checkDataSource().checkConnection(dsName, conn);     

    }

  public void checkConnection(String dsName, Connection conn) throws SQLException {

    System.out.println("Running connection checks on " + dsName);

    //System.out.println("  url             " + conn.getMetaData().getURL());

    System.out.println("  isolation level " + conn.getTransactionIsolation());

    System.out.println("  auto commit     " + conn.getAutoCommit());

    System.out.println("  read only       " + conn.isReadOnly());

    // when 4729 is fixed, remove the startsWith() clause

    if (dsName.endsWith("DataSource") && !dsName.startsWith("Global"))

      System.out.println("  has warnings    " + (conn.getWarnings() != null));

    Statement s1 = conn.createStatement();

    checkStatement(dsName, conn, s1);

    checkStatement(dsName, conn, conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));

    Connection c1 = conn.getMetaData().getConnection();

    if (c1 != conn)

      System.out.println("FAIL incorrect connection object returned for DatabaseMetaData.getConnection()");

    // Derby-33 - setTypeMap on connection

    try {

      conn.setTypeMap(java.util.Collections.EMPTY_MAP);

      System.out.println("setTypeMap(EMPTY_MAP) - ok");

    } catch (SQLException sqle) {

      System.out.println("setTypeMap(EMPTY_MAP) - FAIL " + sqle.getSQLState() + " - " + sqle.getMessage());

    }

    try {

      conn.setTypeMap(null);

      System.out.println("setTypeMap(null) - FAIL  - should throw exception");

    } catch (SQLException sqle) {

      System.out.println("setTypeMap(null) - ok " + sqle.getSQLState() + " - " + sqle.getMessage());

    }

    try {

      // a populated map, not implemented

      java.util.Map map = new java.util.HashMap();

      map.put("name", "class");

      conn.setTypeMap(map);

      System.out.println("setTypeMap(map) - FAIL  - should throw exception");

    } catch (SQLException sqle) {

      System.out.println("setTypeMap(map) - ok " + sqle.getSQLState() + " - " + sqle.getMessage());

    }

    checkConnectionPreClose(dsName, conn);

    conn.close();

    System.out.println("method calls on a closed connection");

    try {

      conn.close();

      System.out.println(dsName + " <closedconn>.close() no error");

    } catch (SQLException sqle) {

      System.out.println(dsName + " <closedconn>.close() " + sqle.getSQLState() + " - " + sqle.getMessage());

    }

    try {

      conn.createStatement();

      System.out.println(dsName + " <closedconn>.createStatement() no error");

    } catch (SQLException sqle) {

      System.out.println(dsName + " <closedconn>.createStatement() " + sqle.getSQLState() + " - " + sqle.getMessage());

    }

    try {

      s1.execute("values 1");

      System.out.println(dsName + " <closedstmt>.execute() no error");

    } catch (SQLException sqle) {

      System.out.println(dsName + " <closedstmt>.execute() " + sqle.getSQLState() + " - " + sqle.getMessage());

    }

  }

       

    /**

     * Make sure this connection's string is unique (DERBY-243)

     */

    protected static void checkToString(Connection conn) throws Exception

    {

        String str = conn.toString();

        if ( conns.containsKey(str))

        {

            throw new Exception("ERROR: Connection toString() is not unique: "

              + str);

        }

        conns.put(str, conn);

    }

   

    /**

     * Clear out and close connections in the connections

     * hashtable.

     */

    protected static void clearConnections() throws SQLException

    {

        java.util.Iterator it = conns.values().iterator();

        while ( it.hasNext() )

        {

            Connection conn = (Connection)it.next();

            conn.close();

        }

        conns.clear();

    }

   

    /**

     * Get connections  using ij.startJBMS() and make sure

     * they're unique

     */

    protected static void checkJBMSToString() throws Exception

    {

        clearConnections();

        // Open ten connections rather than just two to

        // try and catch any odd uniqueness bugs.  Still

        // no guarantee but is better than just two.

        int numConnections = 10;

        for ( int i = 0 ; i < numConnections ; i++ )

        {

            Connection conn = ij.startJBMS();

            checkToString(conn);

        }

       

        // Now close the connections

        clearConnections();

    }

   

    /**

     * Check uniqueness of connection strings coming from a

     * DataSouce

     */

    protected static void checkToString(DataSource ds) throws Exception

    {

        clearConnections();

       

        int numConnections = 10;

        for ( int i = 0 ; i < numConnections ; i++ )

        {

            Connection conn = ds.getConnection();

            checkToString(conn);

        }

       

        clearConnections();

    }

   

    /**

     * Check uniqueness of strings with a pooled data source.

     * We want to check the PooledConnection as well as the

     * underlying physical connection.

     */

    protected static void checkToString(ConnectionPoolDataSource pds)

        throws Exception

    {

        int numConnections = 10;

       

        //  First get a bunch of pooled connections

        //  and make sure they're all unique

        Hashtable pooledConns = new Hashtable();

        for ( int i = 0 ; i < numConnections ; i++ )

        {

            PooledConnection pc = pds.getPooledConnection();

            String str = pc.toString();

            if ( pooledConns.get(str) != null )

            {

                throw new Exception("Pooled connection toString " +

                  "value " + str + " is not unique");

            }

            pooledConns.put(str, pc);

        }

        // Now check that connections from each of these

        // pooled connections have different string values

        Iterator it = pooledConns.values().iterator();

        clearConnections();

        while ( it.hasNext() )

        {

            PooledConnection pc = (PooledConnection)it.next();

            Connection conn = pc.getConnection();

            checkToString(conn);

        }

        clearConnections();

       

        // Now clear out the pooled connections

        it = pooledConns.values().iterator();

        while ( it.hasNext() )

        {

            PooledConnection pc = (PooledConnection)it.next();

            pc.close();

        }

        pooledConns.clear();

    }

   

    /**

     * Check uniqueness of strings for an XA data source

     */

    protected static void checkToString(XADataSource xds) throws Exception

    {

        int numConnections = 10;

       

        //  First get a bunch of pooled connections

        //  and make sure they're all unique

        Hashtable xaConns = new Hashtable();

        for ( int i = 0 ; i < numConnections ; i++ )

        {

            XAConnection xc = xds.getXAConnection();

            String str = xc.toString();

            if ( xaConns.get(str) != null )

            {

                throw new Exception("XA connection toString " +

                  "value " + str + " is not unique");

            }

            xaConns.put(str, xc);

        }

        // Now check that connections from each of these

        // pooled connections have different string values

        Iterator it = xaConns.values().iterator();

        clearConnections();

        while ( it.hasNext() )

        {

            XAConnection xc = (XAConnection)it.next();

            Connection conn = xc.getConnection();

            checkToString(conn);

        }

        clearConnections();

       

        // Now clear out the pooled connections

        it = xaConns.values().iterator();

        while ( it.hasNext() )

        {

            XAConnection xc = (XAConnection)it.next();

            xc.close();

        }

        xaConns.clear();

    }

  protected void checkConnectionPreClose(String dsName, Connection conn) throws SQLException {

    if (dsName.endsWith("DataSource")) {

      // see if setting the state is carried over to any future connection from the

      // data source object.

      try {

        conn.setReadOnly(true);

      } catch (SQLException sqle) {

        // cannot set read-only in an active transaction, & sometimes

        // connections are active at this point.

      }

    }

  }

  protected void checkStatement(String dsName, Connection conn, Statement s) throws SQLException {