Chapter 9 SQL Statements
525
♦
RELATIVE RELATIVE positioning is used to move the cursor by a
specified number of rows in either direction before fetching. A positive
number indicates moving forward and a negative number indicates
moving backwards. Thus, a NEXT is equivalent to RELATIVE 1 and
PRIOR is equivalent to RELATIVE -1. RELATIVE 0 retrieves the same
row as the last fetch statement on this cursor.
♦
ABSOLUTE The ABSOLUTE positioning parameter is used to go to a
particular row. A zero indicates the position before the first row (see
"Using cursors in procedures and triggers" on page 471 of the book ASA
User’s Guide).
A one (1) indicates the first row, and so on. Negative numbers are used
to specify an absolute position from the end of the cursor. A negative
one (-1) indicates the last row of the cursor.
♦
FIRST A short form for ABSOLUTE 1.
♦
LAST A short form for ABSOLUTE -1.
Cursor positioning problems
Inserts and some updates to DYNAMIC SCROLL cursors can cause
problems with cursor positioning. The database server does not put
inserted rows at a predictable position within a cursor unless there is an
ORDER BY clause on the SELECT statement. In some cases, the inserted
row does not appear at all until the cursor is closed and opened again.
This occurs if a temporary table had to be created to open the cursor (see
"Temporary tables used in query processing" on page 824 of the book
ASA User’s Guide for a description).
The UPDATE statement may cause a row to move in the cursor. This will
happen if the cursor has an ORDER BY that uses an existing index (a
temporary table is not created).
BLOCK clause Rows may be fetched by the client application more than
one at a time. This is referred to as block fetching, prefetching, or multi-
row fetching. The first fetch causes several rows to be sent back from the
server. The client buffers these rows, and subsequent fetches are retrieved
from these buffers without a new request to the server.
The BLOCK clause is for use in Embedded SQL only. It gives the client and
server a hint as to how many rows may be fetched by the application. The
special value of 0 means the request will be sent to the server and a single
row will be returned (no row blocking).
If no BLOCK clause is specified, the value specified on OPEN is used. For
more information, see "OPEN statement" on page 570.
To Next Page
Loading...