Behavior question about updateable resultsets...

I have a question regarding a certain behavior of updateable
resultsets. If I update a column using any of the updateXXX methods and
then try to use the getXXX methods from the same column to see if it
updated the results locally and not on the server, I get the same old
value. I have to call updateRow() but that updates the underlying
database and still gives me the old value until I execute the same
query again and get a new resultset. Maybe the code below will clarify
my question more..
Connection con = null;
Statement stmt;
ResultSet rst;
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
System.out.println("Getting connection.");
con = DriverManager.getConnection(url);
System.out.println("Connection successful.");
String st = "select age,sname,snum FROM student;";
stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
rst = stmt.executeQuery(st);
rst.last();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
rst.updateInt(1,23);
rst.updateRow();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
The output is:
Getting connection.
Connection successful.
25 Edward Baker 578875478
25 Edward Baker 578875478
If I were the run the same code again, I get:
Getting connection.
Connection successful.
23 Edward Baker 578875478
23 Edward Baker 578875478
Any/all help is appreciated
Thanks
Devansh Dhutia
University of Iowa
This is a bug and it does not have a trivial fix. I would like to encourage
you to file this using the product feedback website (below).
The problem here is that there are two mutually exclusive places where
column values transit through the driver. The first, used only by getters,
is through the columns array (lives on the statement). The second, used
only by setters, is through the colParam array (also lives on the
statement). The columns array is read only the colParam array is write
only...
Note that the JDBC spec provides (in section 27.1.22, p. 718 - 719 JDBC API
Tutorial and Reference, Third edition, (Fisher, Ellis, Bruce)) that a result
set's own updates need not be visible to it. Obviously we would not like
this to be the default behavior but it is going to take a lot of work and it
would help to have customer feedback that clarified why this behavior should
be changed.
Entering a bug:
Go to http://lab.msdn.microsoft.com/produc...k/default.aspx
Product/Technology:
SQL Server 2005
Category:
JDBC Driver
Make sure to add JDBC SqlServer 2005 to the but title.
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
<devansh.dhutia@.gmail.com> wrote in message
news:1128188361.813553.300740@.g47g2000cwa.googlegr oups.com...
>I have a question regarding a certain behavior of updateable
> resultsets. If I update a column using any of the updateXXX methods and
> then try to use the getXXX methods from the same column to see if it
> updated the results locally and not on the server, I get the same old
> value. I have to call updateRow() but that updates the underlying
> database and still gives me the old value until I execute the same
> query again and get a new resultset. Maybe the code below will clarify
> my question more..
> Connection con = null;
> Statement stmt;
> ResultSet rst;
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> System.out.println("Getting connection.");
> con = DriverManager.getConnection(url);
> System.out.println("Connection successful.");
> String st = "select age,sname,snum FROM student;";
> stmt =
> con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
> rst = stmt.executeQuery(st);
> rst.last();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> rst.updateInt(1,23);
> rst.updateRow();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> The output is:
> Getting connection.
> Connection successful.
> 25 Edward Baker 578875478
> 25 Edward Baker 578875478
> If I were the run the same code again, I get:
> Getting connection.
> Connection successful.
> 23 Edward Baker 578875478
> 23 Edward Baker 578875478
> Any/all help is appreciated
> Thanks
> Devansh Dhutia
> University of Iowa
>