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();
}
}