Delphi Does ADO

Problem/Question/Abstract:

ADO Overview

Answer:

The New Way to Get to Data

Universal Data Access (UDA) is part of Microsoft's strategy to provide fast access to data in both relational and non-relational data stores. UDA provides a language-independent, easy-to-use API for accessing data in any data source that has a UDA-compatible driver. Like the BDE, this technology makes it easy to access data from multiple data sources in a single program. UDA is implemented using the Microsoft Data Access Components (MDAC), which includes Active Data Objects (ADO), Open Database Connectivity (ODBC), and OLE DB.

ADO is the application programming interface of MDAC, while OLE DB is the system-level interface. OLE DB defines a suite of COM interfaces that provide all the data access capabilities required by any data source, from a relational database to a file system. ODBC is included in MDAC for backward compatibility. While existing ODBC drivers will likely be replaced by OLE DB providers in the future, the Microsoft OLE DB provider for ODBC lets you use any ODBC driver via ADO now. Although ADO is relatively new, OLE DB providers are already available for Microsoft Access, Microsoft SQL Server, and Oracle.

Another major advantage of ADO is that it will be built into all future Microsoft operating systems, including Windows 2000. While this means that today you must install ADO on each PC that will use ADO to access data, that task will vanish in the future. If you want to learn more about UDA and ADO, visit Microsoft's data access Web site at http://www.microsoft.com/data/default.htm. From this page, you can download the ADO redistributable, which allows you to install ADO on Windows 95/98/NT machines, or the MDAC SDK, which contains complete documentation and everything you need to develop your own OLE DB providers. The SDK also includes the ADO redistributable.

Everything you need to use ADO with Delphi is on the Delphi 5 CD, including MDAC. Simply go to the MDAC folder on the Delphi 5 CD and run the installation program, MDAC_TYP.EXE. The MDAC installation program is a single EXE file, so it's easy to install MDAC anywhere you need it. You can also use the MDAC installation program to install MDAC as part of your application's installation if you're using an installation program that supports calling EXEs (InstallShield Express does not). If you're installing MDAC as part of your application's installation, you'll want to use the "silent" mode to suppress all screen displays. To install in silent mode, use the command:

mdac_typ.exe /q:a /c:"setup.exe /qt"

For more information on installing MDAC, including file lists and dependencies, see the MDAC SDK documentation.

Using the ADOConnection and ADODataSet Components

Delphi 5 has a suite of six new components that provide complete ADO support, and an easy way to convert existing applications to ADO. To begin building an ADO application, drop an ADOConnection component on a form or data module. The ADOConnection component is the ADO equivalent of the BDE Database component. It allows you to define a connection to a database using its ConnectionString property.

While it's possible to build a connection string manually, it is difficult. The ADO connection string consists of a semicolon-delimited list of many parameters that can easily exceed 150 characters. Fortunately, Microsoft provides a Connection String Editor to make this job easier. To open the Connection String Editor, shown in Figure 1, click the ellipsis button in the ConnectionString property's edit box, or double-click on the component.


Figure 1: The Connection String Editor.

The easy way to build a connection string is to click the Build button to display the Data Link Properties dialog box, shown in Figure 2. The Provider page lets you choose the driver you want to use.


Figure 2: The Data Link Properties dialog box.

What you see on the Connection page depends on the provider you select. Figure 3 shows the Connection page with the Microsoft Jet provider selected, and the path to an Access database entered.


Figure 3: The Connection page.

The Advanced page, shown in Figure 4, lets you specify the type of access to the database, and the All page (see Figure 5) lets you edit any value in the connection string. The All page is particularly important if you're connecting to an Access database with user-level security, because it's the only place you can enter the path to the system database.


Figure 4: The Advanced page.


Figure 5: The All page.

Once a value has been assigned to the ConnectionString property, you can set the Connected property to True, at design or run time, to connect to the database. The ADOConnection component also provides transaction support through its BeginTrans, CommitTrans, and RollbackTrans methods.

The ADODataSet component is really the only one you need to work with data because it allows you to work directly with a table, execute a SQL statement, work with the result set, or call a stored procedure. After dropping an ADODataSet on a form or data module, the first step is to set its Connection property. The Connection property's drop-down list will display all the available ADOConnection components. Next, you need to set two related properties: CommandType and CommandText. Set CommandType first because it determines how CommandText is interpreted. You can set CommandType to indicate that you want to connect directly to a table, call a stored procedure, or enter a SQL statement as text. Choosing cmdTable as the CommandType causes the drop-down list for the CommandText property to display the tables in the database.

Once CommandType and CommandText have been set, using the ADO components is exactly like working with the BDE dataset components. Drop a DataSource component, a DBNavigator, and some data-aware components on your form. Set the DataSet property of the DataSource to the ADODataSet component, and set the DataSource property of the navigator and data-aware controls.

Figure 6 shows a data module containing an ADOConnection, two ADODataSet components, and two DataSource components modeling a one-to-many relationship between two tables in an Access database. The master table is FailureAdoDs, and the detail table is RepairTimeAdoDs. The datasets were linked by setting the DataSource property of the detail dataset to the DataSource component of the master dataset, then setting the MasterFields property of the detail dataset.


Figure 6: Linked ADODataSet components.

The property editor for the MasterFields property is the Field Link Designer, shown in Figure 7. To link the tables, select the master and detail fields that define the relationship between the tables, and click the Add button. In this example, the TrackingNumber field links the tables. If the relation is defined by more than one field, repeat the process of selecting the corresponding master and detail fields and clicking the Add button.


Figure 7: The Field Link Designer.

To use an ADODataSet with a query result set, change the CommandType to cmdText and enter the SQL statement in the CommandText property. With the CommandType set to cmdText, the property editor for the CommandText property changes to the Command Text Editor, shown in Figure 8.


Figure 8: The Command Text Editor.

The Command Text Editor is a major improvement over the String List Editor, used to edit SQL commands in previous versions of Delphi. It provides a list of tables, and a button to add the table name to the SQL statement, as well as a list of field names for the selected table. Even if you don't use the Add buttons, the list of table and field names is very handy. Creating a one-to-many link between the ADODataSet components that execute SQL statements is exactly the same as linking two BDE Query components. The SQL statement for the detail dataset is:

SELECT *
  FROM RepairTime
WHERE TrackingNumber = :TrackingNumber

The name of the parameter in the WHERE clause, :TrackingNumber, matches the name of the primary key in the master table exactly. The detail dataset's DataSource property is set to the master table's DataSource component. Because these two conditions have been met, each time the master dataset is positioned to a new record, the detail dataset is automatically closed, the new value from the master record is assigned to the query parameter, and the detail dataset is opened to retrieve the new set of detail records.

If you will execute a query more than once with different parameters, set the ADODataSet's Prepared property to True. This will cause the query plan to be prepared and stored the first time the query is executed. The stored plan will be used for each subsequent execution. This eliminates the time required to parse and optimize the query for all executions except the first.

To work with a stored procedure, set the CommandType to cmdStoredProc, and choose the stored procedure from the CommandText property's drop-down list. Use the ADODataSet's Parameters property to assign values to input parameters and retrieve values from output parameters.

Although you can do everything with the ADODataSet component, Delphi 5 also includes the ADOTable, ADOQuery, and ADOStoredProc components. These are designed to resemble the BDE Table, Query, and StoredProc components as closely as possible to make converting an application to ADO easy.

Should You Convert to ADO?

Why convert an existing application from BDE to ADO? Neither the native BDE Access driver nor the Access ODBC driver have been ideal solutions for working with Access databases. Using the ADO Jet driver eliminates these problems. With ADO, your Access applications will correctly detect changes made by other users and warn you when you try to post a record that has been changed by another user since you read it. Also, Autoincrement fields work correctly with default values set for other fields.

The big advantage of using ADO with any database, however, is that you are no longer dependent on Borland to update drivers when new releases of the database appear. When a new version of SQL Server or Oracle is released, the new ADO drivers should be available at the same time, and should work because the database vendor writes them.

The ADOCommand Component

In addition to the components for working with datasets, Delphi 5 also provides the ADOCommand component. The ADOCommand component is most useful for executing commands that don't return a result set, such as SQL DDL (Data Definition Language) commands, or a SQL DELETE query.

If you're using one or more ADOConnection components, click the drop-down button in the Connection property of the ADOCommand component and select the connection you want to use. The ADOCommand component, like all the ADO dataset components, has its own ConnectionString property so you don't have to use an ADOConnection component. However, in most cases, you'll want to. The connection component provides a single central place to change the ConnectionString and any other connection-related properties, as well as providing transaction control methods.

The CommandText property of the ADOCommand component contains the command you want to execute, and the CommandType property determines whether CommandText is interpreted as a text string, table name, or stored procedure name. Set CommandType to ctText to execute a SQL statement. If the SQL statement includes parameters, you can set their properties using the Parameters property editor of the ADOCommand component. Although it makes no sense to use the ADOCommand component to retrieve a dataset from a table, query, or stored procedure, you can do it. The ADOCommand's Execute method returns the recordset generated by the command, if any. You can assign the returned recordset to the RecordSet property of an ADODataSet to view the records.

Cursor Types

If you're accustomed to working with the BDE dataset components, there are a number of things you'll find different when you use ADO. One of the most striking is the choice of four different cursor types, which you can set using the CursorType property of ADODataSet. The first is ctStatic, which provides a static dataset that you cannot edit, and that will not show any changes made by other users. A static cursor behaves like the result set from a BDE Query component with its RequestLive property set to False.

Choosing ctOpenForwardOnly provides a cursor that is identical to a static cursor, except that you can only move forward through the dataset. A forward-only cursor is very efficient and is ideal for generating reports. Setting the CursorType to ctDynamic provides a cursor that allows you to navigate both forward and backward, as well as see all additions, deletions, and changes made by other users. The ctKeySet cursor type is identical to ctDynamic except that you can't see records added by other users.

ADO also provides a CursorLocation property with two possible values: clUseClient and clUseServer. Client cursors are somewhat similar to data provided to a MIDAS ClientDataSet, in that all the data is downloaded to the client immediately. For a large dataset, this can impose a significant penalty in time and memory usage. However, client cursors are almost always updateable, support bookmarks, and allow scrolling in both directions. This may not always be true with server cursors. The features available with server cursors will depend on the database and the OLE DB provider you're using.

Transaction Isolation Levels

ADO supports the ANSI SQL-92 standard transaction isolation levels, which are slightly different than those supported by the Delphi Database component's TransIsolation property. ADO supports the following four isolation levels:

Read Uncommitted. Read Uncommitted is also called Dirty Read or Browse isolation. At this level of isolation, a transaction can see uncommitted changes made by other transactions.
Read Committed. A transaction at this level cannot see uncommitted changes made by other transactions, but can see committed changes. This means that reading the same record twice may give two different values because the record could have been changed by another transaction that has committed. If a query is re-executed within the transaction, it can also return new records that have been added by another committed transaction that it did not see the first time the query ran.
Repeatable Read. A Repeatable Read transaction will not see any changes made by other transactions to records it has read, even if the other transactions have committed. However, if a query is re-executed within the transaction, it will see new records added by other committed transactions.
Serializable. This isolation level requires that all concurrent transactions interact in ways that produce the same result as though the transactions executed sequentially. A transaction at this level will not see either changed or newly inserted records from other committed transactions.

Of course, the isolation level that you actually get when you choose one of these options depends on the isolation levels that the database you're using supports.

Conclusion

ADO support is the single most important feature for database application developers in Delphi 5. As Microsoft builds ADO into its next generation of operating systems, you'll no longer have to install additional software with your application to access databases. Perhaps more important is the range of data that ADO will provide access to in the future. Looking beyond relational databases, ADO will provide access to e-mail system message stores, the file system on your hard disk, and any other data store in a Microsoft product.

With the full power of Microsoft behind it, ADO will certainly be adopted by other vendors with products that store data. Finally, ADO relieves Borland of the burden of writing drivers. That is a bigger benefit to you than to Borland because it means you'll get better drivers faster, as new versions of data storage products ship. Best of all, because ADO drivers for Access, SQL Server, and Oracle are already available, and because ADO includes an ODBC provider, you can start using it right now.