Microsoft KB Archive/945738

= Error message when you use the updateRow method in the SQL Server 2005 JDBC Driver to update a result set in SQL Server 2000: &quot;Result set is not updatable&quot; or &quot;The cursor is READ ONLY&quot; =

Article ID: 945738

Article Last Modified on 12/21/2007

-

APPLIES TO

 Microsoft SQL Server 2005 Java Database Connectivity Driver, when used with:  Microsoft SQL Server 2000 Enterprise Edition

 Microsoft SQL Server 2000 Personal Edition

 Microsoft SQL Server 2000 Standard Edition

 Microsoft SQL Server 2000 Developer Edition</li></ul> </li></ul>

-

<div class="symptoms_section">

SYMPTOMS
Consider the following scenario. You create a client application that uses the Microsoft SQL Server 2005 Java Database Connectivity (JDBC) Driver to update a table in Microsoft SQL Server 2000. In this scenario, when you use the updateRow method to update the result set that you obtain from the table, you receive an error message.

If you use the SQL Server 2005 JDBC Driver version 1.1 or earlier versions, you receive the following error message:

com.microsoft.sqlserver.jdbc.SQLServerException: Result set is not updatable.

If you use the SQL Server 2005 JDBC Driver version 1.2, you receive the following error message:

com.microsoft.sqlserver.jdbc.SQLServerException: The cursor is READ ONLY.

<div class="cause_section">

CAUSE
This behavior occurs because implicit cursor conversion occurs in SQL Server 2000. Therefore, SQL Server 2000 does not return an updatable cursor for the result set.

Note In some cases, implicit cursor conversion does occur. For example, implicit cursor conversion occurs if the following conditions are true:
 * In the query that you use to return a result set, you use the ORDER BY clause.
 * In the underlying table, no primary key or no unique index includes the columns that you use in the ORDER BY clause.

For more information about implicit cursor conversion, see the &quot;References&quot; section.

<div class="workaround_section">

WORKAROUND
To work around this behavior, use one of the following methods.

Method 1
Change the query that returns the result set, or change the configuration of the underlying table. When you do this, SQL Server 2000 does not convert the cursor type.

Method 2
Manually create statements to update the table in SQL Server 2000.

Note You can use this method to work around the behavior in the example that is mentioned in the &quot;Cause&quot; section. To do this, create a primary key or a unique index that includes the columns that you use in the ORDER BY clause.

The following example code provides a workaround for the behavior that occurs when you follow the steps in the &quot;Steps to reproduce the behavior&quot; section.

import java.sql.*; import java.io.*;

public class Test1 {   public static void main (String[] args) throws Exception {       Connection connection = null; Class.forName(&quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;); String connectionUrl = &quot;jdbc:sqlserver://<ServerName>;instanceName=<InstanceName>;databaseName=MyDB; user=<MyUser>;password=<MyPassword>;SelectMethod=cursor;&quot;; connection = DriverManager.getConnection(connectionUrl);

Statement stmt = connection.createStatement; PreparedStatement updateStatement = connection.prepareStatement(&quot;UPDATE MyTable SET col2 = ? WHERE col1 = ? AND col2 = ?&quot;); ResultSet rs = stmt.executeQuery(&quot;SELECT col1, col2 FROM MyTable ORDER BY col1&quot;); try {           while (rs.next) {               updateStatement.setInt(1, rs.getInt(2) + 1); updateStatement.setString(2, rs.getString(1)); updateStatement.setInt(3, rs.getInt(2)); updateStatement.executeUpdate; }       }        catch(Exception e)            { System.out.println(&quot;Exception = &quot; + e); System.out.println(&quot;Press any key to quit...&quot;); System.in.read; }       stmt.close; updateStatement.close; connection.close; } }

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
If the SQL Server 2005 JDBC Driver requests an updatable cursor from SQL Server 2000, implicit cursor conversion occurs in SQL Server 2000 when the following conditions are true:
 * The query that returns the result set contains an ORDER BY clause.
 * No primary key or no unique index includes the columns that are used in the ORDER BY clause.

When implicit cursor conversion occurs, the cursor that SQL Server 2000 returns is not updatable. To work around this behavior, the SQL Server 2000 JDBC Driver internally creates statements to update the table in the implementation of the updateRow method.

However, the SQL Server 2005 JDBC Driver does not internally create statements to update the table in the implementation of the updateRow method. The SQL Server 2005 JDBC Driver relies on SQL Server 2000 to return an updatable cursor. Because implicit cursor conversion occurs, the cursor that SQL Server 2000 returns for the result set is not updatable.

This change in the implementation of the updateRow method was made for the following reasons:
 * Transactional consistency
 * Improved security
 * Visibility of row updates through the result set object
 * Improved performance

Steps to reproduce the behavior
<ol>  Run the following statement in SQL Query Analyzer against an instance of SQL Server 2000.

USE master GO

CREATE DATABASE MyDB GO

USE MyDB GO

CREATE TABLE MyTable (   Col1 varchar(50),    Col2 int) GO

INSERT INTO MyTable VALUES ('A', 1) GO

INSERT INTO MyTable VALUES ('B', 2) GO </li>  Compile and then run the following Java code sample.

import java.sql.*; import java.io.*;

public class Test1 {   public static void main (String[] args) throws Exception {       Connection connection = null; Class.forName(&quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;); String connectionUrl = &quot;jdbc:sqlserver://ServerName;instanceName=InstanceName;databaseName=MyDB;user=MyUser;password=MyPassword;SelectMethod=cursor;&quot;; connection = DriverManager.getConnection(connectionUrl);

Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE); String s = null; s = &quot;SELECT col1, col2 FROM MyTable ORDER BY col1&quot;;

ResultSet rs = null; rs = stmt.executeQuery(s); try {           while(rs.next) {               rs.updateInt(&quot;Col2&quot;, rs.getInt(2)+1); rs.updateRow; connection.commit; }       }        catch(Exception e)        { System.out.println(&quot;Exception = &quot; + e); System.out.println(&quot;Press any key to quit...&quot;); System.in.read; }       rs.close; stmt.close; connection.close; } } </li></ol>

<div class="references_section">