Delphi/Kylix Database Development

Contents
Introduction 1
Who This Book Is For ............................................................................1
How This Book Is Organized ..................................................................1
VCL or CLX? ....................................................................................2
Components Developed in This Book................................................3
Sample Applications ..........................................................................3
dbExpress............................................................................................5
Databases Used in This Book ............................................................6
Conventions Used in This Book ..............................................................6
Contacting the Author..............................................................................6
1 Establishing and Using Database Connections 7
Connecting to and Disconnecting from a Database ................................8
Establishing the Connection ..............................................................9
Disconnecting from the Database ....................................................13
Connect and Disconnect Events ......................................................14
Retrieving Database Metadata ..............................................................18
GetTableNames..................................................................................18
GetFieldNames..................................................................................18
GetIndexNames..................................................................................19
GetProcedureNames..........................................................................19
GetProcedureParams ........................................................................19
Executing DDL and DML Statements ..................................................27
DDL Commands ..............................................................................27
DML Commands ..............................................................................29
Transaction Support ..............................................................................37
Checking for Transaction Support....................................................38
Starting a Transaction ......................................................................39
Committing a Transaction ................................................................40
Rolling Back a Transaction ..............................................................40
Multiple Transactions ......................................................................40
Providing Feedback During SQL Operations........................................46
Changing the Cursor While Executing SQL Statements ................47
Creating a Callback Event to Monitor SQL Commands ................47
TSQLMonitor......................................................................................49
Using Multiple Feedback Mechanisms ............................................50
Summary................................................................................................552 dbExpress Datasets 57
What Are dbExpress Datasets?..............................................................58
dbExpress Datasets Are Unidirectional............................................58
dbExpress Datasets Are Read-Only ................................................59
dbExpress Datasets Are Lightweight ..............................................59
Types of Datasets ..................................................................................59
Tables................................................................................................59
Queries..............................................................................................60
Stored Procedures ............................................................................60
General-Purpose Datasets ................................................................60
Data Manipulation ................................................................................63
Opening a Dataset ............................................................................63
Closing a Dataset..............................................................................64
Retrieving Field Contents from a Dataset........................................64
Navigating a Dataset ........................................................................65
BLOB Support ......................................................................................69
Parameterized Queries ..........................................................................71
Ordering Data Returned from the Server ..............................................73
Ordering Data from a Table..............................................................73
Ordering Data from a Query ............................................................74
Master/Detail Relationships ..................................................................74
Retrieving Schema Information ............................................................79
Summary................................................................................................91
3 Client Dataset Basics 93
What Is a Client Dataset? ......................................................................94
Advantages and Disadvantages of Client Datasets................................94
Creating Client Datasets ........................................................................95
Creating a Client Dataset at Design-Time ......................................96
Creating a Client Dataset at Runtime ............................................101
Accessing Fields ............................................................................103
Populating and Manipulating Client Datasets ....................................105
Populating Manually ......................................................................105
Populating from Another Dataset ..................................................106
Populating from a File or Stream: Persisting Client Datasets ......106
Example: Creating, Populating, and Manipulating a
Client Dataset ..............................................................................108
Navigating Client Datasets ..................................................................113
Sequential Navigation ....................................................................113
Random-Access Navigation ..........................................................114
Client Dataset Indexes ........................................................................118
Creating Indexes ............................................................................119
Using Indexes ................................................................................121
Retrieving Index Information ........................................................122
Filters and Ranges................................................................................126
Ranges ............................................................................................126
Filters..............................................................................................127
Searching..............................................................................................136
Nonindexed Search Techniques......................................................136
Indexed Search Techniques ............................................................138
Summary..............................................................................................145
4 Advanced Client Dataset Operations 147
Dataset Events......................................................................................148
Disabling Data-Aware Components ....................................................158
BLOBs ................................................................................................162
Notes ..............................................................................................162
Images ............................................................................................162
Streamed Data ................................................................................165
Streamed Components....................................................................167
File BLOBs ....................................................................................168
Limitations of BLOB Fields ..........................................................168
Nested Datasets....................................................................................172
Undo Support ......................................................................................176
Cancel ............................................................................................177
The Change Log ............................................................................177
Viewing the Change Log................................................................182
Cloning Data from Another Client Dataset ........................................186
Maintained Aggregates ........................................................................192
Creating a Maintained Aggregate at Design Time ........................193
Creating a Maintained Aggregate at Runtime................................195
Aggregate Expressions ..................................................................195
Aggregates Across a Group of Records ........................................196
Enabling and Disabling Aggregates ..............................................197
GetGroupState................................................................................197
Miscellaneous Properties ....................................................................197
Constraints......................................................................................197
DisableStringTrim........................................................................198
ReadOnly ........................................................................................199
Summary..............................................................................................199
5 Data-Aware Components 201
What Are Data-Aware Components? ..................................................202
TDataSource ........................................................................................204
Common Data-Aware Component Characteristics..............................205
Modifying Component Data from Code ........................................205
Controlling When the User Is Allowed to Edit Data ....................206
Formatting and Editing Field Values..............................................206CONTENTS
vii
Simple Data-Aware Components ........................................................211
TDBText ..........................................................................................211
TDBEdit ..........................................................................................212
TDBMemo ..........................................................................................212
TDBCheckBox....................................................................................212
TDBRadioGroup................................................................................213
TDBComboBox....................................................................................213
TDBListBox ....................................................................................218
TDBImage ........................................................................................221
VCL-Only Data-Aware Controls ........................................................222
Lookup Data-Aware Controls..............................................................222
TDBNavigator ......................................................................................223
Creating Your Own Data-Aware Components ....................................225
TFieldDataLink..............................................................................225
Setting Up the TFieldDataLink......................................................226
Setting Up a Connection to the Data Source ................................227
Responding to Changes in the Dataset ..........................................227
Updating the Dataset ......................................................................227
Message Handlers ..........................................................................228
Action Handlers..............................................................................228
Data-Aware TDateTimePicker........................................................228
Sample Application..............................................................................232
Summary..............................................................................................236
6 Data-Aware Grids 239
TDBGrid ................................................................................................240
TDBGrid Basic Operation ................................................................240
Customizing Columns ....................................................................241
Grid Options ..................................................................................244
Events ............................................................................................245
Custom Drawing ............................................................................252
Solutions to Common Grid Questions ..........................................257
Limitations......................................................................................263
TClientDataSetGrid............................................................................263
Automatic Sorting ..........................................................................264
Column Customization ..................................................................265
TDBCtrlGrid ........................................................................................266
Properties ........................................................................................267
Events ............................................................................................267
Third-Party Data-Aware Grids ............................................................271
Summary..............................................................................................272
7 Dataset Providers 273
What Is a Dataset Provider? ................................................................274
Connecting to a Dataset ......................................................................275
Resolving Changes to Data..................................................................276
Applying Updates ..........................................................................276
Resolving to a Dataset....................................................................278
Reconciliation Errors......................................................................278
Resolving Changes to BLOB Fields ..............................................290
Refreshing Data from the Server....................................................290
Update Modes ................................................................................291
Provider Options ..................................................................................293
Provider Events....................................................................................295
Changing Field Values on the Server ..................................................297
Intercepting Data..................................................................................298
Optional Parameters ............................................................................300
Master/Detail Relationships ................................................................301
Providing and Resolving Data from Stored Procedures and Joins ....302
Providing and Resolving Data from a Stored Procedure ..............302
Providing and Resolving Data from a Join ....................................302
Connecting to a Local Database ..........................................................308
Using Providers Located on a Different Form ..............................308
One-Stop Shopping: TSQLClientDataSet ......................................309
Limiting the Amount of Data Returned by the Server ..................309
Summary..............................................................................................315
8 DataSnap 317
What Is DataSnap? ..............................................................................318
Creating the Application Server ..........................................................318
Remote Data Modules ....................................................................318
Creating the Application Server’s User Interface ..........................326
Preparing the Application Server for Testing ................................328
Creating the Client Application ..........................................................329
Connecting to a Local Database Connection ................................329
Connecting to a Remote Database Connection..............................330
A Complete Example ..........................................................................336
The Briefcase Model............................................................................340
Stateless Servers ..................................................................................341
Sharing a Connection Between Multiple Client DataSets ..................343
Brokering Connections Between Multiple Servers ............................344
Summary..............................................................................................3459 The ConMan Application 347
What Is ConMan? ................................................................................348
Database Structure ..............................................................................349
Overview of the Code..........................................................................352
The Server Application ........................................................................352
The Client Application ........................................................................358
Room for Improvement ......................................................................373
Summary..............................................................................................373
Appendixes
A Redistributing dbExpress Applications 375
Redistributable Files ............................................................................376
Redistributing a Windows Application ..........................................376
Redistributing a Linux Application ................................................377
Licensing Issues ..................................................................................378
CD-ROM-Based Applications ............................................................378
B dbExpress Plus 379
What Is dbExpress Plus? ....................................................................380
Scripting..........................................................................................380
Enhanced Metadata ........................................................................381
Data Pumping ................................................................................383
For More Information ..........................................................................384
Index 385

Another Delphi Books
Another Kylix Books
Another Database Books
Download