BSCAN
Synopsis
BSCAN keyvar [,recvar] [FROM filevar[,startkey]] [USING indexname] [RESET] [BY seq] [THEN statements] [ELSE statements]
Arguments
keyvar | BSCAN assigns to keyvar the key or item id returned by the BSCAN operation. |
recvar | Optional — If you specify a recvar, BSCAN assigns the contents associated with keyvar to it. This can be the list of item ids associated with the key returned in keyvar, or the contents of the record associated with the item id returned in keyvar. |
FROM filevar | Optional — A local variable name assigned to the MultiValue file by the OPEN statement. If you do not specify filevar, the default file, specified in the system variable @STDFIL, is used. |
startkey | Optional — An expression that specifies the relative starting position of the scan. startkey can be an index key or item id. If the USING indexname clause is used, startkey is a value in the specified index. |
USING indexname | Optional — The name of a secondary index associated with the file. |
BY seq | Optional — Specifies the direction of the scan. The available seq options are “A” (ascending) and “D” (descending). The default is ascending. |
Description
The BSCAN statement operates in 2 modes:
-
BSCAN with an indexname steps through the unique keys in an index. The keys are returned as keyvar. It optionally returns the item id associated with keyvar as recvar.
-
BSCAN without an indexname steps through the item ids in an inode-type file. The list of item ids is returned as keyvar. It optionally returns the contents of each keyvar item as recvar.
The BSCAN statement scans the leaf nodes of either a B-tree file (type 25) or a secondary index. The record ID returned by the scan operation is assigned to keyvar. If you specify a recvar, BSCAN assigns the contents of the keyvar record to it.
filevar specifies an open file. If you do not specify filevar, the default file is used. (For more information on default files, see the OPEN statement.) If the specified file is neither accessible nor open, BSCAN returns nothing and sets STATUS() to 3.
startkey is an expression that evaluates to a record ID of a record in the B-tree file. If the USING clause is used, startkey is a value in the specified index. startkey specifies the relative starting position of the scan.
startkey need not exactly match an existing record ID or index key. If it does not, the scan finds the next or previous record ID or value, depending on whether the scan is in ascending or descending order. For example, depending on how precisely you want to specify the starting point at or near the record ID or value SMITH, startkey can evaluate to SMITH, SMIT, SMI, SM, or S.
If you do not specify startkey, on the initial BSCAN operation, the scan starts at the beginning (leftmost slot of the leftmost leaf) or end (rightmost slot of the rightmost leaf) of the index or file, depending on the value of the seq expression. The scan then moves in the direction specified in the BY clause. Subsequent BSCAN operations with no startkey specified will continue from the keyvar returned by the previous BSCAN.
indexname is an expression that evaluates to the name of a secondary index associated with the file.
RESET resets the internal scan pointer to the first or last key, depending on the BY seq clause value. If you do not specify seq, the scan is done in ascending order. If you specify startkey in the FROM clause, RESET is ignored.
seq is an expression that evaluates to A or D; it specifies the direction of the scan. "A", the default, specifies ascending order. "D" specifies descending order.
You can optionally specify a THEN clause, an ELSE clause, or both a THEN and an ELSE clause. If the BSCAN statement finds a valid index key, or item id and its associated data, the THEN clause is executed. If the scan does not find a valid index key, or if some other error occurs, the ELSE clause is executed. The statements argument can be the NULL keyword, a single statement, or a block of statements terminated by the END keyword. A block of statements has specific line break requirements: each statement must be on its own line and cannot follow a THEN, ELSE, or END keyword on that line.
Any file updates executed in a transaction (that is, between a BEGIN TRANSACTION statement and a COMMIT statement) are not accessible to the BSCAN statement until after the COMMIT statement has been executed.
Cache supports the BSCAN statement for compatibility with legacy MultiValue systems. When retrieving keys from an index, developers should use OPENINDEX and SELECT with the ATKEY clause, because SELECT ATKEY has a simpler, more intuitive syntax and superior performance.
STATUS Values
The STATUS function returns the following values after the BSCAN statement is executed:
If NLS is enabled, the BSCAN statement retrieves record IDs in the order determined by the active collation locale; otherwise, BSCAN uses the default order, which is simple byte ordering that uses the standard binary value for characters; the Collate convention as specified in the NLS.LC.COLLATE file for the current locale is ignored.
0 | The scan proceeded beyond the first or last key. keyvar and recvar are set to empty strings. |
1 | The scan returned an existing index key, or an index key that matches the key specified by startkey. |
2 | The scan returned an index key that does not match startkey. keyvar is either the next or the previous record ID in the B-tree, depending on the direction of the scan. |
3 | filevar is not open, or is not an inode-type. |
4 | indexname does not exist. |
5 | seq does not evaluate to A or D. |
6 | The index specified by indexname needs to be built. |
10 | An internal error was detected. |
Examples
The following example demonstrates using BSCAN to step through the keys in an index:
0001 EXECUTE 'CREATE-FILE DATE-FILE'
0002 OPEN 'DICT','DATE-FILE' TO DICT.DATE.FILE ELSE STOP 201,'DICT DATE-FILE'
0003 WRITE 'D':@AM:1 ON DICT.DATE.FILE,'DAY.OF.WEEK'
0004 EXECUTE 'CREATE-INDEX DATE-FILE DAY.OF.WEEK'
0005 OPEN 'DATE-FILE' TO DATE.FILE ELSE STOP 201,'DATE-FILE'
0006 FOR I=1 TO 21
0007 ID=DATE()+I
0008 WRITE OCONV(ID,'DWA') ON DATE.FILE,ID
0009 NEXT I
0010 LOOP
0011 BSCAN DOW,IDLIST FROM DATE.FILE USING 'DAY.OF.WEEK' ELSE EXIT
0012 CRT 'DOW=':DOW,' DATES=':IDLIST
0013 REPEAT
This returns the following output:
[421] DICT for file 'DATE-FILE' created. Type = INODE [418] Default data section for file 'DATE-FILE' created. Type = INODE [437] Added default record '@ID' to 'DICT DATE-FILE'. [417] CreateFile Completed. DOW=FRIDAY DATES=14642 14649 14656 DOW=MONDAY DATES=14645 14652 14659 DOW=SATURDAY DATES=14643 14650 14657 DOW=SUNDAY DATES=14644 14651 14658 DOW=THURSDAY DATES=14641 14648 14655 DOW=TUESDAY DATES=14646 14653 14660 DOW=WEDNESDAY DATES=14640 14647 14654
Notice that on each iteration, BSCAN returns the next unique key in the index. The item ids associated with the key are returned as an @AM-delimited list in the optional recvar argument. If you want to process each record for the key, you need to code a loop to do so.
The following example demonstrates using BSCAN to retrieve the keys for a particular key:
0001 OPEN 'DATE-FILE' TO DATE.FILE ELSE STOP 201,'DATE-FILE'
0002 BSCAN DOW FROM DATE.FILE,'SUN' USING 'DAY.OF.WEEK' ELSE NULL
0003 CRT 'DOW=':DOW,' STATUS=':STATUS()
0004 BSCAN DOW FROM DATE.FILE USING 'DAY.OF.WEEK' ELSE NULL
0005 CRT 'DOW=':DOW,' STATUS=':STATUS()
0006 BSCAN DOW FROM DATE.FILE,'Z' USING 'DAY.OF.WEEK' ELSE NULL
0007 CRT 'DOW=':DOW,' STATUS=':STATUS()
This returns the following output:
DOW=SUNDAY STATUS=2 DOW=THURSDAY STATUS=1 DOW= STATUS=0
On line 2 we request key SUN. There is no SUN, so BSCAN returns the next key SUNDAY. The BSCAN on line 4 doesn't specify a start key, so the next key. THURSDAY, is returned. On line 6, we request key Z. There is no Z and nothing after Z, so BSCAN returns status 0
In the following example BSCAN is used to scan the item ids of an inode-type file. In this example, we look for item id SEL in VOC. SEL does not exist, so BSCAN returns the next id SEARCH:
0001 OPEN 'VOC' TO VOC ELSE STOP 201,'VOC'
0002 BSCAN ID,ITEM FROM VOC,'SEL' BY 'D' ELSE NULL
0003 CRT 'ID=':ID,' ITEM=':ITEM
0004
MINE:TRY
ID=SEARCH ITEM=V SEARCH C 2C
See Also
-
OPEN statement
-
OPENINDEX statement
-
SELECT ATKEY statement
-
STATUS function