Scrollable Cursors

Adaptive Server Enterprise allows both scrollable and nonscrollable cursors, which can be either
semisensitive or insensitive.
“Scrollable” means that you can scroll through the cursor result set by fetching any, or many, rows, rather than one row at a time; you can also scan the result set repeatedly.

A scrollable cursor allows you to set the position of the cursor anywhere in the cursor result set for as long as the cursor is open, by specifying the option  first, last, absolute, next, prior, absolute or relative in a fetch statement.
Syntax:
declare cursor_name
[cursor_sensitivity]
[cursor_scrollability] cursor
for cursor_specification

Example:
declare CurSr scroll cursor for
select emp_name from employees
 Scrollable_cursor_Fetch extension

Note: cursor_scrollability can be defined as scroll or no scroll. The default is no scroll.
Note: 
cursor_sensitivity can be defined as insensitive or semi_sensitive.
Note: The default for the cursor is
semisensitive. No support for the concept of “sensitive” exists in ASE 15.
Note: 
For scrollable cursors in ASE 15, the only valid cursor specifica‐tion is “for read only”.
Note: 
All update cursors are nonscrollable.

The UseCursor property must be set correctly in order to obtain the desired scrollable cursor.
Setting the UseCursor connection property:

  • When you set the UseCursor connection property to 1, and the ASE version is 15.0 or later, server‐side scrollable cursors are used. Server‐side scrollable cursors are not available on all pre‐15.0 ASE versions.
  • When you set the UseCursor connection property to 0, client‐side scrollable cursors (cached resultsets) are used, regardless of the ASE version.

Cursor related Global variables:

Cursor_Global variables

Cursor Global Variables

@@fatch_status:

Cursor Fetch Status

Cursor Fetch Status

@@cursor_rows:

@cursor_rows

@cursor_rows

Rules for Scrollable Cursor:
next => A fetch utilizing the next extension when the cursor is already positioned in the last row of the cursor set will result in @@sqlstatus = 2, @@fetch_status = 1, and no data returned by the fetch. Cursor position will remain on the last row of the cursor set.

prior => A fetch utilizing the prior extension when the cursor is already positioned at the first row of the cursor result set will result in @@sqlstatus = 2, @@fetch_status = 1, and no data returned by the fetch. Cursor position will remain on the first row of the cursor set.
Note: A subsequent fetch of the next cursor row will fetch the first row of the cursor result set.

absolute => A fetch utilizing the absolute extension that calls a row that is greater than the rowcount in the cursor set will result in @@sqlstatus = 2, @@fetch_status of 1, and no data returned by the fetch. 

Sensitivity and scrollability:

  • Insensitive: The cursor shows only the result set as it is when the cursor is opened; data changes in the underlying tables are not visible
  • Semisensitive: some changes in the base tables made since opening the cursor may appear in the result set. Data changes may or may not be visible to the semisensitive cursor.

Insensitive scrollable cursors:
When you declare and open an insensitive cursor, a worktable is created and fully populated with the cursor result set. Locks on the base table are released, and only the worktable is used for fetching.
To declare cursor CurSr_I as an insensitive cursor, enter:

declare CurSr_I insensitive scroll cursor for
select emp_id, fname, lname
from emp_tb
where emp_id > 2002000

open CurSr_I

Table

Table (for exaplaining scrollable cursor)

The scrolling worktable is now populated with the data shown in above table.
To change the name “Sam” to “Joe,” enter:
update emp_tab set fname = “Joe”
where fname = “Sam”

Now four “Sam” rows in the base table emp_tab disappear, replaced by four “Joe” rows.

fetch absolute 2 CurSr_I

The cursor reads the second row from the cursor result set, and returns Row 2, “2002020, Sam, Clarac.” Because the cursor is insensitive, the updated value is invisible to the cursor, and the value of the returned row—”Sam,” rather than “Joe”—is the same as the value of Row 2 in above displayed Table.

This next command inserts one more qualified row (that is, a row that meets the query condition in declare cursor) into table emp_tab, but the row membership is fixed in an cursor, so the added row is not visible to cursor CurSr_I. Enter:

insert into emp_tab values (2002101, “Sophie”, “Chen”, .., …, …)

The following fetch command scrolls the cursor to the end of the worktable, and reads the last row in the result set, returning the row value “2002100, Sam, West.” Again, because the cursor is insensitive, the new row inserted in emp_tab is not visible in cursor CurSr_I’s result set.

fetch last CurSr_I

Semisensitive scrollable cursors: Semisensitive scrollable cursors are like insensitive cursors in that they use a worktable to hold the result set for scrolling purposes. But in semi_sensitive mode, the cursor’s worktable materializes as the rows are fetched, rather than when you open the cursor. The membership of the result set is fixed only after all the rows have been fetched once.
To declare cursor CurSr_SS semisensitive and scrollable, enter:

declare CurSr_SS semi_sensitive scroll cursor for
select emp_id, fname, lname
from emp_tab
where emp_id > 2002000

open CurSr_SS

The initial rows of the result set contain the data shown in above displayed Table. Because the cursor is semisensitive, none of the rows are copied to the worktable when you open the cursor. To fetch the first record, enter:

fetch first CurSr_SS

The cursor reads the first row from emp_tab and returns 2002010, Mari, Cazalis. This row is copied to the worktable. Fetch the next row by entering:

fetch next CurSr_SS

The cursor reads the second row from emp_tab and returns 2002020, Sam, Clarac. This row is copied to the worktable. To replace the name “Sam” with the name “Joe,” enter:

update emp_tab set fname = “Joe”
where fname = “Sam”

The four “Sam” rows in the base table emp_tab disappear, and four “Joe” rows appear instead. To fetch only the second row, enter:

fetch absolute 2 CurSr_SS

The cursor reads the second row from the result set and returns employee ID 2002020, but the value of the returned row is “Sam,” not “Joe.” Because the cursor is semisensitive, this row was copied into the worktable before the row was updated, and the data change made by the update statement is invisible to the cursor, since the row returned comes from the result set scrolling worktable.

To fetch the fourth row, enter:

fetch absolute 4 CurSr_SS

The cursor reads the fourth row from the result set. Since Row 4, (2002040, Sam, Burke) was fetched after “Sam” was updated to “Joe,” the returned employee ID 2002040 is Joe, Burke. The third and fourth rows are now copied to the worktable.

To add a new row, enter:
insert into emp_tab values (2002101, “Sophie”, “Chen”, .., …, …)

One more qualified row is added in the result set. This row is visible in the following fetch statement, because the cursor is semisensitive and because we have not yet fetched the last row. Fetch the updated version by entering:

fetch last CurSr_SS

The fetch statement reads 2002101, Sophie, Chen in the result set.

After using fetch with the last option, you have copied all the qualified rows of the cursor CurSr_SS to the worktable. Locking on the base table, emp_tab, is released, and the result set of cursor CurSr_SS is fixed. Any further data changes in emp_tab do not affect the result set of cursor.

Note: Locking schema and transaction isolation level also affect cursor visibility. The above example is based on the default isolation level, level 1



This entry was posted in RDBMS concepts and tagged . Bookmark the permalink.

Leave a Reply