Searching and sorting tables, views, and query result sets is accomplished through several methods of the TEDBTable, TEDBQuery, TEDBScript, and TEDBStoredProc components. The basic searching methods for tables (not views or query result sets) include the FindKey, FindNearest, SetKey, EditKey, GotoKey, and GotoNearest methods. The KeyColumnCount 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 TEDBTable 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. This property should be set to the name of the index that you wish to use as the current index order. Setting the IndexName property to blank ('') will cause the index order to reset to the default order for the table, which is usually the order defined by the primary key of the table, or the natural insertion order of the table if the table does not have a primary key defined. The following example shows how you would set the current index order for a table to an index called "CustomerName":

{
   MyTable->IndexName="CustomerName";
   // do something
}

If you attempt to set the IndexName property to a non-existent index an EEDBError exception will be raised with the error code 401 (EDB_ERROR_NOTFOUND).

The IndexFieldNames property is used to set the current index order by specifying the column names of the desired index instead of the index name. Multiple column 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 columns and do not have any knowledge of the index names available. The IndexFieldNames property will attempt to match the given number of columns with the same number of beginning columns, in left-to-right order, in any of the available indexes for the table. The following example shows how you would set the current index order to an index called "CustomerName" that consists of the CustomerName column and the CustomerNo column:

{
   MyTable->IndexFieldNames="CustomerName;CustomerNo";
   // do something
}

If ElevateDB cannot find any indexes that match the desired column names an EDatabaseError exception will be raised instead of an EEDBError exception. If you are using this method of setting the current index order you should also be prepared to trap for this exception and deal with it appropriately.

Searching Using an Index

---

The TEDBTable FindKey method accepts an array of search values to use in order to perform an exact search for a given row using the active index. The return value of the FindKey method indicates whether the search was successful. If the search was successful then the row pointer is moved to the desired row, whereas if the search was not successful then the row pointer stays at its current position. The search values must correspond to the columns 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 column values for all of the columns in the active index, rather only that you fill in the column values from left to right. The following example shows how to perform a search on the index used to enforce the primary key and comprised of the CustomerNo column:

{
   // Set to the natural order, which in this case
   // is the primary key
   MyTable->IndexName="";
   // Search for customer 100
   // With C++Builder, the column values must be passed
   // as either a TVarRec (for single values) or an
   // ARRAYOFCONST  
   TVarRec SearchValue=(100);
   if (MyTable->FindKey(&SearchValue))
      {
      // Row was found, now do something
      }
   else
      {
      ShowMessage("Row was not found");
      }
}

The FindNearest method accepts an array of search values to use in order to perform a near search for a given row using the active index. If the search was successful then the row pointer is moved to the desired row, whereas if the search was not successful then the row pointer is moved to the next row that most closely matches the current search values. If there are no rows that are greater than the search values then the row pointer will be positioned at the end of the table. The search values must correspond to the columns 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 column values for all of the columns in the active index, rather only that you fill in the column values from left to right. The following example shows how to perform a near search on the index used to enforce the primary key and comprised of the CustomerNo column:

{
   // Set to the natural order, which in this case
   // is the primary key
   MyTable->IndexName="";
   // Search for customer 100 or nearest
   // With C++Builder, the column values must be passed
   // as either a TVarRec (for single values) or an
   // ARRAYOFCONST  
   TVarRec SearchValue=(100);
   MyTable->FindNearest(&SearchValue);
}

The SetKey and EditKey methods are used in conjunction with the GotoKey and GotoNearest methods to perform searching using column assignments instead of an array of column values. The SetKey method begins the search process by putting the TEDBTable component into the dsSetKey state and clearing all column values. You can examine the state of the table using the State property. The application must then assign values to the desired columns 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 TEDBTable component into the dsSetKey state but not clearing any column values. This allows you to change only one column without being forced to re-enter all column values needed for the search. The KeyColumnCount property controls how many columns, based upon the current index, are to be used in the actual search. By default the KeyColumnCount property is set to the number of columns for the active index. The following example shows how to perform an exact search using the SetKey and GotoKey methods and KeyColumnCount property. The active index is an index called "CustomerName" comprised of the CustomerName column and the CustomerNo column:

{
   // Set to the CustomerName index
   MyTable->IndexName="CustomerName";
   // Search for the customer with the
   // name "The Hardware Store"
   MyTable->SetKey();
   MyTable->ColumnByName("CustomerName")->AsString="The Hardware Store";
   // This causes the search to only look at the first column
   // in the current index when searching
   MyTable->KeyColumnCount=1;
   if (MyTable->GotoKey())
      {
      // Row was found, now do something
      }
   else
      {
      ShowMessage("Row was not found");
      }
}

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

{
   // Set to the CustomerName index
   MyTable->IndexName="CustomerName";
   // Search for the customer with the
   // name "The Hardware Store"
   MyTable->SetKey();
   MyTable->ColumnByName("CustomerName")->AsString="The Hardware Store";
   // This causes the search to only look at the first column
   // in the current index when searching
   MyTable->KeyColumnCount=1;
   MyTable->GotoNearest();
}

Searching Without a Specific Index Order Set

---

The Locate method of the TEDBTable, TEDBQuery, and TEDBStoredProc components is used to locate a row 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 columns 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 column names that you wish to search upon. The index columns are checked from left to right, and if an index is found that matches the search columns 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 table scan will be used to execute the search instead. This is usually a sub-optimal solution and can take a bit of time since the table scan will read every row in the table in order to examine the desired column values. The Locate method uses the following criteria when determining whether to use an index or not for the search:

1) ElevateDB matches the index columns to the search columns in left-to-right order.

2) ElevateDB can use an index for the search irrespective of the ascending or descending status of a given column in the index.

3) ElevateDB can only use an index for the search if the first column(s) in the index in left-to-right order match(es) both the column(s) being searched upon and the setting of the loCaseInsensitive flag in the Locate options. If the loCaseInsensitive flag is not specified, then the index column in the index (being examined for possible use in the search) must be assigned a case-sensitive collation. If the loCaseInsensitive flag is specified, then the index column in the index must be assigned a case-insensitive collation.

For example, suppose that you have a Customer table with a State column that was defined with the ANSI_CI (ANSI collation, case-insensitive). An index was created on the State column using the following CREATE INDEX statement:

CREATE INDEX State ON Customer (State)

To execute an optimized search for any rows where the State column contains 'FL', one would use the following code:

{
   Variant SearchValues[1];
   SearchValues[0]=Variant("FL");
   // Search for the customer with the
   // state "FL"
   if (MyTable->Locate("State",VarArrayOf(SearchValues,2),
                       TLocateOptions() << loCaseInsensitive))
      {
      // Row was found, now do something
      }
   else
      {
      ShowMessage("Row was not found");
      }
}

However, suppose that the State column was defined with simply the ANSI collation (case-sensitive) and the index was created using the following CREATE INDEX statement:

CREATE INDEX State ON Customer
(State)

In order to allow ElevateDB to use this index to optimize any searches on the State column, you must now not include the loCaseInsensitive flag:

{
   Variant SearchValues[1];
   SearchValues[0]=Variant("FL");
   // Search for the customer with the
   // state "FL"
   if (MyTable->Locate("State",VarArrayOf(SearchValues,2),
                       TLocateOptions()))
      {
      // Row was found, now do something
      }
   else
      {
      ShowMessage("Row was not found");
      }
}

Please see the Internationalization topic for more information on collations.

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 rows. Please see the Setting Filters on Tables and Query Result Sets 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 rows 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 rows and navigate through them in a table:

{
   // Search for the first customer with the
   // name "The Hardware Store"
   MyTable->Filter="CustomerName="+QuotedStr("The Hardware Store");
   // We want the search to be case-insensitive
   TFilterOptions FilterOptions;
   FilterOptions->Clear();
   FilterOptions << foCaseInsensitive;
   MyTable->FilterOptions=FilterOptions;
   if (MyTable->FindFirst())
      {
      // Row was found, now search through
      // the rest of the matching rows
      while (FindNext())
         {
         // Do something here
         }
      }
   else
      {
      ShowMessage("Row was not found");
      }
}