[DERBY-1087] Updatable result sets behave different depending on the type of query used to generate the result set

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.2.1.6
• Component/s: JDBC, SQL
• Issue & fix info: Patch Available
• Assignee: Fernanda Pizzorno
• Reporter: Fernanda Pizzorno
• Created: 08/Mar/06 19:25
• Updated: 16/May/06 14:24
• Resolved: 16/May/06 00:10

Description

Running the following code with different queries in the first statement produces different results.
Where t1 has two columns: "a" of type int is the primary key, and "b" of type varchar(50); and contains 10 rows of data.

Statement st1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
Statement st2 = conn.createStatement();

ResultSet rs = st1.executeQuery("SELECT a, b FROM t1");
rs.next();
rs.next();
st2.executeUpdate("UPDATE t1 SET a = a + 20 WHERE a = " +
rs.getInt(1));
try

{ rs.updateInt(1, rs.getInt(1) + 30); rs.updateRow(); }

catch (SQLException se)

{ System.out.println(se.getMessage()); }

rs.close();

rs = st2.executeQuery("SELECT a FROM t1");
while(rs.next())

{ System.out.println("A = " + rs.getInt(1)); }

rs.close();

st2.close();
st1.close();

If the first query is "select a, b from t1", the output will be:
A = 1
A = 3
A = 4
A = 5
A = 6
A = 7
A = 8
A = 9
A = 10
A = 32

If the first query is "SELECT a, b FROM t1 WHERE a <= 5", the output will be:
Cursor 'SQLCUR0' is not on a row.
A = 1
A = 3
A = 4
A = 5
A = 6
A = 7
A = 8
A = 9
A = 10
A = 22

If the first query is "SELECT a FROM t1", the output will be:
Cursor 'SQLCUR0' is not on a row.
A = 1
A = 3
A = 4
A = 5
A = 6
A = 7
A = 8
A = 9
A = 10
A = 22