Finishing up our series on extended selects and readnexts, we will now turn our attention to managing the cursors.

When working with the extended select and readnext statements, the developer is required to maintain the status of the select and readnext cursor. Additionally, as we hinted in our previous entries on Latent and Resolved Selects and Selects and File Resizing, the extended select and readnext statements allow the developer to work with multiple cursors. Multiple cursors means that it's possible to have up to 9 active select statements working at any particular time.

We'll start with cursor status, since that needs to be maintained no matter how many selects may be active at any given time.

We previously mentioned that the extended select syntax is:


While that's true, it's a little simplistic as there are two other keywords, SETTING and ASSIGNING, that you may use as well. The following table describes each keyword in a little more detail:

Keyword	Description
Using	Assigns new criteria to the cursor. If a new table is specified, then the existing criteria is lost.
Setting	Assigns the criteria to the next available cursor.
Assigning	Allows you to assign the results of the cursor to a different table. Any existing results are assigned to the new table.

There are a total of 9 cursors available to the developer (0 through 8) and they can all be active at any given time. By allocating specifically nominated cursors, you can set the system so that cursor 1 is processing your CUSTOMERS table while cursor 2 is processing your INVOICES table. This would be useful if you wanted a report on your invoices sorted in a very specific customer order. As you readnext each customer using cursor 1, you can then issue a new series of selects against cursor 2 finding the invoices for each customer.

Note: While cursor 0 is not reserved exclusively for system use, it is the cursor the system uses. When working with multiple cursors, it's best to only use cursors 1 through 8.

We do realize that you can simply call btree.extract() to return results from the INVOICE table. However, using that you will find that the rows will be returned in key sorted order. If you want any additional sorting then you will need to process the sort, which will normally require an additional file pass. Additionally, since you are managing the cursors, you do not have to worry as much about the system using the cursor from under you, or issuing select statements in subroutines. The SETTING keyword ensures you have a new cursor to manage your selection.

USING keyword
The USING keyword is the most analogous to the basic system SELECT statement. By issuing repeated USING calls to a specific cursor, you can refine the results of your select. Normally, this is mostly used when making a two pass selection. The first pass would resolve based on indexed fields, while the second pass can trim down the results working on a subset of the data. Unlike a standard system select, you cannot take the results of one table and apply the results to a different table. If you do, the original results will be cleared and only the new criteria will be applied to the new table. The assigning keyword allows this functionality.

SETTING keyword
The SETTING keyword finds the next unused cursor and applies the criteria to that cursor. It's the recommended method of starting off a new set of criteria. Which cursor you use doesn't matter, and if you nominate specific cursors, you might accidentally step on a cursor you are using. So, for a two pass report, you would set up the initial select using SETTING, then finish it off with USING.

ASSIGNING keyword
The ASSIGNING keyword is only valid on an active cursor. It is used when you wish to assign existing criteria from one table to a different table.

MODE keyword
Sometimes you may not know at design time which keyword you require. MODE allows you to specify the criteria using a variable. The variable must contain an integer value which identifies the keywork type.

Keyword	Mode Value
Setting	1
Using	2
Assigning	3

For example, if you do not know if you are working with an existing cursor, because you are allowing the user to refine the result list, you can allocate "setting" or "using" through a simple check.


READNEXT line
Finally, once you have the criteria resolved as you wish, you need to issue the READNEXT command. It's important to remember to include the "USING cursorVariable" portion of the READNEXT when working with multiple cursors. Without this section, the system will assume you are using cursor 0.

READNEXT also supports an optional "by" clause. It's used to determine the direction keys are extracted from a cursor:

Integer	Literal	Description
0	AT	Ascending Terminating.
1	AN	Ascending Non-Terminating.
2	DT	Descending Terminating.
3	DN	Descending Non-Terminating.

For example:



will return the keys in the reverse order returned in the cursor, giving the same results as:


Note: When using the literals (AT, AN, DT & DN) you must not enclose them in quotes.

CLEARSELECT
CLEARSELECT allows you to clear a cursor by referencing the cursor number you wish to clear.



Additional Commands
A little known feature of the Basic+ language is the ability to use a cursor instead of a file handle in file I/O statements. These can be used in READ, WRITE, DELETE, LOCK, UNLOCK, MATREAD and MATWRITE. The basic syntax is the same for each command, just substitute "CURSOR cursorNumber" for the file handle.
For example



As you've seen, working with multiple cursors gives a developer much more flexibility in working with the system. The small increase in code length and complexity is a small price to pay for the productivity gains you can achieve. As anecdotal proof of this, I once modified a sales report that used a PERFORM SELECT and a series of BTREE.EXTRACTS which was taking over 6 hours to run into a two cursor selection routine and dropped the execution time to just over an hour.

Labels: cursor, latent, reduce, resolved, select