Java Code Examples for java.sql.Connection#getWarnings()

/**
 * Create a new database and populate it.
 * <p>
 * The method fails with an exception if the database already exists.
 * This is because it is the creation process that is to be tested.
 *
 * @param dbName name of the database to create
 * @return A connection the to the newly created database.
 * @throws SQLException if the database already exist, or
 *      a general error happens during database interaction
 */
protected Connection createAndPopulateDB(String dbName)
        throws SQLException {
    Connection con = getConnection(dbName, CORRECT_KEY);
    SQLWarning warning = con.getWarnings();
    // If the database already exists, fail the test.
    if (warning != null) {
        if ("01J01".equals(warning.getSQLState())) {
            fail("Refusing to continue, database already exists <" +
                    warning.getMessage() + ">");
        }
    }
    Statement stmt = con.createStatement();
    stmt.executeUpdate("CREATE TABLE " + TABLE + " (id int NOT NULL, " +
            "val int NOT NULL, PRIMARY KEY(id))");
    stmt.close();
    PreparedStatement ps = con.prepareStatement("INSERT INTO " + TABLE +
            " (id, val) VALUES (?,?)");
    for (int i=0; i < DATA.length; i++) {
        ps.setInt(1, i);
        ps.setInt(2, DATA[i]);
        ps.executeUpdate();
    }
    ps.close();
    return con;
}
 

/**
 * Create a new database and populate it.
 * <p>
 * The method fails with an exception if the database already exists.
 * This is because it is the creation process that is to be tested.
 *
 * @param dbName name of the database to create
 * @return A connection the to the newly created database.
 * @throws SQLException if the database already exist, or
 *      a general error happens during database interaction
 */
protected Connection createAndPopulateDB(String dbName)
        throws SQLException {
    Connection newCon = getConnection(dbName, CORRECT_KEY);
    SQLWarning warning = newCon.getWarnings();
    // If the database already exists, fail the test.
    if (warning != null) {
        if ("01J01".equals(warning.getSQLState())) {
            fail("Refusing to continue, database already exists <" +
                    warning.getMessage() + ">");
        }
    }
    Statement stmt = newCon.createStatement();
    stmt.executeUpdate("CREATE TABLE " + TABLE + " (id int NOT NULL, " +
            "val int NOT NULL, PRIMARY KEY(id))");
    stmt.close();
    PreparedStatement ps = newCon.prepareStatement("INSERT INTO " + TABLE +
            " (id, val) VALUES (?,?)");
    for (int i=0; i < DATA.length; i++) {
        ps.setInt(1, i);
        ps.setInt(2, DATA[i]);
        ps.executeUpdate();
    }
    ps.close();
    return newCon;
}
 

private Connection  passwordExpiring( boolean expiring, String dbName, String user )
    throws Exception
{
    Connection  conn = null;

    reportConnectionAttempt( dbName, user, getPassword( user ), true );

    String  expectedSQLState = DBO.equals( user ) ? DBO_PASSWORD_EXPIRING : PASSWORD_EXPIRING;

    conn = openConnection( dbName, user, true, null );

    SQLWarning  warning = conn.getWarnings();

    if ( expiring )
    {
        assertNotNull( tagError( "Should have seen a warning" ), warning );
        assertSQLState( expectedSQLState, warning );
    }
    else
    {
        assertNull( tagError( "Should not have seen a warning" ), warning );
    }


    return conn;
}
 

/**
 * Helper method available to all commands to dump any warnings
 * associated with a connection. The set of warnings is cleared
 * after display.
 * 
 * @param session The session to use for writing
 * @param conn The connection that may, or may not, contain warnings.
 */
static public void printWarnings(Session session, Connection conn) {
    
    try {
        
        SQLWarning w = conn.getWarnings();
        if (w != null) {
            
            printWarnings(session, w);
            conn.clearWarnings();
        }
    }
    catch (SQLException e) {
        
        /* IGNORED */
    }
}
 

@Override
public String call() throws Exception {
    MyTownDatasource datasource = MyTown.instance.datasource;
    if (datasource == null) {
        return "Datasource is not initialized yet";
    }
    String str = "";

    str += String.format("Class: %s\n", datasource.getClass().getName());
    str += String.format("Stats (Towns: %s, Residents: %s, Nations: %s, Blocks: %s, Ranks: %s, Plots: %s)\n", MyTownUniverse.instance.towns.size(), MyTownUniverse.instance.residents.size(), 0 /*MyTownUniverse.instance.getNationsMap().size()*/, MyTownUniverse.instance.blocks.size(), MyTownUniverse.instance.ranks.size(), MyTownUniverse.instance.plots.size());
    Connection conn = datasource.getBridge().getConnection();

    str += String.format("AutoCommit: %s%n", conn.getAutoCommit());
    str += String.format("----- SQL Warnings -----%n");
    str += String.format("%s8 | %s9 | %s%n", "SQLState", "ErrorCode", "Message");
    SQLWarning sqlWarning = conn.getWarnings();
    do {
        str += String.format("%s8 | %s9 | %s%n", sqlWarning.getSQLState(), sqlWarning.getErrorCode(), sqlWarning.getMessage());
    } while (sqlWarning.getNextWarning() != null);
    return str;
}
 

/**
 * Create a new database and populate it.
 * <p>
 * The method fails with an exception if the database already exists.
 * This is because it is the creation process that is to be tested.
 *
 * @param dbName name of the database to create
 * @return A connection the to the newly created database.
 * @throws SQLException if the database already exist, or
 *      a general error happens during database interaction
 */
protected Connection createAndPopulateDB(String dbName)
        throws SQLException {
    Connection con = getConnection(dbName, CORRECT_KEY);
    SQLWarning warning = con.getWarnings();
    // If the database already exists, fail the test.
    if (warning != null) {
        if ("01J01".equals(warning.getSQLState())) {
            fail("Refusing to continue, database already exists <" +
                    warning.getMessage() + ">");
        }
    }
    Statement stmt = con.createStatement();
    stmt.executeUpdate("CREATE TABLE " + TABLE + " (id int NOT NULL, " +
            "val int NOT NULL, PRIMARY KEY(id))");
    stmt.close();
    PreparedStatement ps = con.prepareStatement("INSERT INTO " + TABLE +
            " (id, val) VALUES (?,?)");
    for (int i=0; i < DATA.length; i++) {
        ps.setInt(1, i);
        ps.setInt(2, DATA[i]);
        ps.executeUpdate();
    }
    ps.close();
    return con;
}
 

protected void printIsolationLevel(Connection conn) {
  try {
  	int isolation = conn.getTransactionIsolation();
  	String isoLevel;
  	switch (isolation) {
  	case Connection.TRANSACTION_NONE:
  		isoLevel = "TRANSACTION_NONE";
  		break;
  	case Connection.TRANSACTION_READ_COMMITTED:
  		isoLevel = "TRANSACTION_READ_COMMITTED";
  		break;
  	case Connection.TRANSACTION_REPEATABLE_READ:
  		isoLevel = "TRANSACTION_REPEATABLE_READ";
  		break;
  	case Connection.TRANSACTION_SERIALIZABLE:
  		isoLevel = "TRANSACTION_SERIALIZABLE";
  		break;
 		default:
  			isoLevel = "unknown";    		    		
  	}
  	Log.getLogWriter().info("the connection isolation level is " + isoLevel);
  	java.sql.SQLWarning w =conn.getWarnings();
  	SQLHelper.printSQLWarning(w);
  } catch (SQLException se) {
  	
  }
}
 

/**
 * Connect to a database to test whether a connection can be made
 *
 * @param writer	connection to send message to
 * @param database 	database directory to connect to
 * @param user		user to use
 * @param password	password to use
 */
private void connectToDatabase(DDMWriter writer, String database, String user, 
	String password) throws Exception
{
	Properties p = new Properties();
	if (user != null)
		p.put("user", user);
	if (password != null)
		p.put("password", password);
 	try {
    		Class.forName(CLOUDSCAPE_DRIVER);
	}
	catch (Exception e) {
		sendMessage(writer, ERROR, e.getMessage());
		return;
  	}
 	try {
		//Note, we add database to the url so that we can allow additional
		//url attributes
		Connection conn = getDriver().connect(Attribute.PROTOCOL+database, p);
		// send warnings
		SQLWarning warn = conn.getWarnings();
		if (warn != null)
			sendSQLMessage(writer, warn, SQLWARNING);
		else
			sendOK(writer);
		conn.close();
	} catch (SQLException se) {
		sendSQLMessage(writer, se, SQLERROR);
  	}
}
 

/**
 * Create a new database and populate it.
 * <p>
 * The method fails with an exception if the database already exists.
 * This is because it is the creation process that is to be tested.
 *
 * @param dbName name of the database to create
 * @param algorithm EncryptionAlgorithm
 * @param otherAttributes array for all other attributes 
 *        (Note: dataEncryption=true is already set in getConnection) 
 * @return A connection the to the newly created database.
 * @throws SQLException if the database already exist, or
 *      a general error happens during database interaction
 */
protected Connection createAndPopulateDB(
    String dbName, String algorithm, String[] otherAttributes)
throws SQLException {
    try {
        Connection con = getConnection(dbName, algorithm, otherAttributes);
        SQLWarning warning = con.getWarnings();
        // If the database already exists, fail the test.
        if (warning != null) {
            if ("01J01".equals(warning.getSQLState())) {
                fail("Refusing to continue, database already exists <" +
                    warning.getMessage() + ">");
            }
        }
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE " + TABLE + " (id int NOT NULL, " +
        "val int NOT NULL, PRIMARY KEY(id))");
        stmt.close();
        PreparedStatement ps = con.prepareStatement("INSERT INTO " + TABLE +
        " (id, val) VALUES (?,?)");
        for (int i=0; i < DATA.length; i++) {
            ps.setInt(1, i);
            ps.setInt(2, DATA[i]);
            ps.executeUpdate();
        }
        ps.close();
        return con;
    }
    catch (SQLException e) {
        // if it fails, it should only be because of non-existing
        // support for unrestricted encryption policy.
        assertSQLState("XJ001", e);
        return null;
    }
}
 

/**
 * Assert that a warning is chained to the connection.
 *
 * @param conn     the connection
 * @param expected the expected SQLState of the warning
 */
public static void assertWarning(Connection conn, String expected)
    throws SQLException {
    SQLWarning firstWarning = conn.getWarnings();
    assertNotNull(firstWarning);

    for (SQLWarning warning = firstWarning; warning != null; warning = warning.getNextWarning()) {
        if (expected.equals(warning.getSQLState())) {
            return;
        }
    }

    fail("Expected to see a SQLWarning with the SQLState " + expected);
}
 

/**
	 * Connect to a database to test whether a connection can be made
	 *
	 * @param writer	connection to send message to
	 * @param database 	database directory to connect to
	 * @param user		user to use
	 * @param password	password to use
	 */
	private void connectToDatabase(DDMWriter writer, String database, String user, 
		String password) throws Exception
	{
		Properties p = new Properties();
		if (user != null)
			p.put("user", user);
		if (password != null)
			p.put("password", password);
	 	try {
     		Class.forName(CLOUDSCAPE_DRIVER);
		}
		catch (Exception e) {
			sendMessage(writer, ERROR, e.getMessage());
			return;
	  	}
	 	try {
			//Note, we add database to the url so that we can allow additional
			//url attributes
// GemStone changes BEGIN
	 	  Connection conn = DriverManager.getConnection(com.pivotal.gemfirexd.Attribute.PROTOCOL, p);
			/* Connection conn = DriverManager.getConnection(Attribute.PROTOCOL+database, p); */
// GemStone changes END
			// send warnings
			SQLWarning warn = conn.getWarnings();
			if (warn != null)
				sendSQLMessage(writer, warn, SQLWARNING);
			else
				sendOK(writer);
			conn.close();
			return;
	  	} catch (SQLException se) {
			sendSQLMessage(writer, se, SQLERROR);
	  	}
	}
 

/**
 * Logs any warnings associated to the given connection. Note that the connection needs
 * to be open for this.
 * 
 * @param connection The open connection
 */
protected void logWarnings(Connection connection) throws SQLException
{
    SQLWarning warning = connection.getWarnings();

    while (warning != null)
    {
        getLog().warn(warning.getLocalizedMessage(), warning.getCause());
        warning = warning.getNextWarning();
    }
}
 

protected void printIsolationLevel(Connection conn) {
  try {
  	int isolation = conn.getTransactionIsolation();
  	String isoLevel;
  	switch (isolation) {
  	case Connection.TRANSACTION_NONE:
  		isoLevel = "TRANSACTION_NONE";
  		break;
  	case Connection.TRANSACTION_READ_COMMITTED:
  		isoLevel = "TRANSACTION_READ_COMMITTED";
  		break;
  	case Connection.TRANSACTION_REPEATABLE_READ:
  		isoLevel = "TRANSACTION_REPEATABLE_READ";
  		break;
  	case Connection.TRANSACTION_SERIALIZABLE:
  		isoLevel = "TRANSACTION_SERIALIZABLE";
  		break;
 		default:
  			isoLevel = "unknown";    		    		
  	}
  	Log.getLogWriter().info("the connection isolation level is " + isoLevel);
  	java.sql.SQLWarning w =conn.getWarnings();
  	SQLHelper.printSQLWarning(w);
  } catch (SQLException se) {
  	
  }
}
 

/**
	 * Connect to a database to test whether a connection can be made
	 *
	 * @param writer	connection to send message to
	 * @param database 	database directory to connect to
	 * @param user		user to use
	 * @param password	password to use
	 */
	private void connectToDatabase(DDMWriter writer, String database, String user, 
		String password) throws Exception
	{
		Properties p = new Properties();
		if (user != null)
			p.put("user", user);
		if (password != null)
			p.put("password", password);
	 	try {
     		Class.forName(CLOUDSCAPE_DRIVER);
		}
		catch (Exception e) {
			sendMessage(writer, ERROR, e.getMessage());
			return;
	  	}
	 	try {
			//Note, we add database to the url so that we can allow additional
			//url attributes
// GemStone changes BEGIN
	 	  Connection conn = DriverManager.getConnection(com.pivotal.gemfirexd.Attribute.PROTOCOL, p);
			/* Connection conn = DriverManager.getConnection(Attribute.PROTOCOL+database, p); */
// GemStone changes END
			// send warnings
			SQLWarning warn = conn.getWarnings();
			if (warn != null)
				sendSQLMessage(writer, warn, SQLWARNING);
			else
				sendOK(writer);
			conn.close();
			return;
	  	} catch (SQLException se) {
			sendSQLMessage(writer, se, SQLERROR);
	  	}
	}
 

/**
 * Logs any warnings associated to the given connection. Note that the connection needs
 * to be open for this.
 * 
 * @param connection The open connection
 */
protected void logWarnings(Connection connection) throws SQLException
{
    SQLWarning warning = connection.getWarnings();

    while (warning != null)
    {
        getLog().warn(warning.getLocalizedMessage(), warning.getCause());
        warning = warning.getNextWarning();
    }
}
 

/**
     * Scroll sensitive cursor tests
     *
     * This method tests scroll sensitive cursors. (Not implemented, so we
     * should get back scroll insensitive curors with read only concurrency.)
     *
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */

    public void testScrollSensitive() throws SQLException {
        Connection conn = getConnection();
        ResultSet rs;
        SQLWarning warning;
        Statement s_s_r = null; // sensitive, read only
        Statement s_s_u = null; // sensitive, updatable

        s_s_r = createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        // We should have gotten a warning and a scroll insensitive cursor
        warning = conn.getWarnings();
        assertNotNull(warning);
        conn.clearWarnings();

        // Verify that result set from statement is
        // scroll insensitive and read only
        rs = s_s_r.executeQuery("select * from t");
        assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
        assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
        rs.close();

        // Close the statement
        s_s_r.close();

        //GemFireXD does not allow scrollable updatable cursors
/*        s_s_u = createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        // We should have gotten 1 warning and a updatable scroll
        // insensitive cursor.
        warning = conn.getWarnings();
        assertNotNull(warning);
        conn.clearWarnings();

        // Verify that result set from statement is
        // scroll insensitive and read only
        rs = s_s_u.executeQuery("select * from t");
        assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
        assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());

        rs.close();
        */
    }
 

/**
 * Authenticate the passed-in credentials against another Derby database. This is done
    * by getting a connection to the credentials database using the supplied username
    * and password. If the connection attempts succeeds, then authentication succeeds.
 *
 * @param userName		The user's name used to connect to JBMS system
 * @param userPassword	The user's password used to connect to JBMS system
 * @param databaseName	The database which the user wants to connect to.
 */
private boolean	authenticateRemotely
       (
        String userName,
        String userPassword,
        String databaseName
        )
       throws StandardException, SQLWarning
{
       // this catches the case when someone specifies db.authentication.provider=NATIVE::LOCAL
       // at the system level
       if ( _credentialsDB == null )
       {
           throw StandardException.newException( SQLState.BAD_NATIVE_AUTH_SPEC );
       }
       
       SQLWarning  warnings = null;

       try {
           Properties  properties = new Properties();
           properties.setProperty( Attribute.USERNAME_ATTR, userName );
           properties.setProperty( Attribute.PASSWORD_ATTR, userPassword );

           String  connectionURL = Attribute.PROTOCOL + _credentialsDB;

           Connection  conn = InternalDriver.activeDriver().connect( connectionURL, properties );
           
           warnings = conn.getWarnings();
           conn.close();
       }
       catch (SQLException se)
       {
           String  sqlState = se.getSQLState();

           if ( SQLState.LOGIN_FAILED.equals( sqlState ) ) { return false; }
           else if ( SQLState.DATABASE_NOT_FOUND.startsWith( sqlState ) )
           {
               throw StandardException.newException( SQLState.MISSING_CREDENTIALS_DB, _credentialsDB );
           }
           else { throw wrap( se ); }
       }

       // let warnings percolate up so that EmbedConnection can handle notifications
       // about expiring passwords
       if ( warnings != null ) { throw warnings; }

       // If we get here, then we successfully connected to the credentials database. Hooray.
       return true;
   }
 

/**
 * Scroll sensitive cursor tests
 * 
 * This method tests scroll sensitive cursors. (Not implemented, so we
 * should get back scroll insensitive curors with read only concurrency.)
 * 
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */

public void testScrollSensitive() throws SQLException {
    Connection conn = getConnection();
    ResultSet rs;
    SQLWarning warning;
    Statement s_s_r = null; // sensitive, read only
    Statement s_s_u = null; // sensitive, updatable

    s_s_r = createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_READ_ONLY);

    // We should have gotten a warning and a scroll insensitive cursor
    warning = conn.getWarnings();
    assertNotNull(warning);
    conn.clearWarnings();

    // Verify that result set from statement is
    // scroll insensitive and read only
    rs = s_s_r.executeQuery("select * from t");
    assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
    assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
    rs.close();

    // Close the statement
    s_s_r.close();

    s_s_u = createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
    // We should have gotten 1 warning and a updatable scroll
    // insensitive cursor.
    warning = conn.getWarnings();
    assertNotNull(warning);
    conn.clearWarnings();

    // Verify that result set from statement is
    // scroll insensitive and read only
    rs = s_s_u.executeQuery("select * from t");
    assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
    assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());

    rs.close();
}
 

/**
 * CallableStatement tests.
 * 
 * @exception SQLException
 *                Thrown if some unexpected error happens
 */

public void testCallableStatements() throws SQLException {
    Connection conn = getConnection();

    SQLWarning warning;
    CallableStatement cs_s_r = null; // sensitive, read only
    CallableStatement cs_s_u = null; // sensitive, updatable
    CallableStatement cs_i_r = null; // insensitive, read only
    CallableStatement cs_f_r = null; // forward only, read only

    cs_s_r = prepareCall("values cast (? as Integer)",
            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

    // We should have gotten 1 warnings
    warning = conn.getWarnings();
    assertNotNull(warning);
    if (!isDerbyNetClient)
        assertEquals("01J02", warning.getSQLState());
    else
        assertEquals("01J10", warning.getSQLState());

    JDBC.assertNoWarnings(warning.getNextWarning());

    conn.clearWarnings();
    cs_s_r.close();

    cs_s_u = prepareCall("values cast (? as Integer)",
            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    // We should have gotten 1 warning
    warning = conn.getWarnings();
    assertNotNull(warning);
    if (!isDerbyNetClient)
        assertEquals("01J02", warning.getSQLState());
    else
        assertEquals("01J10", warning.getSQLState());

    JDBC.assertNoWarnings(warning.getNextWarning());
    conn.clearWarnings();
    cs_s_u.close();

    cs_i_r = prepareCall("values cast (? as Integer)",
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    // We should have gotten 0 warnings
    JDBC.assertNoWarnings(conn.getWarnings());

    conn.clearWarnings();
    cs_i_r.close();

    cs_f_r = prepareCall("values cast (? as Integer)",
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    // We should have gotten 0 warnings
    JDBC.assertNoWarnings(conn.getWarnings());

    conn.clearWarnings();
    cs_f_r.close();

}
 

/**
     * Scroll sensitive cursor tests
     *
     * This method tests scroll sensitive cursors. (Not implemented, so we
     * should get back scroll insensitive curors with read only concurrency.)
     *
     * @exception SQLException
     *                Thrown if some unexpected error happens
     */

    public void testScrollSensitive() throws SQLException {
        Connection conn = getConnection();
        ResultSet rs;
        SQLWarning warning;
        Statement s_s_r = null; // sensitive, read only
        Statement s_s_u = null; // sensitive, updatable

        s_s_r = createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        // We should have gotten a warning and a scroll insensitive cursor
        warning = conn.getWarnings();
        assertNotNull(warning);
        conn.clearWarnings();

        // Verify that result set from statement is
        // scroll insensitive and read only
        rs = s_s_r.executeQuery("select * from t");
        assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
        assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
        rs.close();

        // Close the statement
        s_s_r.close();

        //GemFireXD does not allow scrollable updatable cursors
/*        s_s_u = createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        // We should have gotten 1 warning and a updatable scroll
        // insensitive cursor.
        warning = conn.getWarnings();
        assertNotNull(warning);
        conn.clearWarnings();

        // Verify that result set from statement is
        // scroll insensitive and read only
        rs = s_s_u.executeQuery("select * from t");
        assertEquals(ResultSet.TYPE_SCROLL_INSENSITIVE, rs.getType());
        assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());

        rs.close();
        */
    }