checkdatasource.java

derby database source code.good for you.

	}

	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 {

		Connection c1 = s.getConnection();
		if (c1 != conn)
			System.out.println("FAIL incorrect connection object returned for Statement.getConnection()");

		s.addBatch("insert into y values 1");
		s.addBatch("insert into y values 2,3");
		int[] states = s.executeBatch();
		if (states[0] != 1)
			System.out.println("FAIL invalid update count for first batch statement");
		if (states[1] != 2)
			System.out.println("FAIL invalid update count for second batch statement");

        ResultSet rs = s.executeQuery("VALUES 1");
        if (rs.getStatement() != s)
            System.out.println(dsName + " FAIL incorrect Statement object returned for ResultSet.getStatement");
        rs.close();
		s.close();
	}

	private static void testDSRequestAuthentication() throws SQLException {

		EmbeddedDataSource ds = new EmbeddedDataSource();

		System.out.println("DataSource - EMPTY");
		dsConnectionRequests(ds);

		System.out.println("DataSource - connectionAttributes=databaseName=wombat");
		ds.setConnectionAttributes("databaseName=wombat");
		dsConnectionRequests(ds);
		ds.setConnectionAttributes(null);

		System.out.println("DataSource - attributesAsPassword=true");
		ds.setAttributesAsPassword(true);
		dsConnectionRequests(ds);
		ds.setAttributesAsPassword(false);

		System.out.println("DataSource - attributesAsPassword=true, connectionAttributes=databaseName=kangaroo");
		ds.setAttributesAsPassword(true);
		ds.setConnectionAttributes("databaseName=kangaroo");
		dsConnectionRequests(ds);
		ds.setAttributesAsPassword(false);
		ds.setConnectionAttributes(null);

		System.out.println("Enable Authentication");
		ds.setDatabaseName("wombat");
		Connection cadmin = ds.getConnection();
		CallableStatement cs = cadmin.prepareCall("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)");
		cs.setString(1, "derby.user.fred");
		cs.setString(2, "wilma");
		cs.execute();

		cs.setString(1, "derby.authentication.provider");
		cs.setString(2, "BUILTIN");
		cs.execute();

		cs.setString(1, "derby.connection.requireAuthentication");
		cs.setString(2, "true");
		cs.execute();

		cs.close();

		cadmin.close();

		ds.setShutdownDatabase("shutdown");
		try {
			ds.getConnection();
		} catch (SQLException sqle) {
			System.out.println(sqle.getSQLState() + ":" + sqle.getMessage() );
		}

		ds.setDatabaseName(null);
		ds.setShutdownDatabase(null);

		System.out.println("AUTHENTICATION NOW ENABLED");

		System.out.println("DataSource - attributesAsPassword=true");
		ds.setAttributesAsPassword(true);
		dsConnectionRequests(ds);
		ds.setAttributesAsPassword(false);

		// ensure the DS property password is not treated as a set of attributes.
		System.out.println("DataSource - attributesAsPassword=true, user=fred, password=databaseName=wombat;password=wilma");
		ds.setAttributesAsPassword(true);
		ds.setUser("fred");
		ds.setPassword("databaseName=wombat;password=wilma");
		dsConnectionRequests(ds);
		ds.setAttributesAsPassword(false);
		ds.setUser(null);
		ds.setPassword(null);
		ds = null;

		// now with ConnectionPoolDataSource
		EmbeddedConnectionPoolDataSource cpds = new EmbeddedConnectionPoolDataSource();
		System.out.println("ConnectionPoolDataSource - EMPTY");
		dsConnectionRequests((ConnectionPoolDataSource)cpds);

		System.out.println("ConnectionPoolDataSource - connectionAttributes=databaseName=wombat");
		cpds.setConnectionAttributes("databaseName=wombat");
		dsConnectionRequests((ConnectionPoolDataSource)cpds);
		cpds.setConnectionAttributes(null);

		System.out.println("ConnectionPoolDataSource - attributesAsPassword=true");
		cpds.setAttributesAsPassword(true);
		dsConnectionRequests((ConnectionPoolDataSource)cpds);
		cpds.setAttributesAsPassword(false);
		
		// ensure the DS property password is not treated as a set of attributes.
		System.out.println("ConnectionPoolDataSource - attributesAsPassword=true, user=fred, password=databaseName=wombat;password=wilma");
		cpds.setAttributesAsPassword(true);
		cpds.setUser("fred");
		cpds.setPassword("databaseName=wombat;password=wilma");
		dsConnectionRequests((ConnectionPoolDataSource)cpds);
		cpds.setAttributesAsPassword(false);
		cpds.setUser(null);
		cpds.setPassword(null);
		cpds = null;

		// now with XADataSource
		EmbeddedXADataSource xads = new EmbeddedXADataSource();
		System.out.println("XADataSource - EMPTY");
		dsConnectionRequests((XADataSource) xads);

		System.out.println("XADataSource - databaseName=wombat");
		xads.setDatabaseName("wombat");
		dsConnectionRequests((XADataSource) xads);
		xads.setDatabaseName(null);

		System.out.println("XADataSource - connectionAttributes=databaseName=wombat");
		xads.setConnectionAttributes("databaseName=wombat");
		dsConnectionRequests((XADataSource) xads);
		xads.setConnectionAttributes(null);