Filter
Top Products
Cursors in transactions
312
The rows in a cursor, like those in a table, have no order associated with them.
The
FETCH command steps through the rows, but the order may appear
random and can even be inconsistent. For this reason, you will want to impose
an order by appending an
ORDER BY phrase to your SELECT statement.
Cursors and versioning
When you use cursors, Adaptive Server IQ needs to be able to manage multiple
versions within a single transaction. For example, assume that you open a
cursor called
cust_cursor at time x that uses the customer table. You then update
that table later on at time y. Adaptive Server IQ needs to retain the version of
the
customer table from time x until you are done using cust_cursor.
See “Effect of rollback” for what happens to cursors during a rollback of the
database.
Adaptive Server IQ's support for cursors is oriented toward their likely use in
DSS applications. The following sections discuss specific cursor
characteristics with implications for transaction processing.
Cursor sensitivity
A cursor is said to be sensitive if its membership—the data rows it returns—
can vary from the time it is opened until the time it is closed. An insensitive
cursor has its membership fixed when it is opened. Adaptive Server IQ
supports only insensitive cursors.
Cursor scrolling
Adaptive Server IQ cursors can be either scrolling or non-scrolling. Non-
scrolling cursors allow only the command forms
FETCH NEXT and FETCH
RELATIVE 0
to find and retrieve data. They do not keep track of which rows
have been fetched. A cursor declared as
DYNAMIC SCROLL is the same as a
cursor declared as
SCROLL.
You can force all cursors to be non-scrolling by setting the option
FORCE_NO_SCROLL_CURSORS to ON. You may want to use this option to
save on temporary storage requirements if you are retrieving very large
numbers (millions) of rows.