Data access dilemma
by Marco Cantu
Traditionally, Delphi applications used the BDE to
access databases. But with Delphi 5 there's a new option --
ADO.
Since the release of Delphi 5, which includes the
ADOExpress technology (part of Delphi Enterprise and sold as a
separate option to owners of Delphi Professional), programmers
have been faced with the choice of using the BDE or ADO for
database access. Typically, the answer to the question "Which
database technology should I use?" is "It depends."
The BDE has a long and glorious history. It
originated in Delphi 1 as an engine for accessing Paradox
databases and was later part of the ISAPI initiative involving
IBM, Novell, and WordPerfect. Despite a few problems, the BDE
is one of the reasons for Delphi's success in the database
arena and, having reached version 5, it is a mature
technology. Although there are deployment and a few
programming issues that need fixing, Delphi programmers have
learned to live with the BDE.
A few blemishes
So why move away from the BDE? Deploying it on rented
Internet servers is often impossible because of ISPs' concerns
about running system-level services on their servers. Although
the BDE has been updated to support features like the Oracle 8
object-relational model, some of its features are still bound
to its Paradox roots. For example, the way numeric database
fields are mapped to data types creates compatibility problems
when your Delphi code works with different SQL servers.
Another problem is that the BDE includes the entire
engine used by Paradox and dBase to access data. There is no
way to deploy a thin version of the BDE excluding Paradox
support if you are targeting only SQL servers. (On the other
hand some of these problems, such as running SQL Server on an
ISP's server, apply to using ADO as well.)
Finally, with the advent of Kylix (the technology initiative behind
Delphi and C++Builder for Linux) we'll see a version of Delphi
which won't be able to use the BDE, but will apparently be
based on a new lightweight engine. If the BDE is not in
Delphi's future in Linux, it can hardly be considered the
future in Windows. Borland keeps promising the BDE is here to
stay, but over the last couple of years it has seen little
development.
ActiveX Data Objects (ADO) is part of Microsoft's Universal
Data Access initiative. It provides a simplified framework
for data access based on OLE DB, the real power horse behind
the scene. Programming directly for the OLE DB layer is
complicated so Microsoft has provided a simpler solution.
In providing the ADOExpress technology in Delphi, Borland
has accepted ADO as a common technology and has also
acknowledged Microsoft's Access as a widespread database
engine. Although in the past you could use Access databases
via the BDE, the capabilities were limited. Microsoft also
placed a few roadblocks in the way -- like hiding the
new version of DAO, the Access front end generally used by
Visual Basic -- to push all the other Windows development tool
providers towards ADO.
In any case, ADO is an interesting technology. It is
reasonably lightweight, quite powerful, allows access to data
beyond relational databases, and Microsoft is trying to push
it as an open standard. You can even write OLE DB providers
for ADO in Delphi.
Loss of control
Although ADO is a smart solution, there's one thing I don't
like: You lose a lot of the control that the BDE offers. The
BDE is limited in its ability to update the result of a query,
so to use live queries in Delphi you often attach an UpdateSql
component -- with the SQL statements for updating, inserting,
and deleting records -- to the Query component. This might
seem like an inconvenience, but it actually allows you to
fine-tune your application considerably. ADO, on the other
hand, does a lot of work to let you update the result of a
query without any extra coding, but you don't have control of
the SQL code sent back to the servers unless you place your
own solution on top of it -- giving up the live queries. Some
fine tuning is available in ADO by setting dataset, cursor,
and locking options, but these features behave differently on
different SQL servers and appear to be fine-tuned for
Microsoft SQL Server and Access.
This last is a point to consider: Although using ADO to
access multiple database back-ends is a good idea, there are a
number of ADO features which seem to be specifically related
to Access. For instance, Access is the only database I know
where you have the option of locking the database records as
you read them just in case you want to edit them in the
future. Just as the BDE includes some Paradox-related
features, ADO includes several features which are more
Access-oriented than a universal data access solution should
provide.
Some of the more interesting features of ADO relate to the
use of client-side cursors. You can download an entire dataset
to the client computer and perform a number of operations on
this cache including sorting, filtering, and editing the data.
You can even create a snapshot of the data in a local file and
work offline from the server. There are examples of how to do
this in the Delphi 5 demos and on my site.
The BDE also does local caching but won't allow you to
interact with it. However, a few Delphi programmers have
learned to use the ClientDataSet component to operate on
cached data. I've researched the subject and come to the
conclusion that the ClientDataSet does everything the ADO
client-side cursor does including sorting, filtering, and
local snapshots. Both approaches allow you to define a local
database mapped to a file with no connection to a database
backend. Both have support for XML -- albeit in different
ways. Both can be used for building a three-tier application.
And both provide nested tables to represent master-detail
views of the data.
In addition, the ClientDataSet component offers some
advanced features not found in ADO: grouping, aggregates,
calculated fields, and support for abstract data types (here's
an example).
The ClientDataSet and its related provider component allow
much more control than in ADO. It's not only possible to
specify exactly how updates are applied, but you have greater
support for handling update conflicts.
A plethora of options
ADO and the BDE are not the only alternatives to data
access in Delphi. There are other dataset components available
from Borland and third parties for direct access to SQL
servers like Oracle and InterBase. There are also alternatives
to ADO, access solutions that provide direct DAO support,
and alternative approaches ranging from Btrieve to
AS/400 support. But I still haven't found a solution for
direct OLE DB access.
The common denominator for database access in Delphi is no
longer the BDE. Instead, it's the TDataset class. I've even
written a couple of datasets for accessing non-database data.
You certainly don't need ADO and a specific OLE DB provider
for this.
Oracle, one of the preferred back-ends for Delphi
applications, enjoys such widespread use that I suspect the
success of the ADO strategy depends largely on Oracle's
support -- at least from the perspective of Delphi developers.
Apparently, the Microsoft-supplied OLE DB provider for Oracle
is currently neither a robust nor a complete solution. If
Oracle doesn't push ADO, the future of this technology won't
look so bright. And with Microsoft gearing ADO towards its own
database solutions, Oracle's resistance to promoting ADO is
understandable.
ADO's greatest advantage is that it's ubiquitous to Windows
and is promoted by Microsoft. If you're accessing Microsoft
SQL Server or Access databases, you'll probably prefer to use
ADO. If you're using Paradox or InterBase, then the BDE is
probably still the best bet -- unless you've boarded the
InterBase Express. As with so many questions in the software
biz, the answer to "Which database technology should I use?"
is: "It depends."
Originally written for InPublishing LLC for publication by Inprise Corp. Copyright 1999 Inprise Corp.
|
