Using Holdability
By default, a result set created within a transaction is closed after the transaction is committed to the database, or when it is rolled back. However, it is sometimes useful for the result set to remain open, even after the transaction has been committed. To do this, the Microsoft SQL Server 2005 JDBC Driver supports the use of result set holdability.
Result set holdability can be set by using the setHoldability method of the SQLServerConnection class. When setting the holdability by using the setHoldability method, the result set holdability constants of HOLD_CURSORS_OVER_COMMIT or CLOSE_CURSORS_AT_COMMIT can be used.
Note
The JDBC driver does not support setting holdability when creating one of the Statement objects. Statement objects that have overloads with result set holdability parameters will throw an exception when called.
The holdability of a result set is the holdability of the SQLServerConnection object that is associated with the result set at the time when the result set is created for server-side cursors only. It does not apply to client-side cursors.
In the following example, the result set holdability is set while performing a local transaction consisting of two separate statements in the try block. The statements are run against the Production.ScrapReason table in the SQL Server 2005 AdventureWorks sample database, and a savepoint is used to roll back the second statement. This results in only the first statement being committed to the database.
public static void executeTransaction(Connection con) {
try {
con.setAutoCommit(false);
con.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.executeUpdate("INSERT INTO Production.ScrapReason(Name) VALUES('Bad part')");
ResultSet rs = stmt.executeQuery("SELECT * FROM Production.ScrapReason");
con.commit();
System.out.println("Transaction succeeded.");
//Display results.
while (rs.next()) {
System.out.println(rs.getString(2));
}
stmt.close();
}
catch (SQLException ex) {
ex.printStackTrace();
try {
con.rollback();
System.out.println("Transaction failed.");
}
catch (SQLException se) {
se.printStackTrace();
}
}