Automation in Delphi COM Programming

Automating Microsoft ADO

Almost all the new technology coming out of Redmond is COM-based, and Microsoft's latest database technology is no exception. ActiveX Data Objects (ADO) is Microsoft's latest database technology designed to allow access to any type of data. This section isn't meant to be an exhaustive tutorial on ADO. Rather, I want to show you how you can use Delphi to quickly and easily take advantage of new Windows COM-based technologies.

---

Note - In Delphi versions 5 and later, Borland provides components for ADO development. As such, this section will mostly be of interest to Delphi 3 and Delphi 4 users, although the general concept of how to implement Microsoft COM-based technologies in your applications will apply to everyone.

---

ADO is one of a set of components in Microsoft's Universal Data Access strategy. ADO is the API that programmers can (and should) write to in order to access any type of data, from relational SQL databases to ISAM file formats to non-relational data, such as email messages. Microsoft ADO is essentially a wrapper around OLE DB. OLE DB is very powerful, but difficult to program. ADO goes a long way toward removing the complexity of OLE DB.

Note that because ADO is a Microsoft technology, you can rest assured that most of their efforts have been concentrated on providing the best possible performance with Microsoft Access and Microsoft SQL Server. If performance is of the utmost concern to your application, you might want to evaluate one of these two databases first. However, as time progresses, you can expect to see OLE DB providers appear from other database vendors that will provide optimal performance with their own databases.

ADO is a dual-interface automation server. The latest ADO version available at this time is version 2.1. However, because ADO 1.1 is installed with Internet Explorer 4.0 and Windows 98, most readers will already have ADO 1.1 installed on their machine. For that reason, this section does not discuss new features specific to ADO 2.1.

The ADO Philosophy

Figure 4.18 shows a conceptual diagram of how ADO fits in with OLE DB and ODBC.

Figure 4.18
The ADO architecture.

As you can see, ADO can obtain data directly through an OLE DB provider, or indirectly through ODBC. You might argue that ADO adds yet another layer on top of the existing database architecture. Rest assured, the ADO layer is very thin, and performance is excellent.

ADO uses the concept of a provider to provide access to the data itself. If you're familiar with ODBC, you can think of a provider as the equivalent of an ODBC driver. The provider must expose (at least) a minimal set of interfaces so that ADO can access different types of data in a consistent manner. A provider for an advanced data store will necessarily provide more capability than a provider for a flat-file database, for example.

ADO already provides access to most common database formats, but what do you do if you are dealing with a unique file format? Perhaps you're the developer of a brand new, technologically advanced database system. If you have an ODBC driver for your data, you can use that. However, you can also write an OLE DB provider for your data. Writing an OLE DB provider is an advanced subject in and of itself, so I will not get into the details here. However, Microsoft's documentation covers this subject, and their download even includes a sample OLE DB provider that you can study.

The main components of ADO are the Connection, Recordset, and Command. The Connection object is responsible for connecting to a local or remote database. The Recordset object provides a connection to a set of records. The Command object is useful for issuing commands to the underlying database that do not return result sets, such as an UPDATE SQL statement. These objects will be discussed in detail later.

Obtaining ADO

ADO is freely available for download. Simply visit http://www.microsoft.com/data/download.htm and select the link to the latest download available. You can either redistribute this file (MDAC_TYP.EXE) with your setup program, or you can direct your users to download the file from Microsoft's Web site. The good news is that future versions of Microsoft Windows will ship with ADO. Windows 98 and Microsoft Internet Explorer 4.0 both include ADO 1.1, which will work fine for your applications if you do not take advantage of ADO 2.1-specific features, such as asynchronous database connections and remote datasets.

After you've downloaded the appropriate file from Microsoft's Web site, simply execute it to install ADO, along with an OLE DB driver for the Microsoft Jet Engine, and ODBC drivers for SQL Server, Oracle, and Paradox.

Installing ADO into Delphi

Now that ADO is installed on your machine, you will want to install it into Delphi so you can start writing ADO applications. From the Delphi main menu, select Project, Import Type Library. The dialog box shown in Figure 4.19 appears.

Select Microsoft ActiveX Data Objects 2.1 Library (Version 2.1) and click OK. Delphi will generate the Pascal import unit ADODB_TLB.PAS for you. Open the Import Type Library dialog box again and import Microsoft ActiveX Data Objects Recordset 2.1 Library (Version 2.1). Delphi will generate another import unit, ADOR_TLB.PAS. You should include these units in any ADO applications you write with Delphi.

Figure 4.19
Installing ADO into Delphi.

Connecting to a Database

In ADO terminology, a connection is roughly the equivalent of a Delphi TDatabase. Like the Delphi TDatabase and TTable components, ADO is flexible enough that you can either explicitly establish a connection to a database, or you can let the recordset do it implicitly for you. In most serious applications, you will want to have total control over your database connections, so we're going to establish a connection ourselves.

Depending on what provider you use, you can connect to the database in a number of ways. I'll show you two typical scenarios here. First, you might have an ODBC datasource set up for your database. If that's the case, then you can connect to the database with the following code:

Connection.Open('Data Source=MyDataSourceName', 'UserID', 'Password', -1);

The first three parameters should be obvious from the code snippet. You pass the DSN, the User ID, and the password. For an unsecured Access database, you can leave the User ID and password blank. The last parameter represents the options to use when opening the connection. You will typically set this to -1 to use the default options.

Table 4.2  Valid Flags for Connection.Open

The following code is an example of how to connect to a Microsoft Jet database. Note that rather than going through an ODBC driver, this example connects directly through the Microsoft Jet OLE DB provider.

Connection.Open('Data Source=C:\MyData.mdb;Provider=Microsoft.Jet.OLEDB.3.51',
 'admin', '', -1);

Opening a Recordset

After we have established a connection to the database, we can open a recordset. A recordset is the conceptual equivalent of a TQuery or a TTable. For instance, the following code opens the table named Customers in the database referred to by Connection:

Recordset.Open('Customers', Connection, adOpenForwardOnly, adLockReadOnly, adCmdTable);

Alternately, we can specify an SQL statement to select a subset of the rows from the Customer table, like this:

Recordset.Open('SELECT * FROM Customers WHERE Balance> 1000', 
 Connection, adOpenForwardOnly, adLockReadOnly, adCmdText);

Recordset.Open takes five parameters. The first is used to determine what information to pull into the recordset. In the first example in the previous code, we simply provided the name of a table in the database. In the second example, we provided an SQL statement instead.

The second parameter specifies the connection to use. Because we already explicitly created a connection, we can simply pass the connection as the parameter. If we hadn't created a connection, we could pass the connection information here instead, as follows:

Recordset.Open('Customers', 'Data _Source=C:\MyData.mdb;Provider=Microsoft.Jet.OLEDB.3.51', 
 adOpenForwardOnly, adLockReadOnly, adCmdTable);

The third parameter specifies the type of cursor to use with the connection. Possible values are listed in Table 4.3.

Table 4.3  Valid Cursor Types for Recordset.Open

The fourth parameter specifies the lock type. Possible values are listed in Table 4.4.

Table 4.4  Valid Lock Types for Recordset.Open

The fifth and final parameter specifies the type of command passed by the first parameter. Table 4.5 lists possible values for this parameter.

Table 4.5  Valid Command Types for Recordset.Open

Executing a Command

If you need to execute an action against the database that does not return a result set, you should use a Command object instead of a Recordset object. The Command object works hand in hand with the Parameter object, so I will discuss them together here. The following code snippet shows one way of giving everybody in the Employee table a ten percent raise:

Parameter := Command.CreateParameter('Raise', adDouble, adParamInput, sizeof(Double), _1.10);
Command.CommandText := 'UPDATE Employee SET Salary = Salary * ?';
Command.Execute(RecsAffected, Parameter, -1);

The first parameter is the name of the Parameter object. You can give this any name that sounds reasonable to you. The second parameter is the data type of the parameter. There are too many possible data types to list them all here. Refer either to the Microsoft documentation or to the Delphi-generated import files for a list of possible values.

The third parameter is the parameter type. Possible values are listed in Table 4.6.

Table 4.6  Valid Parameter Types for Command.CreateParameter

The fourth parameter specifies the maximum length of the parameter, in either characters or bytes, depending on the parameter type. The fifth and final parameter is a variant that specifies the value of the parameter.

Accessing Field Values

ADO creates a Field object for each column in the recordset. To retrieve data from a recordset or modify the value of a field, you must access the value of the corresponding Field object(s), as follows:

Recordset.Fields.Item[FieldNo].Value
You can also obtain the field name by using the Name property.
Recordset.Fields.Item[FieldNo].Name

The Field object contains a number of useful properties. Some of the ones you will use most often are listed in Table 4.7.

Table 4.7  Common Properties of the Field Object

Handling Database Errors

In all database programming, you should be prepared to handle errors as they arise, and ADO is no exception. ADO provides an Errors object that provides a detailed explanation of any and all errors that come up. Thanks to Delphi's exception handling mechanism, handling ADO errors is a breeze. The following code snippet illustrates how to trap and respond to an ADO error.

try
 FRecordset.MoveNext;
except
 E := FConnection.Errors[0];

 // Do something with the error here, such as log it to a file

 raise; // Re-raise the exception
end;

As you can probably surmise from the previous code snippet, it's possible for ADO to generate multiple errors simultaneously. In order to relate all error information to the user, you should examine the FConnection.Errors.Count property to determine the number of errors generated. Then you should implement a mechanism to allow the user to scroll through the list of errors, which would be contained in FConnection.Errors[0] through FConnection.Errors[Count - 1]. Listing 4.17 contains a procedure called ShowADOErrors that demonstrates one (very simple) method of doing this.

Example: A Microsoft ADO Application

This sample application does not really do anything useful within itself; rather, it's more of a collection of routines to perform common ADO functions.

Listing 4.17  ADOTest—MainForm.pas

unit MainForm;

interface

uses
 Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
 Dialogs, StdCtrls, ADODB_TLB, Grids;

const
 ConnectionString =
  'Data Source=c:\Program Files\Common Files\Borland Shared\Data\DBDemos.mdb;' +
  'Provider=Microsoft.Jet.OLEDB.3.51';

type
 TForm1 = class(TForm)
  StringGrid1: TStringGrid;
  btnLoadGrid: TButton;
  btnAdd: TButton;
  btnAddDuplicate: TButton;
  btnDelete: TButton;
  btnSearch: TButton;
  btnSort: TButton;
  procedure FormCreate(Sender: TObject);
  procedure btnLoadGridClick(Sender: TObject);
  procedure btnAddClick(Sender: TObject);
  procedure btnAddDuplicateClick(Sender: TObject);
  procedure btnDeleteClick(Sender: TObject);
  procedure btnSearchClick(Sender: TObject);
  procedure btnSortClick(Sender: TObject);
 private
  { Private declarations }
  FConnection: _Connection;
  FRecordset: _Recordset;
  FByOrder: Boolean;
 public
  { Public declarations }
 end;

var
 Form1: TForm1;

implementation

{$R *.DFM}

procedure ShowADOErrors(ErrorList: Errors);
var
 I: Integer;
 E: Error;
 S: String;
begin
 for I := 0 to ErrorList.Count - 1 do begin
  E := ErrorList[I];
  S := Format('ADO Error %d of %d:'#13#13'%s',
   [I + 1, ErrorList.Count, E.Description]);
  ShowMessage(S);
 end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
 FConnection := CoConnection.Create;
 FConnection.Open(ConnectionString, 'admin', '', -1);

 FRecordset := CoRecordset.Create;
 FRecordset.Open('SELECT * FROM Orders ORDER BY OrderNo',
  FConnection, adOpenKeyset, adLockOptimistic, adCmdText);

 FByOrder := True;

 btnLoadGridClick(Sender);
end;

procedure TForm1.btnLoadGridClick(Sender: TObject);
var
 V: OleVariant;
 S: String;
 Row, Col: Integer;
begin
 try
  FRecordset.MoveFirst;

  StringGrid1.ColCount := FRecordset.Fields.Count;
  StringGrid1.RowCount := FRecordset.RecordCount + 1;

  for Col := 0 to FRecordset.Fields.Count - 1 do
   StringGrid1.Cells[Col, 0] := FRecordset.Fields.Item[Col].Name;

  Row := 1;
  while not FRecordset.EOF do begin
   for Col := 0 to FRecordset.Fields.Count - 1 do begin
    V := FRecordset.Fields.Item[Col].Value;
    if VarType(V) <> varNull then
     S := V
    else
     S := '';
    StringGrid1.Cells[Col, Row] := S;
   end;
   Inc(Row);
   FRecordset.MoveNext;
  end;
 except
  ShowADOErrors(FConnection.Errors);
 end;
end;
procedure TForm1.btnAddClick(Sender: TObject);
var
 Fields: OleVariant;
 Values: OleVariant;
begin
 Fields := VarArrayCreate([1, 3], varVariant);
 Values := VarArrayCreate([1, 3], varVariant);

 try
  Fields[1] := 'OrderNo';
  Fields[2] := 'CustNo';
  Fields[3] := 'EmpNo';
  Values[1] := 99999;
  Values[2] := 3615;
  Values[3] := 2;
  FRecordset.AddNew(Fields, Values);
  btnLoadGridClick(Sender);
  ShowMessage('OrderNo 99999 was added');
 except
  ShowADOErrors(FConnection.Errors);
 end;
end;

procedure TForm1.btnAddDuplicateClick(Sender: TObject);
var
 Fields: OleVariant;
 Values: OleVariant;
begin
 Fields := VarArrayCreate([1, 3], varVariant);
 Values := VarArrayCreate([1, 3], varVariant);

 try
  Fields[1] := 'OrderNo';
  Fields[2] := 'CustNo';
  Fields[3] := 'EmpNo';
  Values[1] := 1035;
  Values[2] := 1645;
  Values[3] := 2;
  FRecordset.AddNew(Fields, Values);
 except
  ShowADOErrors(FConnection.Errors);
 end;
end;

procedure TForm1.btnDeleteClick(Sender: TObject);
begin
 FRecordset.MoveFirst;
 FRecordset.Find('OrderNo=99999', 0, adSearchForward, 0);
 if not FRecordset.EOF then begin
  FRecordset.Delete(adAffectCurrent);
  btnLoadGridClick(Sender);
  ShowMessage('OrderNo 99999 was deleted.');
 end else
  ShowMessage('OrderNo 99999 was not found.');
end;

procedure TForm1.btnSearchClick(Sender: TObject);
begin
 FRecordset.MoveFirst;
 FRecordset.Find('OrderNo=1070', 0, adSearchForward, 0);
 if not FRecordset.EOF then
  ShowMessage('OrderNo 1070 was found.')
 else
  ShowMessage('OrderNo 1070 was not found.')
end;

procedure TForm1.btnSortClick(Sender: TObject);
begin
 FByOrder := not FByOrder;

 FRecordset.Close;
 if FByOrder then
  FRecordset.Open('SELECT * FROM Orders ORDER BY OrderNo',
   FConnection, adOpenKeyset, adLockOptimistic, adCmdText)
 else
  FRecordset.Open('SELECT * FROM Orders ORDER BY CustNo, OrderNo',
   FConnection, adOpenKeyset, adLockOptimistic, adCmdText);

 btnLoadGridClick(Sender);
end;

end.

When you run ADOTest, you'll see the screen shown in Figure 4.20.

Figure 4.20
ADOTest shows how to perform several common database tasks.

The application's form has a string grid and six buttons on it. From left to right, the buttons perform the following operations:

• Load Grid: Forces the application to reload all data from the recordset

• Add: Adds a new order to the orders table. The order number is set to 99999, the customer number to 3615, and the employee number to 2. This record does not already exist in the database, so this routine demonstrates a successful add to the database.

• Add Duplicate: Attempts to add a new record with a duplicate order number 1035. This routine demonstrates a key violation.

• Delete: Attempts to find and delete order number 99999. If you have already added order 99999, this routine demonstrates how to successfully locate and delete the record.

• Search: Uses the Find method to search for order number 1070.

• Sort: Toggles between sorting by order number and sorting by customer number.