Building Applications With ClientDataSet and InterBase Express

By: Conference Speaker

Abstract: This paper investigates the advantages of using ClientDataSets in combination with the Interbase Express components to build your InterBase applications.

This is the technical paper from a talk given at the 12th Annual Borland Developer's Conference
By Bill Todd – The Database Group, Inc.

Bill Todd is President of The Database Group, Inc., a database consulting and development firm based near Phoenix. He is co-author of four database programming books and over 80 articles, and is a member of Team Borland, providing technical support on the Borland Internet newsgroups. He is a frequent speaker at Borland Developer Conferences in the U.S. and Europe. Bill is also a nationally known trainer and has taught programming classes across the country and overseas.

Note: No portion of this paper may be distributed or reproduced by any means, or in any form, without the author's and/or Borland's prior written permission.

Introduction

Propblems With Interbase Express

Using ClientDataSet

Building the Data Module

Building the User Interface

Editing Data

Handling Update Errors

Sorting Data On-the-Fly

Showing Record Status


Introduction

Now that the ClientDataSet component is available in the Professional as well as the Enterprise versions of Delphi and C++ Builder it is time to look at the advantages of using ClientDataSets in combination with the Interbase Express components to build your Interbase applications. As you will see later on in this paper there are many advantages to using ClientDataSets in your applications. This is the reason that the ClientDataSet component is the foundation of Borland's new dbExpress architecture.

There are also many reasons for using Interbase Express (IBX) to build your Interbase applications. First, IBX is much smaller and easier to deploy than the BDE. Second, IBX provides faster access to Interbase databases than the BDE. Finally, Interbase Express provides access to features of Interbase that are not available through the BDE such as multiple simultaneous transactions, a wider variety of transaction isolation levels and access to CommitRetaining and RollbackRetaining.

Problems With Interbase Express

The single biggest problem that developers encounter when working with the IBX components is that all access to data in an Interbase database must take place in the context of a transaction. This means that you cannot even read and display the contents of a record without starting a transaction. The problem is that when you commit or rollback the transaction all of the datasets attached to the IBTransaction component are closed and the user is suddenly looking at a blank screen with no data displayed.

You can see this effect in the IBXDemo program that accompanies this paper. The following screen shows the main form after the Open button has been clicked. The next screen shows the main form after clicking the Commit button.

IBX Main Form Showing Data

IBX Main Form After Commit

Of course there are ways to deal with this and the IBXDEMO application shows one of them. If you check the Reopen checkbox before you click the commit button the program code saves the primary key values of the employee and salary history tables, commits the transaction, reopens both IBDataSets and locates the records that were current at the time of the commit. The same thing happens if you click the Rollback button. This works but it is a lot of extra work.

Another solution is to follow the normal client/server design philosophy of starting the user off with a blank form and requiring him/her to enter some selection criteria to allow you to fetch a modest number of records for the user to work with. 'The user can then make whatever changes are required to the set of records and commit the changes. When the changes are committed the user is back to a blank form and must select another set of records. This approach requires less code but can lead to unacceptably long transactions. For example, suppose a user needs to make changes to several hundred customer records in the state of Arizona. If the user has the ability to select all of the customers in Arizona at once he/she may spend several hours working with those records before finishing. This causes two potential problems. First, if the user's workstation crashes all of the changes will be rolled back and all of the user's work will be lost. Second, the transaction may exist for several hours with several hundred records changed and locked so that they cannot be updated by other users.

Another solution that many users discover is to call CommitRetaining or RollbackRetaining instead of Commit or Rollback. This seems like an ideal solution at first because it does not close the datasets and allows the user to continue to view and modify the selected set of records. However, CommitRetaining and RollbackRetaining do not end your transaction. That means that if you are using snapshot transaction isolation you cannot see any changes made by other users until you finally commit or rollback. It also means that you have disabled Interbase's garbage collection by forcing it to maintain all transaction and record version information since your transaction started. This can cause severe performance problems in a multi-user system with many users making many changes. This is a clearly a case where the cure is worse than the disease.

Using ClientDataSet

The ClientDataSet component is one of the Midas components used to implement the provide/resolve strategy of database editing that was first introduced as part of Midas in Delphi 3. Before we get into the details of building an application that uses the DataSetProvider, ClientDataSet and IBX components it is worthwhile to examine the basics of the provide/resolve strategy.

The Provide/Resolve Strategy

The provide/resolve mechanism uses a DataSetProvider component to provide data supplied by a dataset component to a ClientDataSet. The ClientDataSet component holds the records received from the provider in memory. Users can insert, delete or update records and the changes to the data are also held in memory by the ClientDataSet. When the user is finished working with the current set of records a call to the ClientDataSet's ApplyUpdates method sends the changes back to the provider. The provider then generates the SQL statements necessary to apply the changes to the database, starts a transaction, sends the SQL statements to the database server and, if there are no errors, commits the transaction.

From this very basic explanation you can see the advantages of this architecture. First, transactions are very short lived. When you open a ClientDataSet that is connected to a DataSetProvider the provider in tern opens the dataset component it is connected to. When opened the dataset component executes its SQL SELECT statement and returns the records to the provider which then sends the records to the ClientDataSet and closes the dataset and commits the read transaction. When you call the ClientDataSet's ApplyUpdates method the changes are sent to the provider which generates the necessary SQL statements, starts a transaction, sends the SQL statements to the database server and commits the transaction. This makes both the read and update transactions very short which means that other users are not blocked by record locks held by long running update transactions. Short transactions also means that the number of transactions open at any one time will be smaller and, therefore, the load on the database server will be less.

The second big advantage of this strategy is that it gives you control of how much data you work with. since you control the SQL SELECT statement that is executed you control how many records are fetched from the database server at any one time which gives you control of how much network traffic you generate and how much memory you use on the client PC for buffering records. This can be particularly valuable if you are developing an application which will be used over a WAN or other low speed link.

Building the Data Module

The following figure shows the data module from the sample CDSIBX application that accompanies this paper.

CdsIbx Main Data Module

This data module is designed to support a form that shows the one-to-many relationship between the Employee table and the Salary_History table. The employees to view are selected by department. In addition, the Employee dataset includes a lookup field on the Dept_No field which displays the name of the department. The IBDatabase component in the upper left corner is configured exactly the same as it would be in an application that uses only Interbase Express components.

Next comes three IBTransaction components, one for each table the application uses. When using ClientDataSets you must provide a separate transaction component for each table since different datasets could be interacting with the database simultaneously. Next comes an IBQuery component for each table. Note that the Unidirectional property of all of the IBQuery components is set to True to reduce memory consumption and improve performance. Since these query components are only use to supply the result set of the query to the DataSetProvider components there is never any need to scroll backward in the result set. Although the IBQuery component provides a read-only result set that is all that is necessary since updates to the database are handled automatically by the DataSetProvider component. The SQL property of the EmpQry component is set to

select * from EMPLOYEE
where Dept_No = :Dept_No
order by Last_Name, First_Name

so the records for a single department can be selected. The SQL statement for the SalHistQry component is:

select * from SALARY_HISTORY
where Emp_No = :Emp_No
order by Change_Date desc

In this case the parameter, :Emp_No, has the same name as the primary key field in the Employee table and the DataSource property of the SalHistQry component is set to EmpSrc, the DataSource component that is connected to the EmpQry component. This causes the SalHistQry component to get its parameter value from the current record of EmpQry. This fetches just the salary history records for the employees that were selected. The SQL property of the DeptQry component is

select DEPT_NO, DEPARTMENT from DEPARTMENT
order by Dept_No

which selects all of the department numbers and names.

A single DataSetProvider component is provided for the EmpQry component. The DataSet property for the provider is set to EmpQry. No provider is requird for the SalHistQry because its records will be embedded in a dataset field in the records supplied by the EmpQry's DataSetProvider. This happens automatically for any child dataset component that is linked to its master dataset component through the child dataset component's DataSource property. A second DataSetProvider component is linked to the DeptQry component to provide the department records.

Finally, three ClientDataSet components are used to supply data to the application's user interface. The first one, EmpCds, is connected to the EmpProv provider by setting the ClientDataSet's ProviderName property to EmpProv. If you have used ClientDataSet's in multitier applications note that the RemoteServer property is left blank since the provider is in the same application as the ClientDataSet. After connecting the EmpCds ClientDataSet to its provider the next step is to open the Fields Editor and add all of the field objects as shown below.

CdsIbx EmpCds Fields in Fields Editor

Notice the last field object, SalHistQry. This is a field object whose type is TDataSetField and it contains the salary history records returned by the SalHistQry dataset. You must intantiate the field objects in the Fields Editor so this field object will be available or you will not be able to access the salary history records. Now you can add the SalHistCds component and set its DataSetField property to SalHistQry using the drop-down lost. This connects the SalHistCds ClientDataSet to the SalHistQry dataset field in the employee dataset. The third ClientDataSet, DeptCds, is connected to the DeptProv DataSetProvider component via its ProviderName property. Finally, two DataSource components are connected to the EmpCds and SalHistCds ClientDataSets.

Building the User Interface

Since we are interested in the data access components the user interface in the sample CDSIBX application is nothing fancy. The the main form, as it stands at this point is shown below.

CdsIbx Main Form Showing Data & Dept Drop-down

The form contains two grids and two DBNavigators to display the employee and salary history records respectively. To make this more like a normal client/server application where the user only works with a subset of records at one time there is a combo box that displays the departments. Selecting a department from the combo box automatically selects the employees for that department using code in the combo boxes OnChange event handler shown below.

procedure TMainForm.DeptComboChange(Sender: TObject);
begin
  with MainDm do
  begin
    EmpCds.Close;
    EmpCds.Params.ParamByName('Dept_No').AsString :=
      Copy(DeptCombo.Items[DeptCombo.ItemIndex], 1, 3);
    EmpCds.Open;
    SalHistCds.Open;
  end; //with
end;

This code closes the EmpCds ClientDataSet, assigns the first three characters of the selected item in the combo box to EmpCds' Dept_No parameter and reopens EmpCds. The Params property of the ClientDataSet is a list of TParam objects and each TParam object represents one of the parameters in the SQL statement of the dataset component the ClientDataSet's DataSetProvider is linked to, in this case EmpQry. This means that you can easily change the value(s) of the underlying SQL statement's parameter(s) and fetch a new set of records at any time.

If you need more flexibilty you can use the ClientDataSet's CommandText property to change the entire SQL statement. Suppose you want to be able to select employees by either department or grade. The sample application has a Select option on the main menu that offers these two choices. The form actually contains two panels positioned on top of each other. One contains the combo box for choosing a department and the other contains an edit box to allow the user to enter a grade. When the application starts the department panel is visible and the grade panel is not. Here is the code for the OnExecute event handler of the EmpByGrade action item that is attached t the Employee By Grade menu item.

procedure TMainForm.EmpByGradeExecute(Sender: TObject);
begin
  DeptPanel.Visible := False;
  GradePanel.Visible := True;
  with MainDm.EmpCds do
  begin
    Close;
    with MainDm.EmpQry.SQL do
    begin
      Clear;
      Add('SELECT * FROM EMPLOYEE WHERE JOB_GRADE = :JOB_GRADE ORDER BY LAST_NAME, FIRST_NAME');
    end; //with
    Params.Clear;
    FetchParams;
    GradeEdit.SetFocus;
  end; //with
end;

This event handler starts by making the department panel invisible and the grade panel visible. Next, it closes the EmpCds ClientDataSet, clears the IBQuery component's SQL property and adds a new SQL statement to the SQL property. Finally the code clears the Params property to remove the Dept_No Param object and calls the ClientDataSet's FetchParams method to create the parameter objects for the new SQL statement. To select employees by grade just type a grade number in the edit box and press Enter. The following code from the TEdit's OnKeyDown event handler closes the ClientDataSet, assigns the grade to the Job_Grade parameter and reopens the ClientDataSet.

procedure TMainForm.GradeEditKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
var
  Grade:      Integer;
begin
  if Key = VK_RETURN then
  begin
    with MainDm.EmpCds do
    begin
      try
        Grade := StrToInt(GradeEdit.Text);
      except
        Grade := 0;
      end; //try
      Close;
      Params.ParamByName('Job_Grade').AsInteger := Grade;
      Open;
      MainDm.SalHistCds.Open;
    end; //with
  end; //if
end;

Editing Data

As you insert, delete and modify records the changes you make are stored in the ClientDataSet's Delta property. To send these changes to the database you must call the ClientDataSet's ApplyUpdates method. Until you call apply updates you can undo the changes in reverse order, undo all changes to the current record or undo all changes to all records. The Edit menu in the sample application offers all of these choices and the code from the OnExecute event handlers of the action items associated with each menu choice are shown below.

procedure TMainForm.UndoCurrentRecordExecute(Sender: TObject);
{Undo all changes for the current record.}
begin
  MainDm.EmpCds.RevertRecord;
end;

procedure TMainForm.UndoLastChangeExecute(Sender: TObject);
{Undo the last change and move to the effected record.}
begin
  MainDm.EmpCds.UndoLastChange(True);
end;

procedure TMainForm.CancelChangesExecute(Sender: TObject);
{Cancel all updates.}
begin
  MainDm.EmpCds.CancelUpdates;
end;

procedure TMainForm.ApplyUpdatesExecute(Sender: TObject);
{If there are unapplied updates in the Delta property apply them.}
begin
  with MainDm.EmpCds do
  begin
    if ChangeCount > 0 then
    begin
      ApplyUpdates(-1);
      Refresh;
    end; //if
  end; //with
end;

The UndoLastChange method, shown in the third event handler above, takes a single Boolean parameter. If the parameter is true the last change will be undone and that record will become the current record unless it was a newly inserted record. If the parameter is false the last change is undone but the current record does not change unless undoing the last change causes the current record to disappear because it is a newly inserted record and it is the last change.

The last event handler above calls the ApplyUpdates method. Before calling ApplyUpdates the code checks to see if the ChangeCount property is greater than zero. ChangeCount returns the number of unapplied changes in the Delta proprty. If ChangeCount is zero there are no updates to apply so there is no reason to call ApplyUpdates.

ApplyUpdates takes a single parameter which is the number of errors to allow before aborting the update process. Suppose there are ten records in Delta waiting to be applied and you call ApplyUpdates with the MaxErrors parameter set to one. If the second record causes an error the update process will stop and no attempt will be made to apply the other updates to the database. If the MaxErrors parameter is set to minus one there is no limit to the number of errors that will be allowed. Every update will be attempted even if all of them cause an error.

ApplyUpdates prevents updating records that have been changed by another user since you read the records by including a WHERE clause in all of the INSERT, DELETE and UPDATE statements generated by the DataSetProvider. The UpdateMode property of the DataSetProvider controls which fields are included in the WHERE clause. UpdateMode can be set to upWhereAll (the default), upWhereChanged or upWhereKeyOnly. If UpdateMode is set to upWhereAll all of the non-BLOB fields in the record are included in the WHERE clause. This means you will get an error when you apply updates if another user has changed any field in the record even if the field the other user changed is not a field that you changed. Using upWhereChanged includes only the fields you have changed in the WHERE clause so you will get an error only if another user has changed a field that you also have changed. This is the setting you will probably want to use in most cases. The upWhereKeyOnly option includes only the priamry key fields in the WHERE clause. This means that if another user changes a field and you change the same field your changes will overwrite the other user's changes. There is, however, one limitation. The WHERE clause never includes BLOB fields so if another user changes a record since you have read it and the only change is to a BLOB you will not be warned when you update the record.

Updating Datasets That Cannot Be Updated

Working with records supplied by a stored procedure on the server has one major problem. You cannot update the records. However, you can edit datasets from a stored procedure when you use a ClientDataSet and DataSetProvider. This is easy because the DataSetProvider already generates SQL statements to perform the updates. The only problem is that when records come from a stored procedure the provider has no way to find the name of the table it should update. Fortunately, the DataSetProvider has an OnGetTableName event that fires when it is about to generate the SQL statements to apply updates. All you have to do is write a one line event handler that assigns the name of the table to update to the events TableName parameter and you can now apply updates to records supplied by a stored procedure.

This technique is also useful for SQL SELECT statements that would not normally be updateable. Suppose you have a multi-table join query that is necessary so the user can see information from two or more tables but the user only needs to make changes to fields from one table. All you have to do is instantiate field objects at design time for the ClientDataSet and set the ReadOnly property for all of the fields the user cannot change. Then create an OnGetTableName event handler for the DataSetProvider and supply the name of the table to which the updates should be applied.

Life is not as easy if you have a multi-table join and users need to change records in two or more tables. In this case you will need to write a BeforeUpdateRecord event handler for the DataSetProvider component. The Delta property of the ClientDataSet is passed to this event handler in its DeltaDs property. The type of the DeltaDs property is TClientDataSet so you have access to all of the properties and methods of the ClientDataSet component in your event handler. In this event handler you will need to scan the records in the DataSet property and generate your own SQL statements to apply the updates. You can use the UpdateKind method to find out if the current record should be inserted, deleted or modified. The field objects for the DeltaDs parameter have both an OldValue and a NewValue property. This lets you use

DeltaDs.FieldByName('SomeField').OldValue

to get the old value of the field and

DeltaDs.FieldByName('SomeField').NewValue

to get the new value of the field. Obviously records to be deleted have only old values and records to be inserted have only new values. Records that are modified are handled in a way that is not intuitive but makes sense once you understand it. For each record that is modified there are two records adjacent to each other in the DataSet. The first record has an update status of usUnmodified and is the record as it was before any changes were made. The second record's update status is usModifield and it contains values for the fields that were changed only. The OldValue and NewValue properties of the field objects are variants. When you examine the NewValue for a field in the usModified record it will be Unassigned if the value of that field was not changed, Null if the value of the field was changed from something to null and will contain the new value if the field was changed from null or any value to some other value. You can use the VarIsEmpty and VarIsNull functions to see if a field has been changed or has been changed to null. Using this information you can generate your own SQL INSERT, DELETE and UPDATE statements for each table you have to update, assign the SQL statement to the SQL property of an IBSQL component and execute it. As you process each record set the Applied parameter to True so the provider will know not to perform the update.

Handling Update Errors

When you call ApplyUpdates the Delta property is sent to the DataSetProvider which generates SQL statements to apply each change to the database and executes them. If an error occurs the record is saved. When all of the updates have been attempted or when the maximum allowable error count is reached, whichever comes first, any errors are returned to the ClientDataSet which fires an OnReconcileError event. Therefore, you must provide an OnReconcileError event handler to deal with any errors that occur. Fortunately Borland's engineers have made this easy. If you select File | New from the Delphi menu and go to the Dialogs page of the repository you will find a form named Reconcile Error Dialog. All you need to do is add this form to your application, add its unit to the uses clause in each unit that contains a ClientDataSet and add the code shown below to each ClientDataSet's OnReconcileError event handler.

procedure TMainDm.EmpCdsReconcileError(DataSet: TClientDataSet;
  E: EReconcileError; UpdateKind: TUpdateKind;
  var Action: TReconcileAction);
begin
  Action := HandleReconcileError(DataSet, UpdateKind, E);
end;

When an error occurs while applying updates this code causes a dialog box to display which shows the record, the changes made to the record, the type of update attempted (insert, delete or modify) and the error message. The user can edit the record and choose one of the following six actions. The selected action is assigned to the event handler's Action parameter.

  1. raSkip - Do not apply the update but leave the changes in the Delta property.
  2. raAbort - Abort the entire update operation. No changes will be applied.
  3. raMerge - Update the record even though it has been changed by another user.
  4. raCorrect - Replace the changes in Delta with the changes entered by the user.
  5. raCancel - Undo all changes for this record.
  6. raRefresh - Undo all changes for this record and reread the record from the database.

You can modify the appearance or features of the reconcile error dialog from the repository or write your own OnReconcileError event handler from scratch to meet your needs.

Sorting Data On-the-Fly

The fact that the ClientDataSet holds its records in memory in its Data property is a tremendous advantage. One example of that is that you can sort the records by any field or combination of fields by simply assigning the field name to the ClientDataSet's IndexFieldNames property. To sort by more than one field separate the field names with semicolons. The following code is the OnTitleClick event handler for the DBGrid that displays the employee records.

procedure TMainForm.EmpGridTitleClick(Column: TColumn);
begin
  MainDm.EmpCds.IndexFieldNames := Column.FieldName;
end;

This single line of code lets the user click on the title of any column to sort the employee records by that column. This technique has one restriction and two limitations. The restriction is that you cannot use IndexFieldNames on an embedded detail dataset such as the salary history records in the sample application. In fact, there is no way to change the sort order of an embedded detail dataset except changing the ORDER BY clause of the underlying query. The first limitation is that the sort is always ascending. There is no way to specify a descending sort using the IndexFieldNames property. Second, if you have a very large number of records in the ClientDataSet, for example 50,000, the sort may take several seconds.

Both of the limitations can be easily overcome using indexes. You can create indexes on a ClientDataSet at design time using the IndexDefs property or at run time using the AddIndex method. Both methods let you create indexes that sort ascending by some fields and descending by others as well as indexes that are case insensitive on one or more fields. Because all the records are in memory creating an index is very fast. For example, creating an index on 50,000 records takes just a few seconds. Creating an index on 1,000 records is essentially instantaneous. The sample application encludes an index created at design time which sorts the employee records descending on salary and ascending on name. The following figure shows the IndexDef for the index in the Object Inspector and the code following the picture of the Object Inspector shows how you could create the same index using the ClientDataSet's AddIndex method.

IndexDef in Object Inspector

MainDm.EmpCds.AddIndex('DescBySalary', 'Salary;Last_Name;First_Name',
    [ixDescending], 'Salary');

In this call to AddIndex the first parameter is the name of the index, the second is the list of field names to be indexed, the third is the index options parameter and the fourth is the names of the fields to be sorted in descending order. The ClientDataSet AddIndex method can take two additional parameters that are not used in this example. The first is the list of fields that will be case insensitive and the second is the grouping level. Grouping level is used with maintained aggregates to accumulate subtotals. Maintained aggregates are described later in this paper.

The following code is the OnExecute event handler of the DescendingBySalary action that is called by the Descending By Salary menu idem.

procedure TMainForm.DescendingBySalaryExecute(Sender: TObject);
begin
  if DescendingBySalary.Checked then
  begin
    DescendingBySalary.Checked := False;
    MainDm.EmpCds.IndexFieldNames := gByNameFieldNames;
  end else begin
    DescendingBySalary.Checked := True;
    MainDm.EmpCds.IndexName := gDescBySalaryIndex;
  end; //if
end;

This action toggles back and forth between the DescBySalary index and sorting by name using the IndexFieldNames property. The Sort1 menu item's OnClick event handler, shown below, checks the current value of the EmpCds.IndexName property and sets the Checked property of the action item in case the sort order was changed by clicking on the grid's title bar.

procedure TMainForm.Sort1Click(Sender: TObject);
begin
  if MainDm.EmpCds.IndexName = 'DescBySalary' then
    DescendingBySalary.Checked := True
  else
    DescendingBySalary.Checked := False;

Showing Record Status

You can easily display the status of a record, that is, whether it has been modified, inserted, or not modified, using a calculated field. The Status field of the employee ClientDataSet is a calculated field and the following code is the OnCalcFields event handler.

procedure TMainDm.EmpCdsCalcFields(DataSet: TDataSet);
begin
  case EmpCds.UpdateStatus of
    usModified:     EmpCdsStatus.AsString := 'Modified';
    usUnModified:   EmpCdsStatus.AsString := 'Unmodified';
    usInserted:     EmpCdsStatus.AsString := 'Inserted';
    usDeleted:      EmpCdsStatus.AsString := 'Deleted';
  end;
end;

Calling the ClientDataSet's UpdateStatus method returns one of the four constants, usModified, usUnmodified, usInserted or usDeleted. Of course you will never see a record whose status is usDeleted.

Filtering Data

One way that ClientDataSets help you reduce network traffic and server load is by filtering the records in the ClientDataSet instead of executing another query. For example, suppose a user selects all customer records for a state and then needs to see records for just one sales territory in that state. You can display just the records for one territory in one of three ways. First, if you have an index on the Territory field and it is the active index you can use the ClientDataSet's SetRange method as shown below.

CustomerCds.SetRange([23], [23]);

This method call will restrict your view of the data to just those records in territory 23. To remove the range and see all records call the CancelRange method. The second technique is to apply a filter using the ClientDataSet's Filter property. ClientDataSet filters use SQL WHERE syntax and are, therefore, more powerful than the BDE filters you may have used in the past. The following code would display the records for territory 23 using a filter.

with CustomerCds do
begin
  Filter := 'Territory = 23';
  Filtered := True;
end;

To remvoe the filter set the Filtered property to False. The third way to filter records is to write an OnFilterRecord event handler.

Using Local Lookup Tables

Another technique for reducing network traffic and server load is to use ClientDataSets for all lookup tables that you use for data validation, combo boxes and to support lookup fields in ClientDataSets. For table with a few thousand records or less you can select all records from the server. From then on, as long as you do not call the ClientDataSet's Refresh method, your application will use the records held in memory in the ClientDataSet's Data property and never reread these records from the server.

Maintained Aggregates

Maintained aggregates allow you to define summary values that will automatically be maintained by the ClientDataSet. To create a maintained aggregate use the Fields Editor to add a new field, whose type is aggregate, to the ClientDataSet as shown below.

Fields Editor defining aggregate

Maintained aggregates are shown in a separate section of the Fields Editor below the other fields as shown below.

Fields Editor with aggregate

When selected, the maintained aggregate, Total_Salary, appears as shown below in the Object Inspector.

Object Inspecotr showin aggregate

To use the maintained aggregate field you must set its Active property to True and enter an expression that describes the value you want in the Epression property. You must also set the ClientDataSet's AggregatesActive property to True. In this example the expression is a simple one, Sum(Salary), to calculate the total salary for all of the employee records in the ClientDataSet. You can also use the Min, Max, Avg and Count functions. You can also build more complex expressions. For example, the SalHistCds ClientDataSet has a maintained aggregate named Total_Increases whose expression is

sum(New_Salary) - sum(Old_Salary)

to calculate the total amount of salary changes for the currently selected employee. The same result could have been obtained with the expression Sum(New_Salary - Old_Salary). The two major restrictions on maintained aggregate expressions are that you cannot next summary functions, so Avg(Sum(New_Salary - Old_Salary)) is not legal, and you cannot use non-aggregated field values in the expression because there is no way to determine which record the field value should come from.

You can also calculate aggregates for groups of records. For example, you can have subtotals or averages by group. The following screen from the sample application shows the form displayed when you choose View | Employee Statistics from the menu. Notice the rightmost three columns in the grid which show the total salary by department, the average salary by department and the number of employees in the department.

Employee Stats form

The first step in creating a maintained aggregate that uses groups is t create an index that groups the records the way you want them. In the sample application an index on the Dept_No, Last_Name and First_Name fields is defined in the IndexDefs property of the EmpStatsCds ClientDataset. The ClientDataSet's IndexName property is set to this index which is named AscByDept. In additions the GroupingLevel property of the index is set to one to indicate that we only want to group by the first field in the index. It could be set to a higher value up to the number of fields in the index. Creating a maintained aggregate that uses grouping is the same as creating one that does not use grouping except that you must set two additional properties. The first is the GroupingLevel property of the aggregate field which must be set to the number of fields in the index that you want to group by. In this example we want to group by the first field in the index, Dept_No, so GroupingLevel is set to one. The second property is IndexName. In this example the IndexName property of the aggregate field is set to AscByDept. The aggregate will only be calculated when this index is the active index of the ClientDataSet.

Cloning Datasets

Another very handy feature of ClientDataSets is the ability to clone cursors. Suppose you need to look at two different records in the same dataset at the same time. If you are not working with ClientDataSets you must either find the record and save it to an array or use a second query component to fetch a second copy of the records which creates additional network traffic and additional load on the server. If you are using ClientDataSets and need an addtional view of your data all you have to do is add another ClientDataSet to your application and call its CloneCursor method as shown in the following code from the OnCreate event handler of the EmpCloneForm form in the sample application.

procedure TEmpCloneForm.FormCreate(Sender: TObject);
begin
  with EmpCloneCds do
  begin
    CloneCursor(MainDm.EmpCds, False, False);
    Open;
  end; //with
end;

CloneCursor takes three parameters. The first is the source ClientDataSet. The second and third are called Reset and KeepSettings and both are boolean. When Reset and KeepSettings are False the RemoteServer, ProviderName, IndexName, Filter, Filtered, MasterSource, MasterFields and ReadOnly properties are copied from the source ClientDataSet to the clone. When Reset is True the properties of the clone are set to their default values. When KeepSettings is True the property values of the clone are not changed.

Saving Data Locally

The ClientDataSet component has a SaveToFile and a LoadFromFile method. SaveToFile saves the contents of the ClientDataSet's Data property and any indexes that were defined at design time in the IndexDefs property to a local file in a proprietary format. If the file extension is XML only the data will be saved and it will be saved in XML format. LoadFromFile will load the saved data back into the ClientDataSet.

Aside from being a very easy way to convert data to XML this ability has many other uses. One is to create briefcase model applications that can download a subset of data from the database server; save the data locally; disconnect from the network; add, delete and change records; reconnect to the network and apply the changes to the database. You can also use this capability to reduce network traffic for applications that run over a low speed WAN and which use tables that are relatively static and contain moderate numbers of records. Just read the data for the static tables into the ClientDataSet and save it locally. From then on load the data from the local files. All you need is a control table in the database updated by a trigger that shows the date and time each table was last updated. When your application starts check the date and time last updated for each table and only download the data from the server if the table has been changed since your application last retrieved it. You can also use a ClientDataSet as a single user database without using any external database or server.

Using ClientDataSets as In-Memory Tables

You can also use a ClientDataSet without connecting it to a provider. At design time add fields to the FieldDefs property using the property editor. Then all you have to do is right click on the ClientDataSet and choose Create Dataset from the context menu. You now have an in-memory table with all of the features described above. You can also add the FieldDefs at runtime using the FieldDefs.Add method and call the ClientDataSet's CreateDataSet method to create in-memory tables on-the-fly. Use these temporary tables for data manipulation, reporting or any other situation where you can improve performance and reduce network traffic by storing data in memory.

Deploying ClientDataSet

If you use ClientDataSets in your application you must deploy MIDAS.DLL with your application. MIDAS.DLL contains the support code for ClientDataSet. You must place MIDAS.DLL in the directory that contains your EXE, the Windows System directory, the Windows directory or any directory on the search path.

Summary

Using ClientDataSets for your Interbase applications offers a host of benefits. ClientDataSets can reduce both server load and network traffic. Using ClientDataSets improves concurrency by minimizing the length of time transactions are open. In addition they let you sort and filter your data quickly and easily, allow you develop briefcase model applications and make queries and stored procedures updateable. Finally, you can use a ClientDataSet as a very powerful in-memory table.

Server Response from: ETNASC03