checkdatasource.java

derby database source code.good for you.

		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");