Btree.Extract subroutine
Description
Searches one or more Btree indexes for data matching the search criteria passed in. Returns the keys to rows having matching data.
Syntax
Btree.Extract(srch_strng, table, dictvar, keys, option, flag)
Parameters
The Btree.Extract subroutine has the following parameters.
All informational messages will be suppressed.
| Parameter | Description |
|---|---|
| Srch_strng | Must end with a field mark. The basic unit of srch_strng is a search column. If you want to search by more than one criterion, include additional search columns in srch_strng. Each search column is delimited from another by a field mark (@FM), and multiple search columns imply an And relation (conditions must be satisfied for all search columns before any key is returned). The And relation may be overridden for individual search values by prefixing each with a semicolon (;). Searchcolumn structure Syntax searchcolumn = IndexedColumn:@VM:data1 [:@VM:data2 …]:@FM Made up from the name of an indexed column and the search values to be located within that column. A Btree index must have been applied to the specified row column, or an error is generated. Within the search column, search values are delimited from the indexed column name and each other by a value mark (@VM). Within the search column, search values are located based on an Or relation: a row key is returned when any of the values is found within the specified column. The implied Or relation for the @VM-delimited search values may be forced to an And relation by prefixing any desired search value with an ampersand (&). This situation presumes a multi-valued column, because every And relation means that two values must be found before a hit is registered.\\ |
| Table | Pass the name of the table to be searched.\\ |
| Dictvar | Pass the file handle for the dictionary of the specified table.\\ |
| Keys | Returns row keys for all rows that satisfy the criteria in srch_strng. Multiple keys are delimited with value marks. |
| Option | Three values for option are possible: Option Null - All messages will display E - All error messages will be suppressed. S - All informational messages will be suppressed. |
| Flag | Error codes are returned in flag. After execution, keys contains a list of keys matching the search criteria. flag returns one of several possible values, depending on the success of the search process. Value 0 - The search was successful. All possible keys were returned in keys. -1 - The search failed, for reasons other than no keys found. This occurs, for example, when a specified column does not have a Btree index. |
Btree.Extract can look into more than one index in one call, and can also look up more than one data value. The system allows you to use the And and Or logical operators to retrieve values from the Btree index. Btree.Extract only searches Native Table indexes.
Btree.Extract also allows you to provide your own routines for preprocessing search data, as well as for your own search algorithm. Refer to "User Index Facility" and "User Index Extension to Btree.Extract" topics for more information.
You must open the associated dictionary table before calling Btree.Extract.
Note: The Btree.Extract routine will update Btree indices prior to the extract if the environment parameter for ENV_BTREE_FLUSH_ON$ is set to true. The Database Manager's Environment Settings window contains a checkbox to turn the update flag on.
See Also
BTree.Read, Extract_SI_Keys, IXLOOKUP event, Update_Index, Collect.IXVals()
Example
/* The following code fragment opens the dictionary to a table, then sets up a search that looks for all records having either data1 or data2 as values in the specified column. The routine returns this list of keys to the calling procedure. */
Declare Subroutine Btree.Extract
table = "CAR_PARTS"
Open "DICT ":table To @DICT Else
RetVal = Set_FSError()
Return
End
Column = "PART_NAME"
data1 = "WHEEL, STANDARD"
data2 = "TIRE, BIAS PLY"
search_criteria = column:@VM:data1:@VM:data2:@FM
keylist = ""
option = ""
flag = ""
Btree.Extract(search_criteria, table, @DICT, keylist, option, flag)
Return keylist
Searching by NOT The search values in //srch_strng// are substrings, and as such, you can modify the search relations using substring search characters. For example,
srch_strng = "COMPANY_NAME":@VM:"#TRUST INSURANCE":@FM
will find all values of the indexed column COMPANY_NAME that are not "TRUST INSURANCE".
Searching by BETWEEN Btree.Extract supports a special search operator, **between** ("~" (tilde)). The operator **between** differs from **range** in that it is not inclusive. For example, the following search string will return row keys for all rows having ZIP codes between, but not including, 98100 and 98111:
srch_strng = "ZIP":@VM:"98100~98111":@FM
Searching by AND As noted above, multiple search criteria are linked with an implicit And. The OpenList filter sub-statement With STATE = "CT" And CITY = "Stamford" is performed in Btree.Extract by the code:
"STATE":@VM:"CT":@FM:"CITY":@VM:"Stamford":@FM
Searching by OR To change the implicit And to an Or, insert a semicolon (;) before each search value in the second (and subsequent) search column(s). The statement With STATE = "CT" Or CITY = "Stamford" is performed in Btree.Extract by the code:
"STATE":@VM:"CT":@FM:"CITY":@VM:";Stamford":@FM
Starting, Ending, and ContainingThe "starting with" indicator (]) is also a valid search string argument. To extract keys of all rows with City starting with S, code the following search string:
srch_strng = "CITY":@VM:"S]":@FM
The "ending with" indicator ([) is also a valid search string argument. To extract keys of all rows with City ending with P, code the following search string:
srch_strng = "CITY":@VM:"[P":@FM
The "containing" indicators ([]) also are valid search string arguments, allowing for a substring search. To extract keys of all rows with City containing the letter P, code the following search string:
srch_strng = "CITY":@VM:"[P]":@FM
==== ====