Component Overview

The BDE components form a cooperative framework within the data-aware components of the VCL. And their design, being the first in the VCL, set the standard for the other, subsequent component sets, as you will see in subsequent chapters.

Component Architecture

BDE components fall into several categories:

Connection components: TSession , TDatabase
Non-SQL data set component: TTable
SQL data set component: TQuery

BDE components fit into the VCL through the standard data aware framework ”that is, data set components are linked to data sources, which are linked to data-aware controls.

Connection Components

The BDE components connect with the BDE through two linked components ” TSession and TDatabase .

TSession is the representation of a database user or connection, and every thread in a multithread program that wants to access a database needs one. Single-threaded programs automatically get a default TSession object called Session, but you can also drop a TSession object into a data module to use with a TDatabase object. Of course, each TSession object typically uses up a database connection, which can be a problem when your DBMS is licensed on a per connection basis.

If you use a TSession component, you must use a TDatabase component. As with TSession , C++Builder automatically provides a default TDatabase object (which uses the default TSession object). The default TDatabase , called Database, is the TDatabase component used by TTable and TQuery objects that do not specify a particular TDatabase object.

However, using a TDatabase component does not require use of a specific TSession (except in a multithreaded program). Unless otherwise specified, TDatabase components use the default TSession .

The TDatabase component typically refers to a DatabaseName or AliasName . The alias is established in the BDE Administrator, which can be run from the Windows Control Panel. A change to the alias can quickly switch from one database to another without any change to your program ”one of the particular advantages of the BDE.

To open a TSession , set Active to true or call Open () . To open a TDatabase , set Connected to true . To close the TSession , call Close () . To close the database, set Connected to false.

If the TDatabase is opened, its TSession will be opened. If the TSession is closed, all of its databases will be closed. If the Database is closed, any database components (such as TTable , described below) will be closed.

`TTable` ”Non-SQL Dataset

TTable enables you to work with a database without knowing anything about the SQL (Structured Query Language). All you need to do is specify the name of the database alias (or the TDatabase object that represents the database in the program ”take your pick), and the name of the table.

You can create persistent field objects for TTable by right clicking the component and picking the fields editor; then from the field editor, right click, and pick Add. Add all the fields or just those you want.

If the table in the database has an index, you can pick that index in the IndexName property of the TTable .

Naturally, a TTable can drive a TDBGrid or any other data-aware control, through a TDataSource component.

TTable can be opened with Open () or by setting Active to true . It is closed with Close () or by setting Active to false .

Adding and Editing Records in a `TTable`

TTable offers a very simple model for adding and changing records in a database table. To insert a record, use the Append () function followed by setting field values as needed; to edit the current record, use the Edit () function and follow by setting field values as needed.

For either of these activities, the completion of the addition or edit is caused by a call to Post(), which makes the change to the table effective.

For example:

 Table1->Append();  Table1->FieldByName("Somefield")->AsString = "string of some sort";  Table1->FieldByName(FieldNameInAVariable)->AsInteger = 3;  Table1->Post();

Transactions

The BDE supports transactions through the TDatabase component. Transactions have a variety of characteristics, but the most important is that they protect the records involved in a transaction from simultaneous changes, and they hide changes in progress, even across tables, until they are complete.

The TDatabase component offers functions to

StartTransaction () ” Any subsequent Edit() or Append() will participate in this transaction.
Commit () ” Make all the changes permanent and visible, and end the transaction.
Rollback () ” Undo all the changes which occurred under this transaction.

You can also set the degree to which changes made during a transaction are visible to other users of the database, through the TransIsolation property.

One of the powerful aspects of this feature is its capability to use transactions with desktop databases. Normally, transactions are only available for more powerful and expensive client/server databases.

Master/Detail Tables

Many databases contain master/detail tables ”for instance, a customer with orders, or orders with a record for each order line. C++Builder makes it very easy to establish such relationships between tables using the BDE.

The master table must have a TDataSource that is linked to it. Then, the detail table must reference that component from its DataSource property. It also needs to specify which field it contains and which fields from the master table need to match. For instance, an order table might have a CUSTOMER_ID field that needs to match the customer table ID field.

Both the master and the detail table linked TDataSource components can also be used to link to TDBGrid or other data-aware components.

`TQuery` ”SQL Dataset

SQL enables you to have much more control over your data set content than TTable . You can select a subset of fields, you can join multiple tables, and you can even join tables across different aliases, which can mean across different databases.

You code your SQL statement into the SQL property of the TQuery . As with TTable , you need to provide a DatabaseName , which can be an alias or the name of a TDatabase component.

Like TTable , TQuery can be opened with Open() or by setting Active to true . It can be closed with Close() or by setting Active to false .

In addition to allowing you to specify a selection from one or more tables in a database, you can use any other valid SQL statement in the SQL property ”including INSERT , UPDATE , DELETE , CREATE TABLE , and ALTER TABLE . However, you cannot use Active = true or Open() for those types of SQL ”instead, use the ExecSQL () function.

Finally, TQuery components, like TTable components, can participate in transactions.

`TQuery` Master/Detail

As with TTable , TQuery can participate in a master/detail relationship, but setting up that relationship is a little more complex. First you need the normal basis for the relationship ”a common field between the two tables. You need to have a TDataSource linked to the master TTable or TQuery (either can be used). But setting up the relationship in a TQuery detail component is a little more complex because you have to craft your SQL with a parameter string whose name matches the name of the field from the master. Typically, this might look as follows :

 SELECT * FROM ORDER WHERE CUSTOMER_ID = :ID

This assumes the master table is the previously mentioned customer table, with an ID field named ID .

The BDE takes care of matching the type of the parameter, so if the ID were a string, you would not have to write:

 SELECT * FROM ORDER WHERE CUSTOMER_ID = ':ID'

In fact, if you do, you will experience an error, in the sense that the query will try to find an order with a customer ID containing the string :ID ”something that is unlikely to be successful.

Parameters in General

Parameters are a powerful technique for driving TQuery components, and they are not limited to master/detail relationships. You can establish a parameter and set it from within your program. For instance, if you have a TEdit for entering an ID, such as

 Query1->ParamByName("ID")->AsInteger = Edit1->Text.ToInt();

will set the query to use that as the parameter value.

When you reset a parameter such as this, it automatically closes the query, so you need to follow this by setting Active = true or calling Open() .

In this case, of course, you do not need a TDataSource to drive the TQuery .

Parameters are also useful in SQL statements other than SELECT ; for instance, field values for an INSERT or UPDATE can be provided.

 Query1->SQL->Text = "INSERT INTO SOMETABLE(A,B,C) VALUES (:A,:B,:C)";  Query1->ParamByName("A")->AsInteger = 34;  Query1->ParamByName("B")->AsString "Something else";  Query1->ParamByName("C")->AsDouble = 564.32;  Query1->ExecSQL();

Constructing a Query in Your Program

Parameters only go so far ”they are not a general macro facility. For instance, they cannot appear as an attribute or table name, and cannot be used in any clause other than the WHERE clause. But, because the SQL property is available to your program as a TStringList , you can construct a query using values available in your program. For instance, instead of a parameter for the ID , you might use

 Query1->SQL->Text =     "SELECT * FROM ORDER WHERE CUSTOMER_ID = '" + IDEdit->Text + "'";

This can also be used for constructing a query for a variety of tables:

 Query1->SQL->Text =     "SELECT * FROM " + TableNameEdit->Text +     " WHERE CUSTOMER_ID = '" + IDEdit->Text + "'";

UpdateSQL and RequestLive

TQuery has one drawback ”unlike TTable , it is not a component, where direct updates of rows in the table are generally possible.

If a TQuery contains a simple SQL statement, the BDE can generally allow you to update through the TQuery , just as you would with a TTable . This requires you to set the TQuery property RequestLive to true. Setting RequestLive , however, is as the name suggests, a request and does not guarantee success.

One way to ensure that a query can be updated is to use TUpdateSQL components. A single TUpdateSQL component encapsulates the SQL needed to insert, update, and delete from the underlying tables of an SQL SELECT . That SQL is generated from the TQuery associated with the TUpdateSQL component. A double-click opens the TUpdateSQL editor, as shown in Figure 8.1.

Figure 8.1. The `TUpdateSQL` component editor.

graphics/08fig01.jpg

This editor enables you to specify key fields, even from joined tables, but a single TUpdateSQL component only is allowed to update a single table. If a TQuery component represents a SELECT statement that joins several tables, and you need to be able to update them all, you need a TUpdateSQL component for each.

Unfortunately, the TQuery component only has one UpdateObject property that can reference a single TUpdateSQL component. And, although TUpdateSQL has a DataSet property, it isn't available at design time for some reason. The answer is to programmatically assign that property at runtime. A good place to do that is in your Data Module's constructor because its body is executed after all the components have been loaded and their cross references assigned. Here's what the assignment might look like.

 UpdateQuery1->DataSet = Query1;

Cached Updates and `TUpdateSQL`

The VCL takes care of making sure the TUpdateSQL is called when it is needed. But, there are a couple of things you need to set up, as discussed in the next section.

Making TUpdateSQL work also requires that the TQuery have its CachedUpdates property set to true . Applying the cached updates forces the VCL to invoke the appropriate TUpdateSQL components.

Cached updates are a lot like transactions ”they hide the changes made to records until ApplyUpdates () is called. In most cases, however, you want to apply cached updates right away. This is best done in the AfterPost event of the TQuery component. Usually, you also want to follow ApplyUpdates() with CommitUpdates() to clear the various caches.

Heterogeneous Joins

The BDE provides a special feature that can make some very difficult integration projects easier ”the capability to join tables across databases.

This can only be done in TQuery , and requires a special notation in your SQL to indicate the alias name of each source table. For instance:

 SELECT * FROM  :SomeOracleDatabase:ORACLETABLE AS O,  :SomeXBaseDatabase:XBASETABLE AS X  WHERE O.ID = X.ID

This notation leverages the BDE alias in the names surrounded by ":". The BDE handles the differences between the DBMS and performs the join in its own way, without relying on the technology of the DBMS.

Top