FIBPlus: handling database errors

FIBPlus: handling database errors

One of best FIBPlus advantages is its database error handling mechanism. In this article I will consider it in details. 

The main component used for error handling is called TpFibErrorHandler. It enables developers to handle errors centrally.

Preparation for error handling

To demonstrate FIBPlus potential I will use the database FIBSAMPLE.GDB used in most FIBPlus examples. Besides I will use two database tables: TREFCOUNTRY and PERSON, declared as:

CREATE TABLE TREFCOUNTRY (
    NAME         DNAME30,
    FULLNAME     DNAME60,
    CODCTR       DCODCTR NOT NULL,
    CAPITAL      DNAME30,
    REGION       DNAME30,
    DESCRIPTION  DDESCR
);

CREATE TABLE PERSON (
    CODPERS     INTEGER NOT NULL,
    FIRST_NAME  DNAME20,
    LAST_NAME   DNAME20,
    COUNTRY     DCODCTR
);
ALTER TABLE PERSON ADD CONSTRAINT "PERS CHECK LASTNAME NOTNULL"
 check (last_name is not null);
ALTER TABLE PERSON ADD CONSTRAINT "PERS CHECK LASTNAME VALUE"
 check (last_name not containing '***');
ALTER TABLE PERSON ADD CONSTRAINT "PERS PRIMARYKEY"
 PRIMARY KEY (CODPERS);
ALTER TABLE TREFCOUNTRY ADD CONSTRAINT "Country PRIMARY KEY"
 PRIMARY KEY (CODCTR);
ALTER TABLE PERSON ADD CONSTRAINT "PERS FOREIGN KEY"
 FOREIGN KEY (COUNTRY) REFERENCES TREFCOUNTRY (CODCTR);

Create a new project ErrorHandler in Delphi or C++Builder. Then place these components onto the form:

StatusBar1: TStatusBar;
Panel1: TPanel;
Panel2: TPanel;
Splitter1: TSplitter;
Splitter2: TSplitter;
Memo1: TMemo;
DBGrid1: TDBGrid;
DBGrid2: TDBGrid;
BExit: TSpeedButton;
BRefresh: TSpeedButton;
BSRollback: TButton;
BSCommit: TButton;
Database1: TpFIBDatabase;
WriteTransaction: TpFIBTransaction;
CountryData: TpFIBDataSet;
DSCountry: TDataSource;
PersData: TpFIBDataSet;
DSPerson: TDataSource;
ErrorHandler1: TpFibErrorHandler;

Note. As you are using the TpFibErrorHandler component, you should explicitly set the fib module in uses for Delphi or in the include operator in C++Builder:

Delphi

uses fib;

C++

#include <fib>;

The DBName property of the Database1 component refers to the database FIBSAMPLE.GDB. The CountryData component of the TpFIBDataSet type refers to the TREFCOUNTRY table. The PersData component refers to the PERSON table. Using SQL Generator, generate all SQL commands for these components.

Link the DSCountry component with CountryData. Set the DataSource property to DSCountry in DBGrid1. DBGrid1 shows the data in the CountryData dataset. In the same way link the DSPerson component with PersData. Set DataSource to DSPerson in DBGrid2. DBGrid2 shows the data in PersData.

Memo1 will show information about errors.

Picture 1. The ErrorHandler project. Handling database errors.

Write the following click handlers for Rollback and Commit: buttons:

Delphi

procedure TFormMain.BSRollbackClick(Sender: TObject);
begin
  WriteTransaction.RollbackRetaining;
  CountryData.FullRefresh;
  StatusBar1.Panels.Items[1].Text :=
    IntToStr(CountryData.RecordCount);
  DBGrid1.SetFocus;
end;

procedure TFormMain.BSCommitClick(Sender: TObject);
begin
  WriteTransaction.CommitRetaining;
  CountryData.FullRefresh;
  PersData.FullRefresh;
  DBGrid1.SetFocus;
end;

C++

void __fastcall TFormMain::BSRollbackClick(TObject *Sender)
{
WriteTransaction->RollbackRetaining();
CountryData->FullRefresh();
StatusBar1->Panels->Items[1]->Text =
  IntToStr(CountryData->RecordCount);
DBGrid1->SetFocus();
}

void __fastcall TFormMain::BSCommitClick(TObject *Sender)
{
WriteTransaction->CommitRetaining();
CountryData->FullRefresh();
PersData->FullRefresh();
DBGrid1->SetFocus();
}

Now write a click handler for the Refresh button:

Delphi

procedure TFormMain.BRefreshClick(Sender: TObject);
begin
  CountryData.FullRefresh;
  PersData.FullRefresh;
  StatusBar1.Panels.Items[1].Text :=
    IntToStr(CountryData.RecordCount);
end;

C++

void __fastcall TFormMain::BRefreshClick(TObject *Sender)
{
CountryData->FullRefresh();
PersData->FullRefresh();
StatusBar1->Panels->Items[1]->Text =
  IntToStr(CountryData->RecordCount);        
}

You need these handlers to commit or roll back the transaction and to update dataset data.

TpFIBErrorHandler properties and features

The central part of this application is the ErrorHandler1 component and its event handler OnFIBEventError. This will enable you to hook and handle all main exception types while working with the database.

Picture 2. ErrorHandler1 features

TpFibErrorHandler also has the following properties (read-only):

Table 1. Read-only TpFibErrorHandler properties.

This table shows sybproperty values of the Options property.

Table 2. A list of Options subproperties of the TpFibErrorHandler component.

Now write the following OnFIBEventError event handler for the ErrorHandler1 component:

Delphi

procedure TFormMain.ErrorHandler1FIBErrorEvent(Sender: TObject;
  ErrorValue: EFIBError; KindIBError: TKindIBError;
  var DoRaise: Boolean);
var Lasterror: string;
    FKindIBError: string;
begin
  Memo1.Lines.Add(#13#10 + '===== ErrorHandler FIBErrorEvent =====');
  Memo1.Lines.Add('Sender.ClassName = ' + Sender.ClassName);
  Memo1.Lines.Add('Sender.Name = ' + (Sender as TComponent).Name);
  if Sender is TFIBQuery then
    Memo1.Lines.Add('Owner.Name = ' +
      (Sender as TFIBQuery).Owner.Name);
  if Sender is TpFIBStoredProc then
    Memo1.Lines.Add('Sender.StoredProcName = ' +
      (Sender as TpFIBStoredProc).StoredProcName);
  Memo1.Lines.Add('ConstraintName = ' +
    ErrorHandler1.ConstraintName);
  Memo1.Lines.Add('ExceptionNumber = ' +
    IntToStr(ErrorHandler1.ExceptionNumber));
  case ErrorHandler1.LastError of
    keNoError: Lasterror := 'keNoError';
    keException: Lasterror := 'keException';
    keForeignKey: Lasterror := 'keForeignKey';
    keSecurity: Lasterror := 'keSecurity';
    keLostConnect: Lasterror := 'keLostConnect';
    keCheck: Lasterror := 'keCheck';
    keUniqueViolation: Lasterror := 'keUniqueViolation';
    keOther: Lasterror := 'keOther';
  else
    Lasterror := 'Undefined';
  end;
  Memo1.Lines.Add('Lasterror = ' + Lasterror);
  Memo1.Lines.Add('SQLCode = ' + IntToStr(ErrorValue.SQLCode));
  Memo1.Lines.Add('IBErrorCode = ' +
   IntToStr(ErrorValue.IBErrorCode));
  Memo1.Lines.Add('Message = ' + ErrorValue.Message);
  Memo1.Lines.Add('IBMessage = ' + ErrorValue.IBMessage);
  Memo1.Lines.Add('SQLMessage = ' + ErrorValue.SQLMessage);
  case KindIBError of
    keNoError: FKindIBError := 'keNoError';
    keException: FKindIBError := 'keException';
    keForeignKey: FKindIBError := 'keForeignKey';
    keSecurity: FKindIBError := 'keSecurity';
    keLostConnect: Lasterror := 'keLostConnect';
    keCheck: FKindIBError := 'keCheck';
    keUniqueViolation: FKindIBError := 'keUniqueViolation';
    keOther: FKindIBError := 'keOther';
  else
    FKindIBError := 'Undefined';
  end;
  Memo1.Lines.Add('KindIBError = ' + FKindIBError);
//  DoRaise := False;
end;

C++

void __fastcall TFormMain::ErrorHandler1FIBErrorEvent(TObject
 *Sender, EFIBError *ErrorValue, TKindIBError KindIBError,
 bool &DoRaise)
{
AnsiString Lasterror;
AnsiString FKindIBError;
Memo1->Lines->Add("");
Memo1->Lines->Add("========= ErrorHandler FIBErrorEvent =========");
Memo1->Lines->Add("Sender.ClassName = " + Sender->ClassName());
Memo1->Lines->Add("Sender.Name = " +
  dynamic_cast(Sender)->Name);
if (Sender->ClassNameIs("TFIBQuery"))
  Memo1->Lines->Add("Owner.Name = " +
    dynamic_cast(Sender)->Owner->Name);
if (Sender->ClassNameIs("TpFIBStoredProc"))
  Memo1->Lines->Add("Sender.StoredProcName  = " +
    dynamic_cast(Sender)->StoredProcName);
Memo1->Lines->Add("ConstraintName = " +
  ErrorHandler1->ConstraintName);
Memo1->Lines->Add("ExceptionNumber = " +
  IntToStr(ErrorHandler1->ExceptionNumber));
switch (ErrorHandler1->LastError)
{ case keNoError: Lasterror = "keNoError"; break;
  case keException: Lasterror = "keException"; break;
  case keForeignKey: Lasterror = "keForeignKey"; break;
  case keSecurity: Lasterror = "keSecurity"; break;
  case keLostConnect: Lasterror = "keLostConnect"; break;
  case keCheck: Lasterror = "keCheck"; break;
  case keUniqueViolation: Lasterror = "keUniqueViolation"; break;
  case keOther: Lasterror = "keOther"; break;
  default: Lasterror = "Undefined";
}
Memo1->Lines->Add("Lasterror = " + Lasterror);
Memo1->Lines->Add("SQLCode = " + IntToStr(ErrorValue->SQLCode));
Memo1->Lines->Add("IBErrorCode = " +
  IntToStr(ErrorValue->IBErrorCode));
Memo1->Lines->Add("Message = " + ErrorValue->Message);
Memo1->Lines->Add("IBMessage = " + ErrorValue->IBMessage);
Memo1->Lines->Add("SQLMessage = " + ErrorValue->SQLMessage);
switch (KindIBError)
{ case keNoError: FKindIBError = "keNoError"; break;
  case keException: FKindIBError = "keException"; break;
  case keForeignKey: FKindIBError = "keForeignKey"; break;
  case keSecurity: FKindIBError = "keSecurity"; break;
  case keLostConnect: Lasterror = "keLostConnect"; break;
  case keCheck: FKindIBError = "keCheck"; break;
  case keUniqueViolation: FKindIBError = "keUniqueViolation"; break;
  case keOther: FKindIBError = "keOther"; break;
  default: FKindIBError = "Undefined";
}
Memo1->Lines->Add("KindIBError = " + FKindIBError);
//  DoRaise = false;
}

The handler has the following parameters: 

1. ErrorValue is an object of the EFIBError class with these properties:
   
   • IBErrorCode — has an InterBase error code. It's the most informative error descriptor. There are about 400 different error codes. Full list of these codes is available in InterBase Language Reference, Part 5: Error Codes and Messages (table 5.5).
   • IBMessage — has an error message text.
   • SQLCode — has an SQLCODE error code.
   • SQLMessage — has a message about the SQL error.
2. KindIBError is an object of the TKindIBError class. It can have values listed in Table 1 (the LastErrorproperty).
3. DoRaise is a logical variable. It enables you to call the exception once again after the error handling. If DoRaise is set to True (default value), a standard exception with corresponding error messages will be called after all operations in the event handler. If DoRaise is set to False, the operation which caused the error will be cancelled and no messages will be shown. In the demonstrated DB error handler all information about the error will be shown in the Memo1 field (there will be used TpFibErrorHandler properties and parameters transferred to the procedure).

Note. The TpFibErrorHandler component enables you to handle most database errors. But it does not handle errors of DB connection (do not mix them up with lost/restore connection errors). To handle such errors you should use standard Delphi or C++Builder methods:  try…except (Delphi) or try…catch (C++Builder). TpFibErrorHandler also does not handle situations when a database or transactions are not set for the component working with the DB (DataSet, Query, StoredProc, etc).

This example shows how to hook an error message on connecting to a database.

Delphi

try
  Database1.Connected := True;
except
  ShowMessage(’Error messages on connecting to a database’);
  Application.Terminate;
end;

try
{ Database->Connected = true; }
catch(...)
{ ShowMessage("Error messages on connecting to a database");
  Application->Terminate();
}

========= ErrorHandler FIBErrorEvent =========
Sender.ClassName = TFIBQuery
Sender.Name = UpdateQuery
Owner.Name = CountryData
ConstraintName = 
ExceptionNumber = -1
Lasterror = keOther
SQLCode = -625
IBErrorCode = 335544347
Message = FormMain.CountryData.UpdateQuery:
The insert failed because a column definition includes validation constraints.validation error
for column CODCTR, value "*** null ***".

IBMessage = validation error for column CODCTR, value "*** null ***".

SQLMessage = The insert failed because a column definition includes validation constraints.
KindIBError = keOther

Sender.ClassName = TFIBQuery
Sender.Name = UpdateQuery
Owner.Name = CountryData

========= ErrorHandler FIBErrorEvent =========
Sender.ClassName = TFIBQuery
Sender.Name = UpdateQuery
Owner.Name = PersData
ConstraintName = PERS
ExceptionNumber = -1
Lasterror = keUniqueViolation
SQLCode = -803
IBErrorCode = 335544665
Message = violation of PRIMARY or UNIQUE KEY constraint "PERS PRIMARYKEY" on table "PERSON".

IBMessage = violation of PRIMARY or UNIQUE KEY constraint "PERS PRIMARYKEY" on table "PERSON".

SQLMessage = Invalid insert or update value(s): object columns are
constrained - no 2 table rows can have duplicate column values.
KindIBError = keUniqueViolation

========= ErrorHandler FIBErrorEvent =========
Sender.ClassName = TFIBQuery
Sender.Name = RefreshQuery
Owner.Name = CountryData
ConstraintName = 
ExceptionNumber = -1
Lasterror = keLostConnect
SQLCode = -901
IBErrorCode = 335544741
Message = FormMain.CountryData.RefreshQuery:
Unsuccessful execution caused by system error that does not preclude successful execution of
subsequent statements.connection lost to database.

IBMessage = connection lost to database.

SQLMessage = Unsuccessful execution caused by system error that does not preclude successful
execution of subsequent statements.
KindIBError =

CONNECT 'D:\set the path to a database\FIBSAMPLE.GDB'
USER 'SYSDBA' PASSWORD 'masterkey';

CREATE EXCEPTION EXCEPT1 'Exception 1';
commit;

CONNECT 'D:\ set the path to a database\FIBSAMPLE.GDB'
USER 'SYSDBA' PASSWORD 'masterkey';

CREATE PROCEDURE EXEEXCEPT1
AS
begin
  EXCEPTION EXCEPT1;
end;
commit;

Picture 3. TpFIBStoredProc parameters (addressing to the stored procedure)

Write the handler for the Exception button clicking:

Delphi

procedure TFormMain.ExceptionClick(Sender: Tobject);
begin
  FIBStoredProc1.ExecProc;
end;
  

C++

void __fastcall TformMain::ExceptionClick(Tobject *Sender)
{
FIBStoredProc1->ExecProc();
}

Execute the application and click the Exception button. The Memo1 field will show:

========= ErrorHandler FIBErrorEvent =========
Sender.ClassName = TpFIBStoredProc
Sender.Name = FIBStoredProc1
Sender.StoredProcName = EXEEXCEPT1
ConstraintName = 
ExceptionNumber = 1
Lasterror = keException
SQLCode = -836
IBErrorCode = 335544517
Message = Exception 1.
IBMessage = exception 1.
Exception 1.

SQLMessage = exception 268785020.
KindIBError = keException

Sender.ClassName has a value (TpFIBStoredProc), because you have got the exception on calling the stored procedure. Sender.Name contains the name of the component which called the stored procedure (FIBStoredProc1). Sender.StoredProcName has a stored procedure name.

Remember that the user exception works only in the program, which caused this exception for the stored procedure or a trigger. This exception is not shown in other applications.
Finally see the conflict when different clients change the same record. Run two application copies, they will be two different clients. Change any record in one application and the same record in the second application. When moving to the next record in DBGrid (Post will be executed for the modified record) you will get the exception:

========= ErrorHandler FIBErrorEvent =========
Sender.ClassName = TFIBQuery
Sender.Name = UpdateQuery
Owner.Name = CountryData
ConstraintName = 
ExceptionNumber = -1
Lasterror = keOther
SQLCode = -901
IBErrorCode = 335544345
Message = FormMain.CountryData.UpdateQuery:
Unsuccessful execution caused by system error that does not preclude successful execution of
subsequent statements.lock conflict on no wait transaction.
deadlock.
update conflicts with concurrent update.

IBMessage = lock conflict on no wait transaction.
deadlock.
update conflicts with concurrent update.

SQLMessage = Unsuccessful execution caused by system error that does not preclude successful
execution of subsequent statements.
KindIBError = keOther