Data Connectivity In Dephi Database Application - Using DBExpress

Dephi Database Application - Using DBExpress

What is dbExpress?

dbExpress is a light-weight, extensible, cross-platform, high-performance mechanism for accessing data from SQL servers. dbExpress provides connectivity to databases for the Windows, .NET and Linux (using Kylix) platforms.
Initially designed to replace the BDE, dbExpress (introduced in Delphi 6), allows you to access different servers - mySQL, Interbase, Oracle, MS SQL Server, Informix.



One of the most significant features of dbExpress lies in the fact that it accesses databases using unidirectional datasets. Unidirectional datasets do not buffer data in memory - such a dataset cannot be displayed in a DBGrid. To build a user interface using dbExpress you will need to use two more components: TDataSetProvider and TClientDataSet.

Migrating Borland

®

Database Engine

Applications to

dbExpress

The dbExpress architecture

dbExpress (formerly dbExpress) was designed to meet the

following six goals.

•

Minimize size and system resource use.

•

Maximize speed.

•

Provide cross-platform support.

•

Provide easier deployment.

•

Make driver development easier.

•

Give the developer more control over memory usageand network traffic.

dbExpress drivers are small and fast because they provide very

limited functionality. Each driver is implemented as a single

DLL on the Windows

® platform and as a single shared object

library on the Linux

® platform. A dbExpress driver implements

five interfaces that support fetching metadata, executing SQL

statements and stored procedures, and returning a read only

unidirectional cursor to the result set. However, when used with

the DataSetProvider and ClientDataSet to implement the

provide/resolve data access strategy of Borland, dbExpress gives

you a full-featured, high performance, high concurrency system

for working with data in SQL databases.

architecture uses four components to provide

data access and editing. The first is the SQLConnection

component that provides a connection to the dbExpress driver for

the database you are using. Next is one of the dbExpress dataset

components that provides data by executing a

SQL SELECT

statement or calling a stored procedure. The third component is

the DataSetProvider, and the fourth is the ClientDataSet. When

you open the ClientDataSet, it requests data from the

DataSetProvider. The DataSetProvider opens the query or stored

procedure component, retrieves the records, closes the query or

stored procedure component, and supplies the records, with any

required metadata, to the ClientDataSet.

The ClientDataSet holds the records in memory while they are

viewed and modified. As records are added, deleted, or updated,

either in code or via the user interface, the ClientDataSet logs all

changes in memory. To update the database, you call the

ClientDataSet ApplyUpdates method. ApplyUpdates transmits the

change log to the DataSetProvider. The provider starts a

transaction, then creates and executes SQL statements to apply the

changes to the database. If all changes are applied successfully, the

provider commits the transaction; if not, it rolls the transaction

back. Database updates may fail if, for example, a change violates a

business rule enforced by a trigger or if another user has changed a

record you are trying to update since you read the record. If an

error occurs, the transaction is rolled back, and the ClientDataSet

OnReconcileError event is fired, giving you control of how the

error is handled.

Short transaction life

Long transactions force the database server to hold locks, which

reduces concurrency and consumes database server resources.

With provide/resolve architecture, transactions exist for a moment

when updates are applied. This dramatically reduces resource

consumption and improves concurrency on a busy database server.

Make any rows editable

Rows returned by multi-table joins, stored procedures, or readonly

views cannot be edited directly. By using the ProviderFlags

property of the field objects to identify the fields that should be

updated and the DataSetProvider OnGetTableName event to

supply the name of the table, many read only datasets can be

edited easily.

Instantaneous sorting and searching

Since the ClientDataSet holds records in memory, they can be

sorted quickly. If an in-memory sort is too slow, you can create

indexes on the ClientDataSet data at design time or runtime.

These in-memory indexes let you change the viewing order of

records or locate records virtually instantaneously without the

overhead of maintaining indexes in the database.

View subsets of data

Filter expressions using SQL WHERE syntax let you easily

display a subset of the records in a ClientDataSet without the

overhead of executing another query on the database server.

Easier deployment

An application using dbExpress requires just two DLLs to

function. The first is the dbExpress driver, for example

DBEXPINT.DLL

in the case of Borland InterBase,® and the

second is

MIDAS.DLL, the ClientDataSet support library.

Together, these two DLLs are less than half a megabyte in size.

This minimizes application size and simplifies installation. If you

prefer not to distribute these DLLs, you can compile them

directly into your EXE. Deployment on Linux is identical,

except that the DLLs are replaced by two shared object libraries.

The SQLConnection component

To create a simple dbExpress application, begin with the

following steps.

1. Create a

new application and add a data module to

it. Name the data module

MainDm.

2. Use the

Project Options dialog to make sure that the

data module is created automatically before the main

form is created.

3. Drop a

SQLConnection component from the

dbExpress page of the component palette on

the

data module

.

4. Name the SQLConnection component

EmployeeConnection

and set its DriverName

property to

InterBase.

5. Open the

property editor for the Params property and

set the

database parameter to the path to the sample

InterBase

EMPLOYEE.GDB database. On most

installations, this will be

c:\program

files\Borland\interbase\examples\dat

abase\employee.gdb.

6. Change the

UserName and Password parameters if

you require different values to connect to your

InterBase server.

7. Set the

LoginPrompt property to false so you will not

be prompted for a username and password each time

you run the program.

8. Set the

Connected property to true to test your

connection, and then set it to

false again.

The SQLConnection component provides a database

connection for any number of dataset components. You can use

multiple SQLConnection components to connect to many

databases simultaneously. There are three ways to define a

connection to a database. You can use an existing named

connection, create a new named connection or put the

connection parameters in the Params property of the

SQLConnection component. To use an existing named

connection just set the ConnectionName property.

The dbExpress Connection Editor

To create a new named connection, double-click the

SQLConnection component

to open the dbExpress

Connection Editor. The Connection Name list box, on the left,

shows any connections that have already been defined. The

Driver drop-down list lets you

filter the Connection Names to

show only the connections for the driver you select. The

Connection Settings grid on the right shows the connection

settings for the selected connection. All of the connections you

create are stored in the
dbxconnections.ini file.


A video From

http://www.youtube.com/watch?v=Zjt-xe6f6gk