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

The following examples show how to use java.sql.Connection#setHoldability() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: ResultSetMiscTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * 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());

    }
}
 
Example 2
Source File: ScrollCursors1Test.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
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");
}
 
Example 3
Source File: StatementPoolingTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 4
Source File: J2EEDataSourceTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
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);
        }
    }
}
 
Example 5
Source File: DataSourceTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
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);
        }
    }
}
 
Example 6
Source File: ResultSetMiscTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * 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());

    }
}
 
Example 7
Source File: ScrollCursors1Test.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
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");
}
 
Example 8
Source File: DataSourceTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
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);
        }
    }
}
 
Example 9
Source File: StatementPoolingTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 10
Source File: J2EEDataSourceTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
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);
        }
    }
}
 
Example 11
Source File: ResultSetMiscTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * 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());
    }
}
 
Example 12
Source File: Sttest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
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);
}
 
Example 13
Source File: Sttest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
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);
}
 
Example 14
Source File: J2EEDataSourceTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 15
Source File: MyConnectionCustomizer.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
@Override
public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws Exception {
  super.onAcquire(c, parentDataSourceIdentityToken);
  c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
}
 
Example 16
Source File: MyConnectionCustomizer.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
@Override
public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws Exception {
  super.onAcquire(c, parentDataSourceIdentityToken);
  c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
}
 
Example 17
Source File: SimpleTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
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();
}
 
Example 18
Source File: SimpleTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 19
Source File: J2EEDataSourceTest.java    From spliceengine with GNU Affero General Public License v3.0 2 votes vote down vote up
private void setHoldability(Connection conn, boolean hold) throws SQLException {

        conn.setHoldability(hold ? ResultSet.HOLD_CURSORS_OVER_COMMIT : ResultSet.CLOSE_CURSORS_AT_COMMIT);
    }
 
Example 20
Source File: J2EEDataSourceTest.java    From gemfirexd-oss with Apache License 2.0 2 votes vote down vote up
private void setHoldability(Connection conn, boolean hold) throws SQLException {

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