Re: Difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE ResultSets

Jonck van der Kogel wrote:
> Hi everybody,
> While working with the JDBC API (with MySQL as the DB being queried) I
> recently bumped into the terms TYPE_SCROLL_INSENSITIVE and
> TYPE_SCROLL_SENSITIVE ResultSets. The scrolling part is clear, you are
> allowed to move through the ResultSet in a dynamic fashion.
> The sensitive or insensitive part was not so clear to me however. What
> I read was that "... the ResultSet can change as a result of
> modifications to the database by others.".
> Does this mean that if:
> - user A queries row 1 in a table
> - while user A still has the ResultSet open user B comes along and
> makes a change in this same row 1, user A's ResultSet will change
> "live"??
>
> I tried experimenting with this a bit, but saw no such behavior.
> Therefore I am assuming that the difference between sensitive and
> insensitive ResultSets is something different.
>
> Could someone here explain to me what the difference is between the
> two and in which situations one is preferable over the other?
> Thanks very much, Jonck

The SCROLLing part of ResultSet in JDBC is a design flaw introduced by the design committee and has no relation whatsoever with a sound programming model, or with how the way actual RDBMSes work, so even if you wanted to make use of such a design mistake, you can hardly do that in practice, and is better that you don't anyways. It would make some sense only if you were accessing FoxPro tables or MS Access Database, but real men use client/server :)

The bottom line is that proper use of RDBMSes is that you operate on sets. You formulate a qyuery, get a set, if you want you can issue an UPDATE as another query, that's it. The interference or more precisely the non-interference with other concurrent transaction is regulated by the concept of ISOLATION LEVEL, and this is discussed in most standard database books.

Assuming that ResultSet are intimately connected or similar to a file pointer that moves among the records of the table and you can "update in place", as well as move back and forth, and/or observe updates by other, is unreliable and bad engineering for too many reasons to be discussed here, unless you have a particular curiosity and/or don't trust me.

The difference between operating on sets and programming with cursors over record pointers is definitely on-topic for comp.database.theory, but on the other hand it is common knowledge / widely accepted that the second is not sound engineering for applications with databases.

Cheers,
Costin Received on Fri Feb 06 2004 - 20:56:03 CET