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

Using InterBase Express

The examples built earlier in this chapter were created with the new dbExpress database library. Using this server-independent approach allows you to switch the database server used by your application, although in practice doing so is often far from simple. If the application you are building will invariably use a given database, you can write programs that are tied directly to the API of the specific database server. This approach will make your programs intrinsically non-portable to other SQL servers.

Of course, you won't generally use these APIs directly, but rather base your development on dataset components that wrap these APIs and fit into Delphi and the architecture of its class library. An example of such a family of components is InterBase Express (IBX). Applications built using these components should work better and faster (even if only marginally), giving you more control over the specific features of the server. For example, IBX provides a set of administrative components specifically built for InterBase 6.


I'll examine the IBX components because they are tied to InterBase (the database server discussed in this chapter) and because they are the only set of components available in the standard Delphi installation. Other similar sets of components (for InterBase, Oracle, and other database servers) are equally powerful and well regarded in the Delphi programmers' community. A good example (and an alternative to IBX) is InterBase Objects (

IBX Dataset Components

The IBX components include custom dataset components and a few others. The dataset components inherit from the base TDataSet class, can use all the common Delphi data-aware controls, and provide a field editor and all the usual design-time features. You can choose among multiple dataset components. Three IBX datasets have a role and a set of properties similar to the table/query/storedproc components in the dbExpress family:

  • IBTable resembles the Table component and allows you to access a single table or view.

  • IBQuery resembles the Query component and allows you to execute a SQL query, returning a result set. The IBQuery component can be used together with the IBUpdateSQL component to obtain a live (or editable) dataset.

  • IBStoredProc resembles the StoredProc component and allows you to execute a stored procedure.

These components, like the related dbExpress ones, are intended for compatibility with older BDE components you might have used in your applications. For new applications, you should generally use the IBDataSet component, which allows you to work with a live result set obtained by executing a select query. It basically merges IBQuery with IBUpdateSQL in a single component. The three components in the previous list are provided mainly for compatibility with existing Delphi BDE applications.

Many other components in InterBase Express don't belong to the dataset category, but are still used in applications that need to access to a database:

  • IBDatabase acts like the DBX SQLConnection component and is used to set up the database connection. The BDE also uses the specific Session component to perform some global tasks done by the IBDatabase component.

  • IBTransaction allows complete control over transactions. It is important in InterBase to use transactions explicitly and to isolate each transaction properly, using the Snapshot isolation level for reports and the Read Committed level for interactive forms. Each dataset explicitly refers to a given transaction, so you can have multiple concurrent transactions against the same database and choose which datasets take part in which transaction.

  • IBSQL lets you execute SQL statements that don't return a dataset (for example, DDL requests, or update and delete statements) without the overhead of a dataset component.

  • IBDatabaseInfo is used for querying the database structure and status.

  • IBSQLMonitor is used for debugging the system, because the SQL Monitor debugger provided by Delphi is a BDE-specific tool.

  • IBEvents receives events posted by the server.

This group of components provides greater control over the database server than you can achieve with dbExpress. For example, having a specific transaction component allows you to manage multiple concurrent transactions over one or multiple databases, as well as a single transaction spanning multiple databases. The IBDatabase component allows you to create databases, test the connection, and generally access system data, something the Database and Session BDE components don't fully provide.


IBX datasets let you set up the automatic behavior of a generator as a sort of auto-increment field. You do so by setting the GeneratorField property using its specific property editor. An example is discussed later in this chapter in the section "Generators and IDs."

IBX Administrative Components

The InterBase Admin page of Delphi's Component Palette hosts InterBase administrative components. Although your aim is probably not to build a full InterBase console application, including some administrative features (such as backup handling or user monitoring) can make sense in applications meant for power users.

Most of these components have self-explanatory names: IBConfigService, IBBackupService, IBRestoreService, IBValidationService, IBStatisticalService, IBLogService, IBSecurityService, IBServerProperties, IBInstall, and IBUninstall. I won't build any advanced examples that use these components, because they are more focused toward the development of server management applications than client programs. However, I'll embed a couple of them in the IbxMon example discussed later in this chapter.

Building an IBX Example

To build an example that uses IBX, you'll need to place in a form (or data module) at least three components: an IBDatabase, an IBTransaction, and a dataset component (in this case an IBQuery). Any IBX application requires at least an instance of the first two components. You cannot set database connections in an IBX dataset, as you can do with other datasets. And, at least a transaction object is required even to read the result of a query.

Here are the key properties of these components in the IbxEmp example:

object IBTransaction1: TIBTransaction
  Active = False
  DefaultDatabase = IBDatabase1
object IBQuery1: TIBQuery
  Database = IBDatabase1
  Transaction = IBTransaction1
  CachedUpdates = False
  SQL.Strings = (
object IBDatabase1: TIBDatabase
  DatabaseName = 'C:\Program Files\Common Files\Borland Shared\Data\employee.gdb'
  Params.Strings = (
  LoginPrompt = False
  SQLDialect = 1

Now you can hook a DataSource component to IBQuery1 and easily build a user interface for the application. I had to type in the pathname of the Borland sample database. However, not everyone has the Program Files folder, which depends on the local version of Windows, and the Borland sample data files could be installed elsewhere on the disk. You'll solve these problems in the next example.


Notice that I've embedded the password in the code—a naïve approach to security. Not only can anyone run the program, but someone can extract the password by looking at the hexadecimal code of the executable file. I used this approach so I wouldn't need to keep typing in my password while testing the program, but in a real application you should require your users to do so to ensure the security of their data.

Building a Live Query

The IbxEmp example includes a query that doesn't allow editing. To activate editing, you need to add an IBUpdateSQL component to the query, even if the query is trivial. Using an IBQuery that hosts the SQL select statement together with an IBUpdateSQL component that hosts the insert, update, and delete SQL statements is a typical approach from BDE applications. The similarities among these components make it easier to port an existing BDE application to this architecture. Here is the code for these components (edited for clarity):

object IBQuery1: TIBQuery
  Database = IBDatabase1
  Transaction = IBTransaction1
  SQL.Strings = ( 
    'SELECT Employee.EMP_NO, Department.DEPARTMENT, Employee.FIRST_NAME, '+
    '  Employee.LAST_NAME, Job.JOB_TITLE, Employee.SALARY, Employee.DEPT_NO, '+
    '  Employee.JOB_CODE, Employee.JOB_GRADE, Employee.JOB_COUNTRY'
    'FROM EMPLOYEE Employee'
    '   INNER JOIN DEPARTMENT Department'
    '   ON  (Department.DEPT_NO = Employee.DEPT_NO)  '
    '   INNER JOIN JOB Job'
    '   ON (Job.JOB_CODE = Employee.JOB_CODE)  '
    '   AND (Job.JOB_GRADE = Employee.JOB_GRADE)  '
    '   AND (Job.JOB_COUNTRY = Employee.JOB_COUNTRY)  '
    'ORDER BY Department.DEPARTMENT, Employee.LAST_NAME')
  UpdateObject = IBUpdateSQL1
object IBUpdateSQL1: TIBUpdateSQL
  RefreshSQL.Strings = (
    'SELECT Employee.EMP_NO, Employee.FIRST_NAME, Employee.LAST_NAME,'+
      'Department.DEPARTMENT, Job.JOB_TITLE, Employee.SALARY, Employee.DEPT_NO,'+
      'Employee.JOB_CODE, Employee.JOB_GRADE, Employee.JOB_COUNTRY'
    'FROM EMPLOYEE Employee'
    'ON (Department.DEPT_NO = Employee.DEPT_NO)'
    'ON (Job.JOB_CODE = Employee.JOB_CODE)'
    'AND (Job.JOB_GRADE = Employee.JOB_GRADE)'
    'AND (Job.JOB_COUNTRY = Employee.JOB_COUNTRY)'
    'WHERE Employee.EMP_NO=:EMP_NO')
  ModifySQL.Strings = (
    'update EMPLOYEE'
    '  SALARY = :SALARY,'
    '  DEPT_NO = :DEPT_NO,'
    '  JOB_CODE = :JOB_CODE,'
    '  EMP_NO = :OLD_EMP_NO')
  InsertSQL.Strings = (
    'insert into EMPLOYEE'
  DeleteSQL.Strings = (
    'delete from EMPLOYEE '
    'where EMP_NO = :OLD_EMP_NO')

For new applications, you should consider using the IBDataSet component, which sums up the features of IBQuery and IBUpdateSQL. The differences between using the two components and the single component are minimal. Using IBQuery and IBUpdateSQL is a better approach when you're porting an existing application based on the two equivalent BDE components, even if porting the program directly to the IBDataSet component doesn't require much extra work.

In the IbxUpdSql example, I've provided both alternatives so you can test the differences yourself. Here is the skeleton of the DFM description of the single dataset component:

object IBDataSet1: TIBDataSet
  Database = IBDatabase1
  Transaction = IBTransaction1
  DeleteSQL.Strings = (
    'delete from EMPLOYEE'
    'where EMP_NO = :OLD_EMP_NO')
  InsertSQL.Strings = (
    'insert into EMPLOYEE'
    '  JOB_COUNTRY)'
  SelectSQL.Strings = (...)
  UpdateRecordTypes = [cusUnmodified, cusModified, cusInserted]
  ModifySQL.Strings = (...)

If you connect the IBQuery1 or the IBDataSet1 component to the data source and run the program, you'll see that the behavior is identical. Not only do the components have a similar effect; the available properties and events are also similar.

In the IbxUpdSql program, I've also made the reference to the database a little more flexible. Instead of typing in the database name at design time, I've extracted the Borland shared data folder from the Windows Registry (where Borland saves it while installing Delphi). Here is the code executed when the program starts:

procedure TForm1.FormCreate(Sender: TObject);
  Reg: TRegistry;
  Reg := TRegistry.Create;
    Reg.RootKey := HKEY_LOCAL_MACHINE;
    Reg.OpenKey('\Software\Borland\Borland Shared\Data', False);
    IBDatabase1.DatabaseName := Reg.ReadString('Rootdir') + '\employee.gdb';

For more information about the Windows Registry and INI files, see the related sidebar in Chapter 8, "The Architecture of Delphi Applications."

Another feature of this example is the presence of a transaction component. As I've said, the InterBase Express components make the use of a transaction component compulsory, explicitly following a requirement of InterBase. Simply adding a couple of buttons to the form to commit or roll back the transaction would be enough, because a transaction starts automatically as you edit any dataset attached to it.

I've also improved the program by adding an ActionList component. This component includes all the standard database actions and adds two custom actions for transaction support: Commit and Rollback. Both actions are enabled when the transaction is active:

procedure TForm1.ActionUpdateTransactions(Sender: TObject);
  acCommit.Enabled := IBTransaction1.InTransaction;
  acRollback.Enabled := acCommit.Enabled;

When executed, they perform the main operation but also need to reopen the dataset in a new transaction (which can also be done by "retaining" the transaction context). CommitRetaining doesn't really reopen a new transaction, but it allows the current transaction to remain open. This way, you can keep using your datasets, which won't be refreshed (so you won't see edits already committed by other users) but will keep showing the data you've modified. Here is the code:

procedure TForm1.acCommitExecute(Sender: TObject);
procedure TForm1.acRollbackExecute(Sender: TObject);
  // reopen the dataset in a new transaction

Be aware that InterBase closes any opened cursors when a transaction ends, which means you have to reopen them and refetch the data even if you haven't made any changes. When committing data, however, you can ask InterBase to retain the transaction context—not to close open datasets—by issuing a CommitRetaining command, as mentioned before. InterBase behaves this way because a transaction corresponds to a snapshot of the data. Once a transaction is finished, you are supposed to read the data again to refetch records that may have been modified by other users. Version 6.0 of InterBase includes a RollbackRetaining command, but I've decided not to use it because in a rollback operation, the program should refresh the dataset data to show the original values on screen, not the updates you've discarded.

The last operation refers to a generic dataset and not a specific one, because I'm going to add a second alternate dataset to the program. The actions are connected to a text-only toolbar, as you can see in Figure 14.14. The program opens the dataset at startup and automatically closes the current transaction on exit, after asking the user what to do, with the following OnClose event handler:

Click To expand
Figure 14.14: The output of the IbxUpdSql example
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
  nCode: Word;
  if IBTransaction1.InTransaction then
    nCode := MessageDlg ('Commit Transaction? (No to rollback)',
      mtConfirmation, mbYesNoCancel, 0);
    case nCode of
      mrYes: IBTransaction1.Commit;
      mrNo: IBTransaction1.Rollback;
      mrCancel: Action := caNone; // don't close

Monitoring InterBase Express

Like the dbExpress architecture, IBX also allows you to monitor a connection. You can embed a copy of the IBSQLMonitor component in your application and produce a custom log.

You can even write a more generic monitoring application, as I've done in the IbxMon example. I've placed in its form a monitoring component and a RichEdit control, and written the following handler for the OnSQL event:

procedure TForm1.IBSQLMonitor1SQL(EventText: String);
  if Assigned (RichEdit1) then
    RichEdit1.Lines.Add (TimeToStr (Now) + ': ' + EventText);

The if Assigned test can be useful when receiving a message during shutdown, and it is required when you add this code directly inside the application you are monitoring.

To receive messages from other applications (or from the current application), you have to turn on the IBDatabase component's tracing options. In the IbxUpdSql example (discussed in the preceding section, "Building a Live Query"), I turned them all on:

object IBDatabase1: TIBDatabase
  TraceFlags = [tfQPrepare, tfQExecute, tfQFetch, tfError, tfStmt,
                tfConnect, tfTransact, tfBlob, tfService, tfMisc]

If you run the two examples at the same time, the output of the IbxMon program will list details about the IbxUpdSql program's interaction with InterBase, as you can see in Figure 14.15.

Click To expand
Figure 14.15: The output of the IbxMon example, based on the IBMonitor component

Getting More System Data

In addition to letting you monitor the InterBase connection, the IbxMon example allows you to query some server settings using the various tabs on its page control. The example embeds a few IBX administrative components, showing server statistics, server properties, and all connected users. You can see an example of the server properties in Figure 14.16. The code for extracting the users appears in the following code fragment.

Click To expand
Figure 14.16: The server informa-tion displayed by the IbxMon application
// grab the user's data
// display the name of each user
for i := 0 to IBSecurityService1.UserInfoCount - 1 do
  with IBSecurityService1.UserInfo[i] do
    RichEdit4.Lines.Add (Format ('User: %s, Full Name: %s, Id: %d',
      [UserName, FirstName + ' ' + LastName, UserId]));

Previous Section Next Section .  |

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