Dag H. Wanvik (JIRA) wrote:
"FOR UPDATE" required for updatable result set to work ------------------------------------------------------
Key: DERBY-231
URL: http://issues.apache.org/jira/browse/DERBY-231
Project: Derby
Type: Improvement
Components: SQL Versions: 10.0.2.1 Reporter: Dag H. Wanvik
Priority: Minor
To get an updatable result set, the JDBC 3.0 spec, section 14.2.4 "Modifying ResultSet Objects" states:
"ResultSet objects with concurrency CONCUR_UPDATABLE can be updated using ResultSet objects".
In addition, Derby requires the SQL SELECT statement to have a "FOR UPDATE" clause for updates to be allowed. This may be a usability issue, as many examples, e.g. in "JDBC API tutorial and reference and reference" book and the JDBC 3.0 Specification (14.2.4.1) do not include a "FOR UPDATE" clause in the SQL SELECT.
Mamta Satoor says:
"Derby implements the JDBC updatable resultset by using the existing updatable cursor implementation. And in order to do that, it requires that the SELECT statement should include the FOR UPDATE clause. One can change the Derby implementation so that it does not require FOR UPDATE clause to piggyback on updatable cursor implementation."
Dan DeBrunner says:
"Technically I wonder if this is covered by the JDBC standard, I see nothing in the JDBC 3.0 that states any requirements for the SQL statement for an updateable result set. I know the JDBC tutorial book has some guidelines as to what will typically work, but isn't it up to the database engine to define what works here?
Having said that I think that not requiring the FOR UPDATE would be a useful improvement."
In testing MySQL 4.1, PostgreSQL 8.0, and Oracle 10g Release 1, all drivers for these databases function identically regardless whether the query string contains "FOR UPDATE" or not. For source code compatibility with other databases, the current requirement should be lifted. I've added this note to JIRA.
