Introduction

---

Searching and sorting tables and query result sets is accomplished through several methods of the TDBISAMTable and TDBISAMQuery components. The basic searching methods for tables (not query result sets) include the FindKey, FindNearest, SetKey, EditKey, GotoKey, and GotoNearest methods. The KeyFieldCount property is used with the SetKey and EditKey methods to control searching using the GotoKey and GotoNearest methods. The extended searching methods that do not necessarily rely upon an index and can be used with both tables and query result sets include the Locate, FindFirst, FindLast, FindNext, and FindPrior methods. The basic sorting methods for tables include the IndexName and IndexFieldNames properties.

Changing the Sort Order

---

You may use the IndexName and IndexFieldNames properties to set the current index order, and in effect, sort the current table based upon the index definition for the selected index order.

The IndexName property is used to set the name of the current index. For primary indexes, this property should always be set to blank (""). For secondary indexes, this property should be set to the name of the secondary index that you wish to use as the current index order. The following example shows how you would set the current index order for a table to a secondary index called "CustomerName":

begin
   with MyTable do
      begin
      IndexName:='CustomerName';
      { do something }
      end;
end;

If you attempt to set the IndexName property to a non-existent index an EDBISAMEngineError exception will be raised with the appropriate error code. The error code given for an invalid index name is 10022 and is defined as DBISAM_INVALIDINDEXNAME in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

The IndexFieldNames property is used to set the current index order by specifying the field names of the desired index instead of the index name. Multiple field names should be separated with a semicolon. Using the IndexFieldNames property is desirable in cases where you are trying to set the current index order based upon a known set of fields and do not have any knowledge of the index names available. The IndexFieldNames property will attempt to match the given number of fields with the same number of beginning fields in any of the available primary or secondary indexes. The following example shows how you would set the current index order to a secondary index called "CustomerName" that consists of the CustomerName field and the CustomerNo field:

begin
   with MyTable do
      begin
      IndexFieldNames:='CustomerName;CustomerNo';
      { do something }
      end;
end;

Searching Using an Index

---

The FindKey method accepts an array of search values to use in order to perform an exact search for a given record using the active index. The return value of the FindKey method indicates whether the search was successful. If the search was successful then the record pointer is moved to the desired record, whereas if the search was not successful then the record pointer stays at its current position. The search values must correspond to the fields that make up the active index or the search will not work properly. However, FindKey does not require that you fill in all of the field values for all of the fields in the active index, rather only that you fill in the field values from left to right. The following example shows how to perform a search on the primary index comprised of the CustomerNo field:

begin
   with MyTable do
      begin
      { Set to the primary index }
      IndexName:='';
      { Search for customer 100 }
      if FindKey([100]) then
         { Record was found, now do something }
      else
         ShowMessage('Record was not found');
      end;
end;

The FindNearest method accepts an array of search values to use in order to perform a near search for a given record using the active index. If the search was successful then the record pointer is moved to the desired record, whereas if the search was not successful then the record pointer is moved to the next record that most closely matches the current search values. If there are no records that are greater than the search values then the record pointer will be positioned at the end of the table. The search values must correspond to the fields that make up the active index or the search will not work properly. However, FindNearest does not require that you fill in all of the field values for all of the fields in the active index, rather only that you fill in the field values from left to right. The following example shows how to perform a near search on the primary index comprised of the CustomerNo field:

begin
   with MyTable do
      begin
      { Set to the primary index }
      IndexName:='';
      { Search for customer 100 or closest }
      FindNearest([100]);
      end;
end;

The SetKey and EditKey methods are used in conjunction with the GotoKey and GotoNearest methods to perform searching using field assignments instead of an array of field values. The SetKey method begins the search process by putting the TDBISAMTable component into the dsSetKey state and clearing all field values. You can examine the state of the table using the State property. The application must then assign values to the desired fields and call the GotoKey or GotoNearest method to perform the actual search. The GotoNearest method may be used if you wish to perform a near search instead of an exact search. The EditKey method extends or continues the current search process by putting the TDBISAMTable component into the dsSetKey state but not clearing any field values. This allows you to change only one field without being forced to re-enter all field values needed for the search. The KeyFieldCount property controls how many fields, based upon the current index, are to be used in the actual search. By default the KeyFieldCount property is set to the number of fields for the active index. The following example shows how to perform an exact search using the SetKey and GotoKey methods and KeyFieldCount property. The active index is a secondary index called "CustomerName" comprised of the CustomerName field and the CustomerNo field:

begin
   with MyTable do
      begin
      { Set to the CustomerName secondary index }
      IndexName:='CustomerName';
      { Search for the customer with the
        name 'The Hardware Store' }
      SetKey;
      FieldByName('CustomerName').AsString:='The Hardware Store';
      { This causes the search to only look at the first field
        in the current index when searching }
      KeyFieldCount:=1;
      if GotoKey then
         { Record was found, now do something }
      else
         ShowMessage('Record was not found');
      end;
end;

The following example shows how to perform a near search using the SetKey and GotoNearest methods, and KeyFieldCount property. The active index is a secondary index called "CustomerName" comprised of the CustomerName field and the CustomerNo field:

begin
   with MyTable do
      begin
      { Set to the CustomerName secondary index }
      IndexName:='CustomerName';
      { Search for the customer with the
        name 'The Hardware Store' }
      SetKey;
      FieldByName('CustomerName').AsString:='The Hardware Store';
      { This causes the search to only look at the first field
        in the current index when searching }
      KeyFieldCount:=1;
      GotoNearest;
      end;
end;

Searching Without a Specific Index Order Set

---

The Locate method is used to locate a record independent of the active index order or of any indexes at all. This is why it can be used with query result sets in addition to tables. The Locate method will attempt to use the active index for searching, but if the current search fields do not match the active index then the Locate method will attempt to use another available index. Indexes are selected based upon the options passed to the Locate method in conjunction with the field names that you wish to search upon. The index fields are checked from left to right, and if a primary or secondary index is found that matches the search fields from left to right and satisfies the options desired for the search it will be used to perform the search. Finally, if no indexes can be found that can be used for the search, a filter will be used to execute the search instead. This is usually a sub-optimal solution and can take a bit of time since the filter will be completely un-optimized and will be forced to scan every record for the desired field values.

The Locate method accepts a list of field names as its first argument. Multiple field names should be separated with a semicolon. These are the fields you wish to search on. The second argument to the Locate method is an array of field values that should correspond to the field names passed in the first argument. The third and final argument is a set of options for the Locate method. These options control how the search is performed and how indexes are selected in order to perform the search. The return value of the Locate method indicates whether the current search was successful. If the search was successful then the record pointer is moved to the desired record, whereas if the search was not successful then the record pointer stays at its current position.

The following example shows how to use the Locate method to find a record where the CustomerName field is equal to "The Hardware Store":

begin
   with MyTable do
      begin
      { Search for the customer with the
        name "The Hardware Store" }
      if Locate('CustomerName',['The Hardware Store'],[]) then
         { Record was found, now do something }
      else
         ShowMessage('Record was not found');
      end;
end;

The following example shows how to use the Locate method to find a record where the CustomerName field is equal to "The Hardware Store", but this time the search will be case-insensitive:

begin
   with MyTable do
      begin
      { Search for the customer with the
        name "The Hardware Store" }
      if Locate('CustomerName',['The Hardware Store'],
                [loCaseInsensitive]) then
         { Record was found, now do something }
      else
         ShowMessage('Record was not found');
      end;
end;

The FindFirst, FindLast, FindNext, and FindPrior methods all rely on the Filter and FilterOptions properties to do their work. These methods are the most flexible for searching and can be used with both tables and query result sets, but there are some important caveats. To get acceptable performance from these methods you must make sure that the filter expression being used for the Filter property is optimized or at least partially-optimized. If the filter expression is un-optimized it will take a significantly greater amount of time to complete every call to any of the FindFirst, FindLast, FindNext, or FindPrior methods unless the table or query result set being searched only has a small number of records. Please see the Filter Optimization topic for more information. Also, because the Filter property is being used for these methods, you cannot use a different filter expression in combination with these methods. However, you can set the Filtered property to True and show only the filtered records if you so desire. Finally, the FilterOptions property controls how the filtering is performed during the searching, so you should make sure that these options are set properly. The following example shows how to use the Filter property and FindFirst and FindNext methods to find matching records and navigate through them in a table:

begin
   with MyTable do
      begin
      { Search for the first customer with the
        name "The Hardware Store" }
      Filter:='CustomerName='+QuotedStr('The Hardware Store');
      { We want the search to be case-insensitive }
      FilterOptions:=[foCaseInsensitive];
      if FindFirst then
         begin
         { Record was found, now search through
           the rest of the matching records }
         while FindNext do
            { Do something here }
         end
      else
         ShowMessage('Record was not found');
      end;
end;