ClientDataset as a Replacement for Cached Updates
by Dan Miser - DistribuCon
You can download all source code for this article from
CodeCentral.
-
-
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 https://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:
- Add persistent fields to the joined TQuery.
- Set the TField.ProviderFlags=[] for every field on the TQuery that you won't be updating.
- 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:
- The Delphi 4 method of using DatasetProvider.BeforeUpdateRecord() to break the data packet apart
and apply the updates to the underlying tables.
- 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/.
Connect with Us