MySQL Forums
Forum List  »  Oracle

Calling resultSet.getString after resultSet.updateString
Posted by: Dmitry Isakbayev
Date: May 10, 2011 07:42PM

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

}

Options: ReplyQuote


Subject
Views
Written By
Posted
Calling resultSet.getString after resultSet.updateString
5899
May 10, 2011 07:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.