In Delphi, the fundamental unit for accessing data is the dataset family of objects. Your application uses datasets for all database access. Generally, a dataset object represents a specific table belonging to a database, or it represents a query or stored procedure that accesses a database.
All dataset objects that you will use in your database applications descend from the virtualized dataset object, TDataSet, and they inherit data fields, properties, events, and methods from TDataSet. This chapter describes the functionality of TDataSet that is inherited by the dataset objects you will use in your database applications. You need to understand this shared functionality to use any dataset object.
Figure 18.1 illustrates the hierarchical relationship of all the dataset components:
TDataSet is the ancestor for all dataset objects you use in your applications. It defines a set of data fields, properties, events, and methods shared by all dataset objects. TDataSet is a virtualized dataset, meaning that many of its properties and methods are virtual or abstract. A virtual method is a function or procedure declaration where the implementation of that method can be (and usually is) overridden in descendant objects. An abstract method is a function or procedure declaration without an actual implementation. The declaration is a prototype that describes the method (and its parameters and return type, if any) that must be implemented in all descendant dataset objects, but that might be implemented differently by each of them.
Because TDataSet contains abstract methods, you cannot use it directly in an application without generating a runtime error. Instead, you either create instances of TDataSet's descendants, such as TTable, TQuery, TStoredProc, and TClientDataSet, and use them in your application, or you derive your own dataset object from TDataSet or its descendants and write implementations for all its abstract methods.
Nevertheless, TDataSet defines much that is common to all dataset objects. For example, TDataSet defines the basic structure of all datasets: an array of TField components that correspond to actual columns in one or more database tables, lookup fields provided by your application, or calculated fields provided by your application. For more information about TField components, see "Working with field components."
The following topics are discussed in this chapter:
To understand the concepts common to all dataset objects, and to prepare for developing your own custom dataset objects that do not rely on either the Borland Database Engine (BDE) or ActiveX Data Objects (ADO), read this chapter.
To develop traditional, two-tier client/server database applications using the Borland Database Engine (BDE), see "Overview of BDE-enablement" discussed later in this chapter. That section introduces TBDEDataSet and TDBDataSet, and focuses on the shared features of TQuery, TStoredProc, and TTable, the dataset components used most commonly in all database applications.
With some versions of Delphi, you can develop multi-tier database applications using distributed datasets. To learn about working with client datasets in multi-tiered applications, see "Creating multi-tiered applications." That chapter discusses how to use TClientDataSet and how to connect the client to an application server.
To read or write data in a table or through a query, an application must first open a dataset. You can open a dataset in two ways,
CustTable.Active := True;
CustQuery.Open;
You can close a dataset in two ways,
CustQuery.Active := False;
CustTable.Close;
You may need to close a dataset when you want to change certain of its properties, such as TableName on a TTable component. At runtime, you may also want to close a dataset for other reasons specific to your application.
The state--or mode--of a dataset determines what can be done to its data. For example, when a dataset is closed, its state is dsInactive, meaning that nothing can be done to its data. At runtime, you can examine a dataset's read-only State property to determine its current state. The following table summarizes possible values for the State property and what they mean:
When an application opens a dataset, it appears by default in dsBrowse mode. The state of a dataset changes as an application processes data. An open dataset changes from one state to another based on either the
To put a dataset into dsBrowse, dsEdit, dsInsert, or dsSetKey states, call the method corresponding to the name of the state. For example, the following code puts CustTable into dsInsert state, accepts user input for a new record, and writes the new record to the database:
CustTable.Insert; { Your application explicitly sets dataset state to Insert }
AddressPromptDialog.ShowModal;
if AddressPromptDialog.ModalResult := mrOK then
CustTable.Post; { Delphi sets dataset state to Browse on successful completion }
else
CustTable.Cancel; {Delphi sets dataset state to Browse on cancel }
This example also illustrates that the state of a dataset automatically changes to dsBrowse when
Some states cannot be set directly. For example, to put a dataset into dsInactive state, set its Active property to False, or call the Close method for the dataset. The following statements are equivalent:
CustTable.Active := False; CustTable.Close;
The remaining states (dsCalcFields, dsCurValue, dsNewValue, dsOldValue, and dsFilter) cannot be set by your application. Instead, the state of the dataset changes automatically to these values as necessary. For example, dsCalcFields is set when a dataset's OnCalcFields event is called. When the OnCalcFields event finishes, the dataset is restored to its previous state.
Note: Whenever a dataset's state changes, the OnStateChange event is called for any data source components associated with the dataset. For more information about data source components and OnStateChange, see "Using data sources".
The following sections provide overviews of each state, how and when states are set, how states relate to one another, and where to go for related information, if applicable.
A dataset is inactive when it is closed. You cannot access records in a closed dataset. At design time, a dataset is closed until you set its Active property to True. At runtime, a dataset is initially closed until an application opens it by calling the Open method, or by setting the Active property to True.
When you open an inactive dataset, its state automatically changes to the dsBrowse state. The following diagram illustrates the relationship between these states and the methods that set them.
To make a dataset inactive, call its Close method. You can write BeforeClose and AfterClose event handlers that respond to the Close method for a dataset. For example, if a dataset is in dsEdit or dsInsert modes when an application calls Close, you should prompt the user to post pending changes or cancel them before closing the dataset. The following code illustrates such a handler:
procedure CustTable.VerifyBeforeClose(DataSet: TDataSet)
begin
if (CustTable.State = dsEdit) or (CustTable.State = dsInsert) then
begin
if MessageDlg('Post changes before closing?', mtConfirmation, mbYesNo, 0) = mrYes then
CustTable.Post;
else
CustTable.Cancel;
end;
end;
To associate a procedure with the BeforeClose event for a dataset at design time:
When an application opens a dataset, the dataset automatically enters dsBrowse state. Browsing enables you to view records in a dataset, but you cannot edit records or insert new records. You mainly use dsBrowse to scroll from record to record in a dataset. For more information about scrolling from record to record, see "Navigating datasets".
From dsBrowse all other dataset states can be set. For example, calling the Insert or Append methods for a dataset changes its state from dsBrowse to dsInsert (note that other factors and dataset properties, such as CanModify, may prevent this change). Calling SetKey to search for records puts a dataset in dsSetKey mode. For more information about inserting and appending records in a dataset, see "Modifying data".
Two methods associated with all datasets can return a dataset to dsBrowse state. Cancel ends the current edit, insert, or search task, and always returns a dataset to dsBrowse state. Post attempts to write changes to the database, and if successful, also returns a dataset to dsBrowse state. If Post fails, the current state remains unchanged.
The following diagram illustrates the relationship of dsBrowse both to the other dataset modes you can set in your applications, and the methods that set those modes.
A dataset must be in dsEdit mode before an application can modify records. In your code you can use the Edit method to put a dataset into dsEdit mode if the read-only CanModify property for the dataset is True. CanModify is True if the database underlying a dataset permits read and write privileges.
On forms in your application, some data-aware controls can automatically put a dataset into dsEdit state if:
Important: For TTable components, if the ReadOnly property is True, CanModify is False, preventing editing of records. Similarly, for TQuery components, if the RequestLive property is False, CanModify is False.
Note: Even if a dataset is in dsEdit state, editing records may not succeed for SQL-based databases if your application user does not have proper SQL access privileges.
You can return a dataset from dsEdit state to dsBrowse state in code by calling the Cancel, Post, or Delete methods. Cancel discards edits to the current field or record. Post attempts to write a modified record to the dataset, and if it succeeds, returns the dataset to dsBrowse. If Post cannot write changes, the dataset remains in dsEdit state. Delete attempts to remove the current record from the dataset, and if it succeeds, returns the dataset to dsBrowse state. If Delete fails, the dataset remains in dsEdit state.
Data-aware controls for which editing is enabled automatically call Post when a user executes any action that changes the current record (such as moving to a different record in a grid) or that causes the control to lose focus (such as moving to a different control on the form).
For a complete discussion of editing fields and records in a dataset, see "Modifying data".
A dataset must be in dsInsert mode before an application can add new records. In your code you can use the Insert or Append methods to put a dataset into dsInsert mode if the read-only CanModify property for the dataset is True. CanModify is True if the database underlying a dataset permits read and write privileges.
On forms in your application, the data-aware grid and navigator controls can put a dataset into dsInsert state if
Important: For TTable components, if the ReadOnly property is True, CanModify is False, preventing editing of records. Similarly, for TQuery components, if the RequestLive property is False, CanModify is False.
Note: Even if a dataset is in dsInsert state, inserting records may not succeed for SQL-based databases if your application user does not have proper SQL access privileges.
You can return a dataset from dsInsert state to dsBrowse state in code by calling the Cancel, Post, or Delete methods. Delete and Cancel discard the new record. Post attempts to write the new record to the dataset, and if it succeeds, returns the dataset to dsBrowse. If Post cannot write the record, the dataset remains in dsInsert state.
Data-aware controls for which inserting is enabled automatically call Post when a user executes any action that changes the current record (such as moving to a different record in a grid).
For more discussion of inserting and appending records in a dataset, see "Modifying data".
You can search against any dataset using the Locate and Lookup methods of TDataSet. TTable components, however, provide an additional family of GotoKey and FindKey methods that enable you to search for records based on an index for the table. To use these methods on table components, the component must be in dsSetKey mode. dsSetKey mode applies only to TTable components. You put a dataset into dsSetKey mode with the SetKey method at runtime. The GotoKey, GotoNearest, FindKey, and FindNearest methods, which carry out searches, returns the dataset to dsBrowse state upon completion of the search. For more information about searching a table based on its index, see "Searching for records based on indexed fields".
You can temporarily view and edit a subset of data for any dataset by using filters. For more information about filters, see "Displaying and editing a subset of data using filters". TTable components also support an additional way to access a subset of available records, called ranges. To create and apply a range to a table, a table must be in dsSetKey mode. For more information about using ranges, see "Working with a subset of data".
Delphi puts a dataset into dsCalcFields mode whenever an application calls the dataset's OnCalcFields event handler. This state prevents modifications or additions to the records in a dataset except for the calculated fields the handler is designed to modify. The reason all other modifications are prevented is because OnCalcFields uses the values in other fields to derive values for calculated fields. Changes to those other fields might otherwise invalidate the values assigned to calculated fields.
When the OnCalcFields handler finishes, the dataset is returned to dsBrowse state.
For more information about creating calculated fields and OnCalcFields event handlers, see "Using OnCalcFields".
Delphi puts a dataset into dsFilter mode whenever an application calls the dataset's OnFilterRecord event handler. This state prevents modifications or additions to the records in a dataset during the filtering process so that the filter request is not invalidated. For more information about filtering, see "Displaying and editing a subset of data using filters".
When the OnFilterRecord handler finishes, the dataset is returned to dsBrowse state.
When performing cached update operations, Delphi may put the dataset into dsNewValue, dsOldValue, or dsCurValue states temporarily. These states indicate that the corresponding properties of a field component (NewValue, OldValue, and CurValue, respectively) are being accessed, usually in an OnUpdateError event handler. Your applications cannot see or set these states.
Each active dataset has a cursor, or pointer, to the current row in the dataset. The current row in a dataset is the one whose values can be manipulated by edit, insert, and delete methods, and the one whose field values currently show in single-field, data-aware controls on a form, such as TDBEdit, TDBLabel, and TDBMemo.
You can change the current row by moving the cursor to point at a different row. The following table lists methods you can use in application code to move to different records:
The data-aware, visual component TDBNavigator encapsulates these methods as buttons that users can click to move among records at runtime. For more information about the navigator component, see "Using data controls."
In addition to these methods, the following table describes two Boolean properties of datasets that provide useful information when iterating through the records in a dataset.
The First method moves the cursor to the first row in a dataset and sets the Bof property to True. If the cursor is already at the first row in the dataset, First does nothing.
For example, the following code moves to the first record in CustTable:
CustTable.First;
The Last method moves the cursor to the last row in a dataset and sets the Eof property to True. If the cursor is already at the last row in the dataset, Last does nothing.
The following code moves to the last record in CustTable:
CustTable.Last;
Note: While there may be programmatic reasons to move to the first or last rows in a dataset without user intervention, you should enable your users to navigate from record to record using the TDBNavigator component. The navigator component contains buttons that when active and visible enables a user to move to the first and last rows of an active dataset. The OnClick events for these buttons call the First and Last methods of the dataset. For more information about making effective use of the navigator component, see "Using data controls."
The Next method moves the cursor forward one row in the dataset and sets the Bof property to False if the dataset is not empty. If the cursor is already at the last row in the dataset when you call Next, nothing happens.
For example, the following code moves to the next record in CustTable:
CustTable.Next;
The Prior method moves the cursor back one row in the dataset, and sets Eof to False if the dataset is not empty. If the cursor is already at the first row in the dataset when you call Prior, Prior does nothing.
For example, the following code moves to the previous record in CustTable:
CustTable.Prior;
MoveBy enables you to specify how many rows forward or back to move the cursor in a dataset. Movement is relative to the current record at the time that MoveBy is called. MoveBy also sets the Bof and Eof properties for the dataset as appropriate.
This function takes an integer parameter, the number of records to move. Positive integers indicate a forward move and negative integers indicate a backward move.
MoveBy returns the number of rows it moves. If you attempt to move past the beginning or end of the dataset, the number of rows returned by MoveBy differs from the number of rows you requested to move. This is because MoveBy stops when it reaches the first or last record in the dataset.
The following code moves two records backward in CustTable:
CustTable.MoveBy(-2);
Note: If you use MoveBy in your application and you work in a multi-user database environment, keep in mind that datasets are fluid. A record that was five records back a moment ago may now be four, six, or even an unknown number of records back because several users may be simultaneously accessing the database and changing its data.
Two read-only, runtime properties, Eof (End-of-file) and Bof (Beginning-of-file), are useful for controlling dataset navigation, particularly when you want to iterate through all records in a dataset.
When Eof is True, it indicates that the cursor is unequivocally at the last row in a dataset. Eof is set to True when an application
Eof is set to False in all other cases; you should assume Eof is False unless one of the conditions above is met and you test the property directly.
Eof is commonly tested in a loop condition to control iterative processing of all records in a dataset. If you open a dataset containing records (or you call First) Eof is False. To iterate through the dataset a record at a time, create a loop that terminates when Eof is True. Inside the loop, call Next for each record in the dataset. Eof remains False until you call Next when the cursor is already on the last record.
The following code illustrates one way you might code a record-processing loop for a dataset called CustTable:
CustTable.DisableControls;
try
CustTable.First; { Go to first record, which sets EOF False }
while not CustTable.EOF do { Cycle until EOF is True }
begin
{ Process each record here }
...
CustTable.Next; { EOF False on success; EOF True when Next fails on last record }
end;
finally
CustTable.EnableControls;
end;
Tip: This example also demonstrates how to disable and enable data-aware visual controls tied to a dataset. If you disable visual controls during dataset iteration, it speeds processing because Delphi does not have to update the contents of the controls as the current record changes. After iteration is complete, controls should be enabled again to update them with values for the new current row. Note that enabling of the visual controls takes place in the finally clause of a try...finally statement. This guarantees that even if an exception terminates loop processing prematurely, controls are not left disabled.
When Bof is True, it indicates that the cursor is unequivocally at the first row in a dataset. Bof is set to True when an application
Bof is set to False in all other cases; you should assume Bof is False unless one of the conditions above is met and you test the property directly.
Like Eof, Bof can be in a loop condition to control iterative processing of records in a dataset. The following code illustrates one way you might code a record-processing loop for a dataset called CustTable:
CustTable.DisableControls; { Speed up processing; prevent screen flicker }
try
while not CustTable.BOF do { Cycle until BOF is True }
begin
{ Process each record here }
...
CustTable.Prior; { BOF False on success; BOF True when Prior fails on first record }
end;
finally
CustTable.EnableControls; { Display new current row in controls }
end;
In addition to moving from record to record in a dataset (or moving from one record to another by a specific number of records), it is often also useful to mark a particular location in a dataset so that you can return to it quickly when desired. TDataSet and its descendants implement a bookmarking feature that enables you to tag records and return to them later. The bookmarking feature consists of a Bookmark property and five bookmark methods.
The Bookmark property indicates which bookmark among any number of bookmarks in your application is current. Bookmark is a string that identifies the current bookmark. Each time you add another bookmark, it becomes the current bookmark.
TDataSet implements virtual bookmark methods. While these methods ensure that any dataset object derived from TDataSet returns a value if a bookmark method is called, the return values are merely defaults that do not keep track of the current location. Descendants of TDataSet, such as TBDEDataSet, reimplement the bookmark methods to return meaningful values as described in the following list:
To create a bookmark, you must declare a variable of type TBookmark in your application, then call GetBookmark to allocate storage for the variable and set its value to a particular location in a dataset. The TBookmark variable is a pointer (void *).
Before calling GotoBookmark to move to a specific record, you can call BookmarkValid to determine if the bookmark points to a record. BookmarkValid returns True if a specified bookmark points to a record. In TDataSet, BookmarkValid is a virtual method that always returns False, indicating that the bookmark is not valid. TDataSet descendants reimplement this method to provide a meaningful return value.
You can also call CompareBookmarks to see if a bookmark you want to move to is different from another (or the current) bookmark. TDataSet.CompareBookmarks always returns 0, indicating that the bookmarks are identical. TDataSet descendants reimplement this method to provide a meaningful return value.
When passed a bookmark, GotoBookmark moves the cursor for the dataset to the location specified in the bookmark. TDataSet.GotoBookmark calls an internal pure virtual method which generates a runtime error if called. TDataSet descendants reimplement this method to provide a meaningful return value.
FreeBookmark frees the memory allocated for a specified bookmark when you no longer need it. You should also call FreeBookmark before reusing an existing bookmark.
The following code illustrates one use of bookmarking:
procedure DoSomething (const Tbl: TTable)
var
Bookmark: TBookmark;
begin
Bookmark := Tbl.GetBookmark; { Allocate memory and assign a value }
Tbl.DisableControls; { Turn off display of records in data controls }
try
Tbl.First; { Go to first record in table }
while not Tbl.EOF do {Iterate through each record in table }
begin
{ Do your processing here }
...
Tbl.Next;
end;
finally
Tbl.GotoBookmark(Bookmark);
Tbl.EnableControls; { Turn on display of records in data controls, if necessary }
Tbl.FreeBookmark(Bookmark); {Deallocate memory for the bookmark }
end;
end;
Before iterating through records, controls are disabled. Should an error occur during iteration through records, the finally clause ensures that controls are always enabled and that the bookmark is always freed even if the loop terminates prematurely.
You can search any dataset for specific records using the generic search methods Locate and Lookup. These methods enable you to search on any type of columns in any dataset.
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.":
var
LocateSuccess: Boolean;
SearchOptions: TLocateOptions;
begin
SearchOptions := [loPartialKey];
LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.',
SearchOptions);
end;
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 matching records. If the columns to search are indexed and the index is compatible with the search options you specify, Locate uses the index.
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:
var
LookupResults: Variant;
begin
with CustTable do
LookupResults := Lookup('Company', 'Professional Divers, Ltd.', 'Company;
Contact; Phone');
end;
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:
var
LookupResults: Variant;
begin
with CustTable do
LookupResults := Lookup('Company; City', VarArrayOf(['Sight Diver', 'Christiansted']),
'Company; Addr1; Addr2; State; Zip');
end;
Lookup uses the fastest possible method to locate matching records. If the columns to search are indexed, Lookup uses the index.
An application is frequently interested in only a subset of records within a dataset. For example, you may be interested in retrieving or viewing only those records for companies based in California in your customer database, or you may want to find a record that contains a particular set of field values. In each case, you can use filters to restrict an application's access to a subset of all records in the dataset.
A filter specifies conditions a record must meet to be displayed. Filter conditions can be stipulated in a dataset's Filter property or coded into its OnFilterRecord event handler. Filter conditions are based on the values in any specified number of fields in a dataset whether or not those fields are indexed. For example, to view only those records for companies based in California, a simple filter might require that records contain a value in the State field of "CA".
Note: Filters are applied to every record retrieved in a dataset. When you want to filter large volumes of data, it may be more efficient to use a query to restrict record retrieval, or to set a range on an indexed table rather than using filters.
Enabling filters on a dataset is a three-step process:
When filtering is enabled, only those records that meet the filter criteria are available to an application. Filtering is always a temporary condition. You can turn off filtering by setting the Filtered property to False.
There are two ways to create a filter for a dataset:
The main advantage to creating filters using the Filter property is that your application can create, change, and apply filters dynamically, (for example, in response to user input). Its main disadvantages are that filter conditions must be expressible in a single text string, cannot make use of branching and looping constructs, and cannot test or compare its values against values not already in the dataset.
The strengths of the OnFilterRecord event are that a filter can be complex and variable, can be based on multiple lines of code that use branching and looping constructs, and can test dataset values against values outside the dataset, such as the text in an edit box. The main weakness of using OnFilterRecord is that you set the filter at design time and it cannot be modified in response to user input. (You can, however, create several filter handlers and switch among them in response to general application conditions.)
The following sections describe how to create filters using the Filter property and the OnFilterRecord event handler.
To create a filter using the Filter property, set the value of the property to a string that contains the filter conditions. The string contains the filter's test condition. For example, the following statement creates a filter that tests a dataset's State field to see if it contains a value for the state of California:
Dataset1.Filter := 'State = ' + QuotedStr('CA');
You can also supply a value for Filter based on the text entered in a control. For example, the following statement assigns the text in an edit box to Filter:
Dataset1.Filter := Edit1.Text;
You can, of course, create a string based on both hard-coded text and data entered by a user in a control:
Dataset1.Filter := 'State = ' + QuotedStr(Edit1.Text);
After you specify a value for Filter, to apply the filter to the dataset, set the Filtered property to True.
You can also compare field values to literals, and to constants using the following comparison and logical operators:
By using combinations of these operators, you can create fairly sophisticated filters. For example, the following statement checks to make sure that two test conditions are met before accepting a record for display:
(Custno > 1400) AND (Custno < 1500);
Note: When filtering is on, user edits to a record may mean that the record no longer meets a filter's test conditions. The next time the record is retrieved from the dataset, it may therefore "disappear." If that happens, the next record that passes the filter condition becomes the current record.
A filter for a dataset is an event handler that responds to OnFilterRecord events generated by the dataset for each record it retrieves. At the heart of every filter handler is a test that determines if a record should be included in those that are visible to the application.
To indicate whether a record passes the filter condition, your filter handler must set an Accept parameter to True to include a record, or False to exclude it. For example, the following filter displays only those records with the State field set to "CA":
procedure TForm1.Table1FilterRecord(DataSet: TDataSet; var Accept: Boolean); begin Accept := DataSet['State'] = 'CA'; end;
When filtering is enabled, an OnFilterRecord event is generated for each record retrieved. The event handler tests each record, and only those that meet the filter's conditions are displayed. Because the OnFilterRecord event is generated for every record in a dataset, you should keep the event handler as tightly-coded as possible to avoid adversely affecting the performance of your application.
You can code any number of filter event handlers and switch among them at runtime. To switch to a different filter event handler at runtime, assign the new event handler to the dataset's OnFilterRecord property.
For example, the following statements switch to an OnFilterRecord event handler called NewYorkFilter:
DataSet1.OnFilterRecord := NewYorkFilter; Refresh;
The FilterOptions property enables you to specify whether or not a filter that compares string-based fields accepts records based on partial comparisons and whether or not string comparisons are case-sensitive. FilterOptions is a set property that can be an empty set (the default), or that can contain either or both of the following values:
For example, the following statements set up a filter that ignores case when comparing values in the State field:
FilterOptions := [foCaseInsensitive]; Filter := '''State'' = ''CA''';
There are four dataset methods that enable you to navigate among records in a filtered dataset. The following table lists these methods and describes what they do:
For example, the following statement finds the first filtered record in a dataset:
DataSet1.FindFirst;
Provided that you set the Filter property or create an OnFilterRecord event handler for your application, these methods position the cursor on the specified record whether or not filtering is currently enabled for the dataset. If you call these methods when filtering is not enabled, then they
Note: If filtering is disabled and you do not set the Filter property or create an OnFilterRecord event handler, these methods do the same thing as First(), Last(), Next(), and Prior().
All navigational filter methods position the cursor on a matching record (if one if found) make that record the current one, and return True. If a matching record is not found, the cursor position is unchanged, 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 cursor is already on the last matching record in the dataset, and you call FindNext, the method returns False, and the current record is unchanged.
You can use the following dataset methods to insert, update, and delete data:
A dataset must be in dsEdit mode before an application can modify records. In your code you can use the Edit method to put a dataset into dsEdit mode if the read-only CanModify property for the dataset is True. CanModify is True if the table(s) underlying a dataset permits read and write privileges.
On forms in your application, some data-aware controls can automatically put a dataset into dsEdit state if
Important: For TTable components with the ReadOnly property set to True and TQuery components with the RequestLive property set to False, CanModify is False, preventing editing of records.
Note: Even if a dataset is in dsEdit state, editing records may not succeed for SQL-based databases if your application's user does not have proper SQL access privileges.
Once a dataset is in dsEdit mode, a user can modify the field values for the current record that appears in any data-aware controls on a form. Data-aware controls for which editing is enabled automatically call Post when a user executes any action that changes the current record (such as moving to a different record in a grid).
If you provide a navigator component on your forms, users can cancel edits by clicking the navigator's Cancel button. Cancelling edits returns a dataset to dsBrowse state.
In code, you must write or cancel edits by calling the appropriate methods. You write changes by calling Post. You cancel them by calling Cancel. In code, Edit and Post are often used together. For example,
with CustTable do begin Edit; FieldValues['CustNo'] := 1234; Post; end;
In the previous example, the first line of code places the dataset in dsEdit mode. The next line of code assigns the number 1234 to the CustNo field of the current record. Finally, the last line writes, or posts, the modified record to the database.
Note: If the CachedUpdates property for a dataset is True, posted modifications are written to a temporary buffer. To write cached edits to the database, call the ApplyUpdates method for the dataset. For more information about cached updates, see "Working with cached updates."
A dataset must be in dsInsert mode before an application can add new records. In code, you can use the Insert or Append methods to put a dataset into dsInsert mode if the read-only CanModify property for the dataset is True. CanModify is True if the database underlying a dataset permits read and write privileges.
On forms in your application, the data-aware grid and navigator controls can put a dataset into dsInsert state if
Once a dataset is in dsInsert mode, a user or application can enter values into the fields associated with the new record. Except for the grid and navigational controls, there is no visible difference to a user between Insert and Append. On a call to Insert, an empty row appears in a grid above what was the current record. On a call to Append, the grid is scrolled to the last record in the dataset, an empty row appears at the bottom of the grid, and the Next and Last buttons are dimmed on any navigator component associated with the dataset.
Data-aware controls for which inserting is enabled automatically call Post when a user executes any action that changes which record is current (such as moving to a different record in a grid). Otherwise you must call Post in your code.
Post writes the new record to the database, or, if cached updates are enabled, Post writes the record to a buffer. To write cached inserts and appends to the database, call the ApplyUpdates method for the dataset.
Insert opens a new, empty record before the current record, and makes the empty record the current record so that field values for the record can be entered either by a user or by your application code.
When an application calls Post (or ApplyUpdates when cached updating is enabled), a newly inserted record is written to a database in one of three ways:
Append opens a new, empty record at the end of the dataset, and makes the empty record the current one so that field values for the record can be entered either by a user or by your application code.
When an application calls Post (or ApplyUpdates when cached updating is enabled), a newly appended record is written to a database in one of three ways:
A dataset must be active before an application can delete records. Delete deletes the current record from a dataset and puts the dataset in dsBrowse mode. The record that followed the deleted record becomes the current record. If cached updates are enabled for a dataset, a deleted record is only removed from the temporary cache buffer until you call ApplyUpdates.
If you provide a navigator component on your forms, users can delete the current record by clicking the navigator's Delete button. In code, you must call Delete explicitly to remove the current record.
The Post method is central to a Delphi application's interaction with a database table. Post writes changes to the current record to the database, but it behaves differently depending on a dataset's state.
Posting can be done explicitly, or implicitly as part of another procedure. When an application moves off the current record, Post is called implicitly. Calls to the First, Next, Prior, and Last methods perform a Post if the table is in dsEdit or dsInsert modes. The Append and Insert methods also implicitly post any pending data.
Note: The Close method does not call Post implicitly. Use the BeforeClose event to post any pending edits explicitly.
An application can undo changes made to the current record at any time, if it has not yet directly or indirectly called Post. For example, if a dataset is in dsEdit mode, and a user has changed the data in one or more fields, the application can return the record back to its original values by calling the Cancel method for the dataset. A call to Cancel always returns a dataset to dsBrowse state.
On forms, you can allow users to cancel edit, insert, or append operations by including the Cancel button on a navigator component associated with the dataset, or you can provide code for your own Cancel button on the form.
On forms, all data-aware controls except for grids and the navigator provide access to a single field in a record.
In code, however, you can use the following methods that work with entire record structures provided that the structure of the database tables underlying the dataset is stable and does not change. The following table summarizes the methods available for working with entire records rather than individual fields in those records:
These method take an array of Tavern values as an argument, where each value corresponds to a column in the underlying dataset. Use the ARRAYOFCONST macro to create these arrays. The values can be literals, variables, or NULL. If the number of values in an argument is less than the number of columns in a dataset, then the remaining values are assumed to be NULL.
For unindexed datasets, AppendRecord adds a record to the end of the dataset and InsertRecord inserts a record after the current cursor position. For indexed tables, both methods places the record in the correct position in the table, based on the index. In both cases, the methods move the cursor to the record's position.
SetFields assigns the values specified in the array of parameters to fields in the dataset. To use SetFields, an application must first call Edit to put the dataset in dsEdit mode. To apply the changes to the current record, it must perform a Post.
If you use SetFields to modify some, but not all fields in an existing record, you can pass NULL values for fields you do not want to change. If you do not supply enough values for all fields in a record, SetFields assigns NULL values to them. NULL values overwrite any existing values already in those fields.
For example, suppose a database has a COUNTRY table with columns for Name, Capital, Continent, Area, and Population. If a TTable component called CountryTable were linked to the COUNTRY table, the following statement would insert a record into the COUNTRY table:
CountryTable.InsertRecord(['Japan', 'Tokyo', 'Asia']);
This statement does not specify values for Area and Population, so NULL values are inserted for them. The table is indexed on Name, so the statement would insert the record based on the alphabetic collation of "Japan".
To update the record, an application could use the following code:
with CountryTable do
begin
if Locate('Name', 'Japan', loCaseInsensitive) then;
begin
Edit;
SetFields(nil, nil, nil, 344567, 164700000);
Post;
end;
end;
This code assigns values to the Area and Population fields and then posts them to the database. The three NULL pointers act as place holders for the first three columns to preserve their current contents.
Warning: When using NULL pointers with SetFields to leave some field values untouched, be sure to cast the NULL to a void *. If you use NULL as a parameter without the cast, you will set the field to a blank value.
Datasets have a number of events that enable an application to perform validation, compute totals, and perform other tasks. The events are listed in the following table.
For more information about events for the TDataSet component, see the online VCL Reference.
To abort a method such as an Open or Insert, call the Abort procedure in any of the Before event handlers (BeforeOpen, BeforeInsert, and so on). For example, the following code requests a user to confirm a delete operation or else it aborts the call to Delete:
procedure TForm1.TableBeforeDelete (Dataset: TDataset)begin
if MessageDlg('Delete This Record?', mtConfirmation, mbYesNoCancel, 0) <> mrYes then
Abort;
end;
The OnCalcFields event is used to set the values of calculated fields. The AutoCalcFields property determines when OnCalcFields is called. If AutoCalcFields is True, then OnCalcFields is called when
OnCalcFields is always called whenever a value in a non-calculated field changes, regardless of the setting of AutoCalcFields.
Caution: OnCalcFields is called frequently, so the code you write for it should be kept short. Also, if AutoCalcFields is True, OnCalcFields should not perform any actions that modify the dataset (or the linked dataset if it is part of a master-detail relationship), because this can lead to recursion. For example, if OnCalcFields performs a Post, and AutoCalcFields is True, then OnCalcFields is called again, leading to another Post, and so on.
If AutoCalcFields is False, then OnCalcFields is not called when individual fields within a single record are modified.
When OnCalcFields executes, a dataset is in dsCalcFields mode, so you cannot set the values of any fields other than calculated fields. After OnCalcFields is completed, the dataset returns to dsBrowse state.
BDE-enabled datasets provide functionality to the dataset components that use the Borland Database Engine (BDE) to access data. Support for BDE-enablement occurs in TBDEDataSet, which is a direct descendant of TDataSet. Additional database and session control features occur in TDBDataSet, which is a direct descendant of TBDEDataSet.
This section introduces the dataset features provided by TBDEDataSet and TDBDataSet. It assumes you are already familiar with TDataSet discussed earlier in this chapter. For a general understanding of dataset components descended from TDataSet, see the beginning of this chapter.
Note: Although you need to understand the functionality provided by TBDEDataSet and TDBDataSet, unless you develop your own custom BDE-enabled datasets, you never use TBDEDataSet and TDBDataSet directly in your applications. Instead, you use the direct descendants of TDBDataSet: TQuery, TStoredProc, and TTable. For specific information about using TStoredProc, see "Working with stored procedures." For specific information about using TQuery, see "Working with queries." For specific information about TTable, see "Working with tables."
The TBDEDataSet component implements the abstract methods of TDataSet that control record navigation, indexing, and bookmarking. It also reimplements many of TDataSet's virtual methods and events to take advantage of the BDE. The BDE-specific implementations of TDataSet's features do not depart from the general description about using these features with TDataSet, so for more information about them, see at the beginning of this chapter.
In addition to BDE-specific features common to all datasets, TBDEDataSet introduces new properties, events, and methods for handling BLOBs, cached updates, and communicating with a remote database server. TDBDataSet introduces a method and properties for handling database connections and associating a dataset with a BDE session. The following sections describe these features and point to other sections in the Developer's Guide that are also relevant to using them.
The TDBDataSet component introduces the following properties and function for working with database and session connections:
Of the TDBDataSet database and session properties, the most commonly used are DatabaseName and SessionName. If you work with databases on a remote database server, such as Sybase, Oracle, or InterBase, your application usually maintains that connection through a TDatabase component. You should set the DatabaseName property of each dataset to match the name of the database component that establishes the database connection used by the dataset. If you do not use database components, DatabaseName should be set to a BDE alias (or, optionally, a full path specification for dBASE and Paradox).
SessionName indicates the BDE session with which to associate a dataset. If you do not use explicit session components in your application, you do not have to provide a value for this property. It is supplied for you. If your application provides more than one session, you can set a dataset's SessionName property to match the SessionName property of the appropriate session component in your application. If your application uses both multiple session components and one or more database components, the SessionName property for a dataset must match the SessionName property for the database component with which the dataset is associated.
For more information about handling database connections with TDatabase, see "Connecting to databases." For more information about managing sessions with TSession and TSessionList, see "Managing database sessions."
Unless you bypass the built-in functionality of dataset components and make direct API calls to the BDE, you do not need to use the DBHandle, DBLocale, and DBSession properties. These properties are read-only properties that are automatically assigned to a dataset when it is connected to a database server through the BDE. These properties are provided as a resource for application developers who need to make direct API calls to BDE functions, some of which take handle parameters. For more information about the BDE API, see the online help file, BDE32.HLP.
Cached updates enable you to retrieve data from a database, cache and edit it locally, and then apply the cached updates to the database as a unit. When cached updates are enabled, updates to a dataset (such as posting changes or deleting records) are stored in an internal cache instead of being written directly to the dataset's underlying table. When changes are complete, your application calls a method that writes the cached changes to the database and clears the cache.
The recommended approach when caching updates is to use a client dataset rather than a BDE-enabled dataset. However, TBDEDataSet provides an alternate approach, with built-in methods for handling cached updates. The following table lists the relevant properties, events, and methods for cached updating:
Using cached updates and coordinating them with other applications that access data in a multi-user environment is an advanced topic that is fully covered in "Working with cached updates."
For information about using a client dataset instead, see "Creating and using a client dataset".
TBDEDataSet provides the CacheBlobs property to control whether BLOB fields are cached locally by the BDE when an application reads BLOB records. By default, CacheBlobs is True, meaning that the BDE caches a local copy of BLOB fields. Caching BLOBs improves application performance by enabling the BDE to store local copies of BLOBs instead of fetching them repeatedly from the database server as a user scrolls through records.
In applications and environments where BLOBs are frequently updated or replaced, and a fresh view of BLOB data is more important than application performance, you can set CacheBlobs to False to ensure that your application always sees the latest version of a BLOB field.
pubsweb@inprise.com
Copyright © 1999, Inprise Corporation. All rights reserved.