Searches one or more Btree indexes for data matching the search criteria passed in. Returns the keys to rows having matching data.
Btree.Extract (srch_strng, table, dictvar, keys, option, flag)
The Btree.Extract subroutine has the following parameters.
All informational messages will be suppressed.
|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.|
Three values for option are possible:
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.
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.
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,
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:
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:
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:
Starting, Ending, and Containing
The greater than sign (>) and greater than or equal to signs (>=) are valid search string arguments. To extract keys of all rows with City starting with S, code the following search string:
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:
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:
User Index Extension to Btree.Extract
When the standard comparison operators are not applicable to your task, you may specify your own search algorithm using the Btree.Extract user index facility.
The Btree.Extract user index facility consists of two parts: a parsing module and a compare module. The parsing module is a pre-process to Btree.Extract that determines whether the search value should cause Btree.Extract to call a custom comparison module. The compare module is called as an alternative to the comparison logic usually done by Btree.Extract.
User Index Facility
Create a row in the SYSPTRS table called %USER.INDEX%. When Btree.Extract is run, OpenInsight looks for this row before Btree.Extract does any other processing. Be aware that when you create %USER.INDEX%, all system processing that accesses Btree.Extract will access this row and attempt to follow its specifications.
The %USER.INDEX% row must have two fields, and can have an optional third field, which is described in the following table:
|1||USER.INDEX||This literal text must be the first field in USER.INDEX.|
|2||parsing_module||In this field of the %USER.INDEX% row, enter the name of your own parsing routine.|
The routine named in this column should examine the search data that has been passed to Btree.Extract to determine whether the custom comparison routine should be called. If you choose, this preprocessor can alter the search data.
Btree.Extract passes four arguments to parsing_module:
Parser(search_val, start_value, user_index_flag, compare_mod)
Parameters for parsing_module (which must be entered in uppercase) are as follows:
In addition to the arguments just described, the labeled Common area USERIX is available to the parsing and compare routines. There are two labeled common variables to provide additional information about the indexed values.The labeled block is defined as follows.
Common /USERIX/ UIX.SM.FLAG, UIX.DCONV
The variable UIX.SM.FLAG is a Boolean value, true if the column is left-justified sorted, and false if the column is right-justified. The variable UIX.DCONV contains the output conversion to be applied to the value to change it to its external representation (data is stored in the index in its internal representation).