I don't think you need the FOR UPDATE clause. The following simpler code
works for me:
import java.sql.*;
public class Z
{
private static final String SELECT_ALL = "SELECT * FROM t";
public static void main(String... args) throws Exception
{
Connection conn =
DriverManager.getConnection("jdbc:derby:memory:db;create=true");
conn.prepareStatement("CREATE TABLE t (a int)").execute();
conn.prepareStatement("INSERT INTO t VALUES (1), (2)").execute();
printResultSet(conn, "Table before update...");
// now update the rows in place using an updatable ResultSet
try (Statement updatable =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE))
{
try (ResultSet rs = updatable.executeQuery(SELECT_ALL))
{
while(rs.next())
{
rs.updateInt(1, 10 * rs.getInt(1));
rs.updateRow();
}
}
}
printResultSet(conn, "\nTable after update...");
}
private static void printResultSet(Connection conn, String banner) throws
SQLException
{
println(banner);
try (PreparedStatement ps = conn.prepareStatement(SELECT_ALL))
{
try (ResultSet rs = ps.executeQuery())
{
printResultSet(rs);
}
}
}
private static void printResultSet(ResultSet rs) throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
StringBuffer buffer = new StringBuffer();
for (int idx = 1; idx <= count; idx++)
{
buffer.append("| ").append(rsmd.getColumnName(idx)).append(" ");
}
buffer.append(" |\n");
while (rs.next())
{
for (int idx = 1; idx <= count; idx++)
{
buffer.append("| ").append(rs.getString(idx)).append(" ");
}
buffer.append(" |\n");
}
println(buffer.toString());
}
private static void println(String text) { System.out.println(text); }
}
On 2/5/22 7:19 AM, John English wrote:
I've got a problem with using a SELECT ... FOR UPDATE.
The code basically looks like this (stripped down for simplicity):
PreparedStatement stat = conn.prepareStatement(
"SELECT ... FOR UPDATE",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE
);
try (ResultSet r = stat.executeQuery()) {
if (!r.next()) {
throw new Exception();
}
...
r.moveToCurrentRow();
r.updateString(column,value);
r.updateRow(); // throws SQLException
}
The call to updateRow() is throwing the following SQLException:
class java.sql.SQLException: Invalid cursor state - no current row.
I cannot for the life of me see why this is happening, since the test
on r.next() shows that I've found the row I want to update.
Can anyone suggest a reason what might be going on here?
Thanks,
