[ http://issues.apache.org/jira/browse/DERBY-690?page=comments#action_12367771 ]
Daniel John Debrunner commented on DERBY-690: --------------------------------------------- Dag wrote: > Dan wrote: >> My thought here is that the updated row in the ResultSet would then >> reflect a state of the database that never occurred. This seems in >> conflict with all isolation levels except read-uncommitted. I can't >> think of another situation where an application using Derby can see >> a row in that state (excluding read-uncommitted). > Hmm, not quite sure what you mean here. > Let me sum up my understanding [Dag's correct understanding snipped] I agree Dag with what you are saying, but in all the cases you describe every individual row represents a state of the database at some point in time. The trouble I have with the SUR updated row is that it may not match a valid state of the database. No user at any time could have issued a select from the database and seen those values. The row may also be self-inconsistent, that is, column values that are inconsistent with each other. I think this becomes important when the database is enforcing business logic through triggers (or any other mechanism that can modify columns on an UPDATE statement). If an old application updates a discount column in a row from 15% to 30% but new database side business logic has been put in place to limit discounts to 25%, then anyone selecting that row from the database sees either 15% or 25%, never 30%. However, with SUR the application has a scrollable ResultSet with a row that has discount 30%, since the result set is scrollable this incorrect value of 30% can continue to be displayed to the end user of the application. This just seems wrong to me. An example for the row self-inconsistent state, is an update of a customer's address. If there is a calculated column from the address, eg. zip code for the US, post code for the UK, sales-region for a sales application, then the updated row in the SUR will have a mismatch. E.g. a Californian zip code for a New York address. Maybe as you say, we can just document this, and that applications should not rely on this behaviour. To, me it seems like the row in the ResultSet should be either not updated or updated with the full effect of the updateRow() operation (see "own" changes). This also seems closely tied to implementing the ResultSet.refreshRow() method, if we continue with the proposed implementation and document it, then a natural work-around would be to tell the application to issue a refreshRow(), but Derby doesn't support it :-(. Of course if refreshRow was implemented, then we possibly wouldn't need to document this as a refreshRow() could be called after an updateRow() to present the correct information. And on the triggers, I guess I remembered incorrectly, I thought I'd seen code that disallowed modifying the triggered table. > Add scrollable, updatable, insensitive result sets > -------------------------------------------------- > > Key: DERBY-690 > URL: http://issues.apache.org/jira/browse/DERBY-690 > Project: Derby > Type: New Feature > Components: JDBC > Reporter: Dag H. Wanvik > Assignee: Dag H. Wanvik > Priority: Minor > Attachments: DERBY-690-v1.diff, DERBY-690-v1.stat, DERBY-690-v2.diff, > DERBY-690-v2.stat, SURChanges-v1.pdf, sur-proposal.txt, writeup-v1.html, > writeup-v2.html > > JDBC result sets are created with three properties: type, concurrency > and holdability. The type can be one of TYPE_FORWARD_ONLY, > TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE. The concurrency can > be one of CONCUR_READ_ONLY and CONCUR_UPDATABLE. The holdability can > be one of HOLD_CURSORS_OVER_COMMIT and CLOSE_CURSORS_AT_COMMIT. > JDBC allows the full cross product of these. SQL 2003 prohibits the > combination {TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE}, but this > combination is supported by some vendors, notably Oracle. > Currently, Derby supports JDBC result sets in a limited > way. Holdability is supported. Furthermore, the following is > supported: > - forward-only, read-only > - forward-only, updatable (update, delete, but not insert) > Also, in the network driver, support for some data types > conversions is missing. > - scroll insensitive, read-only > We (Fernanda and Andreas will cooperate with me on this) propose a > plan to add support for the combination: > - scroll insensitive, updatable > for both the embedded driver and the network client driver. > As a part of this we would also like to add the missing insert > operation to the {forward-only, updatable} result sets (JIRA-100), and > remove the requirement for an explicit "FOR UPDATE" clause in the SQL > query to achieve updatability if CONCUR_UPDATABLE is specified > (JIRA-231). > The full proposal text is uploaded as an attachment, including a proposed > functional specification. > This JIRA will be used to track sub-issues for this effort. The sub-issues > will be linked back to this issue. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
