db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-690) Add scrollable, updatable, insensitive result sets
Date Sat, 25 Feb 2006 17:21:56 GMT
    [ 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
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,
> be one of CONCUR_READ_ONLY and CONCUR_UPDATABLE. The holdability can
> JDBC allows the full cross product of these. SQL 2003 prohibits the
> 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:
For more information on JIRA, see:

View raw message