784.TXT
=====================================================================
Using Scrolling Cursors
=====================================================================
PRODUCT: R:BASE VERSION: 4.5+ or Higher
=====================================================================
CATALOG: Programmin in R:BASE AREA : Logic & Data Manipulation
R:BASE 4.5 Plus! introduced the concept of scrolling cursors. When a
cursor is defined as a scrolling cursor, you gain the capability of
moving both forwards and backwards through the rows of data and can
also jump past rows. A regular cursor only moves forward through rows,
one row at a time.
To define a cursor as a scrolling cursor, include the word SCROLL in
the DECLARE CURSOR command. For example,
DECLARE c1 SCROLL CURSOR FOR SELECT ...
The word SCROLL comes right after the cursor name. If SCROLL is not
included in the cursor definition, the cursor can only move forward
through the rows one at a time.
Once a cursor is defined as a scrolling cursor, a number of additional
options on the FETCH command become available. These options are as
follows; note that the directions and positions are based on the order
of the rows as specified by the DECLARE CURSOR command, not on the
order of the rows in the actual table:
NEXT - The default option if none is specified on the FETCH command.
NEXT moves the cursor forward through the rows, it gets the next
available row based on the current cursor position. NEXT steps through
the rows one-by-one going forward.
PRIOR - Moves the cursor backwards through the rows. The PRIOR
option gets the previous row based on the current cursor position,
and steps through the rows one-by-one going backwards.
FIRST - Moves the cursor from its current position to the first row.
This option jumps immediately to the first row as determined by the
DECLARE CURSOR command. A FETCH NEXT then finds the second row. The
cursor is repositioned at the beginning of the set of rows.
LAST - Moves the cursor from its current position immediately to
the last row as specified by the DECLARE CURSOR command. A FETCH
PRIOR then finds the next to last row; a FETCH NEXT returns "end of
data encountered". LAST jumps over the rows between the current cursor
position and the last row.
ABSOLUTE n - Moves the cursor the specified number of rows from the
first row of data as determined by the DECLARE CURSOR and OPEN
commands. A positive number must be specified; you can't use this
option to move backwards. The intervening rows are jumped over. You
can't jump past the last row; if the number given is greater than the
number of rows retrieved, an "end of data" error is returned.
RELATIVE n - Moves the cursor the specified number of rows from the
current cursor position. This option moves the cursor either forwards
or backwards - forwards if a positive number is specified, backwards
if a negative number is specified. The intervening rows are jumped
over. You can't jump past the last row or the first row; an "end of
data" error is returned if the specified number would take you past
the beginning or end of the selected rows.
An example
To see how a scrolling cursor can be used in an application, imagine
you have a group of customers to contact each day. The scrolling
cursor retrieves the list of customers for today. They are ordered by
company name. The first row is brought up in a menuless form. The
form remains on the screen when you are done with the record, and a
menu pops up giving the user choices as to which record to select
next. You can:
move through the list of customers one-by-one both forwards and
backwards jump to the last record and back to the first record jump
past a group of records search for a particular record by last name
or by company name
Each time you select a record, the cursor is repositioned ready for
the next selection.
The ASCII menu file:
walklist
POPUP
|Next Customer|
|Previous Customer|
|Jump Forward "n"|
|Jump Backward "n"|
|Last Customer|
|First Customer|
|Search by Lastname|
|Search by Company|
ENDC
*(WALKLIST.CMD
scroll through a list of customers )
SET MESSAGE OFF
SET ERROR MESSAGE OFF
DROP CURSOR C1
CLS
DECLARE C1 scroll CURSOR FOR +
SELECT CustId, LastName, Company FROM Customer +
WHERE calldate = .#DATE ORDER BY Company
OPEN C1
FETCH FIRST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
WHILE SQLCODE <> 100 THEN
EDIT USING cust WHERE CustId = .VCustId
CHOOSE VAction FROM WALKLIST.MNU AT 10 51 FOOT
IF VAction = '[ESC]' THEN
RETURN
ENDIF
SWITCH (.VAction)
CASE 'Next Customer'
FETCH NEXT FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
IF SQLCODE = 100 THEN
FETCH LAST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
ENDIF
BREAK
CASE 'Previous Customer'
FETCH PRIOR FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
IF SQLCODE = 100 THEN
FETCH FIRST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
ENDIF
BREAK
CASE 'Jump Forward "n"'
DIALOG 'How many to jump forward' VNum=4 VEndKey 1
SET VAR VPlus = (INT(.VNum))
FETCH RELATIVE .vplus FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
IF SQLCODE = 100 THEN
FETCH LAST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
ENDIF
BREAK
CASE 'Jump Backward "n"'
DIALOG 'How many to jump backward' VNum=4 VEndKey 1
SET VAR VMinus = (INT(.VNum) * -1)
FETCH RELATIVE .vminus FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
IF SQLCODE = 100 THEN
FETCH FIRST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
ENDIF
BREAK
CASE 'Last Customer'
FETCH LAST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
BREAK
CASE 'First Customer'
FETCH FIRST FROM C1 INTO +
VCustId ICustId, VLastname ILastname, +
VCompany ICompany
BREAK
CASE 'Search by Lastname'
SET VAR vsearch = NULL
DIALOG 'Enter the last name to find' +
VSearch VEndKey 1 at 12
IF VEndKey = '[Esc]' THEN
BREAK
ENDIF
WHILE #PI <> 0.0 THEN
FETCH NEXT FROM c1 INTO +
VCustID ICustId, VLastname ILastname, +
VCompany ICompany
IF VLastname CONT .VSearch THEN
BREAK
ENDIF
IF SQLCODE = 100 THEN
DIALOG +
'No match found. Continue search from beginning?' +
VResp VEndKey YES
IF VEndKey = '[Esc]' THEN
BREAK
ENDIF
IF VResp = 'YES' THEN
FETCH FIRST FROM c1 INTO +
VCustID ICustId, VLastname ILastname, +
VCompany ICompany
IF VLastname CONT .VSearch THEN
BREAK
ENDIF
ELSE
FETCH LAST FROM c1 INTO +
VCustID ICustId, VLastname ILastname, +
VCompany ICompany
BREAK
ENDIF
ENDIF
ENDWHILE
BREAK
CASE 'Search by Company'
SET VAR VSearch = NULL
DIALOG 'Enter the company to find' +
VSearch VEndKey 1 at 12
IF VEndKey = '[Esc]' THEN
BREAK
ENDIF
WHILE #PI <> 0.0 THEN
FETCH NEXT FROM c1 INTO +
VCustID ICustId, VLastname ILastname, +
VCompany ICompany
IF VCompany CONT .VSearch THEN
BREAK
ENDIF
IF SQLCODE = 100 THEN
DIALOG +
'No match found. Continue search from beginning?' +
VResp VEndKey YES
IF VEndKey = '[Esc]' THEN
BREAK
ENDIF
IF VResp = 'YES' THEN
FETCH FIRST FROM c1 INTO +
VCustID ICustId, VLastname ILastname, +
VCompany ICompany
IF VCompany CONT .VSearch THEN
BREAK
ENDIF
ELSE
FETCH LAST FROM c1 INTO +
VCustID ICustId, VLastname ILastname, +
VCompany ICompany
BREAK
ENDIF
ENDIF
ENDWHILE
BREAK
ENDSW
ENDWHILE
DROP CURSOR C1
RETURN
Define the scrolling cursor
Open the cursor and get the first row
Bring up the form with the data from the first row. After the form is
exited, choose from the menu which record to retrieve next
The switch/case block determines which record to retrieve
Move forward one row at a time
If already on the last row, stay there
Move backward one row at a time
If already on the first row, stay there
Move forward the specified number of records R:BASE counts from the
current cursor position
If the number of records to jump past takes you beyond the last
record, the last record is retrieved
Move backward the specified number of records R:BASE counts from the
current cursor position
If the number of records to jump past takes you beyond the first
record, the first record is retrieved
Jump to the last record Next customer from the last record
returns end-of-data
Jump to the first record Prior customer from the first record returns
end-of-data
Prompt for the last name to find
Search forward for a matching record
If a match is found, the row is displayed and the cursor repositioned
at that row
If no match was found, the search can be continued from the first row.
If the search is not continued, the last row is retrieved
Prompt for the company name to find
Search forward for a matching company record If a match is found,
the row is displayed and the cursor repositioned at that row
If no match was found, the search can be continued from the first row.
If the search is not continued, the last row is retrieved.