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:
- x:...DelphiDemosData
- 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.
| 
