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

/**
 * Check to see that ResultSet closes implicitly when holdability is set to
 * CLOSE_CURORS_AT_COMMIT.
 * 
 * @param conn
 *            The Connection
 * @throws SQLException
 */
private void checkSingleRSCloseCursorsAtCommit(Connection conn)
        throws SQLException {
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = s.executeQuery("select * from AutoCommitTable");
    // drain but do not close resultset.
    while (rs.next())
        ;
    assertFalse("Fail Auto-commit unsuccessful", locksHeld());
    try {
        rs.next();
        fail("FAIL. ResultSet not closed implicitly");
    } catch (SQLException e) {
        assertEquals("XCL16", e.getSQLState());

    }
}
 

public void testNoHoldScrollableResults() throws SQLException{
    Connection conn = getConnection();
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    // GemStone changes BEGIN
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // GemStone changes END
    // Beetle 4551 - insensitive cursor uses estimated row count which
    // might be pessimistic and will get out of memory error
    Statement s = createStatement();
    s.executeUpdate("create table big(a int generated always as identity)");

    for (int i = 0; i < 10; i++)
        s.executeUpdate("insert into big values(default)");
    PreparedStatement ps_c1 = conn.prepareStatement("select * from big b1 left outer join  big b2 on b1.a = b2.a left outer join  big b3 on b2.a = b3.a left outer join big b4 on b3.a = b4.a left outer join (big b5 left outer join (big b6 left outer join (big b7 left outer join big b8 on b7.a = b8.a) on b6.a=b7.a) on b5.a = b6.a) on b4.a = b5.a");
    ResultSet rs = ps_c1.executeQuery();
    // GemStone changes BEGIN
    rs.close();
    conn.commit();
    // GemStone changes END
 s.executeUpdate("drop table big");
}
 

/**
 * Test sequence for testing if the connection holdability is reset.
 *
 * @param closeConnection determines if the logical connection is
 *      explicitly closed before a new one is obtained
 * @throws SQLException if something goes wrong...
 */
private void doTestHoldabilityIsReset(final boolean closeConnection)
        throws SQLException {
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(7));
    J2EEDataSource.setBeanProperty(cpDs, "createDatabase", "create");
    PooledConnection pc = cpDs.getPooledConnection();
    // Keep track of our own connection, the framework currently creates
    // a new pooled connection and then obtains a connection from that.
    // Statement pooling only works within a single pooled connection.
    Connection con = pc.getConnection();
    assertEquals("Unexpected default holdability",
            ResultSet.HOLD_CURSORS_OVER_COMMIT, con.getHoldability());
    con.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals("Holdability not updated",
            ResultSet.CLOSE_CURSORS_AT_COMMIT, con.getHoldability());
    if (closeConnection) {
        con.close();
    }
    con = pc.getConnection();
    assertEquals("Holdability not reset",
            ResultSet.HOLD_CURSORS_OVER_COMMIT, con.getHoldability());
    pc.close();
}
 

private void assertConnectionPreClose(String dsName, Connection conn) 
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

private void assertConnectionPreClose(String dsName, Connection conn)
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

/**
 * Check to see that ResultSet closes implicitly when holdability is set to
 * CLOSE_CURORS_AT_COMMIT.
 * 
 * @param conn
 *            The Connection
 * @throws SQLException
 */
private void checkSingleRSCloseCursorsAtCommit(Connection conn)
        throws SQLException {
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = s.executeQuery("select * from AutoCommitTable");
    // drain but do not close resultset.
    while (rs.next())
        ;
    assertFalse("Fail Auto-commit unsuccessful", locksHeld());
    try {
        rs.next();
        fail("FAIL. ResultSet not closed implicitly");
    } catch (SQLException e) {
        assertEquals("XCL16", e.getSQLState());

    }
}
 

public void testNoHoldScrollableResults() throws SQLException{
    Connection conn = getConnection();
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    // GemStone changes BEGIN
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // GemStone changes END
    // Beetle 4551 - insensitive cursor uses estimated row count which
    // might be pessimistic and will get out of memory error
    Statement s = createStatement();
    s.executeUpdate("create table big(a int generated always as identity)");

    for (int i = 0; i < 10; i++)
        s.executeUpdate("insert into big values(default)");
    PreparedStatement ps_c1 = conn.prepareStatement("select * from big b1 left outer join  big b2 on b1.a = b2.a left outer join  big b3 on b2.a = b3.a left outer join big b4 on b3.a = b4.a left outer join (big b5 left outer join (big b6 left outer join (big b7 left outer join big b8 on b7.a = b8.a) on b6.a=b7.a) on b5.a = b6.a) on b4.a = b5.a");
    ResultSet rs = ps_c1.executeQuery();
    // GemStone changes BEGIN
    rs.close();
    conn.commit();
    // GemStone changes END
 s.executeUpdate("drop table big");
}
 

private void assertConnectionPreClose(String dsName, Connection conn) 
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

/**
 * Test sequence for testing if the connection holdability is reset.
 *
 * @param closeConnection determines if the logical connection is
 *      explicitly closed before a new one is obtained
 * @throws SQLException if something goes wrong...
 */
private void doTestHoldabilityIsReset(final boolean closeConnection)
        throws SQLException {
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(7));
    J2EEDataSource.setBeanProperty(cpDs, "createDatabase", "create");
    PooledConnection pc = cpDs.getPooledConnection();
    // Keep track of our own connection, the framework currently creates
    // a new pooled connection and then obtains a connection from that.
    // Statement pooling only works within a single pooled connection.
    Connection con = pc.getConnection();
    assertEquals("Unexpected default holdability",
            ResultSet.HOLD_CURSORS_OVER_COMMIT, con.getHoldability());
    con.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals("Holdability not updated",
            ResultSet.CLOSE_CURSORS_AT_COMMIT, con.getHoldability());
    if (closeConnection) {
        con.close();
    }
    con = pc.getConnection();
    assertEquals("Holdability not reset",
            ResultSet.HOLD_CURSORS_OVER_COMMIT, con.getHoldability());
    pc.close();
}
 

private void assertConnectionPreClose(String dsName, Connection conn)
        throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

/**
 * Tests for two things:
 * 
 * 1) The ResultSet does not close implicitly when the ResultSet completes
 * and holdability == HOLD_CURSORS_OVER_COMMIT
 * 
 * 2) The ResultSet auto-commits when it completes and auto-commit is on.
 * 
 * @param conn
 *            The Connection
 * @throws SQLException
 */
private void checkSingleRSAutoCommit(Connection conn) throws SQLException {
    conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
    // Single RS auto-commit test:
    Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);

    ResultSet rs = s.executeQuery("select * from AutoCommitTable");
    // drain results but don't close
    while (rs.next())
        ;
    // test that resultset was not implicitly closed but autoCommit occured.
    assertFalse("Fail Auto-commit unsuccessful", locksHeld());

    assertFalse("Final call of rs.next() should return false", rs.next());
    rs.close();
    // check that next() on closed ResultSet throws an exception
    try {
        rs.next();
        fail("FAIL Error should have occured with rs.next() on a closed ResultSet");
    } catch (SQLException se) {
        assertEquals("XCL16", se.getSQLState());
    }
}
 

static public Connection mystartJBMS() throws Throwable {
	Connection conn = null;
	if (startByIJ == true) {
		conn = ij.startJBMS();
	} else
		try {
			conn = DriverManager.getConnection(dbURL + ";create=false");
			conn.setAutoCommit(false);
			conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
		} catch (SQLException se) {
			System.out.println("connect failed  for " + dbURL);
			JDBCDisplayUtil.ShowException(System.out, se);
		}
		return (conn);
}
 

static public Connection mystartJBMS() throws Throwable {
	Connection conn = null;
	if (startByIJ == true) {
		conn = ij.startJBMS();
	} else
		try {
			conn = DriverManager.getConnection(dbURL + ";create=false");
			conn.setAutoCommit(false);
			conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
		} catch (SQLException se) {
			System.out.println("connect failed  for " + dbURL);
			JDBCDisplayUtil.ShowException(System.out, se);
		}
		return (conn);
}
 

/**
 * Checks that Holdability gets reset on PooledConnection.getConnection()
 * @param desc
 * @param pc1
 * @throws SQLException
 */
private static void assertPooledConnHoldability(
    String desc, PooledConnection pc1) throws SQLException 
{ 
    // **Test holdability state
    Connection conn  = pc1.getConnection();
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    // reset the connection and see if the holdability gets reset
    // to HOLD_CURSORS_OVER_COMMIT
    conn = pc1.getConnection();
    assertConnHoldability(conn, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    conn.close();
}
 

@Override
public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws Exception {
  super.onAcquire(c, parentDataSourceIdentityToken);
  c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
}
 

@Override
public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws Exception {
  super.onAcquire(c, parentDataSourceIdentityToken);
  c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
}
 

public void testBugFixes() throws SQLException {
    Connection conn= getConnection();
    Statement s = conn.createStatement();
    // -- bug 4430 aliasinfo nullability problem
    ResultSet rs = s.executeQuery("select aliasinfo from sys.sysaliases where aliasinfo is null");
    JDBC.assertEmpty(rs);
    //-- test SQL Error with non-string arguments
    //-- Make sure connection still ok (Bug 4657)
    s.executeUpdate("create table a (a int)");
    assertStatementError("22003",s,"insert into a values(2342323423)");
    s.executeUpdate("drop table a");
  
    conn.setAutoCommit(false);
    
    // bug 4758 Store error does not return properly to client
    s.executeUpdate("create table t (i int)");
    s.executeUpdate("insert into t values(1)");
    conn.commit();
    s.executeUpdate("insert into t values(2)");
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    Connection conn2 = openDefaultConnection();
    PreparedStatement ps2 = conn2.prepareStatement("select * from t");
    assertStatementError("40XL1",ps2);
    assertStatementError("40XL1",ps2);
    
    //-- Bug 5967 - Selecting from 2 lob columns w/ the first one having data of length 0
    Statement s2 = conn2.createStatement();
    s2.executeUpdate("create table t1 (c1 clob(10), c2 clob(10))");
    s2.executeUpdate("insert into t1 values ('', 'some clob')");
    rs = s.executeQuery("select * from t1");
    JDBC.assertFullResultSet(rs, new String[][]{{"","some clob"}});
    rs = s2.executeQuery("select c2 from t1");
    JDBC.assertFullResultSet(rs, new String[][]{{"some clob"}});
    s2.executeUpdate("drop table t1");
    conn2.commit();
    s2.close();
    ps2.close();
    conn2.close();
    
    s.executeUpdate("drop table t");
    s.close();
    conn.commit();
    conn.close();
}
 

public void testBugFixes() throws SQLException {
    Connection conn= getConnection();
    Statement s = conn.createStatement();
    // -- bug 4430 aliasinfo nullability problem
    ResultSet rs = s.executeQuery("select aliasinfo from sys.sysaliases where aliasinfo is null");
    JDBC.assertEmpty(rs);
    //-- test SQL Error with non-string arguments
    //-- Make sure connection still ok (Bug 4657)
    s.executeUpdate("create table a (a int)");
    
    assertStatementError("22003",s,"insert into a values(2342323423)");
    s.executeUpdate("drop table a");

    conn.setAutoCommit(false);

    // bug 4758 Store error does not return properly to client
    // GemStone changes BEGIN
    //   GemFireXD default transaction isolation is NONE, and
    //    we fail fast instead of doing lock timeouts
    s.executeUpdate("create table t (i int)");
    Connection conn2 = openDefaultConnection();
    PreparedStatement ps2 = conn2.prepareStatement("select * from t");
    if (!isAvoidGFXDBugs()) {
      s.executeUpdate("insert into t values(1)");
      conn.commit();
      s.executeUpdate("insert into t values(2)");
      conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
      assertStatementError("40XL1",ps2);

      //-- Bug 5967 - Selecting from 2 lob columns w/ the first one having data of length 0
      Statement s2 = conn2.createStatement();
      s2.executeUpdate("create table t1 (c1 clob(10), c2 clob(10))");
      s2.executeUpdate("insert into t1 values ('', 'some clob')");
      rs = s.executeQuery("select * from t1");
      JDBC.assertFullResultSet(rs, new String[][]{{"","some clob"}});
      rs = s2.executeQuery("select c2 from t1");
      JDBC.assertFullResultSet(rs, new String[][]{{"some clob"}});
      s2.executeUpdate("drop table t1");
      conn2.commit();
      s2.close();
      ps2.close();
      conn2.close();

      s.executeUpdate("drop table t");
      s.close();
      conn.commit();
    } else {
      s.close();
    }
    conn.rollback();
    // GemStone changes END
    conn.close();
}
 

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

        conn.setHoldability(hold ? ResultSet.HOLD_CURSORS_OVER_COMMIT : ResultSet.CLOSE_CURSORS_AT_COMMIT);
    }
 

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

        conn.setHoldability(hold ? ResultSet.HOLD_CURSORS_OVER_COMMIT : ResultSet.CLOSE_CURSORS_AT_COMMIT);
    }