I can't say I've been following the entire thread, but I would like to
support Øystein's points of this being a reasonable use case. Dan's
comment below had me scratching my head, but I didn't have the time or
energy to respond at the time :)
I thought insensitive result sets were just that - insensitive to
changes in database state. It seems this should be true regardless of
whether the changes are caused by an update via another transacation or
by a trigger. And I think Øystein's right there is value in such a use
case.
I have no idea, however, whether the use case Øystein describes is
consistent with JDBC spec...
David
Øystein Grøvlen wrote:
Daniel John Debrunner (JIRA) wrote:
[
http://issues.apache.org/jira/browse/DERBY-690?page=comments#action_12367999
]
Daniel John Debrunner commented on DERBY-690:
---------------------------------------------
Andreas wrote:
--- quote
True. This would also be the situation if:
* The ResultSet selects a,b
* Another statement updates column a on a row
* The ResultSet updates column b on the same row
After this, the ResultSet does not reflect the changes made by the
other statement, (column a would be the same as it initially was) and
it does not reflect a state for the row which ever existed on the
database.
--end quote
Agreed, it's the same issue in my mind, and allowing such a row in a
ResultSet would be wrong. I believe after an update the row in the
ResultSet must reflect the state of the row in the database. Hence,
like Oracle, Derby should perform the equivalent of a refreshRow.
I do not agree with you here. I think you are mixing result sets and
database state. I think the whole point of an insensitive result set is
that it should not be affected by changes to the database state. The
question of visibility of own changes, becomes much clearer if we are
not talking about visibility of changes to the database, but changes to
the result set.
One use of an insensitive result set would be an application where one
displays the result of a query in a table. The user is allowed to
navigate in the table and change individual cell values. The
application uses an insensitive result set because it does not want the
rows to change ones the table has been populated. When the application
changes a cell, the result set is updated. It makes sense to make these
changes to the result set visible. Otherwise, it may seem to a user
that his updates are lost when he scrolls back up to a row he has
previously updated. However, it does not make sense to me that in order
to achieve this, the application must accept that changes made by others
also become visible.
If some application wants the behavior you suggest, then we could make
it possible for them to explicitly call refreshRow. I do not understand
why we should make it mandatory. However, I also do not understand how
providing refreshRow for insensitive result sets can be compatible with
the JDBC standard.
I also do not think how the result set is established is relevant for
this discussion. As you say the JDBC spec allows both for result sets
to reflect the database state at a certain time and for results sets
that are built over time. This means that what happens to a record
before it becomes visible to the user of the result set is not very
relevant.
With respect to triggers, you only seem to be concerned with changes
made by triggers to the row that is updated. I cannot see that it is
more wrong to show an inconsistent column value than to show
inconsistencies between rows (e.g., the result set contains two rows
that due to trigger actions could not possibly exist in the database
concurrently.) As far as I can see, in order to be able to guarantee
that a result set shows all effects triggered by an update to the result
set, it would be necessary to refresh the entire result set on each
update. That sounds like a sensitive result set to me.
In my opinion, another advantage of just showing the actual changes to
the result set and not the database state, is that this behavior is easy
to explain and easy for a user to understand.
--
Øystein
begin:vcard
fn:David W Van Couvering
n:Van Couvering;David W
org:Sun Microsystems, Inc.;Database Technology Group
email;internet:[EMAIL PROTECTED]
title:Senior Staff Software Engineer
tel;work:510-550-6819
tel;cell:510-684-7281
x-mozilla-html:TRUE
version:2.1
end:vcard
