Subject: Detectability of updates in DRDA

[Fernanda Pizzorno <Fe...@Sun.COM>]

I am going to start working on detectability for scrollable insensitive 
update result sets on the client driver and I was wondering if someone 
could answer to the following question posted by Dag:


DERBY-775: Network client: Add support for scrollable, updatable, 
insensitive result sets

[...]

b) Detectability

In the spec, we required that we be able to detect deletes and updates
to the rows in the result set:

     deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
     updatesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true

(Since inserts are not visible, they can not be detectable, either).
DRDA supports detection of holes in the following manner (quote,
P. 656):

   "To present a static size and static ordering for the result table, the
   relational database may return a hole to the application that fetches
   an updated or deleted row in the result table. A hole in the result
   table occurs when there is a difference between the result table and
   the underlying base table. No data can be fetched from a hole, and the
   hole is manifested in the QRYDTA as a row consisting of a non-null
   SQLCARD and a null data group.

   When the current value of a row no longer satisfies the
   select-statement or statement-name, that row is visible in the cursor
   as an update hole , where the SQLCARD has a warning SQLSTATE of 02502.

   When a row of the result table is deleted from the underlying base
   table, the row is visible in the cursor as a delete hole , where the
   SQLCARD has a warning SQLSTATE of 02502."

For deletes, the "delete hole" is exactly what we need to support
ResultSet#rowDeleted().

For updates, is is not exactly what we want, since we do not intend to
requalify a row after it has been updated (thereby possibly making it
an "update hole"). On the other hand, when we update a row and let it
remain in the result table, DRDA offers no means of conveying that the
row has been changed in the sense of JDBC ResultSet#rowUpdated(), as
far as I can tell.

*Question 2*: Is there some way we can detect the latter without
violating the protocol? One could imagine signalling this using
another warning SQLSTATE. Would this be an acceptable tweaking of the
DRDA?

[...]

Thanks in advance.

Fernanda

Re: Subject: Detectability of updates in DRDA

[Bryan Pendleton <bp...@amberpoint.com>]

Dag H. Wanvik wrote:
> Ah, yes, it is a messy area, isn't it? ;-) For insensitive result sets
> I think we should be able to explain it pretty simply though; own
> changes are visible and can be detected; other changes are not visible
> and can not be detected. Given that we want to implement rowUpdated,
> is using a warning interception as suggested kosher DRDA or not in
> your view?

In my view it is reasonable. Thanks for helping me understand the issues better!

bryan

Re: Subject: Detectability of updates in DRDA

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi,

Bryan Pendleton <bp...@amberpoint.com> writes:

> Frankly, my head is spinning trying to think about this. With all the
> various combinations of "own" updates versus "others" updates, transaction
> isolation levels, implicit and explicit rowsets, etc., this seems like
> a horribly complicated behavior to try to explain to a user.

Ah, yes, it is a messy area, isn't it? ;-) For insensitive result sets
I think we should be able to explain it pretty simply though; own
changes are visible and can be detected; other changes are not visible
and can not be detected. Given that we want to implement rowUpdated,
is using a warning interception as suggested kosher DRDA or not in
your view?

Given the current state of the SUR implementation, we can make this
information available, but I can't say much on how valuable it would
be for an app..  At least, it would free the app from keeping track of
which rows were changed by the user, say, when paging back and forth
in a result set, if highlighting changed rows were desired...

Thanks,
Dag

>
> I tried looking at the docs for a couple of other DB vendors, and they
> were full of lots of complex language about when the rowUpdated() method
> would and would not detect an actual update. Some vendors seemed to say
> that rowUpdated only detects updates made by others; some seemed to say
> that it only detects updates made by this result set; some just punted
> and said they don't support the method.
>
> For example:
> http://www.lc.leidenuniv.nl/awcourse/oracle/java.920/a96654/resltset.htm#1020514
> http://www.ipd.uka.de/~oosem/mobiledb/pb/docs/server_embedded/html/htmlfiles/dev_tutorial2.html
>
> Do we know anything about how actual applications use this method? Since
> I've never tried to use it myself, I don't think I can contribute much
> to the discussion about what might or might not be an acceptable implementation.
>
> thanks,
>
> bryan
>
>

-- 
Dag H. Wanvik
Sun Microsystems, Database Technology Group (DBTG)
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax:  +47 73842101

Re: Subject: Detectability of updates in DRDA

[Bryan Pendleton <bp...@amberpoint.com>]

Dag H. Wanvik wrote:
> Hopefully, we should not even need a separate round-trip, but get info
> back with the row data (perhaps via a warning?), so the client would
> always be aware if a row had been updated as soon as it got it from
> server.

Frankly, my head is spinning trying to think about this. With all the
various combinations of "own" updates versus "others" updates, transaction
isolation levels, implicit and explicit rowsets, etc., this seems like
a horribly complicated behavior to try to explain to a user.

I tried looking at the docs for a couple of other DB vendors, and they
were full of lots of complex language about when the rowUpdated() method
would and would not detect an actual update. Some vendors seemed to say
that rowUpdated only detects updates made by others; some seemed to say
that it only detects updates made by this result set; some just punted
and said they don't support the method.

For example:
http://www.lc.leidenuniv.nl/awcourse/oracle/java.920/a96654/resltset.htm#1020514
http://www.ipd.uka.de/~oosem/mobiledb/pb/docs/server_embedded/html/htmlfiles/dev_tutorial2.html

Do we know anything about how actual applications use this method? Since
I've never tried to use it myself, I don't think I can contribute much
to the discussion about what might or might not be an acceptable implementation.

thanks,

bryan

Re: Subject: Detectability of updates in DRDA

["Dag H. Wanvik" <Da...@Sun.COM>]

Bryan Pendleton <bp...@amberpoint.com> writes:

> - That means that now all we have to do is to figure out what message the
>    client should send the server when the client calls rowUpdated(), and
>    what message the server should return.

Hopefully, we should not even need a separate round-trip, but get info
back with the row data (perhaps via a warning?), so the client would
always be aware if a row had been updated as soon as it got it from
server. This would have the server generating a special warning when
returning an updated row and intercept this warning on the client
side, allowing it to keep track of updated status. Is that an
acceptable way to go?

Dag

Re: Subject: Detectability of updates in DRDA

[Fernanda Pizzorno <Fe...@Sun.COM>]

Bryan Pendleton wrote:

> Fernanda Pizzorno wrote:
>
>> What we are looking for is a mechanism that we can use to implement the
>> ResultSet#rowUpdated() method.
>
>
> I think I understand better now. Let me try stating it in my own words,
> and you can tell me if I am on the right track or not.
>
> - We want to implement ResultSet.rowUpdated() in the Derby Net Client
> - On the server side, we have a data structure which holds a bunch of
>   auxiliary information for each scrollable updatable cursor. In 
> particular,
>   this data structure has a boolean field for each row which records
>   whether or not the row was updated.
> - So once we get to the server side, we have the information that we need
>   to be able to answer the question.
> - That means that now all we have to do is to figure out what message the
>   client should send the server when the client calls rowUpdated(), and
>   what message the server should return.
> - Lastly, you have been looking at the "Update Hole" mechanism in DRDA,
>   because it seems to specify a way to convey extra information about
>   a row in the result set.
> - However, the precise specification for the "Update Hole" field in DRDA
>   has to do with the scenario in which the update to a row causes it
>   to *move* within the result set, so that it's no longer at the same
>   *position* in the result set, which is a different semantic than what
>   is needed for the rowUpdated() method.
> - But it seems like the mechanism is roughly similar to what is needed,
>   so you're proposing to extend it slightly to have an alternate sort
>   of "Update Hole" which has the semantic: "row is still in the same
>   position in the result set, but it has been updated in ways which did
>   not change its position".
> - Then, when the client received that special variant Update Hole, it
>   would return "true" for the rowUpdated() method.
>
> Did I understand correctly?
>
> thanks,
>
> bryan
>
>
Yes, that's what I meant.

Fernanda

Re: Subject: Detectability of updates in DRDA

[Bryan Pendleton <bp...@amberpoint.com>]

Fernanda Pizzorno wrote:
> What we are looking for is a mechanism that we can use to implement the
> ResultSet#rowUpdated() method.

I think I understand better now. Let me try stating it in my own words,
and you can tell me if I am on the right track or not.

- We want to implement ResultSet.rowUpdated() in the Derby Net Client
- On the server side, we have a data structure which holds a bunch of
   auxiliary information for each scrollable updatable cursor. In particular,
   this data structure has a boolean field for each row which records
   whether or not the row was updated.
- So once we get to the server side, we have the information that we need
   to be able to answer the question.
- That means that now all we have to do is to figure out what message the
   client should send the server when the client calls rowUpdated(), and
   what message the server should return.
- Lastly, you have been looking at the "Update Hole" mechanism in DRDA,
   because it seems to specify a way to convey extra information about
   a row in the result set.
- However, the precise specification for the "Update Hole" field in DRDA
   has to do with the scenario in which the update to a row causes it
   to *move* within the result set, so that it's no longer at the same
   *position* in the result set, which is a different semantic than what
   is needed for the rowUpdated() method.
- But it seems like the mechanism is roughly similar to what is needed,
   so you're proposing to extend it slightly to have an alternate sort
   of "Update Hole" which has the semantic: "row is still in the same
   position in the result set, but it has been updated in ways which did
   not change its position".
- Then, when the client received that special variant Update Hole, it
   would return "true" for the rowUpdated() method.

Did I understand correctly?

thanks,

bryan

Re: Subject: Detectability of updates in DRDA

[Fernanda Pizzorno <Fe...@Sun.COM>]

Bryan Pendleton wrote:

>> For updates, is is not exactly what we want, since we do not intend to
>> requalify a row after it has been updated (thereby possibly making it
>> an "update hole").
>
>
> Would it be possible for you to expand on this? I've read through your
> message several times, and I've also read through Appendix B of DRDA V.1
> several times, and I'm still struggling to understand the details here.
>
> If you could perhaps just give a short example of a table with a couple
> rows, and a cursor which has this problem, so that I can understand
> this better, I'd be most grateful.
>
> thanks,
>
> bryan
>
>
We want to scrollable insensitive updatable result sets to be able to
detect own updates. So the JDBC ResultSet.rowUpdated() method should
return true if the row has been visibly updated by the owner or
someone else.

If we have the following result set:

ID    Name
--    ------------------
1     Fernanda

we update the name:

ID    Name
--    ------------------
1     Fernanda Pizzorno

if we later call the rowUpdated() method while positioned on the updated
row, this method should return true.

What we are looking for is a mechanism that we can use to implement the
ResultSet#rowUpdated() method.

Fernanda

Re: Subject: Detectability of updates in DRDA

[Bryan Pendleton <bp...@amberpoint.com>]

> For updates, is is not exactly what we want, since we do not intend to
> requalify a row after it has been updated (thereby possibly making it
> an "update hole").

Would it be possible for you to expand on this? I've read through your
message several times, and I've also read through Appendix B of DRDA V.1
several times, and I'm still struggling to understand the details here.

If you could perhaps just give a short example of a table with a couple
rows, and a cursor which has this problem, so that I can understand
this better, I'd be most grateful.

thanks,

bryan