Delphi Programming Guide
Delphi Programmer 

Menu  Table of contents

Part I - Foundations
  Chapter 1 – Delphi 7 and Its IDE
  Chapter 2 – The Delphi Programming Language
  Chapter 3 – The Run-Time Library
  Chapter 4 – Core Library classes
  Chapter 5 – Visual Controls
  Chapter 6 – Building the User Interface
  Chapter 7 – Working with Forms
Part II - Delphi Object-Oriented Architectures
  Chapter 8 – The Architecture of Delphi Applications
  Chapter 9 – Writing Delphi Components
  Chapter 10 – Libraries and Packages
  Chapter 11 – Modeling and OOP Programming (with ModelMaker)
  Chapter 12 – From COM to COM+
Part III - Delphi Database-Oriented Architectures
  Chapter 13 – Delphi's Database Architecture
  Chapter 14 – Client/Server with dbExpress
  Chapter 15 – Working with ADO
  Chapter 16 – Multitier DataSnap Applications
  Chapter 17 – Writing Database Components
  Chapter 18 – Reporting with Rave
Part IV - Delphi, the Internet, and a .NET Preview
  Chapter 19 – Internet Programming: Sockets and Indy
  Chapter 20 – Web Programming with WebBroker and WebSnap
  Chapter 21 – Web Programming with IntraWeb
  Chapter 22 – Using XML Technologies
  Chapter 23 – Web Services and SOAP
  Chapter 24 – The Microsoft .NET Architecture from the Delphi Perspective
  Chapter 25 – Delphi for .NET Preview: The Language and the RTL
       
  Appendix A – Extra Delphi Tools by the Author
  Appendix B – Extra Delphi Tools from Other Sources
  Appendix C – Free Companion Books on Delphi
       
  Index    
  List of Figures    
  List of tables    
  List of Listings    
  List of Sidebars  

 
Previous Section Next Section

Using dbGo Components

Programmers familiar with the BDE, dbExpress, or IBExpress should recognize the set of components that make up dbGo (Table 15.2).

Table 15.2: dbGo Components

dbGo Component

Description

BDE Equivalent Component

ADOConnection

Connection to a database

Database

ADOCommand

Executes an action SQL command

No equivalent

ADODataSet

All-purpose descendant of TDataSet

No equivalent

ADOTable

Encapsulation of a table

Table

ADOQuery

Encapsulation of SQL SELECT

Query

ADOStoredProc

Encapsulation of a stored procedure

StoredProc

RDSConnection

Remote Data Services connection

No equivalent

The four dataset components (ADODataSet, ADOTable, ADOQuery, and ADOStoredProc) are implemented almost entirely by their immediate ancestor class, TCustomADODataSet. This component provides the majority of dataset functionality, and its descendants are mostly thin wrappers that expose different features of the same component. As such, the components have a lot in common. In general, however, ADOTable, ADOQuery, and ADOStoredProc are viewed as "compatibility" components and are used to aid the transition of knowledge and code from their BDE counterparts. Be warned, though: These compatibility components are similar to their counterparts but not identical. You will find differences in any application except the most trivial. ADODataSet is the component of choice partly because of its versatility but also because it is closer in appearance to the ADO Recordset interface upon which it is based. Throughout this chapter, I'll use all the dataset components to give you the experience of using each.

A Practical Example

Enough theory: Let's see some action. Drop an ADOTable onto a form. To indicate the database to connect to, ADO uses connection strings. You can type in a connection string by hand if you know what you are doing. In general, you'll use the connection string editor (the property editor of the ConnectionString property), shown in Figure 15.1.

Click To expand
Figure 15.1: Delphi's connection string editor

This editor adds little value to the process of entering a connection string, so you can click Build to go straight to Microsoft's connection string editor, shown in Figure 15.2.

Click To expand
Figure 15.2: The first page of Microsoft's connec-tion string editor

This is a tool you need to understand. The first tab shows the OLE DB providers and service providers installed on your computer. The list will vary according to the version of MDAC and other software you have installed. In this example, select the Jet 4.0 OLE DB provider. Double-click Jet 4.0 OLE DB Provider, and you will be presented with the Connection tab. This page varies according to the provider you select; for Jet, it asks you for the name of the database and your login details. You can choose an Access MDB file installed by Borland with Delphi 7: the dbdemos.mdb file available in the shared data folder (by default, C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb). Click the Test Connection button to test the validity of your selections.

The Advanced tab handles access control to the database; here you specify exclusive or read-only access to the database. The All tab lists all the parameters in the connection string. The list is specific to the OLE DB provider you selected on the first page. (You should make a mental note of this page, because it contains many parameters that are the answers to many problems.) After closing the Microsoft connection string editor you'll see in the Borland ConnectionString property editor the value that will be returned to the ConnectionString property (here split on multiple lines for readability):

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb;
Persist Security Info=False

Connection strings are just strings with many parameters delimited by semicolons. To add, edit, or delete any of these parameter values programmatically, you must write your own routines to find the parameter in the list and amend it appropriately. A simpler approach is to copy the string into a Delphi string list and use its name/value pairs capability: This technique will be demonstrated in the JetText example covered in the section "Text Files through Jet."

Now that you have set the connection string, you can select a table. Drop down the list of tables using the TableName property in the Object Inspector. Select the Customer table. Add a DataSource component and a DBGrid control and connect them all together; you are now using ADO in an actual—though trivial—program (available in the source code as FirstAdoExample). To see the data, set the Active property of the dataset to True or open the dataset in the FormCreate event (as in the example) to avoid design-time errors if the database is not available.

Tip 

If you are going to use dbGo as your primary database access technology, you might want to move the DataSource component to the ADO page of the Component Palette to avoid moving back and forth between the ADO page and the Data Access page. If you use both ADO and another database technology, then you can simulate installing DataSource on multiple pages by creating a Component Template for a DataSource and installing it on the ADO page.

The ADOConnection Component

When you use an ADOTable component this way, it creates its own connection component behind the scenes. You do not have to accept the default connection it creates. In general, you should create your own connection using the ADOConnection component, which has the same purpose as the dbExpress SQLConnection component and the BDE Database component. It allows you to customize the login procedure, control transactions, execute action commands directly, and reduce the number of connections in an application.

Using an ADOConnection is easy. Place one on a form and set its ConnectionString property the same way you would for the ADOTable. Alternatively, you can double-click an ADOConnection component (or use a specific item of its Component Editor, in its shortcut menu) to invoke the connection string editor directly. With the ConnectionString set to the proper database, you can disable the login dialog box by setting LoginPrompt to False. To use the new connection in the previous example, set the Connection property of ADOTable1 to ADOConnection1. You will see ADOTable1's ConnectionString property reset because the Connection and ConnectionString properties are mutually exclusive. One of the benefits of using an ADOConnection is that the connection string is centralized instead of scattered throughout many components. Another, more important, benefit is that all the components that share the ADOConnection share a single connection to the database server. Without your own ADOConnection, each ADO dataset has a separate connection.

Data Link Files

So, an ADOConnection allows you to centralize the definition of a connection string within a form or data module. However, even though this is a worthwhile step forward from scattering the same connection string throughout all ADO datasets, it still suffers from a fundamental flaw: If you use a database engine that defines the database in terms of a filename, then the path to the database file(s) is hard-coded in the EXE. This makes for a fragile application. To overcome this problem, ADO uses Data Link files.

A Data Link file is a connection string in an INI file. For example, Delphi's installation adds to the system the dbdemos.udl file, with the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;
  Data Source=C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb 

Although you can give a Data Link file any extension, the recommended extension is .UDL. You can create a Data Link using any text editor, or you can right-click in Windows Explorer, select New, select Text Document, rename the file with a .UDL extension (assuming extensions are displayed in your configuration of Explorer), and then double-click the file to invoke the Microsoft connection string editor.

When you select a file in the Connection editor, the ConnectionString property will be set to 'FILE NAME =' followed by the actual filename, as demonstrated by the DataLinkFile example. You can place your Data Link files anywhere on the hard disk, but if you are looking for a common, shared location, then you can use the DataLinkDir function in the ADODB Delphi unit. If you haven't altered MDAC's defaults, DataLinkDir will return the following:

C:\Program Files\Common Files\System\OLE DB\Data Links

Dynamic Properties

Imagine that you are responsible for designing a new database middleware architecture. You have to reconcile two opposing goals of a single API for all databases and access to database-specific features. You could take the approach of designing an interface that is the sum of all the features of every database ever created. Each class would have every property and method imaginable, but it would only use the properties and methods it had support for. It doesn't take much discussion to realize that this isn't a good solution. ADO has to solve these apparently mutually exclusive goals, and it does so using dynamic properties.

Almost all ADO interfaces and their corresponding dbGo components have a property called Properties that is a collection of database-specific properties. These properties can be accessed by their ordinal position, like this:

ShowMessage(ADOTable1.Properties[1].Value);

But they are more usually accessed by name:

ShowMessage(ADOConnection1.Properties['DBMS Name'].Value);

Dynamic properties depend on the type of object and also on the OLE DB providers. To give you an idea of their importance, a typical ADO Connection or Recordset has approximately 100 dynamic properties. As you will see throughout this chapter, the answers to many ADO questions lie in dynamic properties.

Tip 

An important event related to the use of dynamic properties is OnRecordsetCreate, which was introduced in a Delphi 6 update and is available in Delphi 7. OnRecordsetCreate is called immediately after the recordset has been created, but before it is opened. This event is useful when you're setting dynamic properties that can be set only when the recordset is closed.

Getting Schema Information

In ADO, you can retrieve schema information using the ADOConnection component's OpenSchema method. This method accepts four parameters:

  • The kind of data OpenSchema should return. It is a TSchemaInfo value: a set of 40 values including those for retrieving a list of tables, indexes, columns, views, and stored procedures.

  • A filter to place on the data before it is returned. You will see an example of this parameter in a moment.

  • A GUID for a provider-specific query. This parameter is used only if the first parameter is siProviderSpecific.

  • An ADODataSet into which the data is returned. This parameter illustrates a common theme in ADO: Any method that needs to return more than a small amount of data will return its data as a Recordset, or, in Delphi terms, an ADODataSet.

To use OpenSchema, you need an open ADOConnection. The following code (part of the OpenSchema example) retrieves a list of primary keys for every table into an ADODataSet:

ADOConnection1.OpenSchema(siPrimaryKeys, EmptyParam, EmptyParam, ADODataSet1);

Each field in a primary key has a single row in the result set. So, a table with a composite key of two fields has two rows. The two EmptyParam values indicate that these parameters are given empty values and are ignored. The result of this code is shown in Figure 15.3, after resizing the grid with some custom code.

Click To expand
Figure 15.3: The OpenSchema example retrieves the primary keys of the database tables.

When EmptyParam is passed as the second parameter, the result set includes all information of the requested type for the entire database. For many kinds of information, you will want to filter the result set. You can, of course, apply a traditional Delphi filter to the result set using the Filter and Filtered properties or the OnFilterRecord event. However, doing so applies the filter on the client side in this example. Using the second parameter, you can apply a more efficient filter at the source of the schema information. The filter is specified as an array of values. Each element of the array has a specific meaning relevant to the kind of data being returned. For example, the filter array for primary keys has three elements: the catalog (catalog is ANSI-speak for the database), the schema, and the table name. This example returns a list of primary keys for the Customer table:

var
  Filter: OLEVariant;
begin
  Filter := VarArrayCreate([0, 2], varVariant);
  Filter[2] := 'CUSTOMER';
  ADOConnection1.OpenSchema(
  siPrimaryKeys, Filter, EmptyParam, ADODataSet1);
end;
Note 

You can retrieve the same information using ADOX, and this warrants a brief comparison between OpenSchema and ADOX. ADOX is an additional ADO technology that allows you to retrieve and update schema information. It is ADO's equivalent to SQL's Data Definition Language (DDL—CREATE, ALTER, DROP) and Data Control Language (DCL—GRANT, REVOKE). ADOX is not directly supported in dbGo, but you can import the ADOX type library and use it successfully in Delphi applications. Unfortunately, ADOX is not as universally implemented as OpenSchema so there are greater gaps. To just retrieve information and not update it, OpenSchema is usually a better choice.


 
Previous Section Next Section


 


 

Delphi Sources


Copyright © 2004-2024 "Delphi Sources" by BrokenByte Software. Delphi Programming Guide
ร๐๓๏๏เ ยส๎ํ๒เ๊๒ๅ   Facebook   ั๑๛๋๊เ ํเ Twitter