One- and two-tiered applications include the logic that manipulates database information in the same application that implements the user interface. Because the data manipulation logic is not isolated in a separate tier, these types of applications are most appropriate when there are no other applications sharing the same database information. Even when other applications share the database information, these types of applications are appropriate if the database is very simple, and there are no data semantics that must duplicated by all applications that use the data.
You may want to start by writing a one- or two-tiered application, even when you intend to eventually scale up to a multi-tiered model as your needs increase. This approach lets you avoid having to develop data manipulation logic up front so that the application server can be available while you are writing the user interface. It also allows you to develop a simpler, cheaper prototype before investing in a large, multi-system development project. If you intend to eventually scale up to a multi-tiered application, you can isolate the data manipulation logic so that it is easy to move it to a middle tier at a later date.
Delphi provides support for two types of single-tiered applications: applications that use a local database (such as Paradox, dBase, Access, or Local Interbase) and flat-file database applications. Two-tiered applications use a driver to access a remote database.
The considerations when writing single-tired applications that use a local database and two-tiered applications are essentially the same, and depend primarily on the mechanism you choose to connect to the database. Delphi provides three different built-in mechanisms for these types of applications:
Flat file database applications are based on the support for client datasets included in MIDAS.DLL.
Because the data access components (and Borland Database Engine) handle the details of reading data, updating data, and navigating data, writing BDE-based two-tiered applications is essentially the same as writing BDE-based one-tiered applications.
When deploying BDE-based applications, you must include the BDE with your application. While this increases the size of the application and the complexity of deployment, the BDE can be shared with other BDE-based applications and provides many advantages. BDE-based applications allow you to use the powerful library of Borland Database Engine API calls. Even if you do not want to use the BDE API, writing BDE-based applications gives you support for the following features not available to other applications such as flat-file database application:
A BDE-based one- or two-tiered application includes
The relationships between these elements is illustrated in Figure 13.1:
Databases contain information stored in tables. They may also include tables of information about what is contained in the database, objects such as indexes that are used by tables, and SQL objects such as stored procedures. See "Connecting to databases" for more information about databases.
The Data Access page of the Component palette contains various dataset components that represent the tables contained in a database or logical tables constructed out of data stored in those database tables. See "Selecting what data to show" for more information about these dataset components. You must include a dataset component in your application to work with database information.
Each BDE-enabled dataset component on the Data Access page has a published DatabaseName property that specifies the database which contains the table or tables that hold the information in that dataset. When setting up your application, you must use this property to specify the database before you can bind the dataset to specific information contained in that database. What value you specify depends on whether
Sessions isolate data access operations such as database connections, and manage groups of databases. All use of the Borland Database Engine takes place in the context of a session. You can use sessions to specify configuration information that applies to all the databases in the session. This allows you to override the default behavior specified using the BDE administration tool.
If your application may be accessing the same database multiple times simultaneously, you must use multiple sessions to isolate these uses of the database. Failure to do so will disrupt the logic governing transactions on that database (including transactions created for you automatically). Applications risk simultaneous access when running concurrent queries or when using multiple threads. For more information about using multiple sessions, see "Managing multiple sessions".
Unless you need to use multiple sessions, you can use the default session.
The Borland Database Engine includes drivers to connect to different databases. The Standard version of Delphi includes only the drivers for local databases: Paradox, dBASE, FoxPro, and Access. With the Professional version, you also get an ODBC adapter that allows the BDE to use ODBC drivers. By supplying an ODBC driver, your application can use any ODBC-compliant database. Some versions also include drivers for remote database servers. Use the drivers installed with SQL Links to communicate with remote database servers such as InterBase, Oracle, Sybase, Informix, Microsoft SQL server, and DB2.
Note: The only difference between a BDE-based one-tiered application and a BDE-based two-tiered application is whether it uses local databases or remote database servers.
A transaction is a group of actions that must all be carried out successfully on one or more tables in a database before they are committed (made permanent). If one of the actions in the group fails, then all actions are rolled back (undone). By using transactions, you ensure that the database is not left in an inconsistent state when a problem occurs completing one of the actions that make up the transaction.
For example, in a banking application, transferring funds from one account to another is an operation you would want to protect with a transaction. If, after decrementing the balance in one account, an error occurred incrementing the balance in the other, you want to roll back the transaction so that the database still reflects the correct total balance.
By default, the BDE provides implicit transaction control for your applications. When an application is under implicit transaction control, a separate transaction is used for each record in a dataset that is written to the underlying database. Implicit transactions guarantee both a minimum of record update conflicts and a consistent view of the database. On the other hand, because each row of data written to a database takes place in its own transaction, implicit transaction control can lead to excessive network traffic and slower application performance. Also, implicit transaction control will not protect logical operations that span more than one record, such as the transfer of funds described previously.
If you explicitly control transactions, you can choose the most effective times to start, commit, and roll back your transactions. When you develop applications in a multi-user environment, particularly when your applications run against a remote SQL server, you should control transactions explicitly.
Note: You can also minimize the number of transactions you need by caching updates. For more information about cached updates, see "Working with cached updates."
There are two mutually exclusive ways to control transactions explicitly in a BDE-based database application:
One-tiered applications can't use passthrough SQL. You can use the database component to create explicit transactions for local databases. However, there are limitations to using local transactions. For more information on using local transactions, see "Using local transactions".
When writing two-tiered applications (which require SQL links), you can use either a database component or passthrough SQL to manage transactions. For more information about using passthrough SQL, see "Using passthrough SQL".
When you start a transaction, all subsequent statements that read from and write to the database occur in the context of that transaction. Each statement is considered part of a group. Changes must be successfully committed to the database, or every change made in the group must be undone.
Ideally, a transaction should only last as long as necessary. The longer a transaction is active, the more simultaneous users that access the database, and the more concurrent, simultaneous transactions that start and end during the lifetime of your transaction, the greater the likelihood that your transaction will conflict with another when you attempt to commit your changes.
When using a database component, you code a single transaction as follows:
DatabaseInterBase.StartTransaction;
DatabaseInterBase.Commit;
Commit is usually attempted in a try...except statement. That way, if a transaction cannot commit successfully, you can use the except block to handle the error and retry the operation or to roll back the transaction.
DatabaseInterBase.Rollback;
TransIsolation specifies the transaction isolation level for a database component's transactions. Transaction isolation level determines how a transaction interacts with other simultaneous transactions when they work with the same tables. In particular, it affects how much a transaction "sees" of other transactions' changes to a table.
The default setting for TransIsolation is tiReadCommitted. The following table summarizes possible values for TransIsolation and describes what they mean:
Database servers may support these isolation levels differently or not at all. If the requested isolation level is not supported by the server, the BDE uses the next highest isolation level. The actual isolation level used by some servers is shown in "Transaction isolation levels." For a detailed description of how each isolation level is implemented, see your server documentation.
Note: When using transactions with local Paradox, dBASE, Access, and FoxPro tables, set TransIsolation to tiDirtyRead instead of using the default value of tiReadCommitted. A BDE error is returned if TransIsolation is set to anything but tiDirtyRead for local tables.
If an application is using ODBC to interface with a server, the ODBC driver must also support the isolation level. For more information, see your ODBC driver documentation.
With passthrough SQL, you use a TQuery, TStoredProc, or TUpdateSQL component to send an SQL transaction control statement directly to a remote database server. The BDE does not process the SQL statement. Using passthrough SQL enables you to take direct advantage of the transaction controls offered by your server, especially when those controls are non-standard.
To use passthrough SQL to control a transaction, you must
Note: When SQLPASSTHRU MODE is NOT SHARED, you must use separate database components for datasets that pass SQL transaction statements to the server and datasets that do not.
The BDE supports local transactions against local Paradox, dBASE, Access, and FoxPro tables. From a coding perspective, there is no difference to you between a local transaction and a transaction against a remote database server.
When a transaction is started against a local table, updates performed against the table are logged. Each log record contains the old record buffer for a record. When a transaction is active, records that are updated are locked until the transaction is committed or rolled back. On rollback, old record buffers are applied against updated records to restore them to their pre-update states.
Local transactions are more limited than transactions against SQL servers or ODBC drivers. In particular, the following limitations apply to local transactions:
The Borland Database Engine provides support for caching updates. When you cache updates, your application retrieves data from a database, makes all changes to a local, cached copy of the data, and applies the cached changes to the dataset as a unit. Cached updates are applied to the database in a single transaction.
Caching updates can minimize transaction times and reduce network traffic. However, cached data is local to your application and is not under transaction control. This means that while you are working on your local, in-memory, copy of the data, other applications can be changing the data in the underlying database table. They also can't see any changes you make until you apply the cached updates. Because of this, cached updates may not be appropriate for applications that work with volatile data, as you may create or encounter too many conflicts when trying to merge your changes into the database.
You can tell BDE-enabled datasets to cache updates using the CachedUpdates property. When the changes are complete, they can be applied by the dataset component, by the database component, or by a special update object. When changes can't be applied to the database without additional processing (for example, when working with a joined query), you must use the OnUpdateRecord event to write changes to each table that makes up the joined view.
For more information on caching updates, see "Working with cached updates".
Note: If you are caching updates, you may want to consider moving to a multitiered model to have greater control over the application of updates. For more information about the multitiered model, see "Creating multi-tiered applications".
In BDE-based applications, you can use the TTable component to create new database tables and to add indexes to existing tables.
You can create tables either at design time, in the Forms Designer, or at runtime. To create a table, you must specify the fields in the table using the FieldDefs property, add any indexes using the IndexDefs property, and call the CreateTable method (or select the Create Table command from the table's context menu). For more detailed instructions on creating tables, see "Creating a table".
Note: When creating Oracle8 tables, you can't create object fields (ADT fields, array fields, reference fields, and dataset fields).
If you want to restructure a table at runtime (other than by adding indexes), you must use the BDE API DbiDoRestructure. You can add indexes to an existing table using the AddIndex method of TTable.
Note: At design time, you can use the Database Desktop to create and restructure Paradox and dBASE tables. To create and restructure tables on remote servers, use the SQL Explorer and restructure the table using SQL.
Delphi applications that use the ADO components for data access can be either one- or two-tier. Which category an application falls under is predicated on the database type used. For instance, using ADO to access a Microsoft SQL Server database will always be a two-tier application because SQL Server is an SQL database system. SQL database systems are typically located on a dedicated SQL server. On the other hand, an application that uses ADO to access some local database type, like dBASE or FoxPro, will always be a one-tier application.
There are four major areas involved in database access using ADO and the Delphi ADO components. These areas of concern are the same regardless of whether the application is one- or two-tier. These five concerns are:
An ADO-based application includes the following functional areas
The ADO layer of an ADO-based Delphi application consists of Microsoft ADO 2.1, an OLE DB provider or ODBC driver for the data store access, client software for the specific database system used (in the case of SQL databases), a database back-end system accessible to the application (for SQL database systems), and a database. All of these external entities must be present and accessible to the ADO-based application for it to be fully functional.
The ADO page of the Component Palette contains all of the components necessary for connecting to databases and for accessing the tables in them.
All of the metadata objects an ADO-based application are contained in the database accessed through the ADO data store. To access these objects or the data stored in them, an application must first connect to the data store. See "Connecting to ADO data stores" for information on connecting to data stores.
The data of a database is stored in one or more tables. You must include at least one ADO dataset component (TADODataSet, TADOQuery, and so on) in your application to work with the data stored in a database's tables. See "Retrieving data" and "Using ADO datasets" for more information on using ADO dataset components to access data in tables.
An ADO-based application Delphi uses ADO 2.1 to interact with an OLE DB provider to connect to a data store and access its data. One of the things a data store can represent is a database. An ADO-based application requires that ADO 2.1 be installed on the client computer. ADO and OLE DB is supplied by Microsoft and installed with Windows.
The provider can represent one of a number of types of access, from native OLE DB drivers to ODBC drivers. These drivers must also be installed on the client computer. OLE DB drivers for various database systems are supplied by the database vendor or by a third-party.
If the application uses an SQL database, such as Microsoft SQL Server or Oracle, the client software for that database system must also be installed on the client computer. Client software is supplied by the database vendor and installed from the database systems CD (or disk).
To connect the application with the data store, the ADO connection component is configured to use one of the available providers. Once the connection component is connected to the data store, dataset components can be associated with the connection component to access the tables in the database. See "Connecting to ADO data stores" for information on connecting to data stores.
In addition to providing an application's access to the database, the connection component encapsulates the ADO transaction processing capabilities.
Once an application has established a valid connection to a database, dataset components can be used to access data in the database. The ADO page of the Component Palette contains the ADO dataset components needed to access data from ADO data stores.
These components include TADODataSet, TADOTable, TADOQuery, and TADOStoredProc. All of these components are capable of retrieving data from ADO data stores, programmatically modifying the data, and presenting the data to an application's user for interactive use of the data. For more information on using the ADO dataset components to retrieve and modify data, see "Using ADO datasets".
To make the data accessed with an ADO dataset component visually accessible in an application, use the stock data-aware controls. There are no ADO-specific data-aware controls. For details on using data-aware controls, see "Using common data control features".
The standard data source component is used as a conduit between the ADO dataset components and the data-aware controls. There is no dedicated data source component for ADO. For details on using data source components, see "Using data sources".
Where needed, persistent field objects can be used to represent fields in the ADO dataset components. As with the data-aware controls and data source components, simply use the inherent Delphi field classes (TField and descendents). For details on using dynamic and persistent field objects, see "Understanding field components".
Creating and deleting metadata in an ADO database from a Delphi application must be done using SQL. Similarly, restructuring tables is done using SQL statements. Changing other metadata objects cannot be done per se. Instead, you need to delete the metadata object and then replace it with a new one with different attributes.
There are many database types that can be accessed through ADO and not all of the drivers for specific database types support all of the same SQL syntax. It is beyond the scope of this document to describe all of the SQL syntax supported by each database type and all of the differences between the database types. For a comprehensive and up-to-date discussion of the SQL implementation for a given database system, see the documentation that comes with that database system.
In general, use the CREATE TABLE statement to create tables in the database and CREATE INDEX to create new indexes for those tables. Where supported, use other CREATE statements for adding various metadata objects, such as CREATE DOMAIN, CREATE VIEW, and CREATE SCHEMA.
For each of the CREATE statements, there is a corresponding DROP statement to delete a metadata object. These statements include DROP TABLE, DROP VIEW, DROP DOMAIN, and DROP SCHEMA.
To change the structure of a table, use the ALTER TABLE statement. ALTER TABLE has ADD and DROP clauses to create new elements in a table and to delete them. For example, use the ADD COLUMN clause to add a new column to the table and DROP CONSTRAINT to delete an existing constraint from the table.
Issue these metadata statements from the ADO command or the ADO query component. For details on using the ADO command component to execute commands, see "Executing commands".
Flat-file database applications are single-tiered applications that use TClientDataSet to represent all of their datasets. The client dataset holds all its data in memory, which means that this type of application is not appropriate for extremely large datasets.
Flat-file database applications do not require the Borland Database Engine (BDE) or ActiveX Data Objects (ADO). Instead, they only use MIDAS.DLL. By using only MIDAS.DLL, flat-file applications are easier to deploy because you do not need to install, configure, and maintain software that manages database connections.
Because these applications do not use a database, there is no support for multiple users. Instead, the datasets are dedicated entirely to the application. Data can be saved to flat files on disk, and loaded at a later time, but there is no built-in protection to prevent multiple users from overwriting each other's data files.
Client datasets (located on the MIDAS page of the Component palette) form the basis of flat-file database applications. They provide support for most of the database operations you perform with other datasets. You use the same data-aware controls and data source components that you would use in a BDE-based single-tiered application. You don't use database components, because there is no database connection to manage, and no transactions to support. You do not need to be concerned with session components unless your application is multi-threaded. For more information about using client datasets, see "Creating and using a client dataset".
The main differences in writing flat-file database applications and other single-tiered database applications lie in how you create the datasets and how you load and save data.
Because flat-file database applications do not use existing databases, you are responsible for creating the datasets yourself. Once the dataset is created, you can save it to a file. From then on, you do not need to recreate the table, only load it from the file you saved. However, indexes are not saved with the table. You need to recreate them every time you load the table.
When beginning a flat-file database application, you may want to first create and save empty files for your datasets before beginning the writing of the application itself. This way, you do not need to define the metadata for your client datasets in the final application.
How you create your client dataset depends on whether you are creating an entirely new dataset, or converting an existing BDE-based application.
The following steps describe how to create a new client dataset using the Fields Editor:
Continue adding fields in the fields editor until you have described your client dataset.
Note: You can also create the client dataset at runtime using persistent fields that are saved with the client dataset. Simply call the CreateDataSet method.
Creating a client dataset using field and index definitions is much like using a TTable component to create a database table. There is no DatabaseName, TableName, or TableType property to specify, as these are not relevant to client datasets. However, just as with TTable, you use the FieldDefs property to specify the fields in your table and the IndexDefs property to specify any indexes. Once the table is specified, right-click the client dataset and choose Create DataSet at design time, or call the CreateDataSet method at runtime.
When defining the index definitions for your client dataset, two properties of the index definition apply uniquely to client datasets. These are TIndexDef.DescFields and TIndexDef.CaseInsFields.
DescFields lets you define indexes that sort records in ascending order on some fields and descending order on other fields. Instead of using the ixDescending option to sort in descending order on all the fields in the index, list only those fields that should sort in descending order as the value of DescFields. For example, when defining an index that sorts on Field1, then Field2, then Field3, setting DescFields to
Field1;Field3
results in an index that sorts Field2 in ascending order and Field1 and Field3 in descending order.
CaseInsFields lets you define indexes that sort records case-sensitively on some fields and case-insensitively on other fields. Instead of using the isCaseInsensitive option to sort case-insensitively on all the fields in the index, list only those fields that should sort case-insensitively as the value of CaseInsFields. Like DescFields, CaseInsFields takes a semicolon-delimited list of field names.
You can specify the field and index definitions at design time using the Collection editor. Just choose the appropriate property in the Object Inspector (FieldDefs or IndexDefs), and double-click to display the Collection editor. Use the Collection editor to add, delete, and rearrange definitions. By selecting definitions in the Collection editor you can edit their properties in the Object Inspector.
You can also specify the field and index definitions in code at runtime. For example, the following code creates and activates a client dataset in the form's OnCreate event handler:
procedure TForm1.FormCreate(Sender: TObject);
begin
with ClientDataSet1 do
begin
with FieldDefs.AddFieldDef do
begin
DataType := ftInteger;
Name := 'Field1';
end;
with FieldDefs.AddFieldDef do
begin
DataType := ftString;
Size := 10;
Name := 'Field2';
end;
with IndexDefs.AddIndexDef do
begin
Fields := 'Field1';
Name := 'IntIndex';
end;
CreateDataSet;
end;
end;
If you are converting an existing BDE-based application into a single-tiered flat-file application, you can copy existing tables and save them as flat-file tables from the IDE. The following steps indicate how to copy an existing table:
In flat-file database applications, all modifications to the table exist only in an in-memory change log. This log is maintained separately from the data itself, although it is completely transparent to objects that use the client dataset. That is, controls that navigate the client dataset or display its data see a view of the data that includes the changes. If you do not want to back out of changes, however, you should merge the change log into the data of the client dataset by calling the MergeChangeLog method. For more information about the change log, see "Editing data".
Even when you have merged changes into the data of the client dataset, this data still exists only in memory. While it will persist if you close the client dataset and reopen it in your application, it will disappear when your application shuts down. To make the data permanent, it must be written to disk. Write changes to disk using the SaveToFile method. SaveToFile takes one parameter, the name of the file which is created (or overwritten) containing the table.
When you want to read a table previously written using the SaveToFile method, use the LoadFromFile method. LoadFromFile also takes one parameter, the name of the file containing the table.
When you save a client dataset, the metadata that describes the record structure is saved with the dataset, but not the indexes. Because of this, you may want to add code that recreates the indexes when you load the data from file. Alternately, you might want to write your application so that it always creates indexes on the fly in an as-needed fashion.
If you always load to and save from the same file, you can use the FileName property instead of the SaveToFile and LoadFromFile methods. When FileName is set to a valid file name, the data is automatically loaded from the file when the client dataset is opened and saved to the file when the client dataset is closed.
Most of this section has described creating and using a client dataset in a one-tiered application. The one-tiered model can be combined with a multi-tiered model to create what is called the briefcase model.
Note: The briefcase model is sometimes called the disconnected model, or mobile computing.
When operating on site, a briefcase model application looks like a multi-tiered model: a user starts a client application on one machine and connects over a network to an application server on a remote machine. The client requests data from the application server, and sends updates to it. The updates are applied by the application server to a database that is presumably shared with other clients throughout an organization.
Suppose, however, that your onsite company database contains valuable customer contact data that your sales representatives can use and update in the field. In this case, it would be useful if your sales reps could download some or all of the data from the company database, work with it on their laptops as they fly across the country, and even update records at existing or new customer sites. When the sales reps return onsite, they need to upload their data changes to the company database for everyone to use. This ability to work with data off-line and then apply updates online at a later date is known as the "briefcase" model.
By using the briefcase model, you can take advantage of the client dataset component's ability to read and write data to flat files to create client applications that can be used both online with an application server, and off-line, as temporary one-tiered applications.
To implement the briefcase model, you must
In a two-tiered client/server application, the application is a client that talks directly to a database server. Even so, the application can be thought of as having two parts: a database connection and a user interface. To make a two-tiered client/server application into a multi-tiered application you must:
There are a number of ways to proceed, but the following sequential steps may best keep your translation work to a minimum:
pubsweb@inprise.com
Copyright © 1999, Inprise Corporation. All rights reserved.