Database Searching Via Keyed Fields

Various levels of searching are available in Sage-ST ^TM . They range from single key searches of one relation to multiple key/multiple relation searching using procedures available in the QueryLib package. A more detailed description of the procedures and arguments for complex searches may be found in the discussion of this package in the Sage-ST ^TM Reference Manual.

A search using a keyed field of a relation involves locating the required key in the index for that key and reading the associated record into the memory record if desired. The FindRecord procedures are used to locate the specified key in the index. They are useful for determining the presence of data in a relation without the overhead of reading the entire record into memory. The ReadRecord procedures are used both to find the required key in the index and to read the associated record into memory. If a field is not keyed and a search is requested against that field by either a Read- or a FindRecord, a sequential search through the database records themselves (i.e., the DAT file) will be performed. Since this may be rather slow, those fields that will be frequently searched against should usually be keyed.

A successful search on a single keyed field causes a pointer to be established in the index. Thus, if Sage-ST ^TM is directed to read the employee whose last name is "Smith", the last name index in the index file (IDX file) for the relation will be searched. If the search is successful, a pointer will be established pointing to that record. Using the data file address associated with the index, the proper record will be read from the data file (DAT file) into memory (see figure 1). The command can then be given to read the next record based on the same keyed field. If another record is available, the pointer will be reestablished and the record read into memory (see figure 2). If at the end of the index, a command to read the next record will send the pointer off to the nether world and an error will be returned. Searching using another keyed field index of the same relation does not destroy the pointer position of the current index (see figure 3).

Figure 1: Reading the Record From the Data File

Figure 2: Reading the Next Record in the Data File

Figure 3: Searching the Index File Using Another Keyed Field

Using keyed fields, you may search the database with logical operators, positional operators, and/or logical/positional operators. Searching by Logical Operators. Many ReadRecord and FindRecord calls can use a logical operator as their third parameter to compare the key of the record sought with either the current contents of the memory record or a fourth parameter supplied by the programmer. If no record fulfilling the requirements is found, a SageError (usually 112) will be returned. NOTE: The position of the pointer in the index is undefined after an unsuccessful Read or Find operation. The following logical operators are supported by Sage-ST ^TM :

        eq  -  equal to
        ge  -  greater than or equal to
        le  -  less than or equal to
        gt  -  greater than
        lt  -  less than
        ne  -  not equal to

Searching by Positional Operators. Some ReadRecord and FindRecord calls can use a positional operator as their third parameter. To use the locator commands (Sage.Next, Sage.Previous, Sage.NextEq, or Sage.PreviousEq) you must first be positioned on a record. No comparisons are done. If no record fulfilling the requirements is found, a SageError (usually 112) will be returned. NOTE: The position of the pointer in the index is undefined after an unsuccessful Read or Find operation. The following positional operators are supported by Sage-ST ^TM :

        first    -  positions on the first record
        next    -  positions on the next record
        previous  -  positions on the previous record
        last    -  positions on the last record
        current  -  reads the record currently pointed to in the IDX file.

Searching by Logical/Positional Operators. Many ReadRecord and FindRecord calls can use a logical/positional operator as their third parameter. The pointer must have been set using either a positional or a logical Read or Find so that there is a point of reference for the next/previous portion of the operator. If no record fulfilling the requirements is found, a SageError (usually 112) will be returned. NOTE: The position of the pointer in the index is undefined after an unsuccessful Read or Find operation. The following logical/positional operators are supported by Sage-ST ^TM :

        nexteq  -  the next record equal to
        preveq  -  the previous record equal to

Go Back To Sage-ST TABLE OF CONTENTS