db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-690) Add scrollable, updatable, insensitive result sets
Date Mon, 27 Feb 2006 14:44:44 GMT
    [ http://issues.apache.org/jira/browse/DERBY-690?page=comments#action_12367960 ] 

Dag H. Wanvik commented on DERBY-690:
-------------------------------------

I checked how the trigger situation is handled in Oracle, since they
do support Scrollable updatable insensitive result sets. In essence
they take that approach that:

     - refreshRow will update the result set with the values of the
       underlying database, even for insensitive result sets.

     - an updateRow will implicitly do a refreshRow after and thus
       capture any changes of a row to to a trigger action (plus any
       other changes made by "others" to that row).
     
This is a well defined semantic and would solve Dan's concern about
row internal inconsistency, but is it is not JDBC compliant in my
view.  JDBC is quite clear on both points:

- The refreshRow is stated to do nothing for insensitive result sets,
cf. Tutorial book, 3rd ed. p 759: "..it does nothing for those that
are TYPE_SCROLL_INSENSITIVE".

- JDBC 3.0 spec: "The result set is insensitive to changes made to the
underlying data source while it is open. It contains the rows that
satisfy the query at either the time the query is executed or as the
rows are retrieved."
Tutorial, 3rd ed, p. 718, "If updates are visible, calling the
appropriate getter method after an updater method has been called will
return the new value". 

The Oracle doc admits that result of triggers are "others" in the
sense of JDBC; I quote (Oracle9i JDBC Developer's Guide and Reference
Release 2). Note that they use the term "external changes" in the
sense of JDBC's "other's" changes:

    "Note: When you make an internal change that causes a trigger to
    execute, the trigger changes are effectively external
    changes. However, if the trigger affects data in the row you are
    updating, you will see those changes for any scrollable/updatable
    result set (also for insensitive! -Dag), because an implicit row
    refetch occurs after the update.

They also make a point to distinguish between the insensitivity when
scrolling and visibility that is reinstated when doing updates:

    "Note: Explicit use of the refreshRow() method, described in
    "Refetching Rows", is distinct from this discussion of
    visibility. For example, even though external updates are
    "invisible" to a scroll-insensitive result set, you can explicitly
    refetch rows in a scroll-insensitive/updatable result set and
    retrieve external changes that have been made. "Visibility" refers
    only to the fact that the scroll-insensitive/updatable result set
    would not see such changes automatically and implicitly.

So, in my view, Oracle has tweaked the standard here to get semantics
similar to what Dan suggests. We may want to do this for SUR; but I
would like to  hear what more people think of this before we decide:

 Should we be strictly JDBC compliant or choose a more "reasonable" behavior?





> 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


Mime
View raw message