Retrieving result set after deleting a row using deleteRow method throws SQLServerException #477
Description
Driver version or jar name
mssql-jdbc-6.2.1.jre8.jar
SQL Server version
SQL Server 2012
Client operating system
Windows 10
Java/JVM version
java version "1.8.0_121"
Java(TM) SE Runtime Environment (build 1.8.0_121-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)
Table schema
CREATE TABLE DeleteRowTest
(
Id INT PRIMARY KEY NOT NULL IDENTITY,
Name VARCHAR(32) NOT NULL
)
GO
SET IDENTITY_INSERT DeleteRowTest ON
GO
INSERT INTO DeleteRowTest (Id, Name) VALUES
(1, 'Jon Snow'),
(2, 'Tony Stack'),
(3, 'Bran Stack'),
(4, 'Samwell Tarly')
GO
SET IDENTITY_INSERT DeleteRowTest OFF
GOProblem description
Set type of ResultSet to ResultSet.TYPE_SCROLL_SENSITIVE and set concurrency of ResultSet to ResultSet.CONCUR_UPDATEABLE, then running sql to get an updateable ResultSet.
Next, perform delete operation on table using deleteRow() method.
After deleting a row, retrieving result set throws SQLServerException(Cannot get a value from a deleted row).
Expected behavior and actual behavior
Run reproduce code below, it throws a SQLServerException. I also tried run it with MySQL, it works fine. I suppose that this is not correct.
It can be fixed by checking whether current row has been deleted using rowDeleted() method before retrieving the column value. I tried this fix solution with MySQL again, MySQL driver doesn't support rowDeleted() feature.
Seems it's a vendor specific issue, a deleted row leave a visible hole in result set in mssql-jdbc while mysql-jdbc does not.
http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#rowDeleted--
Maybe this difference should be included in document. Or is it possible to eject visible hole in result set?
Repro code
Java code:
public class DeleteRowTest {
private static final String JDBC_MSSQL_URL = "jdbc:sqlserver://localhost:1433;databaseName=JdbcTest";
private static final String JDBC_MSSQL_USER = "sa";
private static final String JDBC_MSSQL_PASSWORD = "your_password";
private static final String JDBC_MYSQL_URL = "jdbc:mysql://localhost:3306/JdbcTest?serverTimezone=UTC&useSSL=false";
private static final String JDBC_MYSQL_USER = "root";
private static final String JDBC_MYSQL_PASSWORD = "your_password";
public static void main(String[] args) {
String msSql = "SELECT * FROM DeleteRowTest";
System.out.println("------- Start of MS SQL -------");
testRetrieveResultSetAfterDeletingRow(msSql, JDBC_MSSQL_URL, JDBC_MSSQL_USER, JDBC_MSSQL_PASSWORD);
System.out.println("------- End of MS SQL -------");
String mySql = "SELECT * FROM delete_row_test";
System.out.println();
System.out.println("------- Start of MySQL -------");
testRetrieveResultSetAfterDeletingRow(mySql, JDBC_MYSQL_URL, JDBC_MYSQL_USER, JDBC_MYSQL_PASSWORD);
System.out.println("------- End of MySQL -------");
}
private static void testRetrieveResultSetAfterDeletingRow(String sql, String jdbcUrl, String user, String password) {
try (Connection connection = DriverManager.getConnection(jdbcUrl, user, password)) {
try (Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("ResultSet before deleting...");
printResultSet(resultSet);
resultSet.absolute(2);
resultSet.deleteRow();
System.out.println();
System.out.println("ResultSet after deleting...");
printResultSet(resultSet);
}
} catch (SQLException e) {
System.out.println(e);
}
}
private static void printResultSet(ResultSet resultSet) throws SQLException {
resultSet.beforeFirst();
while (resultSet.next()) {
System.out.print("Id: " + resultSet.getString("Id"));
System.out.println(", Name: " + resultSet.getString("Name"));
}
}
}pom dependencies:
<dependencies>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.2.1.jre8</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
</dependencies>