|
Using the MIDAS ClientDataSet as a Replacement for Cached Updates
Dan Miser, TeamB member
This paper was originally presented at the Borland Conference '98 in Denver, Colorado
Download zipped word version of the document and the sample source code.
NOTE: The views and information expressed in this document represent those of its author(s) who is solely responsible for its content. Borland does not make or give any representation or warranty with respect to such content.
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 multi-tier solution. Furthermore, MIDAS requires a deployment license fee when used in a multi-tier environment. There are licensing options available for MIDAS. While MIDAS licensing prices are much cheaper 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.
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. According to Chuck Jazdzewski, the principal architect of Delphi, the ClientDataset model will be the official way to handle cached updates in the future.
Delphi Components
Several changes were made to the Delphi VCL to accommodate the ClientDataset model. For example, all TDBDataset components now have a Provider property. This property is of type IProvider, which is a COM interface. The IProvider interface models a standard producer/consumer relationship. The producer is a TDBDataset 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.
The ClientDataset component was introduced in Delphi 3. Database records are bundled up into a data packet from a TDBDataset and sent to the ClientDataset through the IProvider 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 1 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 Provider or ClientDataset on a separate machine. One guidleine to see if you need a MIDAS license would be if you still need to deploy and install the BDE to make your client application run. If you need the BDE on your client machine, you probably don't need a MIDAS license.
Figure 1 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
- On a Remote DataModule, export the IProvider interfaces of the TDBDataset components
- Create a client application
- Place a TRemoteServer on the client, attaching to the server application
- Place a TClientDataset on the client. Attach the RemoteServer property to the TRemoteServer. Assign the ProviderName property to the provider exported from the server application
The act of setting the ProviderName property also sets the Provider property. Remember that the Provider property is of type IProvider, and the IProvider interface controls the flow of data. Therefore, the ClientDataset does not point directly to the database table, but rather, points to the Provider property of the TDBDataset assigned to the ClientDataset. Figure 2 shows a graphical representation of this.
Figure 2 Diagram of Provider interaction
By contrast, using the ClientDataset component in a two-tier application does not require you to create a server application from which you will receive data. However, since the ClientDataset must get its data from somewhere, we will link to a TDBDataset 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 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 IProvider
- Run-time assignment of data
- Design-time assignment of data
- Design-time assignment of IProvider
Assigning IProvider
The two basic choices when using ClientDataset are assigning the Provider or assigning the data. If you choose to assign the Provider, you have a link between the TDBDataset and the ClientDataset that will allow you to have communication between the ClientDataset and TDBDataset 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 TDBDataset component for more information.
At run-time, you can assign the provider property in code. This can be as simple as the following statement, found in FormCreate:
ClientDataset1.Provider:=Table1.Provider;
A very important point to remember is that if you use this method of IProvider assignment, you must add the unit BdeProv to the uses clause. If you do not, you will receive the error message "No provider available" when running the application.
As an alternative to assigning the IProvider interface at run-time, we can assign the data directly from the TDBDataset to the ClientDataset with the following statement:
ClientDataset1.Data:=Table1.Provider.Data;
Delphi can also bind a ClientDataset to a TDBDataset at design-time by selecting the "Assign Local Data" command from the context menu of the ClientDataset component. Then, you specify with which TDBDataset component this ClientDataset should communicate, and the data is brought to the ClientDataset. 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 physical table data associated with the TDBDataset 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.
Lastly, you can use the CDSProvider component included in the source code for this paper to tie the Provider properties together at design-time. This component publishes a DataProvider property that allows you to assign a component that exposes the IProvider interface, such as TTable, TQuery, or TProvider. When you set this property, a link between the Provider properties of the ClientDataset and the specified component will be created. See the code listing below for the implementation of the DataProvider property.
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(FDataProvider);
{Assign the provider from the host provider component to the ClientDataset
(ourself)}
if FDataProvider is TCustomProvider then
Provider:=TProvider(FDataProvider).Provider // This typecast is fine to get at the
Provider property
else
if FDataProvider is TDBDataset then
Provider:=TDBDataset(FDataProvider).Provider
else
raise Exception.Create('DataProvider not valid.');
end
else
Provider:=nil;
end;
end;
Note that version 3.02 and above of Delphi adds functionality to the ClientDataset that allows the design-time assignment of a TProvider to a ClientDataset via the ProviderName property if you leave the RemoteServer property blank. The component presented here has the following advantages to that component:
- CDSProvider is compatible with Delphi 3.0, 3.01, 3.02 and 4.0
- CDSProvider allows direct assignment to TDBDataset components. This bypasses the need to introduce artificial TProvider components.
- CDSProvider can link to Provider sources located on other forms or data modules, whereas ClientDataset only permits connections to components located on the same form.
Figure 3 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 TDBDataset components and ClientDataset is that when you are using ClientDataset, you are using the IProvider interface to broker your requests for data to the underlying TDBDataset component. This means that you will be manipulating the properties, methods, events, and fields of the ClientDataset component, not the TDBDataset component. Think of the TDBDataset component as if it were in a separate application, and therefore couldn't be manipulated directly by you in code.
Note: Delphi 4 makes this component obsolete. You can use a TProvider tied to a TDBDataset to accomplish all of the same things that this component does. It is left as a learning aid for how to manipulate IProvider.
Other Advantages of Using ClientDataset
Using the ClientDataset component will dramatically reduce the network traffic in several instances:
- Retrieving an entire table from a local file
- Static lookup tables
- Sorting a table
In addition, you can also control the number of records retrieved at one time via the PacketRecords property, just as in a multi-tier application.
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 using the FileName property.
ClientDatatset1.SaveToFile('customer.cds');
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 undo changes using the standard ClientDataset methods. When this property is set, the ClientDataset will automatically read and write its contents to the file as it is opened and closed. 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 create a 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 look up 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 using the ClientDataset in this manner 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 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 property 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.
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.
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);
This is similar to the Cached Updates model of reconciling data 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.
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 5 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 5 - 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.
procedure TForm1.btnApplyClick(Sender: TObject);
var
MasterVar,DetailVar: 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
ApplyDelta(cdsMaster, MasterVar);
ApplyDelta(cdsDetail, DetailVar);
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 6 - ApplyUpdates when using Master/Detail ClientDataset
Applying updates to a single table is usually triggered by a call to ClientDataset.ApplyUpdates. This method sends the information needed to update the database to its Provider on the middle tier, where the Provider 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 Provider in the single table scenario above. Since our Provider exists on the same tier as the ClientDataset, we can call ClientDataset.Provider.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.
We could extend this example even further, and take advantage of the briefcase model presented above. This would allow our users to be completely disconnected from the database server, make changes to the data, and apply the changes and reconcile any errors back to the database at a later date when they can be physically connected to the network.
Delphi 4 has introduced a new 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. 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 the Delphi ApplyUpdates method. See the CDSMD project in the accompanying source code for an example of the manual reconciliation. For an example using nested datasets, see the ALCHTEST demo for Delphi 4.
Note: If you are using Delphi 4 with Update Pack 2 installed, you will need to modify DBCLIENT.PAS to make this technique work properly. In the TClientDataset.CheckDetailRecords method, add the following line of code:
procedure TClientDataset.CheckDetailRecords;
begin
...
AddDataPacket(Provider.GetRecords(-1, RecCount), False);
if Active then Resync([]); {!!} {Add this line, as per Josh Dahlby, to fix M/D bug}
...
end;
Lastly, if you want to have 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, they seldom 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 accomplish this:
procedure TDataModule1.Provider1BeforeUpdateRecord(Sender: TObject; SourceDS: TDataset;
DeltaDataset: 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:=SourceDS.FieldByName('ID').Value;
Query1.ExecSQL;
Applied:=true;
end;
end;
Figure 7 - Example of TProvider.BeforeUpdateRecord
You can find this technique demonstrated in the SINGLE project in the accompanying source code.
In the BeforeUpdateRecord event, you can create and execute 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 in this event, be sure to use the OldValue and NewValue properties of the appropriate TField.
In addition, you can enforce business rules here or avoid posting a record to the database altogether. The TUpdateSQLProvider component is no longer supported; however, all of the functionality has been rolled into the TProvider.BeforeUpdateRecord event.
Delphi 4 enhanced the TProvider greatly over the version in Delphi 3. With this version, you can:
- 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 caculated 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 propogated 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)
- Control building of the SQL statements by specifying which fields will go into the appropriate insert, update, and delete SQL statements. (see TField.ProviderFlags)
Deployment
When using the ClientDataset component, you have to deploy one additional file: DBCLIENT.DLL. DBCLIENT implements the interfaces that drive ClientDataset. During the installation of your application, this file should be copied to the \WINDOWS\SYSTEM directory and registered by setting the appropriate option to "Register an OCX". However, 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 these libraries if they are present, but not registered.
Conclusion
This paper 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 overstated. Borland's commitment to this technology shows that you can take advantage of these controls today, while giving your application a headstart to transition to a three-tier model in the future.
About the Author
Dan Miser has been using Delphi since its inception to solve real-world business problems. He is a member of TeamB, where he helps users on the newsgroups to solve technical problems. Dan is also a frequent contributor to Delphi Informant, and has been a Borland [now Borland] Certified Delphi Client/Server Developer since 1996.
This paper is based on material that Dan presented at the last Borland Conference. You can visit his Web site at www.execpc.com/~dmiser, or contact him via e-mail at dmiser@execpc.com.
|
MIDAS
Introduction
