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 DBGrid2DBGrid2 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.

Property Value
ConstraintName A name of the constraint which caused the error.
Note. The constraint name in the current FIBPlus versions should not contain word spaces. If you use word spaces, the property name will have a text written before the first word space.
ExceptionNumber A number of the user exception (exception), which caused the error. If the error is not caused by the exception, the ExceptionNumber value will be  –1.
LastError A type of the last exception, it's an object of the TKindIBError class:
keNoError — no errors occurred,
keException — user exception was handled,
keForeignKey — the foreign key violation was handled,
keLostConnect — the database connection is lost,
keSecurity — the user rights violation was handled,
keUniqueViolation — the unique value violation was handled,
keCheck — the CHECK restriction violation was handled,
keOther — another error type was handled.

This table shows sybproperty values of the Options property.

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

Subproperty  Value
oeException User exceptions are handled. The error text is not shown; the user exception number is handled in the ExceptionNumber component property.
oeForeignKey A foreign key value violation (foreign key).
oeLostConnect Lost database connection
oeCheck CHECK violation.
oeUniqueViolation UNIQUE violation.

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;

    C++

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

    Handling exceptions

    Now you can see how the event handler operates.

    Execute the application. Delete the primary key value (CODCTR) in any string in TREFCOUNTRY (left DBGrid). The field value will be NULL, and it's not appropriate for the primary key. You will see the following text in Memo1:

    ========= 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

    Notice the first three message lines:

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

    Sender.ClassName has a name of the object class which caused the exception (TFIBQuery). Sender.Name is an object name (UpdateQuery), Owner.Name is an object owner's name: the TpFIBDataSet component name (CountryData). If you try to add a new record with the NULL primary key value to the same database, we will get the same error message (and InsertQuery will be the Sender.Name value).

    You will get the same result if you set the CODPERS primary key value to NULL (in PERSON).

    Trying to insert the doubled primary key value into any PERSON line you will get the following message:

    ========= 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

    In the similar way you can check the reaction to other DB restriction violations. Check how FIBPlus will react to the lost connection. Close the InterBase/Firebird server. If the program Guardian is active, close it before closing the server. Then press the Refresh button and you will see this error message:

  • ========= 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 =

    Notice SQLCode and IBErrorCode values. SQLCode = -901. InterBase Language Reference manual shows that 60 error variants correspond to SQLCode = -901. And IBErrorCode = 335544741 has only one error type: «connection lost to database». I'd like to emphasize that the most useful parameter for "lost connection" error messages is IBErrorCode, transferred to the error message handler.

    Now check how the error handler works when being called from a stored procedure or from a user exception trigger. Add an exception and a simple stored procedure which causes this exception to FIBSAMPLE.GDB.

    Create an exception using the script file:

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

    CREATE EXCEPTION EXCEPT1 'Exception 1';
    commit;

    And now create a stored procedure:

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

    CREATE PROCEDURE EXEEXCEPT1
    AS
    begin
    EXCEPTION EXCEPT1;
    end;
    commit;
    To call the exception you should make changes in the test project. Place the Exception button and the TpFIBStoredProc component onto the form. Set the following parameters for TpFIBStoredProc:
  • 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

    Conclusion


    We considered how to use the TpFIBErrorHandler mechanism and to hook all main error types which can occur in practice. The way you handle these exceptions and their meaning depend on the program you develop. FIBPlus provides you with a flexible and user-friendly mechanism for error hooking and analysis.

    Download examples.


  • Preview text: 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.
    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