Introduction to InterBaseExpress

By: Conference Speaker

Abstract: Learn the basics of building high-performance client/server systems using InterBase and InterBaseExpress with Delphi. This session will cover the InterBaseExpress components and how to use them in Delphi.

This is the technical paper from a talk given at the 11th Annual Inprise & Borland Developer's Conference
By Bill Todd
Bill Todd is president of The Database Group, Inc., a database consulting and development firm based near Phoenix. He is co-author of four database programming books, including Delphi: A Developer's Guide. He is a contributing editor to Delphi Informant, and a frequent speaker at Inprise/Borland Conferences in the U.S. and Europe. Bill is also a member of Team Borland, a nationally known trainer, and has taught Delphi programming classes across the country and overseas. He was an instructor on the 1995, 1996, 1997, and 1998 Borland/Softbite Delphi World Tours.

Introduction To Interbase Express

Interbase Express consists of eleven data access components and eleven administration components. Just considering the data access components it is easily the most complex set of data access components supplied with Delphi. Because there are so many components and because it is unlikely that you will use all of the data access components in the most complex application this paper will start with a brief description of each of the components and what it is used for. From there we will move on to examples of using each of the data access components and looking at each component in more depth.

The Data Access Components

Component

Description

IBDatabase

This is the analog to the BDE Database component. It provides a connection to an Interbase database. Note that it does not provide transaction support. All of the IB Express dataset components must specify an IBDatabase to connect through.

IBTransaction

The IBTransaction component provides transaction support. All data access components must specify a transaction component as well as a database component. A separate transaction component allows multiple transactions to be active at the same time.

IBTable

Use this component to access all of the fields and records in a table or view. The records may be restricted using a filter.

IBQuery

IB Query provides read only access to the result set of a SQL statement. Any valid SQL statement which fetches records from one or more tables, a view or a select stored procedure can be used.

IBStroredProc

Use IBStoredProc to execute a stored procedure that does not return a result set.

IBUpdateSQL

The IBQuery and IBTable components include a CachedUpdates property that works much the same as cached updates with the BDE. To use cached updates you must set the IBTable or IBQuerys UpdateObject property to point to an IBUpdateSQL component which specifies the SQL statements to use to update, insert and delete rows.

IBDataSet

IBDataSet is the workhorse data access component of IB Express and the one you will use most often. It allows you to specify the SQL statements to be used to select, insert, update and delete rows.

IBSQL

This is a low overhead component for executing SQL statements. It does not support data aware controls and provides a unidirectional cursor. It is ideal for data access in code or executing statements that do not return a result set.

IBDatabaseInfo

IBDatabaseInfo is not strictly a data access component. It provides access to information about the database you are connected to through an IBDatabase component. IBDatabaseInfo will tell you the ODS version, page size, sweep interval, users connected to the database and much more.

IBSQLMonitor

Use this component to log or display the SQL statements passing between your application and Interbase. This is a diagnostic tool.

IBEvents

Interbase allows triggers and stored procedures to raise events. The IBEvents component lets your application register interest in up to 15 events. When an event occurs the IBEvents components OnEventAlert event fires allowing your application to respond to the event.

Many of these components are very similar to their BDE counterparts. This paper will focus on the differences and not dwell on properties, methods and events that are identical in name and function to the corresponding properties in BDE data access components.

Building an IB Express Application

The basics of building an application using IB Express are no different than any Delphi database application. It is only the data access components themselves that are different. Start with a new application, add a data module and drop an IBDatabase component and an IBTransaction component on the data module. Unlike BDE applications where you could omit the Database component in some cases, both the IBDatabase and IBTransaction components are required.

Using IBDatabase

You must set the following properties of the IBDatabase component.

Property

Description

DatabaseName

This is the path to and name of the database file. If the database is on your local drive this would be a drive letter and path or a relative path. If you are connecting to a remote Interbase server this would be the UNC path.

DefaultTransaction

Set this property to point to the IBTransaction component you want to use as the default transaction for this database.

You may also want to set one or more of the following optional properties of the IBDatabase compoent.

Property

Description

IdleTimer

This is the number of seconds the connection must remain idle before it is dropped. By default IdleTimer is 0 which means the database connection will not be closed automatically. Call the IBDatabase components TestConnected method to determine if the IBDatabase is connected to the server.

LoginPrompt

This is identical to the BDE Database component property of the same name. Set this property to False if you are supplying the user name and password through the IBDatabase components Params property and do not want the user to be prompted for a user name and password.

Params

You can pass the user name, password, SQL role name and character set as parameters. Note that the name of the user name parameter is user_name, not user name as it is with the BDE Database component.

SQLDialect

SQL Dialects were introduced in Interbase 6 to allow the meaning of some SQL features to be changed for SQL 92 compliance. Use dialect 1 for Interbase 5 and earlier databases and dialect 3 for Interbase 6 databases.

TraceFlags

Set TraceFlags to indicate which operations should be tracked by the IBSQLMonitor component.

The easiest way to set the properties of the IBDatabase component is to double click the component to open the Database Component Editor shown in figure 1. You can also right click the component and choose Database Editor from the context menu.

Figure 1  The Database Editor

The Database Editor lets you choose whether you wish to connect to a local or remote Interbase server and provides a Browse button to easily locate the database you want to use. Entering a value in the User Name, Password, SQLRole or Character Set fields automatically generates the appropriate entry in the Settings box. The values in the Settings box are automatically assigned to the IBDatabase components Params property. If you provide a user name and password you can uncheck the Login Prompt checkbox that sets the components LoginPrompt property to False.

Using IBTransaction

Begin by setting the following properties of the IBTransaction component.

Propertie

Description

DefaultDatabase

Select the database component the transaction will apply to.

IdleTimer

If you want the transaction to automatically commit or rollback after being idle for a fixed period of time set IdleTimer to that time and set DefaultAction to the action the transaction should take.

DefaultAction

Set this to the action to take when the IdleTimer time expires. The choices are TACommit, TACommitRetaining, TARollback and TARollbackRetaining.

Params

See the discussion of the Transaction Editor which follows.

To set the Params property double click the IBTransaction component to open the Transaction Editor shown in figure 2.

Figure 2  The Transaction Editor

The transaction editor lets you set the transaction isolation level for your transactions. The choices are shown in the following table. When you use the transaction editor the values shown in the Settings box are assigned to the IBTransaction components Params property.

Isolation Level

Description

Snapshot

The default isolation level. Provides your transaction with a snapshot of the database as it was at the instant your transaction started. Although other transactions can make changes to the database and commit those changes while your transaction is active your transaction will not see changes made by other transactions.

Read Committed

Your transaction will see changes made by other transactions that have committed.

Read-Only Table Stability and Read-Write Table Stability

Other transactions cannot change tables that your transaction has read or changed until your transaction commits or rolls back.

Note that if you select either Snapshot or Read Committed transaction isolation the nowait parameter is also set. This parameter controls how lock conflicts with other transactions are handled. If your transaction tries to update or delete a row locked by another transaction and nowait is specified your transaction will return an immediate error. If wait is specified your transaction will wait for the other transaction to release its lock and then proceed. If you want read committed with wait you must set the parameters manually. This option is not available from the Transaction Editor. If you do not use the Transaction Editor and leave the Params property of the IBTransaction component empty you will get Snapshot with Wait by default, which is what you should use for all but exceptional situations.

Using IBDataSet

The next step in building a basic Interbase Express application is to add a data access component to the data module. If you are writing a new Interbase Express application the data access component you will use for almost everything is the IBDataSet. The other data access components are either special purpose tools or compatibility components designed to make conversion of BDE based programs to Interbase Express easier.

The first step in using IBDataSet is to set its Database property to the IBDatabase component it will use to connect to a database. The second step is to set the SelectSQL property to the SQL statement you want to use to select records. The basic sample application uses the following select statement.

select * from EMPLOYEE where Dept_No = :Dept_No

Since the most efficient way to access and manipulate data on a SQL database server is with SQL that is what IBDataSet does using the following properties.

Property

Description

SelectSQL

The SQL statement used to fetch a set of records from one or more tables.

RefreshSQL

The SQL statement used to fetch a single row.

InsertSQL

The SQL statement used to insert a new row.

ModifySQL

The SQL statement used to update a row.

DeleteSQL

The SQL statement used to delete a row.

My first reaction to this was that having to type five SQL statements for every dataset was going to triple my development time. Fortunately you dont have to do that in most cases. After you have set the Database property right click the IBDataSet and choose DataSet Editor to open the editor shown in figure 3. Note that you cannot double click to get here. Like all Delphi dataset components, double clicking IBDataSet opens the Fields Editor.

Figure 3  The Dataset Editor

Enter the name of the table you wish to edit and click Get Table Fields to fill the Key Fields and Update Fields list boxes. Click Dataset Defaults to select all of the fields in both lists. Click the Select Primary Keys button to automatically select just the primary key field(s) in the Key Fields list. These are the fields that will be used to locate and update records. Note that in some cases you will not want to include all fields in the Update Fields list. The Employee table shown in figure 3 provides a good example because the Full_Name field is a computed field based on First_Name and Last_Name and, therefore, should not be updated. Finally click the Generate SQL button and the SQL statements for all of the SQL properties except SelectSQL will be generated automatically.

Figure 4  The SQL page of the Dataset Editor

To view or edit the generated SQL switch to the SQL tab and select the radio button that corresponds to the SQL statement you want to work with. For the Employee table the modify statement is:

update Employee

set

  EMP_NO = :EMP_NO,

  FIRST_NAME = :FIRST_NAME,

  LAST_NAME = :LAST_NAME,

  PHONE_EXT = :PHONE_EXT,

  HIRE_DATE = :HIRE_DATE,

  DEPT_NO = :DEPT_NO,

  JOB_CODE = :JOB_CODE,

  JOB_GRADE = :JOB_GRADE,

  JOB_COUNTRY = :JOB_COUNTRY,

  SALARY = :SALARY

where

  EMP_NO = :OLD_EMP_NO

The IBDataSet component provides two sets of parameters for you to use in constructing your SQL statements. The first set are identical in name to the names of the fields in the table you are working with. These parameters contain the new value for each field. The second set of parameters have the same name as the fields in the table with the string OLD_ prepended. These parameters return the original value in each field. The UPDATE statement shown above assigns the new value to each field but uses the old value of the primary key field in the WHERE clause to locate the record to be updated. Below are the insert, delete and refresh statements generated by the Dataset Editor.

insert into Employee

  (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, 

   JOB_GRADE, JOB_COUNTRY, SALARY)

values

  (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE, :DEPT_NO, 

   :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)

 

delete from Employee

where

  EMP_NO = :OLD_EMP_NO

 

Select 

  EMP_NO,

  FIRST_NAME,

  LAST_NAME,

  PHONE_EXT,

  HIRE_DATE,

  DEPT_NO,

  JOB_CODE,

  JOB_GRADE,

  JOB_COUNTRY,

  SALARY,

  FULL_NAME

from Employee 

where

  EMP_NO = :EMP_NO

Thanks to the Dataset Editor, generating the four additional SQL statements is easy and this architecture is very powerful. For example, you could construct a SelectSQL statement that joins two tables and include two UPDATE statements in the ModifySQL property so that the fields in both tables would be updated.

There is one other property of IBDataSet that you may want to change and that is Unidirectional. Since Interbase does not provide bi-directional scrollable cursors the ability to scroll backward is provided by a buffering scheme. If you only need to traverse the data in the forward direction set Unidirectional to True to reduce memory requirements and improve performance. A dataset that provides data to a report is a good example. When printing a report the data is only traversed from the first record to the last so Unidirectional should be set to True.

Using IBSQL

Since client/server applications normally force the user to work with a small set of records a means must be provided to let the user select the records to work with. The select statement used in the IBDataSet selects employees based on their department number so the application must provide a way for the user to select a department and fetch the records for that department. Since the number of departments is small using a combo box as shown on the main form in figure 5 works well.

Figure 5  The main form of the basic sample program

When the main form is created the program must select all of the department information needed to load the combo box and add it to the combo boxs items property. This is an ideal task for the IBSQL component. This component is billed as a lightweight tool for using Interbase SQL because it provides no buffering, only unidirectional scrolling and no support for data aware controls. To use an IBSQL component drop it on your data module and set the Database property to the IBDatabase component it should use for its connection. In the sample application this is the EmpDb component. Next set the SQL property and that is all there is to it. The SQL statement used to get the department information is:

select DEPT_NO, DEPARTMENT from DEPARTMENT

order by DEPARTMENT

The following method of the main form is called from its OnCreate event handler to load the combo box.

procedure TBasicForm.LoadDeptList;

begin

  with BasicDm, BasicDm.DeptSQL do

  begin

    EmpTrans.StartTransaction;

    ExecQuery;

    while not EOF do

    begin

      DeptCombo.Items.Add(FieldByName('Dept_No').Value + ' ' +

                          FieldByName('Department').Value);

      Next;

    end; //while

    Close;

    EmpTrans.Commit;

  end; //with

end;

One thing you will notice about the IBSQL component that sets it apart from other Delphi data access components is that it does not have an Active property and it does not have an open method. Instead you call its ExecSQL method to execute the SQL statement and Close to close it. Other situations in which IBSQL is the component of choice include the following.

  • Moving data form one table to another.

  • Exporting data.

  • Getting a value from a generator.

  • Executing DDL statements.

Finishing the User Interface

Finishing the user interface requires dropping a DataSource component on the data module and setting its DataSet property to EmpData to connect it to the IBDataSet component. The finished data module is shown in figure 6.

Figure 6  The data module for the sample application

The main form, shown in figure 5, consists of a DBNavigator and DBEdit controls for each of the fields in the employee relation. The code in the OnChange event handler of the combo box takes care of setting the :Dept_No parameter of the IBDataSets SelectSQL statement and opening the dataset as shown below.

procedure TBasicForm.DeptComboChange(Sender: TObject);

var

  DeptNo:      Integer;

begin

  DeptNo := StrToInt(Copy(DeptCombo.Text, 1, 3));

  with BasicDm, BasicDm.EmpData do

  begin

    Close;

    if not EmpTrans.InTransaction then

      EmpTrans.StartTransaction;

    Params.ByName('Dept_No').AsInteger := DeptNo;

    Open;

  end; //with

end;

This code extracts the department number from the first three characters of the Text property of the combo box, converts it to an integer, then assigns it to the parameter of the IBDataSets SelectSQL statement and finally opens the IBDataSet to display the selected rows.

Working With Transactions

One thing you have to get used to when you start working with Interbase is that everything happens within a transaction. Even a simple SELECT to fetch some rows must take place within a transaction. If you do not explicitly start and commit or rollback a transaction Interbase Express will automatically wrap each SELECT and each row insert, update or delete in its own transaction. While this may be acceptable in many situations, particularly SELECT queryies, you will want to explicitly start and end transactions in any case where the transaction must include more than one change to one row in one table.

Referring again to the code that loads the combo box, shown below, you will see that the EmpTrans components StartTransaction method is called before the query is opened and the transaction is committed by a call to EmpTrans.Commit after the query is closed. While explicit transaction control is not necessary in this case it is used to provide an example.

procedure TBasicForm.LoadDeptList;

begin

  with BasicDm, BasicDm.DeptSQL do

  begin

    EmpTrans.StartTransaction;

    ExecQuery;

    while not EOF do

    begin

      DeptCombo.Items.Add(FieldByName('Dept_No').Value + ' ' +

                          FieldByName('Department').Value);

      Next;

    end; //while

    Close;

    EmpTrans.Commit;

  end; //with

end;

The same is true of the code in the combo boxs OnChange event handler which opens the IBDataSet. Before opening the query it calls StartTransaction if a transaction is not already active.

    if not EmpTrans.InTransaction then

      EmpTrans.StartTransaction;

In this case the user controls when the transaction is committed or rolled back via the buttons below the navigator on the main form. The Commit button calls the transaction components Commit method and the Rollback button the Rollback method.

In addition to Commit and Rollback the IBTransaction component has two other methods that will end a transaction, CommitRetaining and RollbackRetaining. To understand what these methods do you need to understand what happens when a transaction starts in Interbase. Each transaction is assigned a unique number when it starts. In addition, a copy of the transaction inventory pages are made for each transaction so the transaction knows the state of every other active transaction at the instant it started. A transaction uses this information to determine which version of each row it accesses to use. Each record version contains the number of the transaction that created it. A transaction will always access only the most recent record version that was created by a transaction that was committed at the time the accessing transaction started. This is what allows Interbase to provide snapshot (serializable in SQL 92 terms) transaction isolation without blocking other transactions that need to update the database. Obviously there is overhead involved in generating the snapshot of the transaction inventory pages when a new transaction starts.

Consider a situation where a transaction must select some records and, based on the values it finds, perform updates to several records in several tables. It then must perform several lengthy queries to summarize data and return the results to the client. There is a conflict between concurrency and consistency in this situation. From the concurrency side, it would be nice to commit the transaction as soon as the updates have been finished instead of waiting until after the select queries that summarize the data have completed. The reason is that all of the rows that were updated will remain locked until the transaction commits or rolls back and, therefore, no other user will be able to update any of those rows. You could solve this by committing after the updates and starting a new transaction for the summarization queries but that creates a consistency problem. If you start a new transaction the summarization queries will be able to see the changes made by all of the transactions that committed during the time the original transaction was active. That means that it may not see the same values in some rows that the original select statement saw and that may not give you the result you expect. The solution is to perform a CommitRetaining after the updates have been done. Commit retaining commits the changes to data but retains the transaction context, including the snapshot of the transaction information pages, so the transaction can continue with a consistent view of the data. RollbackRetaining does the same thing except that the changes are rolled back.

One of the benefits of a separate transaction component is that Interbase Express allows your application to have multiple transactions active at the same time. Suppose you want to have two IBDataSets open at the same time, each with its own transaction so each can commit or rollback its changes independently. All you have to do is drop two IBTransaction components on your data module, connect both to your IBDatabase by setting their DefaultDatabase properties and then set the Transaction property of each IBDataSet to a different IBTransaction component. This is something you cannot do using the BDE. By the way, if you plan to use Interbase Express with Midas you must provide a separate transaction component for each dataset that provides data to a Midas client.

Interbase supports transactions that span multiple databases, also called two phase commit. To support transactions that span databases the IBTransaction component maintains a list of databases that may be included in a transaction in its Databases property. The AddDatabase method is used to add databases to the list and RemoveDatabase removes databases from the list. You can also call the RemoveDatabases method to remove all databases from the list.

Other Data Access Components

The IBTable and IBQuery components are transition components designed to make it as easy as possible to convert BDE applications to Interbase Express. However IBTable can be handy in any Interbase Express application. One good example is for editing lookup tables that do not contain many records. In this case you may as well let the user browse the table for the record of interest instead of having to code a method the user can use to enter selection criteria for a query. The third data access component covered in this section, IBStoredProc, is a transition component in that it is analogous to the BDE StoredProc component but it is also the component to use in any Interbase Express application where you need to execute a stored procedure that does not return a result set.

Using IBTable

IBTable does what the BDE Table component does but it is faster and more efficient when working with an Interbase database. If your background is with the BDE your first reaction may be, who would ever use a Table component in a client/server application? However, IBTables resemblance to the BDE Table component is only skin deep. Under the hood IBTable issues a SQL Select statement to fetch records. If you do not want to retrieve all of the rows in the table use the Filter property. The Filter property of IBTable supplies the WHERE clause for its SQL statement so you have the full power and syntax of Interbase SQL available to restrict rows that are returned. To limit the rows returned assign a valid WHERE expression to the Filter property, set the Filtered property to True and open the table.

IBTable is certainly not as flexible as IBDataSet in that it does not provide a way to restrict which columns are returned, but it is very easy to use. Just set the Database, Transaction and TableName properties and you are done. You do not have to write any SQL. Since the dataset returned by IBTable is live you can use it to both edit and view data. To control the order of the data use either the IndexName or IndexFieldNames properties. The IndexName property lets you select an existing index to use to order the records. The drop-down list for IndexFieldNames lists the fields for the available indices, however, you can enter any combination of field names, separated by semicolons, to get the sort order you want whether there is a matching index or not.

Using IBQuery

IBQuery returns a read only result set from a SQL SELECT statement assigned to its SQL property and executed by calling its Open method. You can also use IBQuery to execute SQL statements that do not return a result set by calling its ExecSQL method. Note, however, that IBQuery is a poor choice for executing SQL statements that do not return a result set since the IBSQL component can do the same job faster and while consuming fewer system resources.

You can make the result set returned by an IBQuery updatable by using an IBUpdateSQL component and setting the IBQuerys CachedUpdates property to True. To use the IBUpdate SQL compoent set the IBQuerys UpdateObject property to the UpdateSQL component you want to use. Finally, create the SQL statements for the UpdateSQL components ModifySQL, InsertSQL, DeleteSQL and RefreshSQL properties. The easiest way to do this is to right click the UpdateSQL component and open the UpdateSQL Editor. This editor is identical to the Dataset Editor described earlier for the IBDataSet component. However, if you need to update the dataset it is easier to use IBDataSet, which incorporates all of this functionality in a single component.

One place where IBQuery is a good choice is in a Midas server. Since the updates in a Midas server are handled by the DataSetProvider component you do not need to an IBUpdateSQL component.

Using IBStoredProc

Interbase stored procedures are divided into two groups; those that return a result set and those that do not. Stored procedures that return a result set are called select procedures because they can be used in place of a table or view in a SQL SELECT statement. Executing a SELECT statement that calls a select procedure is no different than executing any other SELECT so you can use either an IBDataSet, IBQuery or IBSQL component.

To call a stored procedure that does not return a result set use the IBStoredProc component. After dropping the IBStoredProc component on a form or data module set the following properties.

Property

Description

Database

The IBDatabase component used to connect to the database that contains the stored procedure you want to execute.

StoredProcName

The name of the stored procedure.

Params

Use the Params property editor to assign default values and data types to the stored procedures parameters.

Transaction

This property will automatically be set to the default IBTransaction component for the database you selected. Change it if you want to use a different transaction component.

The Procs sample application that accompanies this paper shows an example of calling the Adjust_Salary_Range stored procedure. This procedure takes a single parameter which is the factor by which all salary ranges are to be adjusted. Figure 7 shows the Non-select Proc page of the programs main form.

Figure 7  The Procs sample application

To adjust the salary ranges enter a factor, for example 1.2, then click the Execute button. The code from the Execute buttons OnClick event handler is shown below.

procedure TProcForm.NonSelectBtnClick(Sender: TObject);

begin

  with ProcDm.AdjustSalRngProc do

  begin

    Prepare;

    Params.ParamByName('Factor').Value := StrToFloat(FactorEdit.Text);

    ExecProc;

    Unprepare;

  end; //with

end;

This code assigns the value from the edit box to the Factor parameter of the stored procedure then executes the stored procedure by calling the IBStoredProcs ExecProc method. Note that the calls to IBStoredProcs Prepare and Unprepare methods are optional. IBStoredProc will automatically prepare the procedure before execution and unprepared it after execution. However, if the stored procedure will be executed multiple times it is faster to call Prepare once, execute the stored procedure as many times as necessary, then call Unprepare when you are finished.

Creating and Dropping a Database

In addition to providing a connection to an existing Interbase database, the IBDatabase component can also be used to create or drop a database. Before calling the CreateDatabase method you must set the Database property to the location and name of the database file you are going to create and the Params property to contain all of the parameters of the Interbase SQL CREATE DATABASE statement. The sample CreateDb program illustrates this. The Database property is set to DemoDb. Since no path is provided the database will be created in the current directory which is the directory that contains CREATEDB.EXE. Params is set to the following.

USER "SYSDBA"
PASSWORD "masterkey"
PAGE_SIZE 4096

The page size is optional. If no page size is specified the database will be created with a 1k page size. In addition to the parameters shown here you can also specify a default character set and one or more secondary files using the syntax for CREATE DATABASE. To drop an existing database the only property of IBDatabase that you need to set is Database. Once the path to and name of the database is assigned to the Database property call the DropDatabase method and the database will be deleted.

Using IBDatabaseInfo

The IBDatabaseInfo component is a diagnostic and information component that is strictly optional. To use it just drop it on a form or data module and set its Database property to connect it to the IBDatabase componet of interest. IBDatabaseInfo has a host of properties that provide information about your database. The DbInfo sample application shown in figure 8 provides an example.

Figure 8  DbInfos main form

This example uses a frame to contain the IBDatabaseInfo and listbox components to make reuse easy in other applications. The following is the code from the frames GetInfo method.

procedure TDbInfoFrame.GetInfo(ADataBase: TIBDatabase);

begin

  EmpInfo.DataBase := ADatabase;

  EmpInfo.Database.Open;

  with EmpInfo, InfoList.Items do

  begin

    Add('Current Memory = ' + IntToStr(CurrentMemory));

    Add('Database File = ' + DbFileName);

    Add('Site Name = ' + DbSiteName);

    Add('SQL Dialect = ' + IntToStr(DBSQLDialect));

    Add('Fetches = ' + IntToStr(Fetches));

    Add('Page Size = ' + IntToStr(PageSize));

  end; //with

end;

Here the IBDatabase component is passed as a parameter and assigned to the IBDatabaseInfo components Database property. The rest of the code adds descriptive text and the values of several of the IBDatabaseInfo comonents properties to the listbox.

Using IBSQLMonitor

Using IBSQLMonitor is quite simple.

  1. Set the TraceFlags of the IBDatabase in the application you want to monitor to True.

  2. Drop an IBSQLMonitor and a list box on a form.

  3. Create an event handler for the IBSQLMonitors OnSQL event as shown below.

procedure TMonitorForm.IBSQLMonitor1SQL(EventText: String);

begin

  SqlList.Items.Add(EventText);

end;

Thats all there is to it. Start the application with the IBSQLMonitor component then start any app with its TraceFlags set to True and you will see all SQL statements in the list box of the monitor application.

Using IBEvent

Interbase triggers can raise events that your application can respond to. To monitor an event add an IBEvents component to your application. Set the Database property to the IBDatabase component whose database will generate the events you are interested in. Use the Events propertys property editor to enter the names of up to 15 events.

Next, create an OnEventAlert event handler to take whatever action should occur when a registered event takes place. Three parameters are passed to the OnEventAlert event handler to help you handle the event.

Parameter

Description

EventName

The name of the event that occurred.

EventCount

The number of times the event occurred.

CancelAlerts

This is a var parameter and is False by default. If you no longer wish to be notified when this event occurs set this parameter to True.

You can call the CancelEvents method to suspend notification of events and the QueEvents method to resume notification of events. You can also call UnregesterEvents to unregister the events in the Events property and RegisterEvents to register them. Calling RegisterEvents automatically calls QueEvents so you will begin receiving event notifications. Setting the Registered property to True also calls RegisterEvents.

When your application starts, after opening the database, set the Registered property of the IBEvents component to True to register your interest in the events with the Interbase server. From then on, whenever an event occurs, your OnEventAlert handler will be called. The Event sample application demonstrates displaying a message whenever a new order is inserted in the Sales table.

Master Detail Relationships

The Interbase Express components let you model complex data relationships in much the same way that the BDE components do. If you are using IBTables you can link a detail to its master by setting the MasterSource and MasterFields properties. If you are using IBQuerys or IBDataSets use a parameterized query for the detail table, make sure the parameter name is identical to the column name of the key field in the master table and set the details DataSource parameter to the master tables DataSource component. The MastDetail sample project demonstrates this using two IBDataSets.

Figure 9  The MastDetail applications data module

Figure 9 shows the data module from the MastDetail sample application. The SelectSQL property of the employee dataset is set to

select * from EMPLOYEE

and the SelectSQL property of the salary history dataset is set to

select * from SALARY_HISTORY

where EMP_NO = :EMP_NO

The DataSource property of the salary history dataset component is set to EmployeeSrc so the value of the EMP_NO parameter is automatically updated by the EmployeeSrc component each time a new employee row is displayed and the SalHistDs is automatically closed and reopened to fetch the salary history records for the current employee.

Preparing Queries and Stored Procedures

If you will be executing a query more than once, changing nothing but the parameter values between executions, you should prepare the query before the first execution and unprepared it after it is closed for the final time. This applies to IBQuery, IBDataSet and IBSQL. Calling the Prepare method tells Interbase to load the query into the servers memory and prepare to execute it. The query will remain in the servers memory ready to go until you call Unprepare. This will improve query performance for the second and subsequent executions. The same applies to calling stored procedures with the IBStoredProc component.

The MastDetail sample application includes the following code in the data modules OnCreate and OnDestroy event handlers.

procedure TMainDm.DataModuleCreate(Sender: TObject);
begin
  EmployeeDs.Prepare;
  EmployeeDs.Open;
  SalHistDs.Prepare;
  SalHistDs.Open;
end;
 
procedure TMainDm.DataModuleDestroy(Sender: TObject);
begin
  EmployeeDs.Close;
  EmployeeDs.Unprepare;
  SalHistDs.Close;
  SalHistDs.UnPrepare;
end;

The Admin Components

Interbase Express also includes eleven components that let you build Interbase administration capabilities into your applications. The following paragraphs provide an overview of these components which you will find on the Interbase Admin page of the component palette.

IBCofigService  Using this component you can start and stop the Interbase service and determine if the service is running. You can also shutdown a database or bring a database on line. IBConfig service also provides methods to set all of the parameters of a database including read only, the SQL dialect, the number of page buffers, the sweep interval, the asynchronous write mode and whether space for record versions is automatically reserved in the database pages.

IBBackupService and IBRestoreService  These components let you backup and restore an Interbase database. Multiple backup files are supported as is backing up to tape. All of the GBAK options are supported.

IBValidationService  This component validates your database and recovers two phase transactions that are in limbo.

IBStatisticalService  The IBStatisticalService component reports various statistics about your database.

IBLogService  Use this component to retrieve the contents of the Interbase log file from the server.

IBSecurityService  Using this component you can add and delete users and change a users security settings.

IBLicensingService  This component lets you add license certificates.

IBServerProperties  This component reports licensing and server configuration information.

IBInstall and IBUninstall  Use these components to install or uninstall Interbase.

Summary

The Interbase Express component suite provides a high performance interface to the Interbase server without using the BDE, ODBC or any other middleware. Iterbase 6 adds a suite of administration components to allow you to install and configure Interbase from a Delphi application. Using Interbase express you can create high performance local and remote server applications that provide the full power and performance of SQL database server.

Server Response from: ETNASC04