A very common task for a database application is to search for a specific record based on some criteria. In Delphi, ADOExpress components implement record searching methods analogous to those found in the BDE approach. This chapter will walk you through various methods of data seeking and locating while developing ADO based Delphi database applications.
Note: the rest of this chapter deals with the aboutdelphi.mdb MS Access database that was introduced in the first chapter of this course. To use the code examples presented in this chapter, set up the data form with the core components (data-access and data-aware) as described in the previous chapters. The following code examples will assume that you are working with the ADOTable component pointing to the Applications table in our database.
When you think of it, a searching algorithm could look like: start at the top of the table, examine the field in each row - to see if it matches the criteria, stop the loop on the selected record or at the bottom row - whichever comes first. Hopefully, Delphi hides those *steps* from us. There are several ways to locate a record in a dataset retrieved by an ADODataset (Table or Query) component.
Locate
This generic search method sets the current record to be the first row matching a specified set of search criteria. By using the Locate method we can look for values of one or more fields, passed in a variant array. The next code puts the Locate method to work finding the first record that contains the string 'Zoom' in the Name field. If the call to Locate returns True - the record is found and is set to be the current one.
Lookup
Lookup does not move the cursor to the matching row, it only returns values from it. Lookup returns a variant array containing the values from the fields specified in a semicolon-delimited list of field names whose values should be returned from the matching row. If there are no matching records, Lookup returns a Null variant.
The following code fills in a LookupRes variant array
var LookupRes: Variant; LookupRes:= ADOTable1.Lookup ('Name', 'Zoom', 'Author; Description'); if not VarIsNull(LookupRes) then ShowMessage(VarToStr(LookupRes[0])) //author nameOne advantage of the Locate and Lookup methods is that they don't require the table to be indexed. However, the Locate function will use the fastest method available to search the table; if a table is indexed, Locate will use the index.
Indexing
An index helps find and sort records faster. You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value. In most cases you'll want to index fields you search/resort frequently. For example, if you search for specific application type in a Type field, you can create an index for this field to speed up the search for a specific type.
The primary key of a table is automatically indexed, and you can't index a field whose data type is OLE Object. Note that if many of the values in the field are the same, the index may not significantly speed up data retrieval. The main drawbacks are that indexes consume additional disk space, and inserting, deleting and updating of data takes longer takes longer on indexed columns than on non indexed columns.
When working with a Table component and the BDE (not ADO) Delhi provides us with a number of functions that will search for values in a database table. Some of these are Goto, GoToKey, GoToNearest, Find, FindKey, Find Nearest, etc. For a complete reference see Delphi's help, topic: Searching for records based on indexed fields. The ADO approach does not support those methods, instead it introduces a Seek method.
The ADO datasets Seek method uses an index when performing a search. If you don't specify an index and you are working with an Access database, the database engine will use the primary key index. Seek is used to find a record with a specified value (or values) in the field (or fields) on which the current index is based. If Seek does not find the desired row, no error occurs, and the row is positioned at the end of the dataset. Seek returns a boolean value reflecting the success of the search: True if a record was found or False if no matching record was found.
The GetIndexNames method of a TADOTable component retrieves a list (for example: items of a combo box) of available indexes for a table.
ADOTable1.GetIndexNames(ComboBox1.Items);The same list is available at design-time in the IndexName property of a TADOTable component. The IndexFieldNames property can be used as an alternative method of specifying the index to use for a table. In IndexFieldNames, specify the name of each field to use as an index for a table.
The Seek method has the following declaration:
function Seek(const KeyValues: Variant; SeekOption: TSeekOption = soFirstEQ): Boolean;
· KeyValues is an array of Variant values. An index consists of one or more columns and the array contains a value to compare against each corresponding column.
· SeekOption specifies the type of comparison to be made between the columns of the index and the corresponding KeyValues.
SeekOption Meaning
soFirstEQ Record pointer positioned at the first matching record, if one is found, or at the end of the dataset if one is not found
soLastEQ Record pointer positioned at the last matching record, if one is found, or at the end of the dataset if one is not found.
soAfterEQ Record pointer positioned at matching record, if found, or just after where that matching record would have been found.
soAfter Record pointer positioned just after where a matching record would have been found.
soBeforeEQ Record pointer positioned at matching record, if found, or just before where that matching record would have been found.
soBefore Record pointer positioned just before where a matching record would have been found.
Note 1: the Seek method is supported only with server-side cursors. Seek is not supported when the dataset's CursorLocation property value is clUseClient. Use the Supports method to determine whether the underlying provider supports Seek.
Note 2: when you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.
Note 3: you cannot use the Seek method on TADOQuery component.
To determine whether a matching record was found, we use the BOF or EOF property (depending on the search direction). The next code uses the index specified in the ComboBox to look for a value typed in the Edit1 edit box.
var strIndex: string; strIndex:= ComboBox1.Text; //from the code above if ADOTable1.Supports(coSeek) then begin with ADOTable1 do begin Close; IndexName:= strIndex; CursorLocation:= clUseServer; Open; Seek (Edit1.Text, soFirstEQ); end; if ADOTable1.EOF then ShowMessage ('Record value NOT found'); End
IV. CONCLUSION
There are many types of database management systems. But they are all based on a common set of basic concepts. As a result, we have a certain class of system concepts, methods and methods of DBMS as a whole can. Thus, the object of research within Microsoft Office paektiniñ use Microsoft Access depends on DBMS.
The main elements of any database tables. The very simple database consists of at least one table. Two-dimensional structure of columns and rows in the table. They just named the database field and the record.
Field Data Group kajdoy opredelyaet svoystvo data, from zapïsannıx from Hatch sootvetstvwyuşçïx polyax. Basic Properties Table bazı data, fields: type the name of the Polish, Polish, Size, Polish, Size, Polish, mask, etc. Sootvetstvïï tïpamï field data have mogwt raznıe then.