JDBC And SQL 2005 With XACT_ABORT UPDATE Statement Bug ?

While troubleshooting a problem at one of our customers, we noticed the following consistent erratic behaviour of our product with the 1.1.105 JDBC driver (as well as the 1.2 CTP) when the XACT_ABORT user option has been set on the SQL server:

Set the user option XACT_ABORT on a database server with the following SQL query:
exec sp_configure 'user options', '16384'
reconfigure

Now run the following





Code Snippet

import java.sql.*;
import java.util.*;

public class Test {

public static void main(String[] args) {
try {
// Load the JDBC driver.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

// Establish the connection to the database.
String url = "jdbc:sqlserver://server:1433";

Properties props = new Properties() ;
props.setProperty("user","user");
props.setProperty("password","password");
props.setProperty("DatabaseName","databasename");
props.setProperty("selectMethod","cursor");

Connection conn = DriverManager.getConnection(url, props);

// Set up simple prepared Update statement
PreparedStatement pstmt = conn.prepareStatement("UPDATE table SET field=0 where field=1", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
Boolean isresultset = pstmt.execute();
System.out.println("IsUpdatecount? " + !(isresultset));
Integer updatedrecords=pstmt.getUpdateCount();

/*updatedrecords should be 0 or higher if isresultset is false
if updatedrecords=-1, the result should be a resultset, which it cannot be, but seems to be*/
System.out.println(updatedrecords+ " row(s) affected");

} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}

You can revert the database server back to the correct behavior bij executing the following SQL query
exec sp_configure 'user options', '0'
reconfigure

As you can see, when the SQL server user option XACT_ABORT is set, prepared UPDATE statements through the SQL JDBC driver will fail consistently when selectMethod=cursor and ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY are set, with the execute method indicating an UpdateCount is returned, but the UpdateCount itself claiming a resultset has been returned. On checking that, there is no resultset.

Somehow the option, which should do the following:







xact abort


Rolls back a transaction if a Transact-SQL statement raises a run-time error.
seems to roll back the results of an update statement in place ?

Is this a bug or something I seem to be doing wrong ?

Kind regards,

Niels

PS: With the 1.0 JDBC driver with this setting another problem occured, which pointed to a rollback done within a transaction without any exception being raised through JDBC, consistent with Angel Saenz-Badillos post on http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-behavior-of-connectioncommit-249105.html

We had hoped the 1.1 driver would at least raise the exception through JDBC, but alas, the result mentioned above happened, which in the end we could track back to the server setting.


--Update--

When an SQL Trace is active on the server the following two error messages appear in the log, none of which are visible through the JDBC driver error handler:

Exception Error: 156, Severity: 15, State: 1
Exception Error: 16954, Severity: 10, State: 1