The dbExpress Library

One of the most relevant new features of Delphi in the recent years has been the introduction of the dbExpress database library (DBX), available both for the Linux and the Windows platforms. I say library and not database engine because, unlike other solutions, dbExpress uses a lightweight approach and requires basically no configuration on end-user machines.

Being light and portable are the two key characteristics of dbExpress; Borland introduced it for those reasons, along with the development of the Kylix project. Compared to other powerhouses, dbExpress is very limited in its capabilities. It can access only SQL servers (no local files); it has no caching capabilities and provides only unidirectional access to the data; and it can natively work only with SQL queries and is unable to generate the corresponding SQL update statements.

At first, you might think these limitations make the library useless. On the contrary: These are features that make it interesting. Unidirectional datasets with no direct update are the norm if you need to produce reports, including generating HTML pages showing the content of a database. If you want to build a user interface to edit the data, however, Delphi includes specific components (the ClientDataSet and Provider, in particular) that provide caching and query resolution. These components allow your dbExpress-based application much more control than a separate (monolithic) database engine, which does extra things for you but often does them the way it wants to, not the way you would like.

dbExpress allows you to write an application that, aside from problems with different SQL dialects, can access many different database engines without much code modification. Supported SQL servers include Borland's own InterBase database, the Oracle database server, the MySQL database (which is popular particularly on Linux), the Informix database, IBM's DB2, and Microsoft SQL Server in Delphi 7. A more detailed description of dbExpress, the related VCL components, and many examples of its use will be provided in Chapter 14; the current chapter focuses on database architecture foundations.

The Borland Database Engine

Delphi still ships with the BDE, which allows you to access local database formats (like Paradox and dBase) and SQL servers as well as anything accessible through ODBC drivers. This was the standard database technology in early versions of Delphi, but Borland now considers it obsolete. This is particularly true for the use of the BDE to access SQL servers through the SQL Links drivers. Using the BDE to access local tables is still officially supported, simply because Borland doesn't provide a direct migration path for this type of application.

In some cases, a local table can be replaced with the ClientDataSet component (MyBase) specifically for temporary and small lookup tables. However, this approach won't work for larger local tables, because MyBase requires the entire table to be loaded in memory to access even a single record. The suggestion is to move larger tables to a SQL server installed on the client computer. InterBase, with its small footprint, is ideal in this particular situation. This type of migration will also open to you the doors of Linux, where the BDE is not available.

Of course, if you have existing applications that use the BDE, you can continue using them. The BDE page of Delphi's Component Palette still has the Table, Query, StoreProc, and other BDE-specific components. I'd discourage you from developing new programs with this old technology, which is almost discontinued by its producer. Eventually, you should look to third-party engines to replace the BDE when your programs require a similar architecture (or you need compatibility with older database file formats).

InterBase Express

Borland has made available another set of database access components for Delphi: InterBase Express (IBX). These components are specifically tailored to Borland's own InterBase server. Unlike dbExpress, this is not a server-independent database engine, but a set of components for accessing a specific database server. If you plan to use only InterBase as your back-end database, using a specific set of components can give you more control over the server, provide the best performance, and allow you to configure and maintain the server from within a custom client application.

You can consider using IBX (or other comparable sets of components) if you are sure you won't change your database and you want to achieve the best performance and control at the expense of flexibility and portability. The down side is that the extra performance and control you gain may be limited. You'll also have to learn to use another set of components with a specific behavior, rather than learn to use a generic engine and apply your knowledge to different situations.

MyBase and the ClientDataSet Component

The ClientDataSet is a dataset accessing data kept in memory. The in-memory data can be temporary (created by the program and lost as you exit it), loaded from a local file and then saved back to it, or imported by another dataset using a Provider component.

Borland indicates that you should use the ClientDataSet component mapped to a file with the name MyBase, to indicate that it can be considered a local database solution. I have trouble with the way Borland marketing has promoted this technology, but it has a place, as I'll discuss in the section "MyBase: Stand-alone ClientDataSet."

Accessing data from a provider is a common approach both for client/server architectures (as you'll see in Chapter 14) and for multitier architectures (discussed in Chapter 16, "Multitier DataSnap Applications"). The ClientDataSet component becomes particularly useful if the data-access components you are using provide limited or no caching, which is the case with the dbExpress engine.

dbGo for ADO

ADO (ActiveX Data Objects) is Microsoft's high-level interface for database access. ADO is implemented on Microsoft's data-access OLE DB technology, which provides access to relational and non-relational databases as well as e-mail and file systems and custom business objects. ADO is an engine with features comparable to those of the BDE: database server independence supporting local and SQL servers alike, a heavyweight engine, and a simplified configuration (because it is not centralized). Installation should (in theory) not be an issue, because the engine is part of recent versions of Windows. However, the limited compatibility among versions of ADO will force you to upgrade your users' computers to the same version you've used for developing the program. The sheer size of the MDAC (Microsoft Data Access Components) installation, which updates large portions of the operating system, makes this operation far from simple.

ADO offers definite advantages if you plan to use Access or SQL Server, because Microsoft's drivers for its own databases are of better quality than the average OLE DB providers. For Access databases, specifically, using Delphi's ADO components is a good solution. If you plan to use other SQL servers, first check the availability of a good-quality driver, or you might have some surprises. ADO is very powerful, but you have to learn to live with it— it stands between your program and the database, providing services but occasionally also issuing different commands than you may expect. On the negative side, do not even think of using ADO if you plan future cross-platform development; this Microsoft-specific technology is not available on Linux or other operating systems.

In short, use ADO if you plan to work only on Windows, want to use Access or other Microsoft databases, or find a good OLE DB provider for each of the database servers you plan to work with (at the moment, this factor excludes InterBase and many other SQL servers).

ADO components (part of a package Borland calls dbGo) are grouped in the ADO page of the Component Palette. The three core components are ADOConnection (for database connections), ADOCommand (for executing SQL commands), and ADODataSet (for executing requests that return a result set). There are also three compatibility components—ADOTable, ADOQuery, and ADOStoredProc—that you can use to port BDE-based applications to ADO. Finally, the RDSConnection component lets you access data in remote multitier applications.

Custom Dataset Components

As a further alternative, you can write your own custom dataset components, or choose one of the many offerings available. Developing custom dataset components is one of the most complex issues of Delphi programming; it's covered in depth in Chapter 17, "Writing Database Components." Reading that material, you'll also learn about the internal workings of the TDataSet class.