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

Real-World Blocks

Up to now, we've discussed specific techniques related to InterBase programming, but we haven't delved into the development of an application and the problems this presents in practice. In the following subsections, I'll discuss a few practical techniques, in no specific order.

Nando Dessena (who knows InterBase much better than I do) and I have used all of these techniques in a seminar discussing the porting of an internal Paradox application to InterBase. The application we discussed in the seminar was large and complex, and I've trimmed it down to only a few tables to make it fit into the space I have for this chapter.


The database discussed in this section is called mastering.gdb. You can find it in the data subfolder of the code folder for this chapter. You can examine it using InterBase Console, possibly after making a copy to a writable drive so that you can fully interact with it.

Generators and IDs

I mentioned in Chapter 13 that I'm a fan of using IDs extensively to identify the records in each table of a database.


I tend to use a single sequence of IDs for an entire system, something often called an Object ID (OID) and discussed in a sidebar earlier in this chapter. In such a case, however, the IDs of the two tables must be unique. Because you might not know in advance which objects could be used in place of others, adopting a global OID allows you more freedom later. The drawback is that if you have lots of data, using a 32-bit integer as the ID (that is, having only 4 billion objects) might not be sufficient. For this reason, InterBase 6 supports 64-bit generators.

How do you generate the unique values for these IDs when multiple clients are running? Keeping a table with a latest value will create troubles, because multiple concurrent transactions (from different users) will see the same values. If you don't use tables, you can use a database-independent mechanism, including the rather large Windows GUIDs or the so-called high-low technique (the assignment of a base number to each client at startup—the high number—that is combined with a consecutive number—the low number—determined by the client).

Another approach, bound to the database, is the use of internal mechanisms for sequences, indicated with different names in each SQL server. In InterBase they are called generators. These sequences operate and are incremented outside of transactions, so that they provide unique numbers even to concurrent users (remember that InterBase forces you to open a transaction to read data).

You've already seen how to create a generator. Here is the definition for the one in my demo database, followed by the definition of the view you can use to query for a new value:

create generator g_master;
create view v_next_id (
  ) as
select gen_id(g_master, 1) from rdb$database

Inside the RWBlocks application, I've added an IBQuery component to a data module (because I don't need it to be an editable dataset) with the following SQL:

select next_id from v_next_id;

The advantage, compared to using the direct statement, is that this code is easier to write and maintain, even if the underlying generator changes (or you switch to a different approach behind the scenes). Moreover, in the same data module, I've added a function that returns a new value for the generator:

function TDmMain.GetNewId: Integer;
  // return the next value of the generator
    Result := QueryId.Fields[0].AsInteger;

This method can be called in the AfterInsert event of any dataset to fill in the value for the ID:

mydataset.FieldByName ('ID').AsInteger := data.GetNewId;

As I've mentioned, the IBX datasets can be tied directly to a generator, thus simplifying the overall picture. Thanks to the specific property editor (shown in Figure 14.17), connecting a field of the dataset to the generator becomes trivial.

Figure 14.17: The editor for the GeneratorField property of the IBX datasets

Notice that both these approaches are much better than the approach based on a server-side trigger, discussed earlier in this chapter. In that case, the Delphi application didn't know the ID of the record sent to the database and so was unable to refresh it. Not having the record ID (which is also the only key field) on the Delphi side means it is almost impossible to insert such a value directly inside a DBGrid. If you try, you'll see that the value you insert gets lost, only to reappear in case of a full refresh.

Using client-side techniques based on the manual code or the GeneratorField property causes no trouble. The Delphi application knows the ID (the record key) before posting it, so it can easily place it in a grid and refresh it properly.

Case-Insensitive Searches

An interesting issue with SQL servers in general, not specifically InterBase, has to do with case-insensitive searches. Suppose you don't want to show a large amount of data in a grid (which is a bad idea for a client/server application). You instead choose to let the user type the initial portion of a name and then filter a query on this input, displaying only the smaller resulting record set in a grid. I've done this for a table of companies.

This search by company name will be executed frequently and will take place on a large table. However, if you search using the starting with or like operator, the search will be case sensitive, as in the following SQL statement:

select * from companies
where name starting with 'win';

To make a case-insensitive search, you can use the upper function on both sides of the comparison to test the uppercase values of each string, but a similar query will be very slow, because it won't be based on an index. On the other hand, saving the company names (or any other name) in uppercase letters would be silly, because when you print those names, the result will be unnatural (even if common in old information systems).

If you can trade off some disk space and memory for the extra speed, you can use a trick: Add an extra field to the table to store the uppercase value of the company name, and use a server-side trigger to generate it and update it. You can then ask the database to maintain an index on the uppercase version of the name, to speed the search operation even further.

In practice, the table definition looks like this:

create domain d_uid as integer;
create table companies
  id          d_uid not null,
  name        varchar(50),
  tax_code    varchar(16),
  name_upper  varchar(50),
constraint companies_pk primary key (id)

To copy the uppercase name of each company into the related field, you cannot rely on client-side code, because an inconsistency would cause problems. In a case like this, it is better to use a trigger on the server, so that each time the company name changes, its uppercase version is updated accordingly. Another trigger is used to insert a new company:

create trigger companies_bi for companies
active before insert position 0
  new.name_upper = upper(;
create trigger companies_bu for companies
active before update position 0
  if ( <> then
    new.name_upper = upper(;

Finally, I've added an index to the table with this DDL statement:

create index i_companies_name_upper on companies(name_upper);

With this structure behind the scenes, you can now select all the companies starting with the text of an edit box (edSearch) by writing the following code in a Delphi application:

dm.DataCompanies.SelectSQL.Text :=
  'select,, c.tax_code,' +
  '  from companies c ' +
  '  where name_upper starting with ''' +
  UpperCase (edSearch.Text) + '''';

Using a prepared parametric query, you might be able to make this code even faster.

As an alternative, you could create a server-side calculated field in the table definition, but doing so would prevent you from having an index on the field, which speeds up your queries considerably:

name_upper  varchar(50) computed by (upper(name))

Handling Locations and People

You might notice that the table describing companies is quite bare. It has no company address, nor any contact information. The reason is that I want to be able to handle companies that have multiple offices (or locations) and list contact information about multiple employees of those companies.

Every location is bound to a company. Notice, though, that I've decided not to use a location identifier related to the company (such as a progressive location number for each company), but rather a global ID for all the locations. This way, I can refer to a location ID (let's say, for shipping goods) without having to also refer to the company ID. This is the definition of the table that stores company locations:

create table locations
  id          d_uid not null,
  id_company  d_uid not null,
  address     varchar(40),
  town        varchar(30),
  zip         varchar(10),
  state       varchar(4),
  phone       varchar(15),
  fax         varchar(15),
constraint locations_pk primary key (id),
constraint locations_uc unique (id_company, id)
alter table locations add constraint locations_fk_companies
  foreign key (id_company) references companies (id)
  on update no action on delete no action;

The final definition of a foreign key relates the id_company field of the locations table with the ID field of the companies table. The other table lists names and contact information for people at specific company locations. To follow the database normalization rules, I should have added to this table only a reference to the location, because each location relates to a company. However, to make it simpler to change the location of a person within a company and to make my queries much more efficient (avoiding an extra step), I've added to the people table both a reference to the location and a reference to the company.

The table has another unusual feature: One of the people working for a company can be set as the key contact. You obtain this functionality with a Boolean field (defined with a domain, because the Boolean type is not supported by InterBase) and by adding triggers to the table so that only one employee of each company can have this flag active:

create domain d_boolean as char(1)
  default 'F'
  check (value in ('T', 'F')) not null
create table people
  id           d_uid not null,
  id_company   d_uid not null,
  id_location  d_uid not null,
  name         varchar(50) not null,
  phone        varchar(15),
  fax          varchar(15),
  email        varchar(50),
  key_contact  d_boolean,
constraint people_pk primary key (id),
constraint people_uc unique (id_company, name)
alter table people add constraint people_fk_companies
  foreign key (id_company) references companies (id)
  on update no action on delete cascade;
alter table people add constraint people_fk_locations
  foreign key (id_company, id_location)
  references locations (id_company, id);
create trigger people_ai for people
active after insert position 0
  /* if a person is the key contact, remove the
     flag from all others (of the same company) */
  if (new.key_contact = 'T') then
    update people
    set key_contact = 'F'
    where id_company = new.id_company
    and id <>;
create trigger people_au for people
active after update position 0
  /* if a person is the key contact, remove the
     flag from all others (of the same company) */
  if (new.key_contact = 'T' and old.key_contact = 'F') then
    update people
    set key_contact = 'F'
    where id_company = new.id_company
    and id <>;

Building a User Interface

The three tables discussed so far have a clear master/detail relation. For this reason, the RWBlocks example uses three IBDataSet components to access the data, hooking up the two secondary tables to the main one. The code for the master/detail support is that of a standard database example based on queries, so I won't discuss it further (but I suggest you study the example's source code).

Each of the datasets has a full set of SQL statements, to make the data editable. Whenever you enter a new detail element, the program hooks it to its master tables, as in the two following methods:

procedure TDmCompanies.DataLocationsAfterInsert(DataSet: TDataSet);
  // initialize the data of the detail record
  // with a reference to the master record
  DataLocationsID_COMPANY.AsInteger := DataCompaniesID.AsInteger;
procedure TDmCompanies.DataPeopleAfterInsert(DataSet: TDataSet);
  // initialize the data of the detail record
  // with a reference to the master record
  DataPeopleID_COMPANY.AsInteger := DataCompaniesID.AsInteger;
  // the suggested location is the active one, if available
  if not DataLocations.IsEmpty then
    DataPeopleID_LOCATION.AsInteger := DataLocationsID.AsInteger;
  // the first person added becomes the key contact
  // (checks whether the filtered dataset of people is empty)
  DataPeopleKEY_CONTACT.AsBoolean := DataPeople.IsEmpty;

As this code suggests, a data module hosts the dataset components. The program has a data module for every form (hooked up dynamically, because you can create multiple instances of each form). Each data module has a separate transaction so that the various operations performed in different pages are totally independent. The database connection, however, is centralized. A main data module hosts the corresponding component, which is referenced by all the datasets. Each of the data modules is created dynamically by the form referring to it, and its value is stored in the form's dm private field:

procedure TFormCompanies.FormCreate(Sender: TObject);
  dm := TDmCompanies.Create (Self);
  dsCompanies.Dataset := dm.DataCompanies;
  dsLocations.Dataset := dm.DataLocations;
  dsPeople.Dataset := dm.DataPeople;

This way, you can easily create multiple instances of a form, with an instance of the data module connected to each of them. The form connected to the data module has three DBGrid controls, each tied to a data module and one of the corresponding datasets. You can see this form at run time, with some data, in Figure 14.18.

Click To expand
Figure 14.18: A form showing companies, office locations, and people (part of the RWBlocks example)

The form is hosted by a main form, which in turn is based on a page control, with the other forms embedded. Only the form connected with the first page is created when the program starts. The ShowForm method I've written takes care of parenting the form to the tab sheet of the page control, after removing the form border:

procedure TFormMain.FormCreate(Sender: TObject);
  ShortDateFormat := 'dd/mm/yyyy';
  ShowForm (TFormCompanies.Create (Self), TabCompanies);
procedure TFormMain.ShowForm (Form: TForm; Tab: TTabSheet);
  Form.BorderStyle := bsNone;
  Form.Align := alClient;
  Form.Parent := Tab;

The other two pages are populated at run time:

procedure TFormMain.PageControl1Change(Sender: TObject);
  if PageControl1.ActivePage.ControlCount = 0 then
    if PageControl1.ActivePage = TabFreeQ then
      ShowForm (TFormFreeQuery.Create (self), TabFreeQ)
    else if PageControl1.ActivePage = TabClasses then
      ShowForm (TFormClasses.Create (self), TabClasses);

The companies form hosts the search by company name (discussed in the previous section) plus a search by location. You enter the name of a town and get back a list of companies having an office in that town:

procedure TFormCompanies.btnTownClick(Sender: TObject);
  with dm.DataCompanies do
    SelectSQL.Text :=
      'select,, c.tax_code' +
      '  from companies c ' +
      '  where exists (select from locations loc ' +
      '  where loc.id_company = and upper( = ''' +
      UpperCase(edTown.Text) + ''' )';

The form includes a lot more source code. Some of it is related to closing permission (as a user cannot close the form while there are pending edits not posted to the database), and quite a bit relates to the use of the form as a lookup dialog, as described later.

Booking Classes

Part of the program and the database involves booking training classes and courses. (Although I built this program as a showcase, it also helps me run my own business.) The database includes a classes table that lists all the training courses, each with a title and the planned date. Another table hosts registration by company, including the classes registered for, the ID of the company, and some notes. Finally, a third table lists people who've signed up, each hooked to a registration for his or her company, with the amount paid.

The rationale behind this company-based registration is that invoices are sent to companies, which book the classes for programmers and can receive specific discounts. In this case the database is more normalized, because the people registration doesn't refer directly to a class, but only to the company registration for that class. Here are the definitions of the tables involved (I've omitted foreign key constraints and other elements):

create table classes
  id           d_uid not null,
  description  varchar(50),
  starts_on   timestamp not null,
constraint classes_pk primary key (id)
create table classes_reg
  id          d_uid not null,
  id_company  d_uid not null,
  id_class    d_uid not null,
  notes       varchar(255),
constraint classes_reg_pk primary key (id),
constraint classes_reg_uc unique (id_company, id_class)
create domain d_amount as numeric(15, 2);
create table people_reg
  id              d_uid not null,
  id_classes_reg  d_uid not null,
  id_person       d_uid not null,
  amount          d_amount,
constraint people_reg_pk primary key (id)

The data module for this group of tables uses a master/detail/detail relationship, and has code to set the connection with the active master record when a new detail record is created. Each dataset has a generator field for its ID, and each has the proper update and insert SQL statements. These statements are generated by the corresponding component editor using only the ID field to identify existing records and updating only the fields in the original table. Each of the two secondary datasets retrieves data from a lookup table (either the list of companies or the list of people). I had to edit the RefreshSQL statements manually to repeat the proper inner join. Here is an example:

object IBClassReg: TIBDataSet
  Database = DmMain.IBDatabase1
  Transaction = IBTransaction1
  AfterInsert = IBClassRegAfterInsert
  DeleteSQL.Strings = (
    'delete from classes_reg'
    'where id = :old_id')
  InsertSQL.Strings = (
    'insert into classes_reg (id, id_class, id_company, notes)'
    'values (:id, :id_class, :id_company, :notes)')
  RefreshSQL.Strings = (
    'select, reg.id_class, reg.id_company, reg.notes, '
    'from classes_reg reg'
    'join companies c on reg.id_company ='
    'where id = :id')
  SelectSQL.Strings = (
    'select, reg.id_class, reg.id_company, reg.notes, '
    'from classes_reg reg'
    'join companies c on reg.id_company ='
    'where id_class = :id')
  ModifySQL.Strings = (
    'update classes_reg'
    '  id = :id,'
    '  id_class = :id_class,'
    '  id_company = :id_company,'
    '  notes = :notes'
    'where id = :old_id')
  GeneratorField.Field = 'id'
  GeneratorField.Generator = 'g_master'
  DataSource = dsClasses

To complete the discussion of IBClassReg, here is its only event handler:

procedure TDmClasses.IBClassRegAfterInsert(DataSet: TDataSet);
  IBClassReg.FieldByName ('id_class').AsString :=
    IBClasses.FieldByName ('id').AsString;

The IBPeopleReg dataset has similar settings, but the IBClasses dataset is simpler at design time. At run time, this dataset's SQL code is dynamically modified, using three alternatives to display scheduled classes (whenever the date is after today's date), classes already started or finished in the current year, and classes from past years. A user selects one of the three groups of records for the table with a tab control, which hosts the DBGrid for the main table (see Figure 14.19).

Click To expand
Figure 14.19: The RWBlocks example form for class registrations

The three alternative SQL statements are created when the program starts, or when the class registrations form is created and displayed. The program stores the final portion of the three alternative instructions (the where clause) in a string list and selects one of the strings when the tab changes:

procedure TFormClasses.FormCreate(Sender: TObject);
  dm := TDmClasses.Create (Self);
  // connect the datasets to the data sources
  dsClasses.Dataset := dm.IBClasses;
  dsClassReg.DataSet := dm.IBClassReg;
  dsPeopleReg.DataSet := dm.IBPeopleReg;
  // open the datasets
  dm.IBClasses.Active := True;
  dm.IBClassReg.Active := True;
  dm.IBPeopleReg.Active := True;
  // prepare the SQL for the three tabs
  SqlCommands := TStringList.Create;
  SqlCommands.Add (' where Starts_On > ''now''');
  SqlCommands.Add (' where Starts_On <= ''now'' and ' +
    ' extract (year from Starts_On ) >= extract(year from current_timestamp)');
  SqlCommands.Add (' where extract (year from Starts_On) < ' +
    ' extract(year from current_timestamp)');
procedure TFormClasses.TabChange(Sender: TObject);
  dm.IBClasses.Active := False;
  dm.IBClasses.SelectSQL [1] := SqlCommands [Tab.TabIndex];
  dm.IBClasses.Active := True;

Building a Lookup Dialog

The two detail datasets of this class registration form display lookup fields. Instead of showing the ID of the company that booked the class, for example, the form shows the company name. You obtain this functionality with an inner join in the SQL statement and by configuring the DBGrid columns so they don't display the company ID. In a local application, or one with a limited amount of data, you could use a lookup field. However, copying the entire lookup dataset locally or opening it for browsing should be limited to tables with about 100 records at most, embedding some search capabilities.

If you have a large table, such as a table of companies, an alternative solution is to use a secondary dialog box to perform the lookup selection. For example, you can choose a company by using the form you've already built and taking advantage of its search capabilities. To display this form as a dialog box, the program creates a new instance of it, shows some hidden buttons already there at design time, and lets the user select a company to refer to from the other table.

To simplify the use of this lookup, which can happen multiple times in a large program, I've added to the companies form a class function that has as output parameters the name and ID of the selected company. An initial ID can be passed to the function to determine its initial selection. Here is the complete code of this class function, which creates an object of its class, selects the initial record if requested, shows the dialog box, and finally extracts the return values:

class function TFormCompanies.SelectCompany (
  var CompanyName: string; var CompanyId: Integer): Boolean;
  FormComp: TFormCompanies;
  Result := False;
  FormComp := TFormCompanies.Create (Application);
  FormComp.Caption := 'Select Company';
    // activate dialog buttons
    FormComp.btnCancel.Visible := True;
    FormComp.btnOK.Visible := True;
    // select company
    if CompanyId > 0 then :=
        'select,, c.tax_code' +
        '  from companies c ' +
        '  where = ' + IntToStr (CompanyId)
    else :=
        'select,, c.tax_code' +
        '  from companies c ' +
        '  where name_upper starting with ''a''';;;;
    if FormComp.ShowModal = mrOK then
      Result := True;
      CompanyId := ('id').AsInteger;
      CompanyName := ('name').AsString;

Another slightly more complex class function (available with the example's source code, but not listed here) lets you select a person from a given company to register people for classes. In this case, the form is displayed after disallowing searching another company or modifying the company's data.

In both cases, you trigger the lookup by adding an ellipsis button to the column of the DBGrid—for example, the grid column listing the names of companies registered for classes. When this button is clicked, the program calls the class function to display the dialog box and uses its result to update the hidden ID field and the visible name field:

procedure TFormClasses.DBGridClassRegEditButtonClick(Sender: TObject);
  CompanyName: string;
  CompanyId: Integer;
  CompanyId := dm.IBClassReg.FieldByName ('id_Company').AsInteger;
  if TFormCompanies.SelectCompany (CompanyName, CompanyId) then
    dm.IBClassReg.FieldByName ('Name').AsString := CompanyName;
    dm.IBClassReg.FieldByName ('id_Company').AsInteger := CompanyId;

Adding a Free Query Form

The program's final feature is a form where a user can directly type in and run a SQL statement. As a helper, the form lists in a combo box the available tables of the database, obtained when the form is created by calling

DmMain.IBDatabase1.GetTableNames (ComboTables.Items);

Selecting an item from the combo box generates a generic SQL query:

MemoSql.Lines.Text := 'select * from ' + ComboTables.Text;

The user (if an expert) can then edit the SQL, possibly introducing restrictive clauses, and then run the query:

procedure TFormFreeQuery.ButtonRunClick(Sender: TObject);
  QueryFree.SQL := MemoSql.Lines;

You can see this third form of the RWBlocks program in Figure 14.20. Of course, I'm not suggesting that you add SQL editing to programs intended for all your users—this feature is intended for power users or programmers. I basically wrote it for myself!

Click To expand
Figure 14.20: The free query form of the RWBlocks example is intended for power users.

Previous Section Next Section  |

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