Delphi Programming Guide
Delphi Programmer 

Menu  Table of contents
Bookmark and Share

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
  List of Figures    
  List of tables    
  List of Listings    
  List of Sidebars  

Previous Section Next Section

A Few dbExpress Demos

Let's look at a demonstration that highlights the key features of these components and shows how to use the ClientDataSet to provide caching and editing support for the unidirectional datasets. Later, I'll show you an example of native use of the unidirectional query, with no caching and editing support required.

The standard visual application based on dbExpress uses this series of components:

  • The SQLConnection component provides the connection with the database and the proper dbExpress driver.

  • The SQLDataSet component, which is hooked to the connection (via the SQLConnection property), indicates which SQL query to execute or table to open (using the CommandType and CommandText properties discussed earlier).

  • The DataSetProvider component, connected with the dataset, extracts the data from the SQLDataSet and can generate the proper SQL update statements.

  • The ClientDataSet component reads from the data provider and stores all the data (if its PacketRecords property is set to –1) in memory. You'll need to call its ApplyUpdates method to send the updates back to the database server (through the provider).

  • The DataSource component allows you to surface the data from the ClientDataSet to the visual data-aware controls.

As I mentioned earlier, the picture can be simplified by using the SimpleDataSet component, which replaces the two datasets and the provider (and possibly even the connection). The SimpleDataSet component combines most of the properties of the components it replaces.

Using a Single Component or Many Components

For this first example, drop a SimpleDataSet component on a form and set the connection name of its Connection subcomponent. Set the CommandType and CommandText properties to specify which data to fetch, and set the PacketRecords property to indicate how many records to retrieve in each block.

These are the key properties of the component in the DbxSingle example:

object SimpleDataSet1: TSimpleDataSet
  Connection.ConnectionName = 'IBLocal'
  Connection.LoginPrompt = False
  DataSet.CommandText = 'EMPLOYEE'
  DataSet.CommandType = ctTable

As an alternative, the DbxMulti example uses the entire sequence of components:

object SQLConnection1: TSQLConnection
  ConnectionName = 'IBLocal'
  LoginPrompt = False
object SQLDataSet1: TSQLDataSet
  SQLConnection = SQLConnection1
  CommandText = 'select * from EMPLOYEE'
object DataSetProvider1: TDataSetProvider
  DataSet = SQLDataSet1
object ClientDataSet1: TClientDataSet
  ProviderName = 'DataSetProvider1'
object DataSource1: TDataSource
  DataSet = ClientDataSet1

Both examples include some visual controls: a grid and a toolbar based on the action manager architecture.

Applying Updates

In every example based on a local cache, like the one provided by the ClientDataSet and SimpleDataSet components, it's important to write the local changes back to the database server. This is typically accomplished by calling the ApplyUpdates method. You can either keep the changes in the local cache for a while and then apply multiple updates at once, or you can post each change right away. In these two examples, I've gone for the latter approach, attaching the following event handler to the AfterPost (fired after an edit or an insert operation) and AfterDelete events of the ClientDataSet components:

procedure TForm1.DoUpdate(DataSet: TDataSet);
  // immediately apply local changes to the database

If you want to apply all the updates in a single batch, you can do so either when the form is closed or when the program ends, or you can let a user perform the update operation by selecting a specific command, possibly using the corresponding predefined action provided by Delphi 7. We'll explore this approach when discussing the update caching support of the ClientDataSet component in more detail later in this chapter.

Monitoring the Connection

Another feature I've added to the DbxSingle and DbxMulti examples is the monitoring capability offered by the SQLMonitor component. In the example, the component is activated as the program starts. In the DbxSingle example, because the SimpleDataSet embeds the connection, the monitor cannot be hooked to it at design time, but only when the program starts:

procedure TForm1.FormCreate(Sender: TObject);
  SQLMonitor1.SQLConnection := SimpleDataSet1.Connection;
  SQLMonitor1.Active := True;
  SimpleDataSet1.Active := True;

Every time a tracing string is available, the component fires the OnTrace event to let you choose whether to include the string in the log. If the LogTrace parameter of this event is True (the default value), the component logs the message in the TraceList string list and fires the OnLogTrace event to indicate that a new string has been added to the log.

The component can also automatically store the log into the file indicated by its FileName property, but I haven't used this feature in the example. All I've done is handle the OnTrace event, copying the entire log in the memo with the following code (producing the output shown in Figure 14.6):

procedure TForm1.SQLMonitor1Trace(Sender: TObject; 
  CBInfo: pSQLTRACEDesc; var LogTrace: Boolean);
  Memo1.Lines := SQLMonitor1.TraceList;
Click To expand
Figure 14.6: A sample log obtained by the SQLMonitor in the DbxSingle example

Controlling the SQL Update Code

If you run the DbxSingle program and change, for example, an employee's telephone number, the monitor will log this update operation:

update EMPLOYEE  set
  EMP_NO = ? and
  FIRST_NAME = ? and
  LAST_NAME = ? and
  PHONE_EXT = ? and
  HIRE_DATE = ? and
  DEPT_NO = ? and
  JOB_CODE = ? and
  JOB_GRADE = ? and
  JOB_COUNTRY = ? and
  SALARY = ? and

By setting the SimpleDataSet's properties there is no way to change how the update code is generated (which happens to be worse than with the SQLClientDataSet component, which had the UpdateMode you could use to tweak the update statements).

In the DbxMulti example, you can use the UpdateMode property of the DataSetProvider component, setting the value to upWhereChanged or upWhereKeyOnly. In this case you'll get the following two statements, respectively:

update EMPLOYEE  set
  EMP_NO = ? and
update EMPLOYEE  set
  EMP_NO = ?

This result is much better than in Delphi 6 (without the patches applied), in which this operation caused an error because the key field was not properly set.

If you want more control over how the update statements are generated, you need to operate on the fields of the underlying dataset, which are available also when you use the all-in-one SimpleDataSet component (which has two field editors, one for the base ClientDataset component it inherits from and one for the SQLDataSet component it embeds). I have made similar corrections in the DbxMulti example, after adding persistent fields for the SQLDataSet component and modifying the provider options for some of the fields to include them in the key or exclude them from updates.


We'll discuss this type of problem again when we examine the details of the ClientDataSet component, the provider, the resolver, and other technical details later in this chapter and in Chapter 16.

Accessing Database Metadata with SetSchemaInfo

All RDBMS systems use special-purpose tables (generally called system tables) for storing metadata, such as the list of the tables, their fields, indexes, and constraints, and any other system information. Just as dbExpress provides a unified API for working with different SQL servers, it also provides a common way to access metadata. The SQLDataSet component has a SetSchemaInfo method that fills the dataset with system information. This SetSchemaInfo method has three parameters:

SchemaType  Indicates the type of information requested. Values include stTables, stSysTables, stProcedures, stColumns, and stProcedureParams.

SchemaObject  Indicates the object you are referring to, such as the name of the table whose columns you are requesting.

SchemaPattern  A filter that lets you limit your request to tables, columns, or procedures starting with the given letters. This is handy if you use prefixes to identify groups of elements.

For example, in the SchemaTest program, a Tables button reads into the dataset all of the connected database's tables:

SQLDataSet1.SetSchemaInfo (stTables, '', '');

The program uses the usual group of dataset provider, client dataset, and data source component to display the resulting data in a grid, as you can see in Figure 14.7. After you're retrieved the tables, you can select a row in the grid and click the Fields button to see a list of the fields of this table:

Click To expand
Figure 14.7: The SchemaTest example allows you to see a database's tables and the columns of a given table.
SQLDataSet1.SetSchemaInfo (stColumns, ClientDataSet1['Table_Name'], '');

In addition to letting you access database metadata, dbExpress provides a way to access its own configuration information, including the installed drivers and the configured connections. The unit DbConnAdmin defines a TConnectionAdmin class for this purpose, but the aim of this support is limited to dbExpress add-on utilities for developers (end users aren't commonly allowed to access multiple databases in a totally dynamic way).


The DbxExplorer demo included in Delphi shows how to access both dbExpress administration files and schema information. Also check the help file under "The structure of metadata datasets" within the section "Developing database applications."

A Parametric Query

When you need slightly different versions of the same SQL query, instead of modifying the text of the query itself each time, you can write a query with a parameter and change the value of the parameter. For example, if you decide to have a user choose the employees in a given country (using the employee table), you can write the following parametric query:

select *
from employee
where job_country = :country

In this SQL clause, :country is a parameter. You can set its data type and startup value using the editor of the SQLDataSet component's Params property collection. When the Params collection editor is open, as shown in Figure 14.8, you see a list of the parameters defined in the SQL statement; you can set the data type and the initial value of these parameters in the Object Inspector.

Click To expand
Figure 14.8: Editing a query component's collection of parameters

The form displayed by this program, called ParQuery, uses a combo box to provide all the available values for the parameters. Instead of preparing the combo box items at design time, you can extract the available contents from the same database table as the program starts. This is accomplished using a second query component, with this SQL statement:

select distinct job_country
from employee

After activating this query, the program scans its result set, extracting all the values and adding them to the list box:

procedure TQueryForm.FormCreate(Sender: TObject);
  while not SqlDataSet2.EOF do
    ComboBox1.Items.Add (SqlDataSet2.Fields [0].AsString);
  ComboBox1.Text := CombBox1.Items[0];

The user can select a different item in the combo box and then click the Select button (Button1) to change the parameter and activate (or re-activate) the query:

procedure TQueryForm.Button1Click(Sender: TObject);
  Query1.Params[0].Value := ListBox1.Items [Listbox1.ItemIndex];

This code displays the employees from the selected country in the DBGrid, as you can see in Figure 14.9. As an alternative to using the elements of the Params array by position, you should consider using the ParamByName method, to avoid any problem in case the query gets modified over time and the parameters end up in a different order.

Click To expand
Figure 14.9: The ParQuery example at run time

By using parametric queries, you can usually reduce the amount of data moved over the wire from the server to the client and still use a DBGrid and the standard user interface common in local database applications.


Parametric queries are generally also used to obtain master-detail architectures with SQL queries—at least, this is what Delphi tends to do. The DataSource property of the SQLDataSet component, automatically replaces parameter values with the fields of the master dataset having the same name as the parameter.

When One-Way Is Enough: Printing Data

You have seen that one of the key elements of the dbExpress library is that it returns unidirectional datasets. In addition, you can use the ClientDataSet component (in one of its incarnations) to store the records in a local cache. Now, let's discuss an example in which a unidirectional dataset is all you need.

Such a situation is common in reporting—that is, producing information for each record in sequence without needing any further access to the data. This broad category includes producing printed reports (via a set of reporting components or using the printer directly), sending data to another application such as Microsoft Excel or Word, saving data to files (including HTML and XML formats), and more.

I don't want to delve into HTML and XML, so I'll present an example of printing—nothing fancy and nothing based on reporting components, just a way to produce a draft report on your monitor and printer. For this reason, I've used Delphi's most straightforward technique to produce a printout: assigning a file to the printer with the AssignPrn RTL procedure.

The example, called UniPrint, has a unidirectional SQLDataSet component hooked to an InterBase connection and based on the following SQL statement, which joins the employee table with the department table to display the name of the department where each employee works:

inner join DEPARTMENT d on d.DEPT_NO = e.DEPT_NO

To handle printing, I've written a somewhat generic routine, requiring as parameters the data to print, a progress bar for status information, the output font, and the maximum format size of each field. The entire routine uses file-print support and formats each field in a fixed-size, left-aligned string, to produce a columnar type of report. The call to the Format function has a parametric format string that's built dynamically using the size of the field.

In Listing 14.1 you can see the code of the core PrintOutDataSet method, which uses three nested try/finally blocks to release all the resources properly:

Listing 14.1: The Core Method of the UniPrint Example
Start example
procedure PrintOutDataSet (data: TDataSet;
  progress: TProgressBar; Font: TFont; toFile: Boolean; maxSize: Integer = 30);
  PrintFile: TextFile;
  I: Integer;
  sizeStr: string;
  oldFont: TFontRecall;
  // assign the output to a printer or a file
  if toFile then
    SelectDirectory ('Choose a folder', '', strDir);
    AssignFile (PrintFile,
      IncludeTrailingPathDelimiter(strDir) + 'output.txt');
    AssignPrn (PrintFile);
  // assign the printer to a file
  AssignPrn (PrintFile);
  Rewrite (PrintFile);
  // set the font and keep the original one
  oldFont := TFontRecall.Create (Printer.Canvas.Font);
    Printer.Canvas.Font := Font;
        // print header (field names) in bold
        Printer.Canvas.Font.Style := [fsBold];
        for I := 0 to data.FieldCount - 1 do
          sizeStr := IntToStr (min (data.Fields[i].DisplayWidth, maxSize));
          Write (PrintFile, Format ('%-' + sizeStr + 's',
        Writeln (PrintFile);
        // for each record of the dataset
        Printer.Canvas.Font.Style := [];
        while not data.EOF do
          // print out each field of the record
          for I := 0 to data.FieldCount - 1 do
            sizeStr := IntToStr (min (data.Fields[i].DisplayWidth, maxSize));
            Write (PrintFile, Format ('%-' + sizeStr + 's',
          Writeln (PrintFile);
          // advance ProgressBar
          progress.Position := progress.Position + 1;
        // close the dataset
      // reassign the original printer font
    // close the printer/file
     CloseFile (PrintFile);
End example

The program invokes this routine when you click the Print All button. It executes a separate query (select count(*) from EMPLOYEE), which returns the number of records in the employee table. This query is necessary to set up the progress bar (the unidirectional dataset has no way of knowing how many records it will retrieve until it has reached the last one). Then it sets the output font, possibly using a fixed-width font, and calls the PrintOutDataSet routine:

procedure TNavigator.PrintAllButtonClick(Sender: TObject);
  Font: TFont;
  // set ProgressBar range
    ProgressBar1.Max := EmplCountData.Fields[0].AsInteger;
  Font := TFont.Create;
    Font.Name := 'Courier New';
    Font.Size := 9;
    PrintOutDataSet (EmplData, ProgressBar1, Font, cbFile.Checked);

Previous Section Next Section



Copyright © 2004-2016 "Delphi Sources". Delphi Programming Guide
     Twitter     Facebook