Databases - Delphi Database Connections

Delphi is a great language, but, based on the help files, how to connect it to databases is not exactly clear.

On the tools pallette, there are 2 applicable tabs

Data Access
Data Controls

The data controls are actually fairly simple to connect once the data access is configured. Just use the pick lists to set Data Source and Data Field, in that order.

Therefore, this page will discuss setting the Data Access components which are used to define Data Sources.

TTable -> TDataSource -> TQuery
TTable -> TDataSource -> TDBComboBox
TDatabase -> TTable -> TDataSource -> TDBComboBox

TTable connects to an ODBC database, a directory for Paradox tables,
or a TDatabase component.
DatabaseName, TableName

The pick list on TTable.DatabaseName lists available ODBC aliases
and TDatabase.DatabaseName values.

TDataSource - DataSet (TDataSet/TTable)
              Use this to handle record level processing.
              It keeps all related controls pointing to the same record.
              All data aware components must attach to a DataSource.

TDBComboBox - DataSource (TDataSource), DataField

more  

First, Next, Last (and the like) are abstract members of TDataSet.
DataSet is a property of TDataSource.
TTable is a desendent of TDataSet.

Use TDatabase when you are using the BDE. Explicit declaration is not necessary unless you want access to the properties and methods at run time. The main advantage is the ability to disable the username/password request dialog box and/or to supply that data programatically.

If a TTable is used without explicitly associating it to an existing TDatabase, then an implicit TDatabase is created as necessary to control the connection.

Basic Procedure to See a Table's Contents

Place 3 components on a new (blank) Form.

Data Access / Table
Data Access / DataSource
Data Controls / DBGrid

Set the following properties

Table1.DatabaseName	Select from the list of ODBC databases. (I am assuming that your system has at least one defined.)
Table1.TableName	Pick a value from the list of available tables. (You will probably be prompted for a username and password.)
Table1.Active	Set this to True. (The default is always false. True allows you to see data, False does not.)
DataSource1.DataSet	Pick the only value from the picklist. (This will be the TTable added ealier.)
DBGrid1.DataSource	Pick the only option from the picklist.

At this point, the table's data should be visible.

In order to step through the records, double click the TTable component (this opens the Fields editor dialog box) and use the VCR buttons to step through the records.

TDatabase

Use TDatabase if you want to connect to several tables in the same database, or if you want to suppress the annoying login prompt (You bet I want to :).

Database1.AliasName	Select from the list of ODBC or BDE database aliases. (I am assuming that your system has at least one defined.) Setting this clears Database1.DatabaseName.
Database1.DatabaseName	This is the name that TTable components will link to. This can be the same as Database1.AliasName, you may make up a name, or you may enter the fully qualified path to a Paradox database (directory). An MS Access mdb file can not be entered here.
Database1.LoginPrompt	Set this False to disable the default dialog box.
Database1.OnLogin	Attaching code to this event will also disable the default dialog box. (Hardcoding userID's and passwords is considered to be a security problem.)
Database1.Params	If Database1.AliasName is blank, and Database1.DriverName is assigned to MSACCESS, then this is how to specify an MS Access database. DATABASE NAME=H:\Shared\xxx\Databases\Converted.mdb ODBC DSN=

Note that when connecting directly to an MS Access database, the fully qualified path goes in the Params string list, not the the DatabaseName parameter.

I can not figure out how to get Microsoft Access Driver (*.mdb) to work.

Sequence to connect to an MS Access mdb file without using ODBC.

Place a TDatabase component and double click it. This opens a dialog box
Enter a Name, this is used to link to the TTable component
Set Driver Name to MSACCESS
Click the Defaults button
Locate the mdb file using Windows Explorer and copy the fully qualified path to the clipboard
Paste the clipboard after DATABASE NAME= (don't place it in double quotes)
While debugging and learning, leave Login prompt checked (it causes a dialog box to be displayed each time a new database connection is made), otherwise, un-check it (unless the database has a password).
Add and connect TTable, TDataSource, and TDBGrid as shown below. Be sure to set TTable.TableName and TTable.Active := True. (That automatically sets TDatabase.Connected := True.)
```
TDatabase -> TTable -> TDataSource -> TDBGrid
```

Field Components

Whenever a TTable (or other TDBDataSet based component) is placed on a form, Delphi automatically creates components (of base type TField) for all the available fields. By using the Fields editor dialog box (double click the TTable component), you can control which field components are created. In addition, you can explicitly create calculated and lookup fields.

There are a number of advantages to creating explicit field components.

You must use the Fields editor to add and remove presistent fields.

BDE

The Borland Database Engine (BDE) provides the ability to access various types of databases using the Data Access components. The main downside is that the BDE must be installed before your programs will work.

However, from the developer's point of view, the BDE based Data Explorer is an excelent tool that you should be very familiar with. (From inside Delphi, select Database / Explore) This tool will allow you to browse tables - you can see the data, determine the structure, and the like.

Run the BDE administrator to define aliases.

ADO

ADO allows you to develop database applications which do not need the BDE to run. You use MDAC instead. The main difference is that the MDAC is part of Windows 98 and beyond (ie, YOU don't have to install it).

Free on-line course

ADO is not part of Delphi 5 Professional - you must import it. Pay special attention to these installation instructions. Project / Import Type Library and select

Microsoft ADO Data Control 6.0 (SP3) (OLEDB) (Version 6.0)
Microsoft ADO Ext. 2.1 for DLL and Security (Version 2.1)

However, the components do not have custom icons and there is a lack of help.

KA DAO

One alternative is to use KA Dao, a freeware component is available from www.delphi.pari.bg, www.delphiwarrior.freeservers.com, or www.torry.net.

The available help is pretty good, but it is not integrated into the Delphi help. Just open KADAO.HLP. Basic instructions are described via

Contents / Tips and tricks /
     How to make an simple application using KADao as a Table component?

In order to access an *.mdb file,

On the form, place

 Tab           Component
KA DAO        TKADaoDatabase
KA DAO        TKADaoTable
Data Access   TDataSource
Data controls TDBEdit
Data controls TDBNavigator

Set the following properties in the order shown

Component Property Value Comments
KADaoDatabase1 DatabaseType Access This is the default value
Database name of the *.mdb file
MdbVersionAutoDetect True Optional, may help
Version 3.5
3.6
4.0 Access XX-97
Access 2000-2002
Access 2002 SP-1
Connected True This connects the database
KADaoTable1 Database KADaoDatabase1
TableName Pick one from the list
Active True
DataSource1 DataSet KADaoTable1
Enabled True
DBEdit1 DataSource DataSource1
DataField Select any field
DBNavigator1 DataSource DataSource1
VisibleButtons Disable those you don't need

Component	Property	Value	Comments
KADaoDatabase1	DatabaseType	Access	This is the default value
Database		name of the *.mdb file
MdbVersionAutoDetect	True	Optional, may help
Version	3.5 3.6 4.0	Access XX-97 Access 2000-2002 Access 2002 SP-1
Connected	True	This connects the database
KADaoTable1	Database	KADaoDatabase1
TableName		Pick one from the list
Active	True
DataSource1	DataSet	KADaoTable1
Enabled	True
DBEdit1	DataSource	DataSource1
DataField		Select any field
DBNavigator1	DataSource	DataSource1
VisibleButtons		Disable those you don't need

KADaoDatabase1 also has UserName, Password, and DatabasePassword which may need to be set. The difference between Password and DatabasePassword is not clearly explained.

There are 18 related dcu's - therefore, I keep them all with the original KADAO directory and link to it.

Tools / Environment Options... / Library / Library path

Running from a CD-ROM

Running software from a CD-ROM (a read only device) is fairly simple

Set KADaoDatabase1.ReadOnly to True
The default is False

Set KADaoDatabase1.Exclusive to True
The default is False

You can test the first step by right clicking the *.mdb file in Windows Explorer and setting its properties to ReadOnly.

Normally, when an *.mdb file is opened, a lock file (*.ldb) is created in the same directory as the *.mdb file. However, since a CD-ROM is read-only, you need to tell the jet database engine not to create the lock file - ReadOnly / Exclusive does this.

"per record" code

You can place "per record" code under either

DataSource1DataChange
KADaoTable1AfterScroll

They both appear to work.

Errors

My Windows 98 system refuses to run the exe because it does not have a DAO license. A Windows 98 se systems has no problem.

dbExpress

dbExpress is supposedly the replacement for the BDE (which is no longer supported). Using it requires loading various *.dll files on the client machines.

Delphi, dbExpress And MySQL explains how to use dbExpress to connect to MySQL. It also provides a warning - dbExpress: Delphi 6 Versus Delphi 7 - not to run both Delphi 6 and Delphi 7 applications on the same machine when accessing Firebird databases - the dbExpress *.dll's are not compatible and will cause errors.

Firebird

Firebird is an open source (free) version of Borland's Interbase SQL database engine. There are several different ways to access tables

Interbase components (uses the BDE)
ODBC/ADO (I was never able to get this to work on Windows XP)
dbExpress (can NOT have both Delphi 6 and Delphi 7 applications on the same machine)

Using the Interbase Components

In order to access an *.gdb file,

On the form, place

 Tab           Component
InterBase     TIBDatabase
InterBase     TIBTransaction
InterBase     TIBTable
Data Access   TDataSource
Data controls TDBEdit
Data controls TDBNavigator

Set the following properties in the order shown

Component Property Value Comments
IBDatabase1 DatabaseName Set this to the *.gdb or *.fdb file
Connected True This connects the database
IBTransaction1 DefaultDatabase IBDatabase1
Active True
IBTable1 Database IBDatabase1
Transaction IBTransaction1 Automatically selected
TableName Pick one from the list
Active True
DataSource1 DataSet IBTable1
Enabled True Default value
DBEdit1 DataSource DataSource1
DataField Select any field
DBNavigator1 DataSource DataSource1
VisibleButtons Disable those you don't need

Component	Property	Value	Comments
IBDatabase1	DatabaseName		Set this to the .gdb or .fdb file
Connected	True	This connects the database
IBTransaction1	DefaultDatabase	IBDatabase1
Active	True
IBTable1	Database	IBDatabase1
Transaction	IBTransaction1	Automatically selected
TableName		Pick one from the list
Active	True
DataSource1	DataSet	IBTable1
Enabled	True	Default value
DBEdit1	DataSource	DataSource1
DataField		Select any field
DBNavigator1	DataSource	DataSource1
VisibleButtons		Disable those you don't need

If you edit some parameter and no data is displayed, check IBDatabase1.Connected, IBTransaction1.Active, and IBTable1.Active, in that order. (Actually, just setting IBTable1.Active to True will automatically set the others.)

In order to automatically connect to a Firebird database, set the following

IBDatabase1.Params
IBDatabase1.LoginPrompt to false

To connect to the Interbase example database distributed with Delphi, set IBDatabase1.DatabaseName to

MachineID:C:\Program Files\Common Files\Borland Shared\Data\employee.gdb

According to a newsgroup post, TIBTable should never be used with Client/Server applications - use IBDataset instead. (They are both based on TIBCustomDataSet) This partly because a TIBTable.Refresh will not return inserted items and IBDataset.Refresh will (I guess that the query is reexecuted).

ODBC/ADO

In order to access a Firebird database via ODBC, you must first define an ODBC connection (DSN). Select Control Panel / Administrative Tools / Data Sources (ODBC) / System DSN / Add... Then select the Interbase driver. In the dialog box, only these 2 fields are important.

Field	Comment
Data Source Name	This is the name (alias) your program uses
Database	Fully qualified path when the Network protocol is Local

Once you have created the DSN, you can test it by re-opening the definition (double click or select Configure...), entering the Username and Password, and clicking Test Connect. Unfortunately, this always failed on my Windows XP system.

In order to access an *.gdb file via ODBC/ADO (I was never able to get this to work),

Add these components - KADaoDatabase, KADaoTable, DataSource, DBGrid
Set KADaoTable1.Database to KADaoDatabase1
Set DataSource1.DataSet to KADaoTable1
Set DBGrid1.DataSource to DataSource1

I got lots of errors related to the Workgroup information file, but, of cource, there is no help on what that may mean. I assume that this is related to the ODBC failure above.

KADaoDatabase1.UserName	sysdba
KADaoDatabase1.Password	masterkey
KADaoDatabase1.DatabaseType	ODBC
KADaoDatabase1.Database	Select from the list of ODBC database aliases. (I am assuming that your system has at least one defined.)
KADaoDatabase1.LoginPrompt	Set this False to disable the default dialog box.
KADaoDatabase1.OnLogin	Attaching code to this event will also disable the default dialog box. (Hardcoding userID's and passwords is considered to be a security problem.)

References

efg's Computer Lab provides DBF Files -- With or Without ADO: This provides very detailed step by step instructions on how to connect to an *.dbf file using both a TTable component and Borland's ADO components. Both examples use Data Modules.

Author: Robert Clemenzi - clemenzi@cpcug.org