Let's start with a little history lesson. When Delphi first
shipped it had one data access framework, the Borland Database Engine (BDE).
While the BDE was a breakthrough product in its early years, providing a fast,
independent, and easy-to-use data access layer, it was cumbersome to install,
used a lot of network bandwidth, and had limited support for remote database
servers. Over time, it became increasingly obsolete.
The need for a new data access mechanism for Delphi became
even more apparent during the development of Kylix, a Delphi-based compiler and
IDE (integrated development environment) for Linux. Porting the BDE to Linux
was ruled out, and dbExpress was born. The dbExpress data access framework is
high-speed and client/server friendly, largely based on pass-through SQL
(structured query language).
The dbExpress framework has one major drawback, however. In
most cases converting a BDE project to dbExpress required a major refactoring
of the data access logic, and dbExpress did not support the old-style file
server databases, such as Paradox, dBase, or MS Access until Delphi XE2. As a
result, for most many developers dbExpress was a poor option.
FireDAC changes that. Conversion from the BDE to FireDAC is
pretty smooth, and Delphi even ships with a tool, named reFind, that helps with
much of the conversion process. It is for this reason that I use FireDAC in all
of my new projects (and all of the database projects included in my source code
for
Delphi Developer Days). In addition, I think that a good argument can be
made for migrating legacy applications to use FireDAC when a major revision is
scheduled. Yes, it is that good.
One Big Difference: UpdateOptions.UpdateMode
One reason why migration to FireDAC from the BDE is so easy
is that FireDAC provides a solid implementation of the TDataSet Interface, and
all of the basic operations available in the BDE can be found in FireDAC.
Better still, FireDAC implements many of the higher-end features previously
only found in ClientDataSets. As a result, if you know the BDE and/or
ClientDataSets, using FireDAC is a no brainer.
There is one very significant difference between how FireDAC
and the BDE/ClientDataSets work, and this difference is very easy to miss, in
part because it is related to the default value of a single property. And
because the effects of this property have such a profound impact on how your
database applications work, it is important that you know about this difference
when you decide to start using FireDAC. The property is UpdateMode.
UpdateMode is a property found in the UpdateOptions property
of FireDAC TDataSets. It is also a property of BDE TDataSets as well as the
TDataSetProvider when used to apply updates to a caching ClientDataSet. In all
of these mentioned classes the UpdateMode property performs the same task, it
influences how the corresponding component generates the SQL used to write
changes to the underlying database.
The UpdateMode property is of the type TUpdateMode. The following
is the declaration of the TUpdateMode type:
TUpdateMode = (upWhereAll, upWhereChanged, upWhereKeyOnly);
Here is how it works. When UpdateMode is set to upWhereAll,
all non-BLOB fields are included in the WHERE clause of UPDATE and DELETE
queries. This results in update failures if any of the non-BLOB fields of the
underlying record were modified since the time that the table was opened, the
query or stored procedure was executed, or the FDMemTable was loaded. This
approach is known as optimistic locking, and when two or more users are trying
to apply changes at about the same time to the same record, only the first to
apply changes will succeed. All others will fail.
When UpdateMode is set to upWhereChanged, only the primary
field keys and the fields that have been modified are included in the WHERE
clause of UPDATE queries. (Again, INSERTs are not affected. DELETE queries
continue to use an exact match criteria since there are no changed fields in a
deleted record.) As long as none of the primary key fields of an updated record
are affected, and all non-primary key fields that have been modified have also
not been updated in the underlying table since the time the data was loaded
into the DataSet, these queries should succeed.
Using upWhereChanged permits two or more users to apply
their changes to the underlying database so long as none of them have made
changes to common fields. For example, if one user changed a field called
Address, and another changed a field called PhoneNumber, and neither of these
fields are part of the primary key, both users will successfully apply their
changes. This type of update permits merges.
The final UpdateMode value is upWhereKeyOnly. With
UpdateMode set to upWhereKeyOnly, the WHERE clause of UPDATE queries only includes
the values of the primary key fields. (INSERT and DELETE queries continue to
act as they do with upWhereChanged.) Using this mode, so long as the key fields
of the underlying record have not been changed, the updates are applied,
replacing any updates that other users may have applied.
Assuming that key fields are not touched (this is a pretty
safe assumption in most database architectures), the use of upWhereKeyOnly
permits everyone to succeed in posting their changes. As a result, the last
user to post is the user whose data appears in the underlying database.
The Difference and Why It Is Important
The default value of FireDAC's UpdateOptions.UpdateMode is
upWhereKeyOnly, and this is a really big deal. It means that if you use FireDAC
datasets without changing this default value, there is a good chance that some
users may report that their changes have disappeared. Or, even worse,
never know that their edits have been overwritten.
Here is how this can happen. If two or more users read the
same record (by way of a query, stored procedure call, or by opening an
FDTable), and two or more post a change to that record, the last user to post
their record will replace those changes posted before them. What's problematic
about this is that the users who posted before the last user will have no idea
that their changes have been overwritten.
By comparison, most developers prefer to use either pessimistic locking
(the first user to start editing the record prevents any other user from
editing the record until changes have been posted), or optimistic locking (once
the first user posts a change to a record, subsequent attempts to post to that
same record will fail, since the original record no longer can be found, based
on the WHERE clause predicates). In these scenarios, the first user to post
wins, and other users must first re-read the record, after which they can
decide whether or not to update the newly posted contents.
FireDAC defaults to an UpdateMode of upWhereKeyOnly, since
the queries required to update the database tend to execute faster. It is up to
you, however, to decide whether or not the performance improvement is more
important that the possible loss of data.
The DataSetProvider class, the class which ClientDataSets
use to resolve changes back to the underlying database, and BDE DataSets
(TTable, TQuery, and TStoredProc) also have an UpdateMode property. For these
objects, the default value of UpdateMode is upWhereAll, the conservative
setting that prevents a user from overwriting another user's edits.
So, the bottom line is this. You need to understand how
FireDAC's UpdateOptions.UpdateMode affects how records are updated in the
underlying database, and set this property to the value that meets the needs of
your application.
An Easy Solution, If You Want to Change UpdateMode
Fortunately, another feature of FireDAC makes it easy to
change the default value of UpdateOptions.UpdateMode property. The
UpdateOptions property, like a number of other properties in FireDAC, is found
in a number of classes in the FireDAC framework, including FDManager,
FDConnection, FDQuery, FDMemTable, and so on.
These classes are related in a hierarchical sense, in that
the settings found in the UpdateOptions property of the FDManager (a singleton
object) are inherited by all FDConnections configured to use the FDManager.
Similarly, any FireDAC dataset, such as an FDQuery or FDMemTable, will employ
the settings found in the FDConnection to which they are connected.
So, if your application is using one FDConnection, changing
the UpdateOptions.UpdateMode property of that FDConnection will automatically
apply that setting to any FDTable, FDQuery, FDStoredProc using that connection
(so long as you have not already touched the UpdateOptions.UpdateMode property
on any of these TDataSets). If you are using more than one FDConnection, add an
FDManager and change the value of the UpdateOptions.UpdateMode property there,
and that change will likewise cascade down to any FDConnections, and then on
down to the individual TDataSets, again, so long as that property has not also
been overridden in any of the individual FDConnections or TDataSets.
Note that if your FDQuery, FDStoredProc, or FDTable is already connected to an FDConnection, and that FDConnection currently uses upWhereKeyOnly, changing the FDConnection UpdateOptions.UpdateMode property alone might not change the FireDAC DataSet. You might have to disconnected, and then re-connect the DataSet to the FDConnection before the DataSet's UpdateMode property updates.
Copyright © 2016 Cary Jensen. All Right Reserved
