I was experimenting with FETCH ABSOLUTE, and I noticed an interesting
property. It is possible to position beyond the result set (before the
first or after the last row). This position seem to be remembered.
This remembering has no effect for FETCH NEXT / FETCH PRIOR. If
positioned before the first row, no matter how far before the first row,
then FETCH NEXT will return the first row, and FETCH PRIOR does
something similar if positioned after the last row, returning the last row.
However, FETCH RELATIVE has some interesting properties in this situation.
For example, with a result set of 3 rows:
* FETCH ABSOLUTE -4 will position immediately before the first row,
using FETCH RELATIVE 1 will then fetch the first row.
* FETCH ABSOLUTE -5 will position 2 before the first row, FETCH RELATIVE
1 will - when accessing the cursor variable - produce an error ("Cursor
C is not positioned in a valid record"), while FETCH RELATIVE 2 will
produce the first record.
* FETCH ABSOLUTE -5 followed by two FETCH RELATIVE 1, followed by
accessing the cursor variable will also produce an error; in other
words, it only works if you immediately specify the right number of rows
to move
* Similar for FETCH ABSOLUTE 4 (directly after last row) and FETCH
RELATIVE -1, and FETCH ABSOLUTE 5 (2 after last row), and FETCH RELATIVE
-1 vs FETCH RELATIVE -2.
For documentation purposes, I was wondering if this was intended
behaviour, both the ability to position beyond the bounds of the result
set using ABSOLUTE, and the remembering of the actual position for
subsequent fetches.
Or if this is just an artifact of implementation, and therefor might
change in a future version and should not be relied on.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
