Devrace FIBPlus: Peculiarities of master-detail mechanism in the course of work with InterBase.

Before speaking about technical details I would like to tell about FIBPlus. It is a library of native components for Borland Delphi, Borland C++ Builder and Borland Kylix, which helps a developer to work more effective with InterBase and all its clones (Firebird and Yaffil) through direct InterBase API. Actually that means that while using FIBPlus the developer can also use all InterBase capacities: full control of transactions, maximum speed, special InterBase features (for example array-fields), etc. Besides all the above-mentioned FIBPlus components are absolutely compatible with standard data-aware components. We will demonstrate this in our examples.

The use of master-detail links is one of the most widespread tasks for any applied developer who uses relational databases. Actually the relational approach itself for database optimization means creation of deeper master-detail links. So after the database optimization the developer needs to simplify as much as possible the development of user's interface through which the further work of all master-detail links in its database will be realized.

We will have a look at control of such link using as an example a standard database from the InterBase kit �EMPLOYEE.GDB� and two tables: DEPARTMENT � EMPLOYEE. Here are the structures of both these tables:

CREATE TABLE DEPARTMENT ( 
  DEPT_NO DEPTNO NOT NULL, 
  DEPARTMENT VARCHAR (25) NOT NULL, 
  HEAD_DEPT DEPTNO, 
  MNGR_NO EMPNO, 
  BUDGET BUDGET, 
  LOCATION VARCHAR (15), 
  PHONE_NO PHONENUMBER DEFAULT '555-1234', 
  DEPT_NO1 CUSTNO); 
  
ALTER TABLE DEPARTMENT ADD PRIMARY KEY (DEPT_NO); 


CREATE TABLE EMPLOYEE ( 
  EMP_NO EMPNO NOT NULL, 
  FIRST_NAME FIRSTNAME NOT NULL, 
  LAST_NAME LASTNAME NOT NULL, 
  PHONE_EXT VARCHAR (4), 
  HIRE_DATE DATE DEFAULT 'NOW' NOT NULL, 
  DEPT_NO DEPTNO NOT NULL, 
  JOB_CODE JOBCODE NOT NULL, 
  JOB_GRADE JOBGRADE NOT NULL, 
  JOB_COUNTRY COUNTRYNAME NOT NULL, 
  SALARY SALARY NOT NULL, 
  FULL_NAME COMPUTED BY (last_name || ', ' || first_name)); 
  
ALTER TABLE  EMPLOYEE ADD PRIMARY KEY (EMP_NO); 
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO)  REFERENCES DEPARTMENT (DEPT_NO); 

It is obvious that DEPARTMENT is a master-table and EMPLOYEE � a detail one. The master-detail link is realized by the following fields:

DEPARTMENT.DEPT_NO <- EMPLOYEE.DEPT_NO

That is for each department we can store information about its employees.

Step 1. Getting data from the table and showing it in TDBGrid.

Let�s analyze the process of writing an application from the very beginning. With its help we will be able to edit data about departments and employees. It is necessary to drop on the form the main component, which allows connection to the InterBase database (TpFIBDatabase) and call the editor of this component.

Figure 1.

Figure 2.

For connection to the database it is necessary at least to set a path (in our example it is a path to the local file), a user's name and a password. You can check the correctness of the set parameters by pressing the �Test� button. We can also set parameters of the connection to the database at run-time.

Figure 3.

procedure TForm1.Button1Click(Sender: TObject);
begin
  pFIBDatabase1.DBName := DBNameE.Text;
  pFIBDatabase1.ConnectParams.UserName := UserNameE.Text;
  pFIBDatabase1.ConnectParams.Password := PasswordE.Text;
  pFIBDatabase1.Open;
end;

The next step is getting data from the database. For this we need two components: TpFIBDataSet and TpFIBTransaction. TpFIBDataSet is a descendant of TDataSet and it is fully compatible with all standard visual components (DataSource1, in particular, is connected with pFIBDataSet1). TpFIBTransaction is intended for transaction control.

Figure 4.

You need to set the level of transaction isolation with the help of the TPBMode property, and also link pFIBTransaction1 with pFIBDatabase1.

Figure 5.

Similar to this set the Database and Transaction properties for the pFIBDataSet1 component:

Figures 6-7

Now we can set all necessary queries to the database for getting and changing data in the table. Write a query for getting data with the help of SelectSQL. In our case the query is very simple:

SELECT * FROM DEPARTMENT

Now let's make some small code changes in the procedure:

procedure TForm1.Button1Click(Sender: TObject);
begin
  pFIBDatabase1.DBName := DBNameE.Text;
  pFIBDatabase1.ConnectParams.UserName := UserNameE.Text;
  pFIBDatabase1.ConnectParams.Password := PasswordE.Text;
  pFIBDatabase1.Open;

  pFIBDataSet1.Open;
end;

Before opening a query we do not need to start the transaction explicitly because pFIBDataSet1.Options by default contains the poStartTransaction key and the component itself starts the transaction. After the application start and database opening we see the following:

Figure 8.

See the full example code.