Subject Re: [Firebird-Architect] Re: Cursors
Author Ann W. Harrison
At 07:16 AM 6/6/2004, Daniel Rail wrote:

> > So we are back to the discussion "Scrollable cursors: to be or not to
> > be". Can anobody tell do Oracle, DB2 and MS SQL implement them and how?
>They all support scrollable cursors on the "result set", by using the
>DECLARE CURSOR statement. It is just a more fully implemented version
>of the DECLARE CURSOR(and related commands) in FB. It is part of the
>SQL standards, and surprisingly MS-SQL is pretty close to the standard.

Support for that syntax is in the code, though ifdef'd out. It was done
in the early 90's for dbase compatibility and never released. If you
give it an absolute (or relative) offset forward, it reads that many
records and returns the last one. For backward offsets, my recollection
is that it saves the dbkeys.

On the question of using the index to find the nth record in an ordered
result set, there are three problems. The first is that index keys (and
soon the pointers as well) are compressed, thus variable length. The
second is that the index is multi-generational, meaning that it may
contain records that aren't committed, have been deleted, or are
unavailable to the current transaction. The third is that the index is
not stable. You can't just count across level 0, determine that there
are 438,526 leaf pages and go back to number 219,263. It could have
split or been recombined, depending on activity.