I n t r o d u c t i o n 1
Delphi for Windows
Copyright
Agreement
C h a p t e r 1
I n t r o d u c t i o n
Delphi enables you to create robust database applications quickly and easily. Delphi
database applications can work directly with desktop databases like Paradox, dBASE,
the Local InterBase Server, and ODBC data sources. The Delphi Client/Server edition
also works with remote database servers such as Oracle, Sybase, Microsoft SQL Server,
Informix, InterBase, and ODBC data sources. Delphi client applications can be scaled
easily between mission critical network-based client/server databases, and local
databases on a single machine.
This chapter introduces Delphi’s database tools, including the Data Access and Data
Controls component pages, the Fields Editor, the Database Desktop, and the Database
Forms Expert.
What you should know first
Building a database application is similar to building any other Delphi application. This
book assumes you understand the basic application development techniques covered in
the Delphi User’s Guide, including:
• Creating and managing projects
• Creating forms and managing units
• Working with components, properties, and events
• Writing simple Object Pascal source code
You also need to have a working knowledge of the Database Management System
(DBMS) your Delphi database applications access, whether it is a desktop database such
as dBASE or Paradox, or an SQL server. For information specific to building
client/server applications with Delphi, see Chapter 6, “Building a client/server
application.”
This book assumes you have a basic understanding of relational databases, database
design, and data management. There are many third-party books covering these topics
if you need to learn more about them.
Database Application
Developer’s Guide
2 D a t a b a s e A p p l i c a t i o n D e v e l o p e r ’ s G u i d e
Overview of Delphi’s database features and capabilities
A Delphi database application is built using Delphi database development tools, Delphi
data-access components, and data-aware GUI components. A database application uses
Delphi components to communicate with the Borland Database Engine (BDE), which in
turn communicates with databases. The following figure illustrates the relationship of
Delphi tools and Delphi database applications to the BDE and data sources:
Figure 1.1 Delphi database architecture
The following table summarizes Delphi’s database features.
Table 1.1 Database features summary
Tool Purpose
Data Access components Access databases, tables, stored procedures, and custom component
editors.
Data Control components Provide user interface to database tables.
Database Desktop (DBD) Create, index, and query Paradox and dBASE tables, and SQL
databases. Access and edit data from all sources.
InterBase
for Windows
Borland Database Engine (BDE)/IDAPI
Local Data Sources
Paradox dBASE
Remote Data Sources
Oracle
Sybase
Informix
Delphi IDE
Database
Desktop (DBD)
ReportSmith
SQL Links ODBC Drivers
TCP/IP, SPX/IPX, etc.
BDE
Configuration
Utility
ReportSmith Drivers
ASCII
. . .
Delphi Application
Report
I n t r o d u c t i o n 3
These features enable you to build database applications with live connections to
Paradox and dBASE tables, and the Local InterBase Server through the BDE. In many
cases, you can create simple data access applications with these components and their
properties without writing a line of code.
The BDE is built into Delphi components so you can create database applications
without needing to know anything about the BDE. The Delphi installation program
installs drivers and sets up configuration for Paradox, dBASE, and the Local InterBase
Server, so you can begin working with tables native to these systems immediately. The
BDE Configuration Utility enables you to tailor database connections and manage
database aliases.
Advanced BDE features are available to programmers who need more functionality.
These features include local SQL, which is a subset of the industry-standard SQL that
enables you to issue SQL statements against Paradox and dBASE tables; low-level API
function calls for direct engine access; and ODBC support for communication with other
ODBC-compliant databases, such as Access and Btrieve.
Delphi includes Borland ReportSmith, so you can embed database report creation,
viewing, and printing capabilities in Delphi database applications. Delphi also includes
the Database Desktop (DBD), a tool that enables you to create, index, and query desktop
and SQL databases, and to copy data from one source to another. For more information
about ReportSmith, see Creating Reports. For more information about the DBD, see
Appendix A, “Using Database Desktop.”
The Local InterBase Server is a single-user, multi-instance, 16-bit, ANSI SQL-compliant,
Windows-based version of Borland’s 32-bit InterBase SQL server that is available for
Novell NetWare, Windows NT, and Unix. For more information, see the Local InterBase
Server User’s Guide.
The following table lists the additional database features available in the Client/server
edition of Delphi. These features extend Delphi’s database capabilities to access remote
ReportSmith Create, view, and print reports.
Borland Database Engine (BDE) Access data from file-based Paradox and dBASE tables, and from
local InterBase server databases.
BDE Configuration Utility Create and manage database connection Aliases used by the BDE.
Local InterBase Server Provides a single-user, multi-instance desktop SQL server for
building and testing Delphi applications, before scaling them up to a
production database, such as Oracle, Sybase, Informix, or InterBase
on a remote server.
InterBase SQL Link Native driver that connect Delphi applications to the Local InterBase
Server.
Table 1.1 Database features summary (continued)
4 D a t a b a s e A p p l i c a t i o n D e v e l o p e r ’ s G u i d e
SQL database servers such as Sybase, Microsoft SQL Server, Oracle, Informix, and
InterBase.
SQL Links provide Delphi applications with SQL access to data residing on remote
servers, including Sybase, Microsoft SQL Server, Oracle, and Informix. When an SQL
Link driver is installed, SQL statements are passed directly to the server for parsing and
execution. For more information about using passthrough SQL, see Chapter 5, “Using
SQL in applications.”
What is a database?
Delphi programmers should understand some basic concepts about databases, data,
and data access, before building database applications. A database consists of one or
more tables, where each table contains a series of columns into which records (also
called “rows”) are stored. Each record is identical in structure. For example, a database
of addresses consists of a table with name, street address, city, state, and zipcode
columns. The intersection of a single column and row is referred to as a field. Fields
contain values. The following figure illustrates these concepts:
Figure 1.2 Structure of a table
The current field is one field in a single record. The current record is a single record in a
multi-record set that is the focus of attention. For example, some Delphi database
applications display multiple columns and records in a grid format for editing. As far as
Delphi controls are concerned, only one field in a single record is “current,” meaning
that editing tasks affect only the data in that field.
Different databases vary widely in structure. A database in Paradox consists of one or
more files, each of which contains a single table or index, but an SQL relational database
on a remote server generally consists of a single file that contains all tables, indices, and
Table 1.2 Additional Delphi Client/Server database features
SQL Drivers Both SQL Links and ReportSmith provide native drivers that connect Delphi
database applications to remote SQL database servers, such as Oracle, Sybase,
Microsoft SQL Server, Informix, and InterBase.
Visual Query Builder Creates SQL statements by visually manipulating tables and columns.
CustNo Name Street City
1221
1231
1251
1254
Kauai Dive Shoppe
Unisco
Sight Diver
Cayman Divers Wo
4-976 Sugarloaf Highway
PO Box Z-547
1 Neptune Lane
PO Box 541
Kapaa Kauai
Freeport
Kato Paphos
Column names
Records
Current record Current field
I n t r o d u c t i o n 5
other database structures. Delphi’s Data Access and Data Control components
encapsulate the underlying structure of the databases your application uses, so that
your application can present the same interface to an end user whether it accesses a local
Paradox file or a database on a remote SQL server.
The information in most databases is constantly changing. When you access a
networked database from a Delphi application, many users may be accessing and
updating the database at the same time. When any database application accesses a
database, whether to process a query or generate a report, the application receives a
snapshot of the database as it was at the time the application accessed the database. An
application’s view of data may differ from the data currently in the database, so
database applications should always be robust enough to react to such data changes. For
more information about building client/server applications that access remote data, see
Chapter 6, “Building a client/server application.”
What is data?
In this book, “data” refers to information stored in a database. Data may be a single item
in a field, a record that consists of a series of fields, or a set of records. Delphi
applications can retrieve, add, modify, or delete data in a database.
What is data access?
Delphi applications can access data from desktop database tables on a file server or local
disk drive and from remote database servers. To access a data source, a Delphi
application uses Data Access components to establish a connection through the BDE.
The installation program for Delphi installs drivers and sets up configurations for
Paradox, dBASE, and the Local InterBase Server so you can begin working with tables
native to these systems immediately.
To connect to another data source requires the installation of a driver for that specific
database and subsequent configuration of the BDE to recognize the driver. Connecting
to remote database servers requires the Client/Server edition of Delphi that includes
SQL Links to access to Sybase, Microsoft SQL Server, Oracle, Informix, and InterBase on
NT, NetWare, and Unix servers. For more information about installing and configuring
the SQL Link drivers, see the SQL Links User’s Guide.
The BDE uses aliases as convenient shorthand names for often-used data sources,
whether local or remote. The BDE Configuration Utility enables you to define and
modify aliases that Delphi applications can use immediately. For more information
about defining aliases, see Appendix B, “Using the BDE configuration utility.”
Once drivers are installed and network connections established, Delphi applications can
access data from any authorized server. The examples in Chapter 2 demonstrate
techniques for accessing data from a database—specifically, sample data tables that are
shipped and installed as part of the Delphi package. Although the example project in
the next chapter deals with local desktop data, the techniques for accessing remote data
are essentially the same, as subsequent chapters demonstrate.
Note An SQL version of the example project is provided in the DEMOS directory.
6 D a t a b a s e A p p l i c a t i o n D e v e l o p e r ’ s G u i d e
Data sources
Delphi database applications get their data through the BDE. The different data sources
(not to be confused with the TDataSource component) that the BDE can use are shown in
Table 1.3.
Understanding Delphi database architecture
Delphi uses object-oriented components to create database applications, just as it does
with non-database applications. Like standard components, database components have
attributes, or properties, that are set by the programmer at design time. These properties
can also be set programmatically at run time.
Database components have default behavior that enables them to perform useful
functions with little or no programming. The Delphi Component palette provides two
database component pages:
• The Data Access page contains Delphi objects that simplify database access by
encapsulating database source information, such as the database to connect to, the
tables in that database to access, and specific field references within those tables.
Examples of the most frequently used data access objects include TTable, TQuery,
TDataSource, and TReport.
• The Data Controls page contains data-aware user interface components for displaying
database information in forms. Data Control components are like standard user
interface components, except that their contents can be derived from or passed to
database tables. Examples of the most frequently used data control components
include TDBEdit, TDBNavigator, and TDBGrid.
Datasets, such as TTable, TQuery, and TStoredProc components, are not visible at run
time, but provide applications their connection to data through the BDE. Data Control
components are attached to dataset components by a TDataSource component, to
provide a visual interface to data.
Table 1.3 Delphi data sources
Data source Description File extension
Paradox Tables created with Paradox or Database
Desktop. Each table is in a separate file.
.DB
dBASE Tables created with dBASE or Database Desktop.
Each table is in a separate file.
.DBF
ASCII files Tables created with Database Desktop. Each table
is in a separate file.
.TXT
Local InterBase Server Database created with InterBase Windows ISQL.
Multiple tables in a single database file.
.GDB
SQL Database Server: Oracle, Sybase,
Microsoft SQL Server, Informix, InterBase
Database created with server-specific tools, or the
DBD, accessed across network with SQL Links.
Delphi Client/Server Edition only.
Depends on server
ODBC data sources Databases such as Microsoft Access, Btrieve,
FoxPro, etc.
Depends on data source
I n t r o d u c t i o n 7
The following figure illustrates how Data Access and Data Control components relate to
data, to one another, and to the user interface in a Delphi database application:
Figure 1.3 Database components architecture
As this figure illustrates, a form usually contains at least three database components: a
dataset component (TTable and TQuery in the figure) that communicates with the BDE; a
TDataSource component that acts as a conduit between a dataset component and the
user interface; and one or more data control components, such as TDBEdit or TDBGrid,
that enable a user to browse, edit, or enter data.
Overview of the Data Access page
The Data Access page of the Delphi Component palette provides a set of database
encapsulation objects that simplify database access.
JoePapa