Using the MIDAS ClientDataset as a replacement for cached updates

By: John Kaster

Abstract: Dan Miser has updated his classic article on the many ways to freely leverage the MIDAS ClientDataset

ClientDataset as a Replacement for Cached Updates

by Dan Miser - DistribuCon

You can download all source code for this article from CodeCentral.

Introduction

Retrieving Data

VCL Components

ClientDataset Linking

Best Practices

Other Advantages

Editing Data

Reconciling Data

Single Table

Master/Detail

Joins

C++ Builder

Deployment

Where To Go From Here

Conclusion


Introduction

Delphi has brought the concept of distributed computing to every programmer's desktop. With the introduction of MIDAS, one can quickly and easily build, test, and deploy a multi-tier application. However, not every problem requires a physical multi-tier solution. Furthermore, MIDAS requires a deployment license fee when used in a physical multi-tier environment. See http://www.borland.com/midas/papers/licensing for licensing details. While MIDAS licensing prices are much cheaper (currently $300 per server machine for unlimited clients) than any other alternative on the market that has functionality close to MIDAS, it may still be cost-prohibitive for smaller programming shops. This paper will explore how to take advantage of Borland's multi-tier technologies in one- or two-tier applications, thus eliminating the need to purchase a MIDAS license.

Retrieving Data

Throughout the course of a database application, it is necessary to bring data from server to client to edit that data. By bringing the data to a local cache, you can reduce network traffic and minimize transaction times. In previous versions of Delphi, you would use cached updates to perform this task. However, Borland has stated that the ClientDataset model will be the official way to handle cached updates in the future.

VCL Components

The main components you need to use in order to use MIDAS in a 2-tier setting are the ClientDataset and TDatasetProvider components. IAppServer is the COM interface used to model a standard producer/consumer relationship. The producer is the  TDatasetProvider component, while the consumer is a ClientDataset component. Using this model, data can flow to and from the producer and consumer with minimal intervention on the programmer's part.


type
  IAppServer = interface(IDispatch)
    ['{1AEFCC20-7A24-11D2-98B0-C69BEB4B5B6D}']
    function  AS_ApplyUpdates(const ProviderName: WideString; Delta: OleVariant;
                              MaxErrors: Integer; out ErrorCount: Integer; var OwnerData: OleVariant): OleVariant; safecall;
    function  AS_GetRecords(const ProviderName: WideString; Count: Integer; out RecsOut: Integer;
                            Options: Integer; const CommandText: WideString;
                            var Params: OleVariant; var OwnerData: OleVariant): OleVariant; safecall;
    function  AS_DataRequest(const ProviderName: WideString; Data: OleVariant): OleVariant; safecall;
    function  AS_GetProviderNames: OleVariant; safecall;
    function  AS_GetParams(const ProviderName: WideString; var OwnerData: OleVariant): OleVariant; safecall;
    function  AS_RowRequest(const ProviderName: WideString; Row: OleVariant; RequestType: Integer;
                            var OwnerData: OleVariant): OleVariant; safecall;
    procedure AS_Execute(const ProviderName: WideString; const CommandText: WideString;
                         var Params: OleVariant; var OwnerData: OleVariant); safecall;
  end;

Figure 1 - Declaration of IAppServer interface

Database records are bundled up into a data packet and sent to the ClientDataset through the IAppServer interface. Once the data packet arrives at the ClientDataset, it is placed in the Data property where it can be decoded.

The ClientDataset component descends directly from the virtual TDataset class, and therefore, is database-independent. Since it descends from TDataset, it will allow you to use all of the data-aware controls as you always have. One other advantage of the ClientDataset component is that all of the data is stored in memory, so it is very fast.

Figure 2 shows the communication between all of the components related to database development in an n-tier application. Machine A contains all of the components necessary to communicate directly with the DBMS, and as such, is categorized as a physical two-tier application. If you introduce Machine B to communicate to the DBMS on Machine A. s behalf, you have a physical three-tier application. You are only required to purchase a MIDAS license if the ClientDataset gets its data from a DatasetProvider or ClientDataset on a separate machine. One quick guideline to see if you need a MIDAS license would be if you still need to deploy and install the BDE (or other data-access libraries) to make your client application run. If you need these files on your client machine, you probably don't need a MIDAS license. Contact your local Borland sales representative for official confirmation of the need to purchase a MIDAS license


Figure 2 - Diagram of 3-tier (client, server app, DBMS) vs. 2-tier (client, DBMS)

Let's walk through a high-level overview of ClientDataset use in a three-tier application:

  • Create a server application
  • Place a TDatasetProvider and TDataset component on a Remote DataModule. This will export the IAppServer interface.
  • Create a client application
  • Place a TDispatchConnection on the client, and attach it to the application server
  • Place a TClientDataset on the client. Attach the RemoteServer property to the TDispatchConnection. Assign the ProviderName property to the provider exported from the application server

The act of setting the ProviderName property also sets the AppServer property, which is of type IAppServer. Remember that the IAppServer interface controls the flow of data. Therefore, the ClientDataset does not point directly to the database table, but rather, points to the AppServer property exported from the Remote DataModule. Figure 3 shows a simple graphical representation of this.


Figure 3 - Diagram of Provider interaction

By contrast, using the ClientDataset component in a two-tier application does not require you to create an application server from which you will receive data. However, since the ClientDataset must get its data from somewhere, we will link to a TDatasetProvider on the same tier. By doing this, you will not be required to purchase a MIDAS license, but you will get the added benefits of using MIDAS technology.

We've seen how to assign the provider, and therefore the data, to the ClientDataset in a three-tier application. How do we accomplish this in a two-tier application? There are four ways to accomplish this:

  • Run-time assignment of data
  • Design-time assignment of data
  • Run-time assignment of Provider
  • Design-time assignment of Provider

ClientDataset linking

The two basic choices when using ClientDataset are assigning the AppServer property or assigning the data. If you choose to assign AppServer, you have a link between the TDatasetProvider and the ClientDataset that will allow you to have communication between each other as needed. If, on the other hand, you choose to assign the data, you have effectively created a local storage mechanism for your data and will not communicate with the TDatasetProvider component for more information.

In order to assign the data directly from the TDatasetProvider to the ClientDataset at run-time, use the following statement:

  ClientDataset1.Data:=DatasetProvider1.Data;

Note: This is different from previous versions of MIDAS, where you could use Table1.Provider.Data as the source of the assignment.

The ClientDataset can also retrieve the data from a TDataset at design-time by selecting the "Assign Local Data" command from the context menu of the ClientDataset component.After specifying the TDataset component that contains the data you want, the data is brought to the ClientDataset and stored in the Data property. A word of caution: if you were to save the file in this state and compare the size of the DFM file to the size before executing this command, you would notice an increase in the DFM size. This is because Delphi has stored all of the metadata and records associated with the TDataset in the DFM. Delphi will only stream this data to the DFM if the ClientDataset is Active. You can also trim this space by executing the "Clear Data" command on the ClientDataset context menu.

If you want the full flexibility that an AppServer assignment allows, you need to assign the AppServer property. At run-time, you can assign the AppServer property in code by using the ClientDataset.SetProvider method. For example, use the following statement, found in FormCreate to link the :

  ClientDataset1.SetProvider(DatasetProvider1);

Note: Due to a change in Delphi 5, you will need to call this method after closing - and before opening - the ClientDataset. Also, you can only assign the AppServer property using a TDatasetProvider, as opposed to simply using the TDataset.

Lastly, you can assign the AppServer property at design-time. If you leave the RemoteServer property blank on a ClientDataset, you can assign a TDatasetProvider component to the ClientDataset.ProviderName property.

Alternatively, you can use the component provided here to tie the AppServers together at design-time. This component publishes a DataProvider property that allows you to assign either a TDatasetProvider or TDataset component. When you set this property, a link between the AppServer property of the ClientDataset and the specified component will be created. See the code listing below for the implementation of the DataProvider property.


procedure TCDSProvider.InitAppServer;
begin
  if FDataProvider is TCustomProvider then
    AppServer := TLocalAppServer.Create(TCustomProvider(FDataProvider)) else
    AppServer := TLocalAppServer.Create(TDataset(FDataProvider));
end;

procedure TCDSProvider.SetDataProvider(Value : TComponent);
begin
  if Value <> FDataProvider then
  begin
    FDataProvider:=Value;
    {Calling FreeNotification ensures that this component will receive an}
    {opRemove when Value is either removed from its owner or when it is destroyed.}
    if FDataProvider<>nil then
    begin
      FDataProvider.FreeNotification(Self);
      {Assign the app server from the host provider component}
      InitAppServer;
    end
    else
      AppServer:=nil;
  end;
end;

Figure 4 - Code listing for assigning AppServer at design-time

The component presented here has the following advantages over the TProvider approach:

  • CDSProvider is compatible with all versions of Delphi 3, 4 and 5.
  • CDSProvider allows direct assignment to TDataset components. This bypasses the need to introduce artificial TDatasetProvider components.
  • CDSProvider can link to components located on other forms or data modules, whereas the ClientDataset only permits connections to components located on the same form. This is extremely important when implementing the "Server Data Module" suggestion described below.
  • When using the component template strategy described below, you will notice that the component template does not properly increment the DatasetProvider.Dataset property to point to the correct dataset. This component does not suffer from that limitation.

Figure 5 - Screen shot of CDSProvider in use

By using this component, you will have full access to the database table to which the ClientDataset is indirectly connected. Since the ClientDataset component is a TDataset descendant, you have the ability to add fields to the dataset through the Fields Editor, just as you can do with any other TDataset descendant.

The major difference between using TDataset components and ClientDataset is that when you are using ClientDataset, you are using the IAppServer interface to broker your requests for data to the underlying TDataset component. This means that you will be manipulating the properties, methods, events, and fields of the ClientDataset component, not the TDataset component. Think of the TDataset component as if it were in a separate application, and therefore couldn't be accessed directly by you in code.

Best practices

When writing a 2-tier application that uses the MIDAS technology, you need to deal with many components. This section will provide an overview of the techniques that I have found extremely useful in maximizing the efficiency in writing applications in this manner.

  • Use component templates. After you place all of the necessary components on a form a few times, you will notice there is a definite pattern of the components, and the properties that you must set. An easy way to avoid having to drop all of the components on a form over and over again, is to create a component template instead. This technique is also very useful for diagnosing problems with MIDAS in general. The mechanism used to return data from a TProvider to a TClientDataset is the same whether you create an application server with a Remote DataModule (RDM) or utilize the components all in one tier.

    To create a component template, drop TDatasource, TClientDataset, TCDSProvider, TQuery and TDatabse components on a form. After hooking up all of the properties, select all of the components and select the menu item Component | Create Component Template. Give it a meaningful name, like TLocalMIDASTemplate. Now any time you want to test a MIDAS technique, you can drop the template on the form and all of your components will be hooked up for you already. You can create more templates for other situations too, like TNestedLocalMIDASTemplate to have all of the wiring done for a typical nested dataset.

    When creating a component template, you will need to use the CDSProvider here to ensure that the appropriate ClientDataset gets attached to the appropriate CDSProvider. Using the standard TDatasetProvider component will not work in this scenario due to the way the ClientDataset.ProviderName property is declared.

  • Place all of your "server" components on a separate DataModule. By placing the TDatabase, TDataset and TDatasetProvider components on a separate DataModule, you are effectively preparing your application for an easier transition to a multi-tier deployment later on. Another benefit of doing this is that it may help you think of the DataModule as something that the client cannot touch easily. Again, this is a good preparation for your application, and your own mindset, when it comes time to port this application to a multi-tier deployment.

    You must use the TCDSProvider component for this technique too. The TClientDataset.ProviderName property cannot be assigned to providers that reside on another Form or DataModule. You could set the ClientDataset.AppServer property at run-time, but you lose the ability to work with the controls at design-time by doing this.

Editing Data

Once you've retrieved the data into the ClientDataset, you have the full arsenal of ClientDataset capabilities at your disposal. For example, using ClientDataset.SavePoint, you can easily implement what-if scenarios that by placing a marker to act as a baseline for your data. You can continue to edit data, and if you don't like the changes made, you can roll back to the baseline you set in your SavePoint.

Other features that you will find yourself using deal with change control. You can display the status of the current record with the UpdateStatus property. You can undo changes that you made to the ClientDataset data using a variety of ClientDataset methods. For example, CancelUpdates removes all of the changes you've made since you initially retrieved the data. UndoLastChange will undo the last change you made to the ClientDataset. Finally, RevertRecord will restore one specific record back to the values it had when retrieved from the dataset. As you can see, plenty of power and flexibility exist within this component.

Using the ClientDataset component will also dramatically reduce the network traffic in several instances:

  • Briefcase files
  • Static lookup tables
  • Sorting a table

Briefcase model

ClientDataset has the ability to read and write its contents to local files. This is accomplished by using the methods LoadFromFile and SaveToFile, or by setting the FileName property. When the FileName property is set, the ClientDataset automatically reads and writes to the briefcase file when the ClientDataset is opened and closed.

The briefcase model is very powerful because in addition to storing the metadata associated with a table, the data and change log for that table is also stored. This means that you can retrieve data from the database, edit the data, and save the data to a local CDS file. Later, when you are fully disconnected from the database, you can load the data from that CDS file and still have the ability to revert changes back to the values it initially retrieved from the database. Saving changes back to the database is as simple as calling ClientDataset1.ApplyUpdates when you are once again connected to the network. Another great use for the briefcase model is to store lookup tables.

Lookup

Lookup tables have a unique quality in most database applications - they are finite, and they rarely change. If they rarely change, they do not need to take up bandwidth to send this static data across the network every time a client application starts. Instead, we can save the data locally in ClientDataset format. However, if you implement this method with dynamic tables, you need to implement some mechanism to let your application know when the lookup table has changed on the database server. This way, your application can download the latest version of the lookup table into the local cache.

Since the data is stored in a component derived from TDataset, we can use this component in a lookup capacity. For example, using a TDBLookupComboBox component requires a Datasource and a Lookup Datasource. Until now, this Lookup Datasource needed to attach itself to the database server. This would tie up precious resources and require more network traffic. With the ClientDataset method, we can store the data locally, and let the user lookup the data from the data stored on the client. See the LOOKUP sample included in the accompanying source code for a demonstration of a ClientDataset being used in this capacity.

Sorting

If you want to sort the result set in ClientDataset, you can use the IndexFieldNames property in the same way as you did with TTable. In addition, the two methods AddIndex and DeleteIndex are supplied to give you complete control over the indexing of a ClientDataset. For example, using these methods, you can control whether an index is ascending or descending.

  ClientDataset1.AddIndex('ByCityDesc', 'City', [ixDescending]);

Since the ClientDataset uses the data stored on the local machine, there will be no need to ask the database server to rerun a query in order to sort on a different field. The benefits of this method are many: reduced network traffic, incredibly fast sort times, and the ability to sort on calculated fields.

In order to take advantage of calculated field sorting, you must specify the FieldKind of the calculated field as fkInternalCalc. However, you should only specify that a field is internally calculated if you plan to filter or sort on it, because marking this field as internally calculated will cause the ClientDataset to store the field in memory just like a regular field. If you don't need the added capabilities for this calculated field, continue to identify this field as a calculated field, and the values will be derived only when necessary.

You can define the calculated field type either at design-time or at run-time. At design-time, you can add a new calculated field for the ClientDataset just as you always have done with TDBDataset components. Invoke the Fields Editor by double-clicking on the ClientDataset. Then, press the right mouse button inside the Fields Editor to bring up the context menu, where you will select the menu item "New field...". When the New Field dialog appears, you can select either Calculated or InternalCalc to set the TField.FieldKind. You can also change the field type at design-time by using the Object Inspector to change the value of the FieldKind property from fkCalculated to fkInternalCalc. Finally, to modify this attribute at run-time, simply assign the FieldKind property to the value of fkInternalCalc in code after you have created the corresponding TField. Failure to set this property correctly will result in a "Field Index out of Range" error when you try to sort on the field.

Reconciling data

All of the preceding uses of ClientDataset are geared to mimic the use of local, or in-memory, tables. After we are done manipulating the data in the local cache, we need a way to return the data back to the database. This has long been the Achilles' heel of the cached update model. Fortunately, the ClientDataset model allows for greater flexibility in reconciling and writing data back to the database.

Single table update

Using the ClientDataset model, reconciling the data back to the database server is as simple as a call to:

  ClientDataset1.ApplyUpdates(-1);
The parameter passed to the ApplyUpdates method specifies how many errors can occur before the entire process is aborted. A value of -1 here means that the update can have unlimited errors without aborting. A noteworthy side-effect to using -1 is that the transaction will always commit whatever records were able to be written successfully; regardless of failures in other records. This setting effectively means "Commit as many records as you can". The more classic approach of transaction processing would require you to pass zero (0) in to the ApplyUpdates method, essentially saying "Commit all or none".

The call to ApplyUpdates causes a transaction to be started for the appropriate database. All of the modifications to the table will occur inside this transaction. If the number of errors is greater than the number of errors allowed (passed in as the parameter to ApplyUpdates), then the transaction is rolled back. If -1 was used as the parameter, all of the updates will be attempted and the transaction will be committed. The errors will then be passed back to the ClientDataset for further processing.

You can use the standard error reconciliation dialog provided with Delphi to give the end-user a way to diagnose an error and resolve that error as the updates occur. In order to take advantage of this unit, select File | New | Dialogs | Reconcile Error Dialog. Remember to take this unit out of the Auto-create section. Once the form is available to the project, implement the ClientDataset.OnReconcileError event with the following code:

  Action:=HandleReconcileError(Dataset, UpdateKind, E);
Since you have the source code to this unit, you can customize this form's appearance as you see fit. You don't even have to use the form at all. Instead, you could handle the errors programmatically in this event with no user-interaction.

If you want to take ultimate control over the update process - including changing the SQL that will execute for an insert, update or delete - you can do that in the Provider.BeforeUpdateRecord event. For example, when a user wants to delete a record, you may not want to actually perform a delete operation on the database. Instead, a flag is set to tell applications that this record is not available. Later, an administrator can review these deletions, and commit the delete operation. The following example shows how to do this:

procedure TDataModule1.Provider1BeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataset; DeltaDS: TClientDataset; UpdateKind: TUpdateKind;
  var Applied: Boolean);
begin
  if UpdateKind=ukDelete then
  begin
    {Assumes Query1 is on the datamodule and connected to the database}
    Query1.SQL.Text:='update CUSTOMER set STATUS="DEL" where ID=:ID';
    Query1.Params[0].Value:=DeltaDS.FieldByName('ID').OldValue;
    Query1.ExecSQL;
    Applied:=true;
  end;
end;

Figure 6 - Example of TDatasetProvider.BeforeUpdateRecord

You can create as many queries as you'd like, controlling the flow and content of the update process based on different factors, such as UpdateKind and values in the Dataset. When inspecting or modifying records of the DeltaDS, be sure to use the OldValue and NewValue properties of the appropriate TField. Using TField.Value or TField.AsXXX will yield unpredictable results.

In addition, you can enforce business rules here or avoid posting a record to the database altogether. Any exception you raise here will wind it's way through MIDAS's error-handling mechanism, stopping at DatasetProvider.OnUpdateError and finally making it's way back to the ClientDataset.OnReconcileError event. By doing this, you can have full control of how you handle these errors.

Delphi 3 users will be interested to know that the TUpdateSQLProvider component is not supported in MIDAS 2 or 3 due to the fact that it cannot handle nested datasets properly. However, all of the functionality has been rolled into the TProvider.BeforeUpdateRecord event.

Master/Detail update

The Cached Updates model of reconciling data shares many similarities to the ClientDataset model, especially when working with a single table. However, some of the shortcomings of the Cached Update model are:

  • Using Master/Detail Queries, you cannot cache detail records from different master records.
  • Inserting records in detail tables is not possible without changes to the VCL.
  • Cached updates with non-live queries require use of TUpdateSQL, whereas ClientDataset can handle this natively if so desired.

Version 3.01 and above of Delphi corrects some of the problems associated with the first two bullets above; however, there is still one major limitation to using cached updates. Due to the way cached updates are implemented, you must apply the updates any time you move from a master record. This effectively means that your transactions and updates must occur on one batch of master/detail records. This may suit your needs, and if it does, you can use the following code written by Mark Edington of Borland. Attach the code in Figure 7 to the BeforeClose event of the detail table.

procedure TForm1.DetailBeforeClose(DataSet: TDataSet);
begin
  if Master.UpdatesPending or Detail.UpdatesPending then
    if Master.UpdateStatus = usInserted then
      Database1.ApplyUpdates([Master, Detail]) else
      Database1.ApplyUpdates([Detail, Master])
end;

Figure 7 . Automatic ApplyUpdates when using CachedUpdates

Remember that a key benefit of ClientDataset is that it will allow us to delay the processing and reconciliation of the data until absolutely necessary. In order to reconcile the master/detail data back to the database in one transaction, we need to write our own ApplyUpdates logic. This is not as simple as most tasks in Delphi, but it does give you full, flexible control over the update process.

Applying updates to a single table is usually triggered by a call to ClientDataset.ApplyUpdates. This method sends the changed records from the ClientDataset to its DatasetProvider on the middle-tier, where the DatasetProvider will then write the changes to the database. All of this is done within the scope of a transaction, and is accomplished without any intervention from the programmer. To do the same thing for master/detail tables, you must understand what Delphi is doing for you when you make that call to ClientDataset.ApplyUpdates.

Any changes you make to ClientDataset data are stored in the Delta property. Delta contains all of the information that will eventually be written to the database. This is what Delphi passes to the DatasetProvider in the single table scenario above. Since our DatasetProvider exists on the same tier as the ClientDataset, we can call ClientDataset.AppServer.AS_ApplyUpdates. Remember to wrap these calls inside a transaction so you can write all of the changes as one unit. After applying the updates, a call to Reconcile will finish clearing the cache for this ClientDataset.

procedure TForm1.btnApplyClick(Sender: TObject);
var
  MasterVar,DetailVar: OleVariant;
  ErrCount: integer;
  vOwnerData: OleVariant;
begin
  cdsMaster.CheckBrowseMode;
  cdsDetail.CheckBrowseMode;
  {Setup the variant with the changes (or NULL if there are none)}
  if cdsMaster.ChangeCount > 0 then
    MasterVar := cdsMaster.Delta else
    MasterVar := NULL;
  if cdsDetail.ChangeCount > 0 then
    DetailVar := cdsDetail.Delta else
    DetailVar := NULL;
  {Wrap updates in a transaction. If any step gives an error, raise}
  {an exception, which will Rollback the transaction.}
  {This would normally be done on the middle-tier, i.e.:
      SocketConnection.AppServer.ApplyUpdates(DetailVar, MasterVar);}
  Database.StartTransaction;
  try
    if not VarIsNull(MasterVar) then
    begin
      MasterVar :=
       cdsMaster.AppServer.AS_ApplyUpdates(
	     'prvMaster', MasterVar, 0, ErrCount, vOwnerData);
	  if ErrCount >  0 then
        SysUtils.Abort;    // This will cause Rollback
    end;
    if not VarIsNull(DetailVar) then
    begin
      DetailVar :=
       cdDetail.AppServer.AS_ApplyUpdates(
	     'prvDetail', DetailVar, 0, ErrCount, vOwnerData);
	  if ErrCount >  0 then
        SysUtils.Abort;    // This will cause Rollback
    end;
    Database.Commit;
  except
    Database.Rollback
  end;
  {If previous step resulted in errors, Reconcile error datapackets}
  if not VarIsNull(DetailVar) then
    cdsDetail.Reconcile(DetailVar) else
    if not VarIsNull(MasterVar) then
      cdsMaster.Reconcile(MasterVar) else
      begin
        cdsDetail.Reconcile(DetailVar);
        cdsMaster.Reconcile(MasterVar);
        cdsDetail.Refresh;
        cdsMaster.Refresh;
      end;
end;

Figure 8 - ApplyUpdates when using Master/Detail ClientDataset

While the method in Figure 8 works quite well, it really doesn't provide for opportunities for code reuse. Using the methods in the CDSUTIL.PAS unit (included in the source code for this paper), we can perform the steps necessary to apply multiple Deltas in one transaction easily.

The main steps required to abstract the update process are:

  • Place the Deltas for each CDS in a variant array
  • Place the Providers for each CDS in a variant array
  • Apply all the deltas in one transaction
  • Reconcile the error datapackets returned above and refresh data

Let's look at the code presented in Figure 9, rewritten to take advantage of the new unit.

procedure TForm1.btnApplyClick(Sender: TObject);
var
  vDelta: OleVariant;
  vProvider: OleVariant;
  arrCDS: array[0..1] of TClientDataset;
begin
  arrCDS[0]:=cdsMaster;  // Set up ClientDataset array
  arrCDS[1]:=cdsDetail;

  vDelta:=RetrieveDeltas(arrCDS);                 // Step 1
  vProvider:=RetrieveProviders(arrCDS);           // Step 2
  CDSApplyUpdates(Database1, vDelta, vProvider);  // Step 3
  ReconcileDeltas(arrCDS, vDelta);                // Step 4
end;

Figure 9 - Using CDSUTIL.PAS to apply updates in a single transaction

Note the simple nature of this code as compared to the previous listing. Furthermore, you can use this unit in either 2-tier or 3-tier applications. To move from 2-tier to 3-tier, you would export a function on the server that would call CDSApplyUpdates instead of calling CDSApplyUpdates on the client. Everything else on the client remains the same.

Note: In a multi-tier scenario, be sure to declare your method to pass back the Delta variant array.

For example:

procedure ApplyUpdates(var vDelta: OleVariant; vProvider: OleVariant);
Forgetting to do this will result in a "Mismatch in data packet" error.

Delphi 4 and 5 also have a feature known as nested datasets. Nested datasets allow a master table to actually contain detail datasets. By doing this, Delphi can write all of the changes for a master table, including the detail datasets, within one transaction. When using nested datasets, you only need to call cdsMaster.ApplyUpdates. The detail datasets will be applied automatically at the same time within the same transaction.

Standard master/detail relationships should use this new feature in order to reduce the complexity of code during the reconciliation process. However, the method presented above can still be used when the tables are not structured in a master/detail fashion, or if you want finer control over the reconciliation process than the standard offering provided by Delphi's ApplyUpdates method. See the MDCDS project in the accompanying source code for an example of manual reconciliation.

Join updates

Writing a relational database application depends heavily on walking the relationships between tables. Often, you'll find it convenient to represent your highly normalized data in a view that's more flattened than the underlying data structure. However, updating the data from these joins takes some extra care on your end.

One- Table Update

Applying updates to a joined query is a special case in database programming, and MIDAS is no exception. The problem lies in the join query itself. Although some join queries will produce data that could be automatically updated, there are others that will never conform to rules that will allow automatic retrieval, editing, and updating of the underlying data. To that end, Delphi currently forces you to resolve updates to join queries yourself.

For joins that require only one table to be updated, Delphi can handle most of the updating details for you. Here are the steps required in order to write one table back to the database:

  1. Add persistent fields to the joined TQuery.
  2. Set the TField.ProviderFlags=[] for every field on the TQuery that you won't be updating.
  3. Write the following code in the DatasetProvider.OnGetTableName event to tell MIDAS which table you want to update. Keep in mind that this new event makes it easier to specify the table name, although you could do the same thing in Delphi 4 by using the DatasetProvider.OnGetDatasetProperties event.

procedure TJoin1Server.prvJoinGetTableName(Sender: TObject;
  DataSet: TDataSet; var TableName: String);
begin
  TableName := 'Emp';
end;
By doing this, you're telling the ClientDataset to keep track of the table name for you. Now when you call ClientDataset1.ApplyUpdates(), MIDAS knows to try and resolve to the table name that you specified, as opposed to letting MIDAS try and figure out what the table name is.

An alternate approach would be to use a TUpdateSQL component that only updates the table of interest. This new feature of Delphi 5 allows the TQuery.UpdateObject to be used during the reconciliation process and more closely matches the process used in traditional client/server applications.

Multi table Update

For more complex scenarios, such as allowing the editing and updating of multiple tables, you need to write some code yourself. There are two approaches to solving this problem:
  1. The Delphi 4 method of using DatasetProvider.BeforeUpdateRecord() to break the data packet apart and apply the updates to the underlying tables.
  2. Using the Delphi 5 method of applying updates by using the UpdateObject property.
When using cached updates with a multi-table join, you need to configure one TUpdateSQL component for each table that will be updated. Because the UpdateObject property can only be assigned to one TUpdateSQL component, you needed to link all the TUpdateSQL.Dataset properties to the joined dataset programmatically in TQuery.OnUpdateRecord and call TUpdateSQL.Apply to bind the parameters and execute the underlying SQL statement. In our case, the dataset we're interested in is the Delta dataset. This dataset is passed as a parameter into the TQuery.OnUpdateRecord event.

However, the problem in using this technique in MIDAS becomes readily apparent when you try to do this for the first time. The TUpdateSQL.Dataset property is declared as a TBDEDataset. Because the Delta dataset is a TDataset, we cannot make this assignment legally. Rather than give up and use the DatasetProvider.BeforeUpdateRecord method of applying updates, I created a TUpdateSQL component descendant that will work seamlessly when resolving multi-table joins. The key to writing this component was to change the Dataset declaration to TDataset, and override the Apply method so we can call our own version of SetParams that will bind parameters to the target TDataset. Additionally, SessionName and DatabaseName properties were exposed to allow the update to occur in the same context as other transactions. The resulting code for the TQuery.OnUpdateRecord event is shown in Figure 10.


procedure TJoin2Server.JoinQueryUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  usqlEmp.SessionName := JoinQuery.SessionName;
  usqlEmp.DatabaseName := JoinQuery.DatabaseName;
  usqlEmp.Dataset := Dataset;
  usqlEmp.Apply(UpdateKind);

  usqlFTEmp.SessionName := JoinQuery.SessionName;
  usqlFTEmp.DatabaseName := JoinQuery.DatabaseName;
  usqlFTEmp.Dataset := Dataset;
  usqlFTEmp.Apply(UpdateKind);

  UpdateAction := uaApplied;
end;

Figure 10 - Resolving Multi-table Join Using the TDSUpdateSQL component
Because we've complied with the rules of updating data within the MIDAS architecture, the whole update process is seamlessly triggered as it always is in MIDAS, with a call to ClientDataset1.ApplyUpdates(0).

Note: Now that Delphi 5 supports the UpdateObject property during reconciliation, it's entirely reasonable to assume that the same method of applying updates to multi-table joins that exists for cached updates will be available for MIDAS. However, at the time of this writing, this functionality was not available.

See the source code in the JOIN2 example of the accompanying source code for a working model of these concepts, including the TDSUpdateSQL component.

C++ Builder and MIDAS

While MIDAS was introduced first with Delphi 3, MIDAS is meant to be language-neutral - and now, even platform neutral. The MIDAS concepts remain the same whether you are using Delphi, C++ Builder or JBuilder. You provide data. You resolve data. You deal with reconciliation errors. The main thing that changes is the syntax appropriate for the language you are writing in.

The version of MIDAS that shipped with BCB5 is MIDAS 3, which is the same version of MIDAS that ships with Delphi 5. Therefore, all of the components, concepts, and techniques presented here in this paper will work equally well whether you use Delphi 5 or C++ Builder 5. For example, the JOIN1 sample included in the source code for this paper was written with BCB5. The CDSProvider component that was presented earlier is used in this project. To use this component in BCB, I had to add the component to a package via the Component | Install Component menu and compile. After placing the resultant CDSPROV.HPP file into a directory on the BCB Include path, I could use the component with no problems. One other point with this project is that it highlights the different syntax for dealing with Variants in BCB as opposed to Delphi.

Deployment

When using the ClientDataset component in a 2-tier application, you have to deploy one additional file, namely MIDAS.DLL.

MIDAS.DLL implements the interfaces that drive ClientDataset, and contains information on the IAppServer interface. During the installation of your application, MIDAS.DLL should be copied to the appropriate WINDOWSSYSTEM directory and registered by setting the option to "Register an OCX" in your install program. If your installation program does not allow automatic registration, you can use regsvr32.exe, or Borland's tregsvr.exe, to register this file externally. One last point: the VCL automatically tries to register MIDAS.DLL if they are present, but not registered. I would highly recommend that you don't rely on this option, but rather follow good programming techniques by explicitly stating what you want to accomplish. In this case, register the files at installation. This will minimize configuration problems you may encounter.

Where To Go From Here

There is much more to using MIDAS than this paper covers. Other interesting aspects of MIDAS 3 are listed here to help you continue your exploration of all that MIDAS offers.

  • Reconcile data to the database by using the linked TDBDataset. This means that all of the code written in the events for a TTable, for example, will work. (see TProvider.ResolveToDataset)
  • Automatically retrieve a calculated TField from TDBDataset to TClientDataset. (see TDBDataset Field Editor)
  • Specify exactly which TFields get sent from to TDBDataset to TClientDataset. (see TDBDataset Field Editor)
  • Take advantage of cascading updates and deletes without writing a single line of code. (see TProvider.ProviderOptions)
  • Control the fetching of BLOB fields and detail records. (see TProvider.ProviderOptions)
  • Automatically transport certain TField properties from TDBDataset to TClientDataset. For example, Alignment, Visible, EditFormat, MinValue and MaxValue are propagated from TDBDataset to TClientDataset. This will allow business rules to be defined at the Provider level, yet the TClientDataset can use these properties to enforce the rules at the client. (see TProvider.ProviderOptions or http://www.borland.com/codecentral/midas/1999/constraints)
  • Control building of the SQL statements by specifying which fields will go into the appropriate insert, update and delete SQL statements. (see TField.ProviderFlags)

Conclusion

This session has shown many advantages of using ClientDataset architecture in a two-tier application. In addition, several components and examples were given to illustrate these points. The importance of becoming acquainted with these tools cannot be understated. Borland. s commitment to this technology shows that you can take advantage of these controls today, while giving your application a head-start to transition to a three-tier model in the future.

About the Author

Dan Miser is a long-time Delphi user, speciailizing in multi-tier application design and development. He is active on the Borland newsgroups, where he serves as a proud member of TeamB. In addition to being a regular speaker at BorCon, Dan also finds time to write for Delphi Informant and was a contributing author to "Delphi 5 Developer's Guide".

You can visit his Web site at http://www.distribucon.com/.



Server Response from: ETNASC01