db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-690) Add scrollable, updatable, insensitive result sets
Date Wed, 01 Mar 2006 14:54:40 GMT
Well, this is handled differently by a variety of vendors.  For example 
Oracle and the IBM DB2 drivers currently throw an exception for every 
case except ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE

Some other vendors only follow the guidelines in the javadocs as to when 
they throw a SQLException.

So as i said earlier, not a straightforward and quick answer on this i 
am afraid

-lance

Dag H. Wanvik (JIRA) wrote:
>     [ http://issues.apache.org/jira/browse/DERBY-690?page=comments#action_12368284 ]

>
> Dag H. Wanvik commented on DERBY-690:
> -------------------------------------
>
> (reposting a mail comment by Bernt. -Dag)
>
>   
>>>>>>>>>>>>> Dag H. Wanvik (JIRA) wrote (2006-02-27
15:44:44):
>>>>>>>>>>>>>                           
>>     [ 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.
>>     
>
> The API (1.4.2) spec of refreshRow states:
>
>    All values are refetched subject to the transaction isolation level
>    and cursor sensitivity. 
>
> Hence there is a difference in behaviour wrt sensitivity. This can
> only imply that refreshRow is a null-operation for insensitive
> resultsets. This is also in agreement with the tutorial as others had
> pointed out(See refreshRow on page 759).
>
> We should also strive to make "insensitivity" as close to the SQL
> defintion as possible (SQL 2003 p. 96):
>
>    A change to SQL-data is said to be independent of a cursor CR if
>    and only if it is not made by an <update statement: positioned> or a
>    <delete statement: positioned> that is positioned on CR.
>
>    A change to SQL-data is said to be significant to CR if and only if
>    it is independent of CR, and, had it been committed before CR was
>    opened, would have caused the table associated with the cursor to
>    be different in any respect.
>
>    A change to SQL-data is said to be visible to CR if and only if it
>    has an effect on CR by inserting a row in CR, deleting a row from
>    CR, changing the value of a column of a row of CR, or reordering
>    the rows of CR.
>
>    [...]
>
>    - If the cursor is insensitive, then significant changes are not visible.
>
> Bernt
>
>   
>> 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.
>>     
>
>   

Mime
View raw message