Re: Cursors problem

Michael Habarta wrote:
>
>
> Is there a possbility to fetch previous records using cursors in Pro*c/c++.
>

There are a number of possibilities here. Remember that there is no intrinsic ordering to a relation or table and therefore no possibility of backward (or forward) chaining: the only sequence which is *guaranteed* is that specified with an ORDER BY clause.

I. If you are retrieving rows (in a specified sequence) in a cursor and your user just wants to be able to scroll back through the last few, you have to decide how many a "few" might be and maintain a rolling cache (in your program) of that many sets of row values. Scrolling back then is a process of getting them from your memory cache. This guarantees read-consistency (see below).

II. If a "few" may be indefinitely many, then your best bet is to open and use another cursor containing a statement of the form:

"select ... from <table>
where <order_by_key> < <last_order_by_key> order by order_by_key desc"

A problem here might be that other users may have been updating the re-retrieved rows, which will not necessarily have the same values that the user originally retrieved (unless you lock them on initial fetch)

III. If your user is retrieving rows singly and just wants, at arbitrary times, to look at the "previous" row according to some sequence, then your second cursor might contain:

"select /* index_desc <table> <key> */ ... from <table> where <order_by_key> =
(select max(order_by_key) from <table>
 where order_by_key < <last_key>)"

The same caveat applies as in II above.

Hope this helps.

Chrysalis. Received on Tue Feb 25 1997 - 00:00:00 CST