Columns & Rows
Delphi 3 / MIDAS
ClientDataset
MIDAS on the Cheap
Delphi 3 has brought the concept of distributed computing to every programmer’s desktop. With MIDAS, a developer can quickly and easily build, test, and deploy a multi-tier application. However, not every problem requires a multi-tier solution. Furthermore, MIDAS comes with a retail price tag of $5,000 per server for deployment. While this price is much cheaper than any other alternative on the market, it may still be cost-prohibitive for smaller programming shops. This article will explore how to take advantage of Borland’s multi-tier technologies in 2-tier applications.
Delphi 3 VCL Enhancements
A new component, ClientDataset, was created for Delphi 3. This component descends directly from TDataset, and as such, is a database-independent component. Furthermore, all the data of a ClientDataset is stored in memory, and therefore is very fast.
Another modification to Delphi 3 is that all DBDataset components 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 DBDataset component, while the consumer will typically be the ClientDataset. In this way, data can flow to and from producer and consumer with minimal intervention on the programmer’s part.
Figure 1 shows the communication between all the components related to database development in an n-tier application. Machine A contains all the components necessary to communicate directly with the DBMS, and as such, is categorized as a 2-tier application. If you introduce Machine B to communicate to the DBMS on Machine A’s behalf, you have a 3-tier application. You are only using MIDAS if the ClientDataset gets its data from a DBDataset on a separate machine. Another way to check 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 MIDAS.
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 3-tier application:
• Create a server application.
• On a Remote DataModule, export the IProvider interfaces of the DBDataset components.
• Create a client application.
• Place a RemoteServer component on the client, attaching to the server application.
• Place a ClientDataset component on the client. Attach the RemoteServer property to the RemoteServer. 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 (see Figure 2). Therefore, the ClientDataset does not point directly to the database table, but rather, points to the Provider property of the DBDataset assigned to the ClientDataset.
Figure 2: Diagram of Provider interaction.
By contrast, using the ClientDataset component in a 2-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 DBDataset 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 ClientDataset technology. You don’t set the RemoteServer or ProviderName properties of the ClientDataset; those properties are only used when accessing the component in a 3-tier application.
We’ve seen how to assign the data to the ClientDataset in a 3-tier application. How do we accomplish this in a 2-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
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 don’t, you will receive the error message “No provider available” when running the application.
We can also assign the data directly from the DBDataset to the ClientDataset at run time with the following statement:
ClientDataset1.Data := Table1.Provider.Data;
Delphi can also bind a ClientDataset to a DBDataset at design-time by selecting the Assign Local Data command from the context menu of the ClientDataset component. Then, you specify with which DBDataset 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 the physical table data associated with the DBDataset 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 component provided here to tie the Provider properties together at design-time. (In the Delphi 3.02 update, functionality was added to the ClientDataset that would allow the design-time assignment of IProvider if you leave the RemoteServer property blank. The component presented here can still be used for those with Delphi 3.0 or Delphi 3.01). This component publishes a DataProvider property where you can assign a component that exposes the IProvider interface, such as Table, Query, and Provider. When you set this property, a link between the Provider properties of the ClientDataset and the specified component will be created.
By using this component, you will have full access to the table to which the ClientDataset is indirectly connected. This means that you can add fields from the table and create calculated fields. Note that the IProvider interface will not send calculated fields across from the DBDataset to the ClientDataset. If you want calculated fields for a ClientDataset, you must create them on the ClientDataset.
The big difference between using DBDataset 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 DBDataset component. This means that you will be manipulating the properties, methods, events, and fields of the ClientDataset component, not the DBDataset component. Think of the DBDataset component as a separate application that can’t be manipulated directly by you with code.
Advantages of Using ClientDataset in 2-tier applications
Using the ClientDataset component will dramatically reduce the network traffic in several instances:
• Reading an entire table
• 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 LoadFromFile and SaveToFile methods. These methods are very powerful; in addition to storing the metadata associated with a table, they also store the data and the change log for that table. This means 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. Another great use for this is lookup tables.
Lookup
Typically, lookup tables are relatively small, and rarely change. If they rarely change, they don’t 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. If you implement this method with dynamic tables, however, 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 DBLookupComboBox component requires a DataSource and a ListSource. Until now, this ListSource 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 sample project Lookup.dpr (available for download; see end of article for details) for an example of how this can be put to use.
Indexing ClientDataset
If you want to sort the result set in ClientDataset, you can use the IndexFieldNames property, just as you would with the Table and Query components. In addition, the AddIndex and DeleteIndex methods are supplied to give you complete control over indexing of a ClientDataset. For example, using these methods, you can control whether an index is ascending or descending.
Since the ClientDataset uses the data stored on the local machine, there will be no need to ask the database server to re-run a query to sort on a different field. The benefits of this method are many: reduce network traffic, incredibly fast sort times, and the ability to sort on calculated fields.
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 which type of calculated field this is at design time or at run time. At design time, you can add a new calculated field for the ClientDataset just as you have always done with DBDataset components. Invoke the Fields Editor by double-clicking on the ClientDataset; then right-click to display its context menu, and select New field. When the New Field dialog box 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 out of Range” error when you try to sort on the field. See the example CDSIndex project for a demonstration of this technique.
Cached Updates
All the preceding uses of ClientDataset are geared to mimic the use of local, or in-memory, tables. The final example presented here will show how to use the ClientDataset to greatly enhance cached update logic. According to Chuck Jazdzewski, the principal architect of Delphi, ClientDataset will be the official way to handle cached updates in the future.
Cached updates were introduced in Delphi 2 and gave developers another way to present and edit data in a multi-user application. Using cached updates reduced network traffic, but introduced the problem of data concurrency when trying to post the data stored on the client machine, back to the database server
The implementation of cached updates did have some problems, however. Some of the shortcomings of the CachedUpdate model are:
1) Using master/detail queries, you cannot cache detail records from different master records.
2) Inserting records in detail tables is not possible without changes to the VCL.
3) When using joined tables, you must use multiple TUpdateSQL and OnUpdateRecord events.
Delphi 3.01 corrected some of the problems associated with numbers 1 and 2 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 code written by Mark Edington of Borland (see Figure 3; attach the code 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 3: Automatic ApplyUpdates when using CachedUpdates.
By contrast, all the changes made to the data are stored locally on the client machine — even across different master records. 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. To reconcile the data back to the database, we need to write our own ApplyUpdates logic (see Figure 4). This isn’t 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 creates an
error, raise an exception and Rollback the transaction.
This would normally be done on the middle-tier, i.e.
MIDASConnection.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 4: ApplyUpdates when using ClientDataset.
Applying updates in a multi-tier application 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 accomplished within a transaction, and is done without programmer intervention. To accomplish the same thing in a 2-tier application, 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 the information that will eventually be written to the database. This is what Delphi passes to the Provider in the multi-tier scenario above. Since our Provider exists on the same tier as the ClientDataset, we can call ClientDataset.Provider.ApplyUpdates. Remember to wrap these calls in a transaction so you can write all the changes as one unit. After applying the updates, a call to Reconcile will finish clearing the cache for this ClientDataset.
Note that there were some inconsistencies in using this method depending on what database back-end you were using. For instance, native Paradox access yielded sporadic results in the testing of the MDCDS sample application. Switching to the ODBC drivers for Paradox faired a little better, but still produced some anomalies during the update process. However, the sample worked flawlessly with Microsoft SQL Server and Sybase SQL Anywhere back-ends.
We could extend this example 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.
Lastly, if you find yourself missing the functionality of the UpdateSQL component, you can find a version that is compatible with ClientDataset in the \Demos\Midas\Usqlprov directory of the Delphi 3.01 update. The UpdateSQLProvider component expands on the functionality of the Provider component by providing an OnUpdateRecord event. With this event, the developer can have record by record control of the update process, which is useful for implementing business rules. This component is also necessary for performing updates using stored procedures. In addition, it also expands on the functionality of the UpdateSQL component by generating SQL code at run time. This is necessary if you are working with data that contains NULL values that need to be updated.
Deployment
When using the ClientDataset component, you have to deploy two additional files: DBClient.DLL and STDVCL32.DLL. DBClient implements the interfaces that drive ClientDataset, while STDVCL32 is a type library for Delphi’s standard VCL. COM uses the Windows registry to read and write information about its components. Since these files are COM-based, they need to be registered.
During the installation of your application, these files should be copied to the \Windows\System directory and registered by setting the appropriate option to “Register an OCX.” However, if your installation program doesn’t allow automatic registration, you can use Regsvr32.exe, or Borland’s Tregsvr.exe (in the \Bin directory), to register these files externally. One last point: The VCL automatically tries to register these libraries if they are present, but not registered.
Conclusion
This article has demonstrated the advantages of using ClientDataset architecture in a 2-tier application. The importance of becoming acquainted with these tools cannot be understated. Borland’s commitment to this technology shows you can take advantage of these controls today, while giving your application a head-start to transition to a 3-tier model in the future.
The projects referenced in this article are available for download.
Dan Miser is a Design Architect for Stratagem, a consulting company in Milwaukee. He has been a Borland Certified Client/Server Developer since 1996, and is a frequent contributor to Delphi Informant. You can contact him at http://www.iinet.com/users/dmiser.
Mr Miser demonstrates working with the ClientDataset component, and shows that while it doesn’t provide all the functionality of MIDAS, it can provide a lot of the benefits in 2-tier situations.
All the benefits in working with the ClientDataset component.