Calling resultSet.getString after resultSet.updateString

Hello,

While testing my application with MySQL I noticed a difference with 
Oracle's JDBC implementation.  In the following excerpt, I expect the 
call to getString to return the "new-value"

/* Sometimes the application calls moveToInsertRow() to insert a new row 
or next() to update an existing row in the database */
...
resultSet.updateString(2,"new-value");
resultSet.getString(2).equals("new-value");
...
/* Sometimes the database does not need to be updated and the changes 
are canceled */

It works as expected when inserting a new row (calling 
resultSet.moveToInsertRow() before the two lines above).
However, it does not work when updating a row (calling resultSet.next() 
before the the two lines above). In this case the call to getString 
still returns the value in the database.

Is there a way to make getString after updateString to work consistently 
for both uses case?

Thank You,
Dmitry

Sample Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.util.BaseBugReport;

public class BugReportVisibilityOfUpdate extends BaseBugReport {
  Connection con;
  Statement stm;
 
  public BugReportVisibilityOfUpdate() {
    super();
  }

  public void setUp() throws Exception {
    con = DriverManager.getConnection(getUrl());
    stm = con.createStatement();
    stm.executeUpdate("CREATE TABLE bug (id INTEGER UNSIGNED NOT 
NULL,value VARCHAR(25), PRIMARY KEY(id));");
    stm.executeUpdate("INSERT INTO bug(id,value) 
VALUES(1,'current-value')");
  }

  public void tearDown() throws Exception {
    con = DriverManager.getConnection(getUrl());
    stm = con.createStatement();
    stm.executeUpdate("DROP TABLE bug");
    stm.close();
  }

  public void runTest() throws Exception {
    con = DriverManager.getConnection(getUrl());
    stm = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = null;
   
   
    /* Test Case 1 - Passed. In the rs.moveToInsertRow test, calling 
getString after updateString works as expected */
    try {
      rs = stm.executeQuery("SELECT id, value from bug where id = '2'");
      rs.moveToInsertRow();
      assertTrue("Before update getString should return null", 
rs.getString(2)==null);

      rs.updateString("value", "new-value");
      assertTrue("After update should return 'new-value'", 
rs.getString(2).equals("new-value"));

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

    /* Test Case 2 - Failed. In the rs.next test, calling getString 
after updateString still returns the value from the database
       instead of the value used in the updateString */
    try {
      rs = stm.executeQuery("SELECT id, value from bug where id = '1'");

      rs.next();

      assertTrue("Before update getString should return 
'current-value'", rs.getString(2).equals("current-value"));

      rs.updateString("value", "new-value");

      /* The following assert failes.  Oracle returns the "new-value" */
      assertTrue("After update should return 'new-value'", 
rs.getString(2).equals("new-value"));

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

  public static void main(String[] args) throws Exception {
    (new BugReportVisibilityOfUpdate()).run();
  }

}


-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]