Accessing Databases Using ADO and Delphi

By: Charles Calvert

Abstract: This paper is designed to show you how easy it is to get up to speed with ActiveX Data Objects (ADO) in a Delphi application. By using ADO and Delphi, you can create database applications that do not rely on the BDE.

Delphi
Accessing Databases Using ADO and Delphi

Copyright (c) 1999 by Charlie Calvert

First Published: Jan 5, 1999

Sample Code

This paper is designed to show you how easy it is to get up to speed with ActiveX Data Objects (ADO) in a Delphi application. By using ADO and Delphi, you can create database applications that do not rely on the BDE.

This article does not cover the ADO components found in Delphi 5. Instead, it was written against Delphi 4, and is meant to show how to access ADO if you do not have the Delphi ADO components. The code should work fine in Delphi 5, however it shows how to work with ADO using a relatively low level API.

ADO is designed to allow you to quickly and easily access ODBC data sources in your application via relatively simple COM commands. There are Microsoft ActiveX components such as grids and list boxes that make it easy for you to work with ADO data sources. However, in this paper I'm only going to show you how to access the data programaticly. I'm not going to use any database related visual components.

This paper will show how to access and update ADO data using either Variants or interfaces. The subject matter of this paper is very easy to understand, and the code shown here should work with any version of Delphi 3 or 4.

Installing and Accessing an ADO Database

The code found in this article is based on one simple Delphi program that is less that 150 lines long. However, you will need to have both ADO and an ODBC DSN set up before you can run the code.

At the time of this writing, I have only used ADO on Windows NT machines, but everything I say here should apply equally well to Windows 95 and 98. However, if you are using Windows 95, you must install DCOM95 first.

ADO is a set of COM components (DLLs) that allow you to access databases with just a few lines of very simple code. You may already have ADO installed on your system, in which case you can skip the next paragraph. Certainly it is Microsoft's intention to make ADO available for free on a very wide range of systems.

If you don't have ADO installed, you can get these components from several sources, but one of the simplest and best ways to get set up is to download the Microsoft Data Access Software Development kit (dasdksa.exe, 36 MB) from the Microsoft web site. If you are on a 28 KB modem, there are smaller files which you can download that don't give you the full SDK. Go to http://www.microsoft.com/data/download.htm to find either the entire SDK or these smaller files.

ADO is generally associated with OLEDB, Universal Data Access, and Microsoft Data Access Components (MDAC). If you find any of those words mentioned you are probably on the right track when searching for the files to download. OLEDB came out before ADO, and forms the basis for that latter technology, so be sure you get a package that contains both technologies.

After you have installed ADO on your system, you should be able to find ADODB.dll, or (C:Program FilesCommon FilesSystemadomsado15.dll) which contains a type library that specifies all the interfaces and constants used in ADO programming. In Delphi, go to Project | Import Type Library, and select this DLL. After doing so, click Ok, and a copy of ADODB_TLB.pas will be created for you automatically, based on the declarations in ADODB.DLL. This file contains all the declarations you need to use ADO in your Delphi programs.

There may be some low end versions of Delphi that won't let you import this file automatically. As a result, I will include ADODB_TLB.pas with the example program used in this paper. If you absolutely cannot obtain a copy of this file, you will still be able to access data using Variants, but you won't be able to access the data using interfaces.

The final step to complete before you run the sample program from this paper is to set up a DSN (ODBC System Data Source) that points at a table called Clients.dbf that you can play with while developing a program.

Everyone who has a copy of Delphi should have the Clients.dbf file that ships in the data directory. By default, this file is found in either of these directories:

  1. x:...DelphiDemosData
  2. c:program filescommon filesborland shareddata

After ascertaining that you have the file, you should load the Windows Control Panel, and choose the ODBC applet. This applet comes with Office and other Microsoft tools, so it is available on most systems by default, and certainly it should be available on any system where you have successfully installed ADO.

In the ODBC applet, turn to the System DSN page and choose Add. A dialog showing you a list of available ODBC drivers should pop up. Pick the DBase driver. If there is no dBase driver on your system, just pick another driver and work with some other table than the one I use in my example. It doesn't matter which table you use. Note, however, that Microsoft gives these drivers away, so you can probably find them on the Microsoft Web site or on the CD for some Microsoft product which you own, such as Office, Excel or Access.

After you choose a driver, the ODBC applet will ask you to specify a Data Source Name. Enter DBDemosDBase. Create a description for your DSN, specify the version as DBase 5, and uncheck the Use Current Directory button. Select the browse button and find the directory where Clients.dbf is stored. When you are done, the dialog should look as it does in Figure 1.

Figure 1: Setting up a Data Source Name (DSN) in the Microsoft ODBC applet.

To save your work, just clikc the Ok button.

At this stage you have installed ADO, created the ADODB_TLB.pas file, and set up a DSN that can be used in a sample program. That means you are all set to move on to the next section where I show how to access the data.

Using Delphi to Access ADO Data Sources

I'm going to show you how to access the data in Clients.dbf in two different ways: once with variants, and a second time with interfaces. Both examples are shown in Listing 1, which contains a simple Delphi form.

Listing 1: The code for accessing ADO data from a Delphi application using both variants and interfaces.

unit Main;

{---------------------------------------------------------------------
  Created Jan 5, 1999.
  
  Copyright (c) 1999 by Charlie Calvert
----------------------------------------------------------------------}
interface

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

const
  SELECTSTRING = 'SELECT * FROM Clients.dbf';
  DSNSTRING = 'DBDemosDBase';
    
type
  TForm1 = class(TForm)
    StringGrid1: TStringGrid;
    Panel1: TPanel;
    VariantBtn: TButton;
    InterfaceBtn: TButton;
    UpdateBtn: TButton;
    Edit1: TEdit;
    procedure VariantBtnClick(Sender: TObject);
    procedure InterfaceBtnClick(Sender: TObject);
    procedure UpdateBtnClick(Sender: TObject);
    procedure StringGrid1SelectCell(Sender: TObject; ACol, ARow: Integer;
      var CanSelect: Boolean);
  private
    procedure Display(RecordSet: _RecordSet);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses
  ActiveX;

{$R *.DFM}

procedure TForm1.Display(RecordSet: _RecordSet);
var
  Y, i: Integer;
begin
  Y := 1;
  repeat
    for i := 0 to 6 do
      StringGrid1.Cells[i, Y] :=  RecordSet.Fields[i].Value;
    RecordSet.Move(1, EmptyParam);
    Inc(Y);
  until RecordSet.EOF;
end;

procedure TForm1.InterfaceBtnClick(Sender: TObject);
var
  RecordSet: _RecordSet;
  DSN: string;
begin
  // Create an empty recordset object
  OleCheck(CoCreateInstance(CLASS_RecordSet, nil, 
    CLSCTX_ALL, IID__RecordSet, RecordSet));
  DSN := 'dsn=' + DSNSTRING;

  // Fill the recordset
  RecordSet.Open(SelectString, DSN, adOpenForwardOnly, 
    adLockReadOnly, adCmdUnspecified);

  // Display the data
  Display(RecordSet);
  UpdateBtn.Enabled := True;
end;

procedure TForm1.UpdateBtnClick(Sender: TObject);
var
  RecordSet: _RecordSet;
  DSN: string;
begin
  OleCheck(CoCreateInstance(CLASS_RecordSet, nil, 
    CLSCTX_ALL, IID__RecordSet, RecordSet));

  DSN := 'dsn=' + DSNSTRING;

  // Fill the recordset
  RecordSet.Open(SELECTSTRING, DSN, adOpenDynamic, 
    adLockOptimistic, adCmdUnspecified);

  // Update
  RecordSet.Move(StringGrid1.Row - 1, EmptyParam);
  RecordSet.Fields[StringGrid1.Col].Value := Edit1.Text;
  RecordSet.Update(EmptyParam, EmptyParam);

  RecordSet.MoveFirst;  
  Display(RecordSet);
end;

procedure TForm1.VariantBtnClick(Sender: TObject);
var
  RecordSet: OleVariant;
  Y, i: Integer;
begin
  // Create an empty recordset object
  RecordSet := CreateOleObject('ADODB.Recordset');

  // Fill the recordset
  RecordSet.Open(SELECTSTRING, DSNSTRING);

  // Display the data
  Y := 1;
  repeat
    for i := 0 to 6 do
      StringGrid1.Cells[i, Y] :=  RecordSet.Fields[i].Value;
    RecordSet.Move(1);
    Inc(Y);
  until RecordSet.EOF;
end;
 
procedure TForm1.StringGrid1SelectCell(Sender: TObject; ACol,
  ARow: Integer; var CanSelect: Boolean);
begin
  Edit1.Text := StringGrid1.Cells[ACOl, ARow];
end;

end.

This program has three buttons, one TStringGrid, and one edit control on it, as shown in Figure 2. The first button allows you to populate the string grid with ADO data using variants. The second button gives you the same functionality using interfaces.

The third button is associated with an edit control. If you click on any of the fields shown in the string grid, then the text from that field will appear in the edit control next to the third button. You can edit this field, then press the button to update the data in the string grid, and in the Clients.dbf table itself.

Figure 2: The ADOTest1 program allows you to view and edit data from the Clients.dbf table.

Accessing ADO with Variants

There are several different ADO objects, but in this paper I'm going to concentrate on the RecordSet and Fields objects.

To create an instance of the RecordSet object, write the following code:

var
  RecordSet: OleVariant;
begin
  RecordSet := CreateOleObject('ADODB.Recordset');
  ... // Code omitted here
end;

CreateOleObject is found in ComObj.pas. It is a simple wrapper around CoCreateInstance, a core OLE function found in ActiveX.pas:

function CreateOleObject(const ClassName: string): IDispatch;
var
  ClassID: TCLSID;
begin
  ClassID := ProgIDToClassID(ClassName);
  OleCheck(CoCreateInstance(ClassID, nil, CLSCTX_INPROC_SERVER or
    CLSCTX_LOCAL_SERVER, IDispatch, Result));
end;

CoCreateInstance retrieves an instance of the ADO object and places it in an instance IDispatch. You can set a Variant equal to an instance of class IDispatch, so you are able to access an instance of the ADO services simply and easily with this call.

To open the Clients table, write the following code:

const
  SELECTSTRING = 'SELECT * FROM Clients.dbf';
  DSNSTRING = 'DBDemosDBase';

begin
  RecordSet := CreateOleObject('ADODB.Recordset');
  RecordSet.Open(SELECTSTRING, DSNSTRING);
  .. // Code omitted here
end;

The open command takes several parameters, but in this case we need pass in only two. The first is the SQL statement you want to execute, and the second is the DSN that you want to open.

Once you have opened the data set, you have a cursor on the first row. To access the fields from the first row, write the following code:

MyString := RecordSet.Fields[0].Value;
MyString := RecordSet.Fields[1].Value;

And so on.

To move to the next row, write the following code:

RecordSet.Move(1);

Using these functions, you can easily write a loop that will dump the contents of the dataset into a string grid:

var 
  Y, i: Integer;
begin
  .. // Initialization code omitted here
  Y := 1;
  repeat
    for i := 0 to 6 do
      StringGrid1.Cells[i, Y] :=  RecordSet.Fields[i].Value;
    RecordSet.Move(1);
    Inc(Y);
  until RecordSet.EOF;
end;

Note that I use the RecordSet.EOF property to check for the end of the dataset.

There you have it. The few lines of code I've outlined in this section allow you to display an entire data set to the user without installing BDE on a system.

Accessing ADO with Interfaces

You follow essentially the same steps outlined in the previous section when access ADO using interfaces:

procedure TForm1.InterfaceBtnClick(Sender: TObject);
var
  RecordSet: _RecordSet;
  DSN: string;
begin
  // Create an empty recordset object
  OleCheck(CoCreateInstance(CLASS_RecordSet, nil, 
    CLSCTX_ALL, IID__RecordSet, RecordSet));

  DSN := 'dsn=' + DSNSTRING;

  // Fill the recordset
  RecordSet.Open(SelectString, DSN, adOpenForwardOnly, 
    adLockReadOnly, adCmdUnspecified);

  // Display the data
  Display(RecordSet);
  UpdateBtn.Enabled := True;
end;

Unlike the code in the previous section, you must include ADODB_TLB.pas in your uses clause or this method will not compile. Earlier in the paper, I discussed how to obtain a copy of this file.

Instead of working with Variants, this time you are working with an interface of type _RecordSet. This interface is declared in ADODB_TLB.pas.

I call CoCreateInstance directly to create an instance of this class. In the first parameter, I pass in the class ID, CLASS_RecordSet, which is declared in ADODB_TLB.pas. In the fourth parameter I pass in IID__RecordSet, which is again declared in ADODB_TLB.pas, and which constant contains two underscores! The final parameter is the instance of the class I want to create.

When calling RecordSet.Open, I now need to pass in all five parameters used by the function:

const
  SELECTSTRING = 'SELECT * FROM Clients.dbf';
  DSNSTRING = 'DBDemosDBase';

begin
  .. // Code omitted
  DSN := 'dsn=' + DSNSTRING;
  RecordSet.Open(SelectString, DSN, adOpenForwardOnly, 
    adLockReadOnly, adCmdUnspecified);
  .. // Code omitted
end;

In the previous example you could pass in only two parameters, because Variants allow you to ignore parameters that can receive default values. When working with interfaces, you must be more specific. If I just wanted to use the default values, I could write something like this:

  
RecordSet.Open(SelectString, DSN, EmptyParam, 
  EmptyParam, EmptyParam);

Here I am using the EmptyParam variant, declared in System.pas, for all the parameters that accept default values. In the previous examples, I filled in the actual default values, all declared in ADODB_TLB.pas. I found out which parameters to pass by reading the help file that comes with the Data Access SDK.

Once you have the data set open, you can display it using the same technology laid out in the section on Variants. The only difference is that when you call RecordSet.Move, you need to pass in two parameters:

procedure TForm1.Display(RecordSet: _RecordSet);
var
  Y, i: Integer;
begin
  Y := 1;
  repeat
    for i := 0 to 6 do
      StringGrid1.Cells[i, Y] :=  RecordSet.Fields[i].Value;
    RecordSet.Move(1, EmptyParam);
    Inc(Y);
  until RecordSet.EOF;
end;

Updating Data

When updating data, you cannot use the default values when opening a dataset. Instead, you should pass in the following parameters:

const
  adOpenDynamic = $00000002;
  adLockOptimistic = $00000003;
  adCmdUnspecified = $FFFFFFFF;
begin
  .. // Call CoCreateInstance
  DSN := 'dsn=' + DSNSTRING;
  RecordSet.Open(SELECTSTRING, DSN, adOpenDynamic, adLockOptimistic, 
    adCmdUnspecified);
  .. // Code ommitted here
end;

This code should work whether you are using Variants or Interfaces. I have included the declarations for the constants in case you can't find a copy of ADODB_TLB.pas.

When you have a dataset open in a read-write mode such as adLockOptimistic, you can update data with the following two lines of code:

RecordSet.Fields[0].Value := Edit1.Text;
RecordSet.Update(EmptyParam, EmptyParam);

The code shown here would allow you to update the first field of the first row of data in a table. You can use the Fields array and the Move function to update any other field you want to access, as shown in the following paragraphs. If you are using Variants, you need not pass any parameters to Update.

In the example program I provide, I use a simple technique to allow you to edit particular fields of the table. If the user clicks on a field in the string table, I show the value in an edit control:

procedure TForm1.StringGrid1SelectCell(Sender: TObject; ACol,
  ARow: Integer; var CanSelect: Boolean);
begin
  Edit1.Text := StringGrid1.Cells[ACOl, ARow];
end;

This function is called whenever the user selects or clicks on a particular field using the mouse or a keyboard.

The code I use to update the database looks like this:

RecordSet.Move(StringGrid1.Row - 1, EmptyParam);
RecordSet.Fields[StringGrid1.Col].Value := Edit1.Text;
RecordSet.Update(EmptyParam, EmptyParam);

The first line moves to the row in the ADO data set that the user has selected in the string grid. The second line of code updates the field of the ADO dataset that the user has selected in the string grid. The third line of code commits your work.

The final step in the updating process is to move back to the beginning of the dataset, and call the same display function used when displaying data in the interface example:

RecordSet.MoveFirst;
Display(RecordSet);

This code has nothing to do with updating the data, it just displays the result of the update to the user.

Summary

You now know the basics of using ADO in a Delphi application. This simple example shows you how to access and edit data on a machine that does not contain the BDE. Its true that you must have ADO installed on the machine or this example will not work. However, ADO should soon be present on most Windows machines.

I should add that there are a set of free data access components that come with ADO, and which are probably installed on your machine if you have ADO installed. These are ActiveX components, so you should be able to use them in Delphi. However, I do not cover that subject in this short paper.


Server Response from: ETNASC04