Foundations in Database Development with Delphi and C++Builder by Cary Jensen

By: David Intersimone

Abstract: Delphi and C++Builder are the best tools on the market for creating Windows-based database applications. This presentation provides an introduction to the basic techniques for building database applications using Delphi or C++Builder.

Foundations in Database Development with Delphi and C++ Builder

Cary Jensen
President, Jensen Data Systems, Inc.

Note: The following paper was presented at the 1999 Inprise/Borland Conference in Philadelphia Pennsylvania. Click on the source code link to download the examples used in this paper.

Delphi and C++ Builder, both of which are based on the Visual Component Library (VCL), provide database developers with unprecedented easy and flexibility. This presentation begins with a general overview of the heart of database development using the VCL, the Borland Database Engine. It then continues onto a discussion of the role played by many of the most common components used for data access. The placement and configuration of Table, Query, StoredProc, and Database components is also discussed. Use of non-VCL database development available in Delphi 5 is also addressed.

 Overview of the Borland Database Engine (BDE)

For the most part, the VCL provides you with access to your data using the BDE (non-BDE data access is discussed briefly at the end of this paper). From time to time you will also see the BDE referred to as IDAPI (Integrated Database Application Programming Interface).

The BDE is a common data access layer for all of Borland's products, including Delphi, C++ Builder, and JBuilder (through DataGateway). Several Corel products, including Paradox 8 for Windows and Quattro Pro also use it. In addition, by default, database applications written with these products use the BDE. In other words, Delphi uses the BDE, as well as those database application you write with Delphi.

 The BDE consists of a collection of DLLs (Dynamic Link Libraries). Consequently, one installation of the BDE can be used by two or more BDE-aware applications simultaneously. As a result, each individual VCL-based application is smaller in size that it would be if all data-accessing code needed to be linked into the executable. Furthermore, since two or more applications can share the same copy of the BDE loaded into memory, overall RAM usage is reduced when two or more BDE-aware applications are running simultaneously when compared to linking the code into each EXE.

The purpose of the BDE is to insulate you from the mundane tasks of data access. These include table and record locking, SQL construction, record updates, and basic I/O, to mention a few. The BDE permits you to concentrate on what data you want to access, instead of how to access it. Due to the BDE, your Delphi and C++ Builder applications can just as easily use data in dBASE or Paradox tables, files on a remote database server, and files supported by ODBC (Open DataBase Connectivity) drivers.

The BDE API (Application Programming Interface) consists of approximately 200 procedures and functions, which are available through the BDE unit. Fortunately, you almost never need to call any of these routines directly. Instead, you use the BDE through the VCL's data access components, which are found on the Data Access page of Component Palette. These components encapsulate calls to the BDE API, providing you with a much simpler interface. However, if you have special data needs not provided by these components, you can use the BDE API directly.

The relationship between Delphi (and other applications), the BDE, and underlying file is depicted in Figure 1. As you can see, the BDE is a software layout that lies between BDE-aware applications (Delphi and the database applications you create with it) and the sources of your data. It also shows that the BDE can access local tables, such as Paradox and dBASE directly. (Connecting to MS Access and Fox Pro tables requires that you already have the DAO (Data Access Objects) DLLs from Microsoft installed).

Figure 1. The Relationship between BDE-aware applications, the BDE, and data sources

Connection to other data sources requires additional drivers. For the best performance in connecting to a remote database server, such as Oracle, MS SQL Server, InterBase, and so forth, you use Borland SQL Links drivers. These native language drivers are provided in the Delphi client/server and Delphi Enterprise editions. In addition, SQL Links drivers must use an additional network protocol to connect to the remote server.

Finally, the BDE also supports access to any file type for which there exists an ODBC driver. ODBC is a Microsoft standard for accessing databases, and it is based on the Open SQL CLI (call level interface). In order to use one of these drivers, it must be installed and configured (using the ODBC 32-bit Administrator, available on the Control Panel).

The following are some of the benefits provided by the BDE:

  • It provides seamless access to any data source, whether it is Paradox, dBASE, InterBase, Oracle, Sybase, Informix, Microsoft SQL Server, or data accessed through ODBC (Open Database Connectivity) drivers.
  • It provides the basic data engine for all your applications. Your clients only need one copy of the BDE on their systems, and this can be accessed by all the applications you write.
  • It creates an OS (operating system) independent layer for all your applications. The BDE manages all file I/O, network access, and memory management for data access.
  • It provides enhanced performance with BLOB (Binary Large Objects) through BLOB caching services.
  • It includes internal support for language drivers, providing you with an easier path to creating applications for the international market.
  • It performs data translation between various data sources.
  • It includes a SQL generator. Data requests, other than pass-through SQL, are translated into a common local SQL, which is a subset of ANSI92 SQL. Using the Borland SQL Links for Windows, these can be translated into the appropriate dialect of SQL, based on the SQL driver the BDE is sending its data requests to.
  • It offers access to data stored in formats supported by your installed ODBC drivers.

 One of the most important benefits provided by the BDE is that your VCL applications do not need to be written to a particular database standard. Specifically, you use the same, simple interface provided by data access components to access your data. Even if you later need to change the underlying data type, for instance, from Paradox to InterBase, or MS SQL Server to Oracle Server, your applications do not necessarily need to be recompiled. The description of where to find the data, and how to access the data, can be configured outside of your applications, using the BDE Administrator.

 Understanding the BDE Administrator

You can define where your data is located, and which driver to use to access it, using the BDE Administrator. This program can be accessed from the Delphi or C++ Builder folder, but it also appears on the Control Panel.

The BDE Administrator, shown in Figure 2, consists of two panes. The left pane has two pages. The Database page displays aliases, and the Configuration pages displays drivers and settings. The right pane contains the Definitions page. This page is used to display the various parameters of the selected database name, driver, or setting.

Figure 2. The BDE Administrator

Working with Global Aliases

Aliases, or database names, are labels that reference both a directory or server and a driver which can be used to access data in that directory in on that server. Aliases defined on the Databases page of the BDE Administrator are available to all Delphi applications on that machine. Consequently, they are referred to as global aliases. By comparison, aliases defined within an application using a Database component are referred to as local aliases, since they are available to that application only.

Global aliases defined here are especially useful for data that must be accessed by more than one application. For example, a company may maintain a single table for storing general information about its employees. If more than one application needs to access this information, an alias can be created which points to the directory in which that table is stored. If all applications access this table using the defined alias (which is assigned to the DatabaseName property of the DataSet being used for the access), a developer does not need to know the physical location of the table.

To create a new alias, right-click on Databases in the Database page and select New. From the New Database Alias dialog box, select the driver that corresponds to the type of data being accessed. For local tables, including Paradox, dBASE, and and ASCII, select STANDARD. Otherwise, select one of the other types available from the Database Driver Name combo box.

The BDE Administrator will generate a default name for the driver. Select the driver in the Database pane and enter the name you want to use for the driver. Then, enter the appropriate parameters for the new alias in the Definitions page.

To change a given alias, select it in the Database pane. To change the alias name, click on the current name until it appears as an editable field. To change parameters, select the name and then modify the parameters in the Definitions pane.

To delete a global alias, select it in the Database page, right-click and select Delete. You cannot delete an alias when it is open. An alias is open when a green box appears surrounding the bitmap to the left of the alias. To close an alias, select it, right-click and select Close.

Configuring Drivers

Drivers are configured from the Configuration page of the BDE Administrator. To change a particular driver's configuration, select that driver and then modify its attributes in the Definitions page.

There are two types of drivers. Native drivers, which include both local and SQL Links, are installed when you install Delphi. You cannot add new native drivers from the BDE Administrator. These can only be installed from the installation disk supplied by Borland or that created by InstallShield Express.

The driver settings you enter on the Configuration page of the BDE Administrator provide default settings for global aliases. However, when configuring a particular alias you can override any of these parameters.

Database Applications That Do Not Use the BDE

Under normal circumstances, the database applications that you create using Delphi use the BDE. However, there are a number of alternatives that do not require the BDE. These include:

  • Applications that make use of explicit file I/O. For example, you can define your own files structures, and then take responsibility for reading and writing this data manually. The drawback to this type of application is that you cannot use the data access and data control components that ship with Delphi. Similarly, if you want the data to be accessed in a multi-user environment, you are responsible for programming the necessary file and record locking mechanisms.
  • Applications that make use of an alternative database engine. Several third-party developers have created add-on products for Delphi that provide for data access. These either support file types not supported directly by the BDE (such as Clipper or b-trieve) or have a smaller footprint than the BDE. Some of these third-party products can be used in conjunction with Delphi's data access and data control components, while others provide their own component set for data access and manipulation.
  • Applications that use the ClientDataSet component that ships with the Client/Server and Enterprise editions of Delphi and C++ Builder. This component, which can be used in place of other DataSet components, permits for the reading and writing of single user flat files. The ClientDataSet component relies on a 150K DLL named DBCLIENT.DLL, but does not make use of the BDE.
  • Client applications that use Borland's MIDAS (Multi-Tier Distributed Application Services) technology. With MIDAS, your VCL-based client application receives data over a TCP/IP connection or through the use of sockets. The data is provided by an application server, which you also write using Delphi. While the application server does make use of the BDE, the client application does not. Client applications created using MIDAS are often referred to as thin clients, since they require less configuration and fewer files (specifically, no BDE).
  • Delphi 5 applications that make use of ADO and InterBase Direct components.

 Overview of BDE-based Database Components

 BDE-based database applications written in Delphi and C++ Builder rely on both data access and data control components that ship with Delphi. The relationship between these components is depicted in Figure 3.

Figure 3. The relationship between the various data access components and data aware controls.

The following sections describe each of these sets of components.

TSessions Components

The Session component represents a connection to the BDE. Conceptually, it represents the user for the purpose of file and record locking. Every database application has its own session, and VCL applications create this session automatically. (The automatically created session component can be referenced by the instance variable named Session.) It is because of the session that two applications running on the same machine are seen by the BDE as two different users (different sessions equate to different users). Even if one user runs two copies of a given application, each copy will have a different session, and therefore will appear to the BDE as two different users.

There is a session component on the Data Access page of the component palette. One of the few times that you ever need to manually add additional session components to a single project is when that project is multi-threaded, and data needs to be accessed by more than one thread. Since each thread accesses data from a separate session, the BDE treats the threads as separate users. This provides a consistent mechanism for the resolution of record and table locking between threads.

TDatabase Components

The Database component provides a pointer to a directory (on a stand alone machine or an a LAN) or to a remote database server. Every database application will have at least one Database, and more than one if data in more than one directory and/or server is being accessed.

If you use a DataSet, but have not specifically associated it with a Database component, Delphi will create one Database component for each directory and/or sever being accessed. It will do this in response to an attempt to open the DataSet.

Unlike a Session component, which you really only have to place manually if you are creating multi-threaded database applications, you may want to manually place a Database component if you want to control access to a database. For example, if you want to define custom parameters for access to a database server, store a username and password, or explicitly control transactions, you will probably place at least one Database component in your application.

In a single-threaded application all Databases are associated with a single Session. However, many different DataSets can use a one Database.

Database components, like sessions, are globally available within your application. In other words, so long as your Database component appears on an auto-created form, or appears on the main form, it is available to all forms and data modules in the application, without the need for a corresponding uses clause statement.

TBDEDataSet Components

BDEDataSets, which include TTables, TQueries, and TStoredProc components, are associated with individual tables or SQL files. (In the case of TQuery and TStoredProc components, it is possible for these entities to be associated with more than one, or even no underlying data file. This is the exception, however, rather than the rule).  

Each BDEDataSet has its own cursor. Furthermore, you use the methods and properties of the BDEDataSets to get information about and to control a table. For example, using a BDEDataSet you can read data from a table, modify existing records, insert new records, or delete records.

From a developer standpoint, the BDEDataSets greatly simplify access to data. Specifically, they encapsulate calls to the BDE from within an easy to use interface. For example, if you wanted to create a pointer to a record using BDE API calls, you would have to call at least five different BDE API functions. By comparison, calling a Table's Open method performs exactly the same operation.

TDataSource Components

A DataSource is a component that provides for the interaction between DataSets (which includes all BDEDataSets as well as non-BDE DataSets like TADOTable) and data controls, which are the user interface elements for data access. This interaction is two-way. For example, if a user tries to type a character into a DBGrid, the DBGrid will first inquire through the DataSource whether the Table is in a state that permits editing (such as dsEdit or dsInsert). If the Table is not, the DataSource will attempt to place the Table in the dsEdit state. If successful, the DataSource will accept the data entered into the DBGrid. Likewise, when a Table pointer is changed to point to a new record, the DataSet informs the DataSource, which instructs the DBGrid to repaint itself.

Beginning VCL database developers often wonder why the database functionality represented by the DataSet and DataSource components are not combined into a single component. The answer is that, from a component design standpoint, it is often better to create two simple components that perform well-defined tasks, as opposed to a single more complex single component. The simpler components are easier to debug and use.

Since DataSource components are used for primarily for managing the interaction between data controls and DataSets, you rarely need to use a DataSource for data access that is entirely programmatic. In other words, if you have no user interface, you probably do not need a DataSource.

The Data Control Components

Data control components, those that appear on the Data Controls page of the component palette, are used to either display and or manipulate data being pointed to by a DataSet. For example, using either a DBGrid or DBEdit, a user can both see and edit data. Using a DBNavigator, a user can navigate a DataSet, as well as insert and delete records.

Delphi's data access and data control components play an important role in rapid application development (RAD). Specifically, rather than having to explicitly program how a user interacts with data, including fetching, detecting changes to, and saving data, you can simply place the appropriate data controls and data access components onto your form and most all of this interaction is taken care of for you.

Data and Data Files

In earlier versions of Delphi and C++ Builder the classes being discussed here were referred to as DataSet classes. In the current versions the DataSet class also include the TClientDataSet class as well as a number of other non-BDE DataSets in Delphi 5. (For a detailed discussion of TClientDataSet as well as InterBase Direct and ADO Components, please see one of the other papers in this conference.) The distinction is that the BDEDataSet classes all make use of the BDE, while the non-BDE DataSets do not, instead relying on other mechanisms. While Tables, Queries, StoredProcs, ClientDataSets, IBTable, ADOTable are all DataSet descendents, only the first three are BDEDataSet descendents.

The following discussion focuses on basic configuration and use of these classes. However, before discussing how to access data using BDEDataSet classes, it is worth taking a moment to consider the origin of the data that these components access.

Tables, Data, and Files

The data that is used in the examples that normally appear in this column can be found in Program FilesCommon FilesBorland SharedData subdirectory. These database files where installed when you installed either Delphi or C++ Builder. While these files are perfect for a demonstration, you will use other files when creating your own databases. This raises a fundamental question: Where do you get your own data files?

There are a variety of answers to this question, and they range from the simple to the complex. In some instances, you are provided with existing files that contain the data that will be employed by the application. This is often the case when you are building an application in Delphi based on an existing one (such as creating a Windows version of an old DOS or main frame application). This situation is even easier when the BDE directly supports the file type of the tables you are provided, such as when you are creating a VCL-based application based on a pre-existing Paradox for Windows application or dBASE application. All but the earliest Paradox and dBASE files can be used in VCL applications without modification.

If you are provided with files that are in a format not supported directly by the BDE you must first convert these files into a supported format. How difficult the conversion will be depends on the file type you are converting from. For example, if the data is in a delimited ASCII format, a number of tools exist that will permit you to convert these files to dBASE, or some other useful file format. In a worse-case scenario, you will need to manually write a data translation program. (This can be done in using standard I/O functions.) Note, however, that unsupported file formats are not common, due to the BDE's support for files for which ODBC (Open Database Connectivity) drivers exists.

If data does not already exist, you will be responsible for creating the tables yourself. If the application specification includes the specifications for the tables, you can use the Database Desktop that ships with Delphi and C++ Builder to create the table structures. (A table structure defines the number of fields (columns) in the table, as well as their names and data types.)

If no definition of the tables required by the application exists, you will either need to define these yourself, or enlist an experience database developer or database analyst to help you do so. Creating a specification involves identifying the data that needs to be stored, and designing table structures that permit this data to be stored correctly and efficiently. There are a number of books on the market that describe this process.

Basic BDEDataSet Configuration

Regardless of which BDEDataSet component you use, you must provide it with two pieces of information, at a minimum. The first is the type of driver to use for data access. The BDE uses this driver to read and write the data, in the case of local databases. For remote database servers, the driver defines how the BDE communicates with the database server.

The second piece of information required for data access is a reference to the data itself. For example, when using local Paradox tables, a BDEDataSet must know which table to access. Likewise, when using a remote server, it is necessary to indicate which tables, views, or stored procedure you want to use.

Each of the BDEDataSets have different properties for defining these two basic pieces of data. In the following sections these are describe separately.

Accessing Data Using Tables

A Table component is the most general and easiest to use of the DBDataSets. It can be used to access data in any BDE-supported format. For example, you can use a Table component to read and write from Paradox tables as well as from Oracle databases.

The basic access to data with a Table component involves a single property, at a minimum, but more often two properties. When you are accessing local tables, you merely need to define the TableName property. This property can contain either the name of a Paradox or dBASE table, or it can contain the fully-qualified DOS path and table name. Including the DOS path in the TableName property is required if you want to activate the table at design time. If you do not need design-time access to data, you can enter only the table name and the BDE will look for that table in the same directory as your project's EXE.

Normally the BDE will determine which driver to use to access your data based on the DatabaseName property. However, when you do not assign a value to this property, using the TableName property alone, the BDE will determine which driver to use (specifically, which of several available BDE DLLs to use) based on the file extension of the table name. If the file extension is omitted, it will choose a driver based on the TableType property.

As mentioned in the preceding paragraph, the BDE can determine which driver to use based on the DatabaseName property. This is the second of the two properties typically used to configure a Table component (again, the first being TableName). You assign to this property one of three possible values. First, you can assign any configured ODBC data source name. (A configured ODBC driver includes a data source location.) When you do so, the BDE will use the ODBC socket to communicate with the specified driver, converting all instructions to SQL statements. (ODBC is a standard based on the OpenSQL call level interface.)

The second type of value that you can assign to the DatabaseName property is the name of a Database configured using the BDE Administrator. A configured database is often referred to as a global alias, due to the fact that it can be used by any BDE-aware application. Among other things, a configured global alias defines both the driver as well data location.

The third type of value that you can assign to the DatabaseName property is the value of a DatabaseName property of a Database component within your project. Such a value is referred to as a local alias, since it is available only within the project in which the Database component appears. In order to use a local alias, the Database component must be configured, which involves defining the driver type and data location. Configuring a Database component will be covered in a future DBNavigator.

Note that this example makes use of the Local InterBase Server (LIBS). If you currently do not have LIBS installed, you must do so first before continuing with this example.

To demonstrate the configuration of a Table, use the following steps:

  1. Create a new project. Add to it one DBNavigator, one DBGrid, one DataSource, and one Table.
  2. Set the DBNavigator's Align property to alTop, and the DBGrid's Align property to alClient. Next, set the DataSource property of both the DBNavigator and DBGrid to DataSource1.
  3. Set the DataSource's DataSet property to Table1.
  4. Now it is time to configure the Table. Begin by setting its DatabaseName property to IBLOCAL. This is a global alias that was created during installation. Next, set the Table's TableName property to CUSTOMER (at this point you will have to enter the InterBase password. Type masterkey.). If you now set the Table's Active property to true, you will see the design-time view of your data in the DBGrid, as shown in Figure 4. In addition, it ensures that the Table is opened when the form is created at runtime.

Figure 4. Accessing data using a Table component

If design-time access to your data is not needed, you can leave the Table's Active property set to False, and add the following code to the form's OnCreate event handler:

Table1.Open;  //Delphi

or

Table1->Open(); //C++ Builder

Calling the Table's Open method has the same effect as setting its Active property to True.

Press F9 to compile and run the form. Your screen should now look like that shown in Figure 5.

Figure 5. Data displayed in a DBGrid on a running form

Basic Query Configuration

You use the Query component to execute SQL (Structured Query Language) statements against one or more databases. These SQL statements can either be DDL (Data Definition Language) statements such as CREATE TABLE, ALTER INDEX, and so forth, or they can be DML (Data Manipulation Language) statements, such as SELECT, UPDATE, and DELETE. The most common statement, however, is the SELECT statement, which produces a view similar to that available using a Table component.

The minimum configuration of a query involves setting its SQL property. For example, the following SQL statement returns all fields and records (columns and rows) from the CUSTOMER table in the directory pointed to by the DBDEMOS alias:

SELECT * FROM ":IBLOCAL:CUSTOMER"

Alternatively, if you set the DatabaseName property of the Query to IBLOCAL, you can omit the alias name and quotation marks from the SQL statement. For example, if you set DatabaseName to DBDEMOS, the following SQL statement is equivalent to the preceding one:

SELECT * FROM CUSTOMER

After you set the SQL property, and optionally the DatabaseName property, setting the Active property to True causes the query to be executed. (Queries that do not return a result set, such as DELETE queries, are executed by calling ExecSQL.)

If your query uses a SELECT SQL statement, it is sometimes possible to edit the result set. Two conditions must exist in order to edit the result set of a query. The first is that the queries RequestLive property must be set to True. Second, the result set must contains records from a single table, and there must be a one-to-one correspondence between the records in the result set and the queries table. In other words, if the query contains a join between two tables, or makes use of statements such as DISTINCT or SUM, the query cannot be edited (unless the query's CachedUpdates property is set to True. Cached updates will be discussed in detail in a future DBNavigator).

You can test whether a query is live (editable) or not by calling its CanModify method. If CanModify returns True, the BDE will issue a corresponding UPDATE, INSERT, or DELETE query against the Table you queried in response to posting changes, inserting records, or deleting records, respectively.

Preparing Queries

Before a DML query can be executed against a database server the query must be prepared. The process of preparation involves generating an execution plan on the remote database server. If you open or execute a query without preparing it, the BDE will explicitly prepare the query prior to execution, and unprepare it when the query is closed.

Note that this discussion of preparation does not apply to DDL statements. In general, you should never prepare DDL statements.

Preparing a query is a time-consuming process. If you need a query to execute very quickly when it is opened, you should explicitly prepare the query in advance. One common event used for performing this operation is the OnCreate event handler for the form or data module on which the query appears. When you explicitly prepare a query, the BDE does not unprepare it for you. Instead, you must explicitly call Unprepare to release the resources on the database server. This can easily be done from the OnClose event handler for the form or data module.

Explicitly calling Prepare is particularly important when you are working with parameterized queries. A parameterized query is one that includes one or more parameters. A parameter is a label, similar to a variable, which must be assigned data prior to the query's execution. A parameterized query does not need to be re-prepared each time the parameter is changed. This permits you to execute a parameterized query repeatedly, without the overhead of preparation. However, if you fail to explicitly call Prepare on a parameterized query, the BDE will prepare and unprepare the query each time you open and close the query (you must close a query before changing its parameter).

Note that explicit calls to prepare a query have no influence when you include the alias in the SQL statement. When the SQL statement includes alias names the VCL will always prepare and unprepare the query, regardless of any explicit calls you make.

A Query Example

Use the following code to demonstrate the use of a Query component. This example makes use of the Local InterBase Server (LIBS). If you are using Delphi 3, note that installing LIBS is a separate process from the installation of Delphi. If you currently do not have LIBS installed, you must do so first before continuing with this example. Make sure to restart your computer after installing LIBS, as it is automatically loaded when you boot your system. The code in this example is Object Pascal. Refer to the code disk for a C++ Builder example

(If you are using Delphi Standard Edition, you do not have LIBS. Still, you can following along with this example using a local Paradox table, but the query will be executed by the BDE, rather than a remote database server. In order to use a Paradox table, set the Querys DatabaseName property to DBDEMOS in step 4, instead of IBLOCAL.)

  1. Create a new project. Add to it one DBNavigator, one DBGrid, one DataSource, and one Query.
  2. Set the DBNavigator's Align property to alTop, and the DBGrid's Align property to alClient. Next, set the DataSource property of both the DBNavigator and DBGrid to DataSource1.
  3. Set the DataSource's DataSet property to Query1.
  4. Now you are ready to configure the Query. Set its DatabaseName property to IBLOCAL, and its SQL property to SELECT * FROM CUSTOMER. Next, set the RequestLive property to True.
  5. You should now add code to control the Query's preparation. Add an OnCreate handler to the form and add to it the following code:
  6. If Query1.Active then Query1.Close;
    Query1.Prepare;
    Query1.Open;
  7. Finally, create an OnClose event handler for the form and add to it the following code:
  8. If Query1.Active then Query1.Close;
    Query1.Unprepare;
  9. Press F9 to run the form. Once the form is created, the query is prepared and then executed. Executing the query requires access to the server. Since this access requires a password, a password dialog box is displayed. Enter the password masterkey in the Password field of the Database Login dialog box (this password is case-sensitive). Once you have accepted the password dialog box your form is displayed, as shown in Figure 6.

Figure 6. A live query result set can be edited like a Table

Tables Versus Queries

Since both Queries and Tables can be used interchangably to edit data, an obvious question is which one of these components should you use in your applications. Fortunately, in most cases the answer to this question is somewhat straight-forward. When accessing local Paradox and dBASE tables, a Table component generally provides for faster access. On the other hand, when you are using data on a remote database server, you will typically get better performance if you use a Query component.

Keep in mind, however, that this is a general rule of thumb, which means that it may not apply in all situations. If performance is your primary concern, it would be a good idea to actually test the relative performance for your particular data. On the other hand, you might consider using a Table with a remote database if you are more comfortable with having the BDE generate your SQL, rather than writing it manually. In fact, if your database is indexed properly, and you make use of Table methods that leverage indexes, such as Locate, FindKey, FindNearest, and SetRange, a Table components performance against remote database servers can be more than satisfactory. Furthermore, since the BDE performs caching, even normally time-consuming tasks such as navigating a tables records one at a time is extremely fast even with remote tables when the number of records are few (several hundred or fewer). Observations like this serve to emphasize the importance of doing performance comparisons when performance is of the highest importance.

Using Stored Procedures

Unlike Table and Query components, which can be used with any database (local or remote), StoredProc components can only be used with remote database servers. Stored procedures are a pre-compiled subroutine that resides on the server. A stored procedure can consist solely of SQL statements, but can also include additional statements defined by the servers compiler. For example, stored procedure can include control structures to provide program logic on the server.

There are two primary advantages of stored procedures. The first is that, since they reside on the server, they can be used by two or more applications in order to consistently perform a particular operation. For example, a stored procedure to add a new employee to a database ensures that all applications that use it insert a new employee correctly.

The second advantage, and the reason why stored procedures are so attractive, is that they provide the most efficient means of data manipulation possible. Stored procedures can be executed without having to send lengthy SQL statements to the server. In addition, since stored procedures are pre-compiled, they can be executed with less overhead than an equivalent SQL query. Finally, since stored procedures can include statements beyond SQL, they can be used to leverage the strengths of the database server on which they are defined.

The VCL permits you to execute stored procedures, but you cannot write stored procedures using Delphi or C++ Builder. Instead, you use the tools associated with your database server to write and compile your stored procedures. Consequently, it goes without saying that you can create stored procedures only if you have sufficient access rights to the server on which your data resides.

There are two general types of stored procedures, those that return a cursor to a result set (similar to a SELECT query), and those that do not. Stored procedures that do not return a result set may perform some operation on the server, such as emptying a table, or they may return single values by means of output parameters. In fact, both those stored procedures that return datasets, as well as those that do not, can make use of parameters that permit you to send information to the stored procedure. For example, a stored procedure used to archive old records may be passed a date which it will use to determine which records are old and which are not.

Whether a stored procedure returns a dataset or not may influence which component you use to execute the stored procedure. For example, when executing a stored procedure on an InterBase server you can use a StoredProc component only if the stored procedure does not return a result set. You must use a Query component for those InterBase stored procedures that return a result set. On the other hand, if you are using stored procedures on an MS SQL Server, you can always use the StoredProc component, regardless of whether the routine returns a result set or not.

Preparing Stored Procedures

Like queries, stored procedures must be prepared prior to their execution. In fact, you can apply the same principles concerning the preparation of queries to stored procedures. Specifically, if you do not explicitly prepare a stored procedure, Delphi will do it for your. Furthermore, stored procedures prepared automatically are also unprepared automatically when they are closed. However, if you explicitly prepare a stored procedure by calling its Prepare method, the BDE will not unprepare it - meaning that you will be responsible for also calling the StoredProcs UnPrepare method. As a result, it is generally considered a good idea to explicitly prepare a stored procedure that is called repeatedly, explicitly calling its UnPrepare method it once you no longer need it.

Stored Procedure Example 1

This first example demonstrates how to execute a stored procedure that includes both input and output parameters, but does not return a result set. For this purpose we will use the stored procedure named MAIL_LABEL, which is available in the EMPLOYEE.GDB database that is installed along with LIBS. Consequently, in order to follow along with this example you must have LIBS installed. You cannot execute stored procedures with local tables.

  1. Begin by creating a new project. Place onto it two Panel components. Set the Caption property of both panels to an empty string. Next, set the Align property of one of the panels to alRight, and the Align property of the other to alClient.
  2. Place into the Panel aligned to client one DBNavigator and one DBGrid. Align the DBNavigator to alTop and the DBGrid to alClient.
  3. Next, into the Panel aligned to the right place six Label components. Adjust their position so that they are align left with with respect to one and other, and are spaced equally. Their positioning should resemble the lines of a correctly addressed letter. Your form should look something like that shown in Figure 7.
  4. Now add to the form one DataSource component, one Query, and one StoredProc. Set the DataSet property of DataSource1 to Query1.
  5. Select Query1 and set its DatabaseName property to IBLOCAL, and its SQL property to SELECT * FROM CUSTOMER.
  6. Select StroredProc1 and set its DatabaseName property to IBLOCAL, and its StoredProcName property to MAIL_LABEL.
  7. Select both the DBNavigator and the DBGrid, and set their DataSource property to DataSource1.
  8. Its now time to add code to control the preparation of the stored procedure. (In this case we will not prepare the query, since it will be executed only once.) Select Form1 in the Object Inspector, and double-click the space that appears to the right of the OnCreate event handler on the Events page. Enter the following code into the method generated by Delphi:
  9. StoredProc1->Prepare();
    Query1->Open();

    This code prepares the stored procedure and then opens (executes) the query.

  10. Since we explicitly prepared the stored procedure, we must also unprepare it. With Form1 still selected, double-click the OnClose event property in the Object Inspector, and enter the following code:
  11. StoredProc1->UnPrepare();
  12. The execution of the stored procedure will be performed each time the user navigates to a new record in the query. This can be achieved by adding the following code to the OnDataChange event handler of DataSoure1. Select DataSource1, and then double-click the OnDataChange event property in the Object Inspector. Add the following code to the generated method:
  13. StoredProc1->ParamByName("CUST_NO")->AsString =
      Query1->FieldByName("CUST_NO")->AsString;
    StoredProc1->ExecProc();
    Label1->Caption = StoredProc1->ParamByName("Line1")->AsString;
    Label2->Caption = StoredProc1->ParamByName("Line2")->AsString;
    Label3->Caption = StoredProc1->ParamByName("Line3")->AsString;
    Label4->Caption = StoredProc1->ParamByName("Line4")->AsString;
    Label5->Caption = StoredProc1->ParamByName("Line5")->AsString;
    Label6->Caption = StoredProc1->ParamByName("Line6")->AsString;

    This stored procedure has seven parameters. The parameter CUST_NO is an input parameter, and it must be assigned a value before executing the stored procedure. The parameters named LINE1, LINE2, and so on, are output parameters, and are assigned a value as a result of the execution of the stored procedure.

  14. Now run the project. Enter the password masterkey when prompted. Once the project is running, your screen should look something like that in Figure 8. Now navigate between the various records in the DBGrid. Notice that the mailing label represented by the Label components is updated each time you arrive on a new record.

Figure 7. The Stored procedure example under construction

Figure 8. The mailing label is formatted by a stored procedure on the Local InterBase Server

Stored Procedure Example 2

This next example demonstrates how to execute an InterBase stored that returns a result set. As mentioned earlier, this involves the use of a Query component.

  1. Create a new project. Add to the main form one DBNavigator, one DBGrid, one DataSource, and one Query.
  2. Set the Align property of the DBNavigator to alTop, and the Align property of the DBGrid to alClient. Set the DataSource property for both the DBNavigator and DBGrid to DataSource1.
  3. Select DataSource1 and set its DataSet property to Query1.
  4. Now select Query1 and set its DatabaseName property to IBLOCAL and its SQL property to SELECT * FROM ORG_CHART.
  5. Finally, set the Querys Active property to True. This causes the SQL statement to execute, producing a call to the ORG_CHART stored procedure. Your main form should look like that in Figure 9.

Figure 9. The result set returned by a stored procedure on the Local InterBase Server is accessed using a Query component.

If you want, you can run the project, but it will result in a display similar to the one you see at design time.

Using Database Components

As you may recall from the discussion earlier in this paper, a Database component is responsible for providing BDEDataSet components with information about the nature and location of your data. (TTable, TQuery, and TStoredProc objects are all BDEDataSet components.) Specifically, a Database stores the location of the data (whether it is local or remote), and what driver to use to access the data. It is also responsible for holding configuration information pertaining to the data access. For example, a Database component can define parameters that control how data is accessed and updated.

There is another, equally important role that the Database plays. The database component represents your connection to a remote server in a client/server application. Using the TDatabase methods you can connect to or disconnect from a server, start, commit, and rollback transactions, as well as store schema information about the files of your database. (Scheme information includes data about your database, including the tables, fields, indexes, and so forth.)

It is worth noting that a Database plays a similar role with respect to local data - those file server-based applications that make use of Paradox or dBASE tables. However, these tables are controlled directly by the BDE (Borland Database Engine). Consequently, no true login connection is required, all transactions are controlled directly by the BDE, and local databases do not store schema information that needs to be read by the database. (Since the BDE controls all access to local tables, schema information is always up-to-date, meaning that it does not need to be explicitly read from a remote server.)

Global versus Local Aliases

You control which Database a particular BDEDataSet uses by assigning an alias to the BDEDataSets DatabaseName property. An alias is either the DatabaseName property of a Database component, or the name of a configured database within the BDE configuration file. (You modify your BDE configuration using the BDE Administrator.) An alias that references a database configuration from BDE is referred to as a global alias, and one that references the DatabaseName property of a Database component is referred to as a local alias.

Technically speaking, there is a third value that can be assigned to a BDEDataSets DatabaseName property. If your data is stored in local tables, you can assign the path of your data files to the DatabaseName property. While this value is not a true alias, it bears more similarity to global aliases than local aliases, in that such a value uses the parameters defined on the Configuration page of the BDE Administrator based on the data type of file you are accessing.

A global alias gets its name from the fact that it is available to any application that can use the BDE. For example, any developer using Delphi, C++ Builder, or Data Gateway for Java can use a global alias for the access to data. DBDEMOS and IBLOCAL are examples of global aliases that are configured during Delphis installation, and which refer to sample data files that are used by numerous Delphi sample projects.

An application that uses a global alias is configurable outside of the program logic in your application. Specifically, it is possible to write an application that is completely unaware of the details of the data location, driver type, or connection parameters. Such applications are easily scaleable, meaning that you can change the data location, type, and parameters without needing to re-compile your application. For example, an application that makes use of a global alias can be designed and tested on a stand-alone machine, yet be deployed in a client/server environment without re-compiling the application. All that is necessary is to update the database configuration for the alias using the BDE Administrator.

Local aliases, in contrast to global aliases, are available only to the application in which the corresponding Database component appears. Local aliases are created by adding a Database component to one of your forms or data modules, and then setting the Databases properties to identify the driver and data access parameters.

Normally you add a Database component that defines a local alias at design time. However, it is perfectly valid, although typically more work, to add one programmatically at runtime. This can be done by calling the TDatabase classs constructor, and then configuring the properties of the Database object the constructor returns.

Once a Database has been created, the DatabaseName property of the Database component is visible to all objects within the application. Specifically, the DatabaseName property is stored in the global name space, making this alias visible even to code that appears in units that do not use the unit in which the Database component is referenced (either as a variable or a member field of type declaration).

While a global alias has the advantage of being configurable outside of your application, local aliases also have an important advantage. Using a local alias your code can control all aspects of the connection. For example, your code can be written to define the Database driver and access control parameters based on information determined at runtime. For instance, your application could read an INI file on a shared network drive in order to determine the location of the data, or it could determine the location of the data by testing the location of the applications executable (using the Application.ExeName method).

These two alias types, global aliases and local aliases, are not mutually exclusive. Indeed, it is not uncommon for the data access information used by a BDEDataSet to come from both alias types at the same time. Specifically, a Database (whose DatabaseName property constitutes a local alias) can be configured to read its initial configuration information from a global alias. This is often done for one of two reasons. A local Database can selectively override parameters stored in a global alias. This permits your application to leverage the configuration flexibility provided through the BDE configuration, while still maintaining final control over particular parameters. A very simple case of this involves using the parameters stored in a global alias, and then adding or changing one or more parameters, such as password, in the Database. (Note that a password cannot be stored in a BDE configuration file.)

The second reason for combining global and local aliases is to provide a component for controlling transactions and server connections. Such a local Database may use all of the configuration information from a global alias, while providing a convenient component for starting, commiting, and rolling back transactions.

Configuring global aliases using the BDE Administrator is beyond the scope of this paper. For information on configuring global aliases, please refer to the BDE Administrators online help. Configuring local Database components, on the other hand, is the topic of the remainder of this paper.

Databases versus Aliases

It is important to make a clear distinction between a Database component and an alias. A Database component is an instance of the TDatabase class. Every BDEDataSet requires a Database component to define and control its access to data. An alias, by comparison, defines either an existing Database component, or a set of parameters that will be applied to an automatically created Database component.

When you attempt to activate a BDEDataSet, it first determines whether or not it is connected to a Database (via the DatabaseName property). If the Database name property contains a local alias (the DatabaseName property of an existing Database), the BDEDataSet first checks whether or not this Database is open. If the Database is open, then the BDEDataSet attempts to open itself. If the Database is not yet opened, the BDEDataSet must first open the Database before opening itself.

When the very first BDEDataSet making use of a given global alias attempts to open, it will begin by creating an instance of the TDatabase class. The parameters used for this Database are drawn from the BDE configuration, based on the global alias name. As each additional BDEDataSet that uses the same global alias attempts to open, each will note the Database created by the first BDEDataSet to open, and will attach to that Database.

Configuring a Local Alias

As mentioned earlier, a local alias is one associated with a Database component that appears in your application. Although it does not matter how this component is created, the typical technique is to add a Database to a form or data module and then use the Database Editor dialog box to configure the Database. This technique provides for the automatic creation of the Database component (as part of the creation of the form or data module on which it appears), as well as simplifying the process of configuring a BDEDataSet to use the Database.

This process is demonstrated in the following steps.

  1. Begin by creating a new project.
  2. Add a Data Module by selecting File | New from Delphis main menu, and then double-clicking the Data Module Wizard on the New page of the Object Repository.
  3. Add to this Data Module a Database component from the Data Access page of the component palette.
  4. Now display the Database Editor for this Database by either double-clicking the Database, or by right-clicking the Database and selecting Database Editor. The Database Editor is shown in Figure 10.
  5. At Name enter temp. This value is the local alias name.
  6. Next, move to the Driver Name field (we are not going to use the Alias field in this example). Using the Driver Name drop-down list, select STANDARD. The STANDARD driver is used with both Paradox and dBASE tables.
  7. Next, click the Default button. Doing this loads the Parameter overrides listbox with default parameters based on the BDE configuration file. In this case, since a very simple local alias is being created, there are only three parameters: PATH, DEFAULT DRIVER, and ENABLE BCD (Binary Coded Decimal).
  8. Leave DEFAULT DRIVER and ENABLE BCD with their default values. For PATH, enter the fully-qualified directory path where the BDE stored its sample Paradox and dBASE tables. In Delphi 4 this path is C:Program FilesCommon FilesBorland SharedData. In all previous versions, it is the DemosData directory under the directory in which Delphi or C++ Builder was installed. For example, with Delphi 3 this path is C:Program FilesBorlandDelphi 3DemosData. When you are done your Database Editor should look like that shown in Figure 11.
  9. Next you want to make sure that the data module is created before the main form. To do this, select Project | Options to display the Project Options dialog box. From here drag the name of DataModule2 to the top position in the Auto-create forms list. Alternatively, you can simply edit the project DPR file, moving the call to CreateForm for the data module to the line before the call to the forms CreateForm. (Note that only data modules can appear before the main form in the Auto-create forms list. By definition, the main form is the first auto-created form.)
  10. Now continue with the building of your main form. Return to Form1 and add to it a DBNavigator and a DBGrid. Set the DBNavigators Align property to alTop, and the DBGrids Align property to alClient.
  11. Now add your data access components. Add to your form one DataSource and one Table. Set the DataSources DataSet property to Table1. Set the DataSource property of both the DBNavigator and the DBGrid to DataSource1.
  12. Finally we are ready to configure the Table to use the local alias. Set the Tables DatabaseName property to temp (this is the DatabaseName property of the Database you entered in step 5). Next set the Tables TableName property to customer.db. Finally, set the Tables Active property to True.

If you now run the application, your form will look like that shown in Figure 12.

 

Figure 10. The Database Editor

Figure 11. The Database Editor defining a local alias

Figure 12. A sample database application that uses a local alias

When you run this application the data module is created first, making the Database available. Then, the form is created which causes the creation of the objects that have been placed on it. Once these objects are created their properties are loaded, which include the Active property of the Table. Before the table can open, it locates the Database referenced using the local alias, and it attempts to open the Database. Once the Database is opened, the Table can then attempt to open itself. Once the Table is open, the DataSource informs the DBNavigator and the DBGrid to read the data and paint themselves appropriately.

Experienced VCL database developers who read the above description will no doubt be wondering why I did not place the DataSource and the Table on the Data Module along with the Database. The answer is that I wanted to demonstrate that the local alias was accessible from Table1s DatabaseName drop-down list even though the unit in which Form1 is defined does not use the unit in which the data module is defined. If we had placed the Table and/or the DataSource on the data module, Form1s unit would be required to use DataModule2s unit.

Controlling Database Parameters at Runtime

In the preceding example the path to the data was hard coded. As mentioned earlier in this article, it is possible to define the parameters of a Database at runtime. To demonstrate this, modify the example project you created by following the preceding steps. The following steps demonstrate this modification.

  1. Return to Form1. Set Table1s Active property to False.
  2. Now return to the Data Module. Double-click the Database to display its Database Editor. Click the Clear button to erase the parameters from the Parameters override listbox.
  3. Return again to Form1. Select Form1 in the Object Inspector. From the Events page, double-click the field next to the OnCreate event property to generate an OnCreate event handler for the form.
  4. Edit the OnCreate event handler to look like the following:
  5. procedure TForm1.FormCreate(Sender: TObject);
    begin
    DataModule2.Database1.Params.Clear;
    DataModule2.Database1.Params.Add('PATH=D:Program FilesCommon FilesBorland SharedData');
    DataModule2.Database1.Params.Add('DEFAULT DRIVER=PARADOX');
    DataModule2.Database1.Params.Add('ENABLE BCD=FALSE');
    Table1.Open;
    end;
  6. Finally, with Form1 selected, select File | Use Unit (or File | Include Unit Hdr in C++ Builder) to display the Use Unit dialog box. Select Unit2 from this list. Note that although the local alias temp is available to the Table without using unit2, the Database reference (Database1) is not available to unit1 unless it is using unit2.
  7. Press F9 to run this project. Again, your application should look like the one shown earlier in Figure 12.

Although the actual parameters added to the Databases Params property were hard coded in this example, it would have been perfectly acceptable to base these parameters on information determined at runtime.

Creating a Local Alias Based on a Global Alias

Earlier in this article you learned that local aliases can be based on global aliases. The following steps demonstrate how this is done.

  1. Begin by creating a new project.
  2. Add a Data Module by selecting File | New from Delphis main menu, and then double-clicking the Data Module Wizard on the New page of the Object Repository.
  3. Add to this Data Module a Database component from the Data Access page of the component palette.
  4. Now display the Database Editor for this Database by double-clicking the Database.
  5. At Name enter csdemo. Again, this value is the local alias name.
  6. Next, move to the Alias name field and select IBLOCAL. When you use the Alias name field, you are specifying that the parameters of your local alias will be based on the named global alias. If there are any parameters you want to add or override, you specify these using the Parameter overrides listbox.
  7. In this case, we will be adding the password to the local alias. To do this, enter the following line into the Parameter overrides listbox:
  8. PASSWORD=masterkey
  9. Since the password is now stored with the Database component, it is no longer necessary to prompt the user for the password. To disable the display of the Database Login dialog box, remove the check from the Login Prompt check box of the Database Editor. Your Database Editor should now look like that shown in Figure 13.
  10. The remaining steps of this example are identical to steps 9 through 12 in the example given under the section Configuring a Local Alias. Consequently, those steps are not repeated here. However, there is one difference. Instead of adding a Table, use a Query. To configure the Query set its DatabaseName property to csdemo, and its SQL property to the following SQL string:
  11. SELECT * FROM CUSTOMER
  12. If you now set the Querys Active property to True the query will execute and the returned records are displayed in the DBGrid. Now press F9 to run the project.

When you run this project you will notice that the main form is displayed without first asking your to enter the password. In this case, the local alias uses all parameters of IBLOCAL, which identify where the data is located and what driver to use. The Database, however, adds to this the password, which is used to establish a connection to the server when the Query attempts to execute.

Figure 13. Creating a local alias based on a global alias

The preceding example was provided as a demonstration of overriding global alias parameters with a local alias. However, it is rarely a wise idea to permit unchallanged access to a database server. Consequently, this technique is normally only used during development, where you would like to avoid having to enter the password each time you test your application. Before deploying such as application you should return to the Database Editor dialog box and remove the password parameter from the Parameter overrides listbox, and enable the Login Prompt checkbox.

Using Data Modules

A data module is a form-like container. Unlike a form, however, a data module is never visible to the user. Instead, its sole purpose is to hold one or more components that can be shared by other parts of your program. One of the most common uses for a data module is to hold data sets (including the TClientDataSet, which is available in the client/server editions of Delphi 3 and later), permitting two or more forms within the application to share the properties and methods defined for those data sets.

The alternative to using a data module is to place a different set of data set components on each form in your application. While there is nothing fundamentally wrong with this approach, it means that every form contains data set components that must be individually configured. If two or more forms need to display the same data or event handlers (for providing client-side data validation, for example), placing those data sets on a single data module that is shared by the two or more forms provides for easier development and maintenance.

But data sets are not the only components that can be used with data modules. In fact, a data module can hold any component that does not descend from TControl. This includes MainMenus, PopupMenus, OLEContainers, IBEventAlerters, Timers, as well as any components on the Data Access and Dialogs pages of the component palette, to name a few.

Just because a data module can hold a certain component, however, does not mean that it is necessarily a good idea to always place that type of component on a data module. For example, imagine that you place a MainMenu on a data module. Any event handlers for that menu would naturally be placed on that data module as well. As a result, any reference to the object variable Self from within those event handlers would refer to the data module, and not the form from which a particular menu item was selected. Such an arrangement would require complex code that would probably be difficult to maintain, thereby canceling out any advantage afforded by the data module.

When Should You Use a Data Module?

Data modules are a perfect solution for those situations where two or more forms, or other similar containers, need to share a common set of components. For example, if two forms need to share a common view of a given table, including any ranges, filters, sort orders, calculated fields, and so on, a data module provides an easy and effective means for permitting this sharing. This sharing, however, is not limited to single Tables either. There is not reason why two or more forms cannot share a multitude of data sets, dialogs, timers, and the like, placed on one or more data modules.

In fact, there are a number of situations where you must use a data module. For example, if you are using the MIDAS you must place your BDEDataSet components, as well as any provider components that you need, onto a remote data module. Remote data modules are special data modules that implement certain interfaces necessary for the cross-application communication that is required by MIDAS.

Another example where you are required to use a data module can be found with the Web Broker components. These components, also available in Delphi 3 and later client/server, as well as available separately from borland.com, make use of a WebModule (a TDataModule descendant). Using the WebModule you define the actions to which your web server extension can respond. (If you use a WebDispatch component, a web module is not necessary.)

When Should You Avoid Data Modules?

There are situations under which you should not use a data module, and then there are other situations where a data module can be used, but doing so unnecessarily complicates your applications.

The general rule of thumb is that you do not use a data module when there should be no sharing of a data set. The classic example of this is when you are creating reports that use Delphi data sets for their data. These data sets should never be shared. The reason for this is that VCL-based reporting tools must navigate a data set in order to print data. Imagine what can happen if one of these reports uses a data set on a data module, and that same data set is used by data-aware controls on a form. The user is viewing the form and then prints the data set. The next thing the user sees is their form scrolling frantically, as the report navigates the data set. Not only can this be confusing to the user, but is causes a catastrophic loss of performance for the report, since the data-aware controls in the user interface must be repainted as each record is navigated to.

What is interesting about the preceding example is that it represents a best-case scenario for data module sharing with reports. Imagine what happens to the report if the user is currently editing a record, and that record contains errors that prevent the cursor from leaving it. Imagine what would happen if the user prints two reports simultaneously, and both of those reports share a data module. They would be fighting for the control of the cursor, but the user may never know this. Clearly, reports should not share data set.

While reports provide a clear example where data set sharing is not acceptable, there are two other situations where data modules are generally not acceptable. The first is when you have one form that uses a completely unique view of data. That view either may involve a table that is never viewed from any other form, or a table that makes use of a range, filter, or sort order that is not used anywhere else in the application.

A second instance where data modules should typically be avoided is when you are writing multiple instance forms. A multiple instance form is one where more than one copy can be displayed simultaneously. Of course, part of such a design is that each instance displays a different record, or set or records, or different sort order, or some similar difference. Obviously, such forms cannot share a single data set. The easiest way to design a multiple instance form is to add the data set or data sets directly to the form. This ensures that each instance of the form has its own data set or sets, meaning that each form has its own cursor(s), and view(s), of the data.

For both of these last two examples it could be argued that a data module could still be used. For unique view forms, a data module can be used, just not shared. Likewise, with multiple instance forms, each instance of the form can be responsible for creating its own instance of a data module. However, using a data module in these cases unnecessarily complicates your application. Why use two containers (a form and a data module) when one will suffice. Since the primary benefit of data modules is simplicity, it seems absurd to use a data module when it increases complexity.

A final note about data modules is certainly in order here. By default, they are auto-created. If you always use data modules, and they are always auto-created, it is likely that all of your data sets will be opened when you start your application. This can result in long application start up times, and an unnecessary number of table locking resources being used. I once saw an application that had a data module that was auto-created, and it contained about 100 data sets. As you can imagine, one reason that the client asked me to look at this application in the first place was that they were unhappy with the load time.

The solution to problems caused by auto-created data modules is to remove them from the Auto-created forms list on the Project Options dialog. Once you do this, however, you must take responsibility for creating your data modules on-the-fly, prior to displaying a form that makes use of the components on the data module. This can be complicated, however. If one data module can be used by two or more form, each form must test for the pre-existence of the data module upon the form's creation. If the data module does not yet exist, it must be created. Releasing the data module, if this is desired, also requires more coding. Specifically, since one data module may be used by more than one form, it is not enough to simply free the data module when a form is closing. Instead, you must implement some form of reference counting for the data module, so that you release it only when the last form requiring it is being closed.

Using Non-BDE DataSets

As mentioned earlier, there are a number of examples where non-BDE datasets can be used. Delphi 5 has introduced several new pages of DataSet descendants that do not use the BDE. These are the ADO components and the InterBase Direct components.

ADO, which stands for ActiveX Data Objects, is a Microsoft technology for access data via ODBC drivers using OLE DB. OLE DB is a COM-based technology, requiring the installation of a number of ActiveX controls. These can be downloaded for free from www.microsoft.com/data.

ADO is an emerging standard for accessing data. By supporting ADO, Delphi 5 has once again demonstrated what Borland calls "co-opetition," a play on the words cooperation and competition. As a result, using Delphi you are assured of being able to build industry standard applications with the same ease with which you create BDE applications.

Like ADO, InterBase Direct components do not require the installation of the BDE. Instead, they call directly to the InterBase server.

Whether you use ADO components or InterBase Direct components, your data access controls can be used to access your data. Although these controls are not currently in C++ Builder, it seems reasonable to assume that they will find their way into the next version.

Summary

Borland's VCL has proven to be one of the most useful tools available today for creating database applications on the Windows platform. This paper has provided an introduction to the basics of Delphi database development. For more information, please see Delphi documentation and online help.

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a Houston-based company that provides database developers for application development and support. He is an award-winning, best-selling co-author of eighteen books, including Oracle JDeveloper 2 Starter Kit (Fall 1999, Oracle Press), Oracle JDeveloper 2 (1998, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), Delphi In Depth (1996, Osborne/McGraw-Hill), and Programming Paradox 5 for Windows (1995, Sybex). Cary is also Contributing Editor of Delphi Informant Magazine, where his column DBNavigator appears monthly. He is a popular speaker at conferences, workshops, and training seminars throughout North America and Europe, and has served on four of the Inprise/Borland conference advisory boards. Cary has a Ph.D. in Human Factors Psychology, specializing in human-computer interaction. You can reach him at cjensen@compuserve.com. The Jensen Data Systems Inc. Web site is http://www.jensendatasystems.com/.

Cary is available for onsite training when you have 6 or more people who need training in JBuilder, Delphi, or Oracle JDeveloper. For more information contact Jensen Data Systems at (281) 359-3311. For training in Europe contact Susan Bennett at +44 (0) 181 789-2250.


Server Response from: ETNASC04