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.

searchcolumn = IndexedColumn:@VM:data1 [:@VM:data2 ...]:@FM

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 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:

srch_strng = "CITY":@VM:">=S":@FM: "CITY" : @VM : "<T" : @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

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:

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).

Example 1 (Parser)

Subroutine Parser(search_val, start_value, user_index_flag,|comp_mod)
/* This subroutine establishes three independent subroutines, 
each with a different purpose within the user index facility of Btree.Extract. */
 
/* This code is an example of a user index parser routine to intercept Soundex lookups 
(it looks for values starting with "$"). */
Declare Subroutine Soundex   ;* code supplied below
 
Equate TRUE  To 1
Equate TRIGGER To "$"
Equate COMP_MOD To "COMPARE"
 
/* The following code examines the index lookup value and determines whether it is a Soundex lookup. 
If not, the search value is passed through to Btree.Extract as normal. */
If search_val[1,1] = TRIGGER Then
       search_val[1,1] = ""  ;* delete the trigger character
       Soundex(search_val)     ;* convert to Soundex value
       user_index_flag = TRUE;* use custom compare in ascending
   start_value = search_val[1,1];* start at first letter
       COMP_MOD = COMP_MOD ;      * specify the compare module
End
Return

Example 2 (Compare)

Subroutine COMPARE(candidate, search_val, flag)
 
Declare Subroutine Soundex
 
Equate HIT_TRUE$ To 1
Equate HIT_FALSE$ To 0
Equate QUIT_SEARCH$ To 2
 
If candidate[1,1] GT search_val[1,1] Then
  flag = QUIT_SEARCH$  ;* end search if first char not same
End Else
  Soundex(candidate)    ;* convert CANDIDATE to Soundex
  If search_val Eq CANDIDATE Then
    flag = HIT_TRUE$
   End Else
    flag = HIT_FALSE$
  End
End
Return

Example 3 (Soundex)

Subroutine Soundex(soundex_value)
 
/* This is code for a subroutine that returns the Soundex equivalent of a single word. 
First, establish the numeric code equivalents for all letters of the alphabet-vowels, 
plus 'w', 'y', and 'h' are ignored. */
Equate SOUND.CODES To "01230120022455012623010202"
Equate PUNCTUATION To ".,/`';][-=<>?:~}{+_)(*&^%$#@!\|":CHAR(34)
Equate NUMERALS To "1234567890"
Equate NULL$   To ""
 
/* Be sure there is only one word, no punctuation and no lower case or numeric characters. */
text = Trim(soundex_value)
text = Field(text, " ", 1)
Convert PUNCTUATION To null In text
Convert NUMERALS To null In text
Convert @LOWER.CASE To @UPPER.CASE In text
 
/* In accordance with the Soundex algorithm, start with the 2nd character. */
first_char  = text[1,1]
text     = text[2,999]
text_length  = Len(text)
soundex_value = first_char
previous_char = NULL$
 
FOR loop_count=1 To text_length While Len(soundex_value) < 4
* strip off next character
    next_char = text[loop_count, 1]
    If next_char NE previous_char Then
      Convert @UPPER.CASE To SOUND_CODES In next_char
      If next_char NE 0 Then
        soundex_value := next_char
        previous_char = next_char
      End
    End
Next loop_count
 
/* Format is four characters in length, zero-padded at right if necessary. */
soundex_value = Fmt(soundex_value, "L(0)#4")
 
Return