Part 2 part 3 part 4


Transactions in InterBase/Firebird: how to use them in FIBPlus (part 1)

Introduction


Transactions in multi-user environment in InterBase/Firebird (and other database servers) are often not the trivial topic. Programmers prefer to use only the isolation level READ COMMITTED regardless the task conditions.

I will consider how to use transactions and their characteristics for work with FIBlus components.

Besides such documentation as InterBase Language Reference, Embedded SQL Guide and API Guide, and "The Firebird Book: A Reference for Database Developers" by Helen Borrie there are many articles about transactions. I won't repeat any of them but tell about their main characteristics and their influence on multi-user database environment.

I have written some test applications based on FIBPlus to experiment with transactions. Here are some results of work with the TpFIBTransaction component, checking whether transaction parameters correspond to the SQL language used to describe transaction characteristics.

Database description

I have created a new database FIBTRANSACT.FDB, which supports Firebird 1.5. There are two tables: a country reference REFCOUNTRY and a region reference REFREGION (with a list of some regions). This is a part of real database. At first you need to create domains:

CREATE DOMAIN DCodCtr AS CHAR(3);
CREATE DOMAIN DName30 AS VARCHAR(30) COLLATE PXW_CYRL;
CREATE DOMAIN DName60 AS VARCHAR(60) COLLATE PXW_CYRL;
CREATE DOMAIN DDescr AS BLOB SUB_TYPE 1 SEGMENT SIZE 400;

Table creation script (partially):

 /*** Country reference REFCOUNTRY ***/
CREATE TABLE REFCOUNTRY
( Name DName30, /* Short name of country */
FullName DName60, /* Full name of country */
CodCtr DCodCtr NOT NULL, /* Country code */
Capital DName30, /* Capital */
Region DName30, /* Region name */
Description DDescr, /* Additional information */
CONSTRAINT "Country_PRIMARY_KEY" PRIMARY KEY (CodCtr)
);
COMMIT;
/*** Region reference REFREGION ***/
CREATE TABLE REFREGION
( CodCtr DCodCtr NOT NULL, /* Country code */
CodReg DCodCtr NOT NULL, /* Region code */
Center DName30, /* Name of the region centre */
RegName DName60, /* Region name */
Description DDescr, /* Additional information */
CONSTRAINT "Region_PRIMARY_KEY"
PRIMARY KEY (CodCtr, CodReg),
CONSTRAINT "Region_FOREIGN_KEY"
FOREIGN KEY (CodCtr) REFERENCES REFCOUNTRY (CodCtr)
ON DELETE CASCADE
ON UPDATE CASCADE
);
COMMIT;
The tables have been filled with data about USA ENGLAND countries and their regions.

Test application

Create a new application in Delphi or C++Builder IDE; create a toolbar with TButton; place two TDBDrid components TDBDrid: DBGridCountry and DBGridRegion; add two TDataSource components: DataSourceCountry and DataSourceRegion.

Then get the following components from FIBPlus tab: Database1 (TpFIBDatabase type), Transaction1 (TpFIBTransaction type), two TpFIBDataSet components: DataSetCountry and DataSetRegion; and the ErrorHandler component:

 1

Picture 1. The main form of the test application

How to set necessary values for the database object:

Database name (DBName property) FIBTRANSACT.FDB (put the database to the project directory).

User name (UserName) SYSDBA,

Password (Password) masterkey,

CharSet (CharSet) WIN1251,

Database SQLDialect (SQLDialect) equal to 3.

Select Transaction1 as DefaultTransaction and UpdateTransaction:

 2

Picture 2. Database component properties

Set the following values for the Transaction1 component.

Select Database1 as a database name (DefaultDatabase) from the the popup menu.

Then select the tpbDefault value from the popup menu for the TPBMode transaction isolation level, as only this value will enable you to change the transaction parameter buffer (TPB). If you select tpbReadCommitted or tpbRepeatableRead values, corresponding constants will be placed to the parameter buffer on starting the transaction and you won't be able to change this.

 3

Picture 3. Transaction component properties

For DataSetCountry dataset you can leave most properties as default. Set the following values:

Database (Database) Database1.

Transaction (Transaction) and UpdateTransaction (UpdateTransaction) Transaction1.

Important: set poStartTransaction to False in the list of options (Option) in order to be able to manage transaction Start and Commit (or Rollback) explicitly. Set poKeepSorting to True. This setting can be useful for ordering datasets in case of data changes in ordered columns (ORDER BY clause).

You can also set psAskRecordCount to True in PrepareOptions (not necessary in the demonstrated case). Use this option to show the number of retrieved records in the status bar after reopening the dataset.

Call the SQL generator (double click on the component and select SQL Generator string in the context menu). Select REFCOUNTRY from the list of tables and double click it. You will see the SELECT operator generated. Add ORDER BY NAME clause to the operator end to order the dataset by country names.

 4

Picture 4. SELECT generation

Now click the Generate SQLs button on the Generate Modify SQLs tab. You will get Insert, Update, Delete and Refresh operators.

 5

Picture 5. Generation of modifying operators

Link the DataSourceCountry component to the DataSetCountry dataset (the DataSet property), then do the same actions with the DataSetRegion component. Besides as this dataset is detail in the master-detail relationship you need to set DataSource to DataSourceCountry and also dcForceOpen and dcWaitEndMasterScroll to True in DetailConditions. On generating the SELECT operator in the SQL generator you should modify it:

SELECT
CODCTR,
CODREG,
CENTER,
REGNAME,
DESCRIPTION
FROM
REFREGION WHERE CODCTR = ?CODCTR
ORDER BY CENTER

From the list of regions the clause WHERE CODCTR = ?CODCTR selects only records which refer to the current country. More details about master-detail relationship in FIBPlus read at www.devrace.com.

Now create two forms: Transaction's Characteristics will form a list of transaction parameters - TRParams, whereas Transaction's Parameters will show the transaction parameter buffer (TPB).

 6

Picture 6. Transaction's Characteristics Form

You will need the Transaction's Characteristics form to create a list of transaction characteristics. Set the Name property to AllParameters for the left ListBox component, and to SelectedParameters for the right ListBox. Now write the following TButton.OnClick event handlers. On clicking > selected strings are moved from the left ListBox to the right component.

procedure TFormTrans.Button1Click(Sender: TObject);
var I: Integer;
begin
I := 0;
while (I <= AllParameters.Items.Count - 1) do
begin
if
AllParameters.Selected[I] then
begin

SelectedParameters.Items.Add(AllParameters.Items[I]);
AllParameters.Items.Delete(I);
I := I - 1;
end;
I := I + 1;
end;
end;

Note. I do not write C++Builder code, as it's rather easy to translate from Delphi to C++.

You can select several strings in any ListBox component by pressing Ctrl and clicking the srtings.

Click on the >> button to move all the strings from the left ListBox to the right. I have described this function just in case, as you don't need it for the present task.

procedure TFormTrans.Button2Click(Sender: TObject);
var I: Integer;
begin
for
I := 0 to AllParameters.Items.Count - 1 do
SelectedParameters.Items.Add(AllParameters.Items[I]);
AllParameters.Items.Clear;
end;

Click < to move all selected strings from the right ListBox component to the left:

procedure TFormTrans.Button4Click(Sender: TObject);
var I: Integer;
begin
I := 0;
while (I <= SelectedParameters.Items.Count - 1) do
begin
if
SelectedParameters.Selected[I] then
begin

AllParameters.Items.Add(SelectedParameters.Items[I]);
SelectedParameters.Items.Delete(I);
I := I - 1;
end;
I := I + 1;
end;
end;

Click < to move all the strings from the right ListBox component to the left:

procedure TFormTrans.Button3Click(Sender: TObject);
var I: Integer;
begin
for
I := 0 to SelectedParameters.Items.Count - 1 do
AllParameters.Items.Add(SelectedParameters.Items[I]);
SelectedParameters.Items.Clear;
end;

 7

Picture 7. Transaction's Parameters Form

Transaction's Parameters Form shows the list of transaction parameters (TRParams) and transaction parameter buffer vector (TPB). On calling the form, Memo1 shows the parameter list and numeric values from TPB.

procedure TFormTransactionParam.FormShow(Sender: TObject);
var I: Integer;
begin
Memo1.Clear;
for I := 0 to FormMain.Transaction1.TRParams.Count - 1 do
Memo1.Lines.Add(FormMain.Transaction1.TRParams.Strings[I]);
Memo1.Lines.Add('============================');
for I := 0 to FormMain.Transaction1.TPBLength - 1 do
Memo1.Lines.Add(IntToStr(Integer(FormMain.Transaction1.TPB[I])));
end;

Now return to the main module and write the form event handlers. Connect to the database in the OnShow form event and disconnect from the database in OnClose correspondingly:

Database1.Open; and

Database1.Close;

Click Open DataSet to open the DataSetCountry dataset and the DataSetRegion dataset will open automatically.

procedure TFormMain.BOpenDataSetClick(Sender: TObject);
begin
DataSetCountry.Open;
end;

Click Close DataSet to close both datasets:

DataSetCountry.Close;

Click BStartTransaction to start the transaction and BStopTransaction to stop it:

Transaction1.StartTransaction;
Transaction1.Active := False;

You can commit the transaction by clicking BCommit. After Commit the transaction will be started again, then the dataset opens (it will be automatically closed after the transaction end by COMMIT or ROLLBACK):

procedure TFormMain.BCommitClick(Sender: TObject);
begin
Transaction1.Commit;
Transaction1.StartTransaction;
DataSetCountry.Open;
end;

The same goes for TButton.OnClick event by BRollBack.

procedure TFormMain.BRollBackClick(Sender: TObject);
begin
Transaction1.Rollback;
Transaction1.StartTransaction;
DataSetCountry.Open;
end;

Click Characteristics button to call Transaction's Characteristics Form and to form a user list of transaction characteristics.

procedure TFormMain.BCharactTransactClick(Sender: TObject);
var I: Integer;
begin
if
FormTrans.ShowModal <> IDOK then exit;
if Transaction1.Active then
Transaction1.Active := False;
Transaction1.TRParams.Clear;
for I := 0 to FormTrans.SelectedParameters.Items.Count - 1 do
Transaction1.TRParams.Add(
FormTrans.SelectedParameters.Items[I]);
end;

Click the ParamTransact button to call Transaction's Parameters Form and show current transaction characteristics.

Finally write the database exception handler. Then in case of exceptions you will be able to see SQLCODE and GDSCODE code values as well as database server message. For these purposes use the OnFIBErrorEvent event of the ErrorHandler component:

procedure TFormMain.ErrorHandler1FIBErrorEvent(Sender: TObject;
ErrorValue: EFIBError; KindIBError: TKindIBError;
var DoRaise: Boolean);
var S: String;
begin
S := S + 'SQLCode = ' + IntToStr(ErrorValue.SQLCode) + #10#13;
S := S + 'IBErrorCode = ' + IntToStr(ErrorValue.IBErrorCode) + #10#13;
S := S + 'IBMessage = ' + ErrorValue.IBMessage + #10#13;
Application.MessageBox(PAnsiChar(S), 'Database Error',
MB_OK + MB_ICONSTOP);
DoRaise := False;
end;

Add fib to the list of program modules (uses clause).

Note. This exception handler shows only an exception message and does nothing else. You need to close the transaction manually, start it anew and open the dataset to continue working. Of course in real situations you will analyze the error and do everything to avoid it. In this case you will have a working application.

Part 2 part 3 part 4


Preview text: 

Transactions in multi-user environment in InterBase/Firebird (and other database servers) are often not the trivial topic. Programmers prefer to use only the isolation level READ COMMITTED regardless the task conditions.


Prices in Euro:

235 (1 copy)
1250 (unlimited)

Volume discounts are available...

Navigation



What a good job you did! Keep coding great pieces of fast and efficient software:) We started using FIBPlus back in 2001. Since then, every project we offer uses FIBPlus to connect to FirebirdSQL. You library has always been stable, flexible and rocket fast ! Our major project, an helpdesk/data mining application, concurrently used daily by more that a hundred technicians and analysts, uses FIBPlus. Now that FirebirdSQL 2.0 is available, we have been able to upgrade our application quickly and painlessly - that's another reason you can be proud of your work ! >>

Benoit Le Bourhis, Adhoc Innovations
FOR CUSTOMERS
Download full versions and updates in your Personal Area