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 Fri, 24 Feb 2006 18:43:38 GMT
    [ http://issues.apache.org/jira/browse/DERBY-690?page=comments#action_12367693 ] 

Dag H. Wanvik commented on DERBY-690:

Thanks for your comments!

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. Other (read-uncommitted) transactions would see all the
changes including the (trigger) side-effects. And so would the present
transaction when querying the data base via other statement objects
than the result set. The question, then, is what changes should be
reflected in the result set itself as a consequence of the updateRow
(or deleteRow) trigger action. Even if we did reflect the trigger
action's changes to rows which are part of the result set, that data
set is still not the same as the (uncommitted) view in the database 
for two reasons:

* other changes made in the transaction (via other statement objects)
  are "other"s and not reflected in the result set. If they were, this
  would breach JDBC's stated semantics as far as I can understand.

* Rows inserted via ResultSet#insertRow are not visible, either, in
  the present implementation. This is optional, though. JDBC provides
  metadata calls to allow for variation here.

In light of this, I think the data in the (insensitive) result set
should be viewed as a convenience, rather the "truth"; insensitive (by
design) result sets provide more isolation than the current isolation
level requires. But I agree this behavior is not obvious and should be
documented clearly.

> Though currently it may not be possible in Derby to have
> side-effects on an UPDATE statement. Currently triggers are
> read-only on the firing table.

That would alleviate a user's potential expectation problem. But is this
correct? I checked the reference manual for restrictions and could not
find this. I also tried it and it seemed to work, cf. sample trigger:

st.execute("CREATE TABLE tmpResultSet (id int primary key, name varchar(50))");
st.execute("CREATE TRIGGER trig AFTER UPDATE OF id ON tmpResultSet"+
        " FOR EACH ROW MODE DB2SQL UPDATE tmpResultSet SET name='triggered' "+
        " WHERE UPDATEDROW.id = 10");

An updateRow of a row's 'id' to 10 caused the trigger action here.
But maybe I am missing something here.

> 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, SURChanges-v1.pdf, sur-proposal.txt,
> 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