SQLMemTable: Embedded BDE Alternative
In-Memory SQL Database for Delphi / C++Builder
Searching records on tables is accomplished through several methods of the TSQLMemTable component. The basic search methods are FindFirst, FindLast, FindNext, and FindPrior.
You may also use Locate and Lookup methods to find one matched record.
All search operations use current index order.
Search also performs in a master/detail and filtered tables.
Searching by Find methods.
Searching records with an SQLMemTable component by Find methods is a three-step process:
|2.||Set filter options for string-based filter tests, if necessary.
|3.||Call some of the following navigational methods: FindFirst(), FindLast(), FindNext(), and FindPrior().
All these navigational methods position the record pointer to a matching record (if any), make it current, and return True. If a matching record is not found, the record pointer position is unchanged (remains as it is), and these methods return False.You can check the status of the Found property to wrap these calls, and only take action when Found is True. For example, if the record pointer is already on the last matching record in the table, and you call FindNext, the method returns False, and the current record remains unchanged.
Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":
SearchOptions := [loPartialKey];
LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.', SearchOptions);
If Locate finds a match, the first record containing the match becomes the current record. Locate returns True if it finds a matching record, False if it does not. If a search fails, the current record does not change.
The real power of Locate comes into play when you want to search on multiple columns and specify multiple values to search for. Search values are variants, which enables you to specify different data types in your search criteria. To specify multiple columns in a search string, separate individual items in the string with semicolons.
Because search values are variants, if you pass multiple values, you must either pass a variant array type as an argument (for example, the return values from the Lookup method), or you must construct the variant array on the fly using the VarArrayOf function. The following code illustrates a search on multiple columns using multiple search values and partial-key matching:
with CustTable do
Locate('Company;Contact;Phone', VarArrayOf(['Sight Diver','P']), loPartialKey);
Locate uses the fastest possible method to locate the matching record.
Lookup searches for the first row that matches specified search criteria. If it finds a matching row, it forces the recalculation of any calculated fields and lookup fields associated with the dataset, then returns one or more fields from the matching row. Lookup does not move the cursor to the matching row; it only returns values from it.
In its simplest form, you pass Lookup the name of field to search, the field value to match, and the field or fields to return. For example, the following code looks for the first record in the CustTable where the value of the Company field is "Professional Divers, Ltd.", and returns the company name, a contact person, and a phone number for the company:
with CustTable do
LookupResults := Lookup('Company', 'Professional Divers, Ltd.', 'Company; Contact; Phone');
Lookup returns values for the specified fields from the first matching record it finds. Values are returned as variants. If more than one return value is requested, Lookup returns a variant array. If there are no matching records, Lookup returns a Null variant. For more information about variant arrays, see the online help.
The real power of Lookup comes into play when you want to search on multiple columns and specify multiple values to search for. To specify strings containing multiple columns or result fields, separate individual fields in the string items with semi-colons.
Because search values are variants, if you pass multiple values, you must either pass a variant array type as an argument (for example, the return values from the Lookup method), or you must construct the variant array on the fly using the VarArrayOf function. The following code illustrates a lookup search on multiple columns:
with CustTable do
LookupResults := Lookup('Company; City', VarArrayOf(['Sight Diver', 'Christiansted']), 'Company; Addr1; Addr2; State; Zip');
Lookup also uses the fastest possible method to locate the matching record.