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."