TYPE_SCROLL_INSENSITIVE is not compatible with CONCUR_UPDATABLE

There are two options when setting ResultSet to be scrollable:

• TYPE_SCROLL_INSENSITIVE - The result set is scrollable. Its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to its current position. The result set generally does not show changes to the underlying database that are made while it is open. The membership, order, and column values of rows are typically fixed when the result set is created.
• TYPE_SCROLL_SENSITIVE - The result set is scrollable. Its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to its current position. The result set is sensitive to changes made while it is open. If the underlying column values are modified, the new values are visible, thus providing a dynamic view of the underlying data. The membership and ordering of rows in the result set may be fixed or not, depending on the implementation.

Generally, TYPE_SCROLL_INSENSITIVE is the preferred option. The data contained in the ResultSet object is fixed (a snapshot) when the object is created. Here is a sample program that shows you how to create a scrollable ResultSet and how to move the cursor backward:

/**
 * DerbyScrollableResultSet.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class DerbyScrollableResultSet {
  public static void main(String [] args) {
    Connection con = null;
    try {
      con = DriverManager.getConnection(
        "jdbc:derby://localhost/TestDB");

// Create a Statement for scrollable ResultSet
      Statement sta = con.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);

// Catch the ResultSet object
      ResultSet res = sta.executeQuery("SELECT * FROM Profile");

// Check ResultSet's scrollability
      if (res.getType() == ResultSet.TYPE_FORWARD_ONLY) {
        System.out.println("ResultSet non-scrollable.");
      } else {
        System.out.println("ResultSet scrollable.");
      }

      System.out.println("List of Profiles:");

// Move the cursor to the last row
      res.last();

// Stop the loop when the cursor is positioned before the first row
      while (!res.isBeforeFirst()) {
        String firstName = res.getString("FirstName");
        String lastName = res.getString("LastName");
        System.out.println("   "+firstName+" "+lastName);

// Move the cursor backward one row
        res.previous();
      }

// Close ResultSet and Statement
      res.close();
      sta.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

The output matches my expectation: profiles are listed backward.

ResultSet scrollable.
List of Profiles:
   Keith Harris
   19c2 8770b
   1090 3fb07
   13ba 88356
   1352 17d9
   11bd 58ad0
   1002 e3873
   21d6 efd17
   Janet Gates

TYPE_SCROLL_INSENSITIVE | Static cursor | Heavy | Only works with read-only concurrency (updatable is downgraded).

SQL Server generates a temporary table, so changes made by others are not visible. Scrollable.

 

 

Which confirms that the driver will downgrade when specifying CONCUR_UPDATABLE.

You might want to consider using TYPE_SCROLL_SENSITIVE or simply not combine scrollability with updatability.

 

 

Result Sets That Are Not Updateable

---

An updatable result set is a result set in which rows can be inserted, updated, and deleted. In the following cases, SQL Server cannot create an updatable cursor. The exception generated is, "Result set is not updatable."

 

Cause	Description	Remedy
Statement is not created by using JDBC 2.0 (or later) syntax	JDBC 2.0 introduced new methods to create statements. If JDBC 1.0 syntax is used, the result set defaults to read-only.	Specify result set type and concurrency when creating the statement.
Statement is created by using TYPE_SCROLL_INSENSITIVE	SQL Server creates a static snapshot cursor. This is disconnected from the underlying table rows to help protect the cursor from row updates by other users.	Use TYPE_SCROLL_SENSITIVE, TYPE_SS_SCROLL_KEYSET, TYPE_SS_SCROLL_DYNAMIC, or TYPE_FORWARD_ONLY with CONCUR_UPDATABLE to avoid creating a static cursor.
Table design precludes a KEYSET or DYNAMIC cursor	The underlying table does not have unique keys to enable SQL Server to uniquely identify a row.	Add unique keys to the table to provide unique identification of each row.

http://msdn.microsoft.com/en-us/library/ms378709.aspx

http://jtds.sourceforge.net/resultSets.html

http://stackoverflow.com/questions/13343703/how-to-create-a-scrollable-resultset