Databases - Delphi IBX Database Connections

IBX Database Connections

Another page covers the main details for connecting to a Firebird or Interbase database via IBX (InterbaseExpress).

However, I have a real time data acquisition system that needs to run for months without failing ... the existing IBX components won't do that without a little tweaking.

Background

I wrote a real time data collecting application that wrote to a remote database every 5 seconds. It would work fine from 1 to 3 days before it crashed. At the time, I was using Delphi 6 with the default IBX components. Because of a design issue, when there was any type of network issue, the program just stopped without any warning - no dialog box and the FormClose method was not called. Actually, Windows stopped the program because of an "access violation".

After a couple of days troubleshooting, I "fixed" this problem by updating the IBX components.

Of course, that was only the first layer of the onion. While the program no longer crashes, it still needs to deal with temporary network issues.

Causes of Network Reliability Problems

Network connections are inherently unreliable

Someone could unplug a cable
High network traffic - backups and the like - could cause temporary connection problems
Wireless networks nearly always have intermittent connections
Hardware failure
Temporary power failure - the computers have battery backups, but not every network switch

With most applications, you would see some king of error and would just try again.

With database applications, you could loose data or perhaps corrupt the database.

With realtime data collecting applications, the entire purpose of the application will fail - it will quit collecting data.

As a result of these known network problems, it is necessary to store the data on the local hard drive - assets on the network simply can not be trusted to be available.

Suggested Component Enhancements

To simplify implementation, I suggest creating a new database component with the following additions. (I tried to simply derive a new component with these fixes ... but because none of the methods are virtual, I had to give up.)

New properties

DisplayConnectionError: Used to stop the display of an error
DoForceCloseOnLostConnection: Temporary connection interruption causes ForceClose to be called
ProcessLostConnection: Replaces the RaiseError flag in the Call method

New events

OnLostConnection: Called if the connection is lost - intentionally kept different from the non-existing OnConnectionLost referenced in the IBX source

Overloaded methods

Call: Calls OnLostConnection if it is defined and replaces the RaiseError flag with ProcessLostConnection
Unfortunately, this method is not virtual ... that means that there is no easy way to fix this problem.

All data must be saved locally if it can not be posted to the database. A separate program will read the local text logs and make the changes to the database. Only inserts need to be handled since a human will always be present for modifications and deletes ... but inserts will take place 24 hours a day 7 days a week.

***

I originally thought that the place to put the correct code is in IBDataBaseError because all errors cause this to be called ... the problem is that this is a procedure that is not attached to the IBDataBase component. Since it is not part of an object, it can not be overloaded. Since it is not associated with any component, there is no way for it to know what components need to be alerted to the problem.

Now that I have a better understanding of IBDataBaseError, I realize that only a small part of the code could (or should) ever go there.

Testing the Connection

How to check the connection

if IBDataBase.Connected then: Just test if the database was connected at some time in the past (it actually just checks if the handle has been assigned)
if IBDataBase.TestConnected then: This actually tries to access the database and reports whether or not there was a problem

I tried to use the IBDataSet events - OnEditError and OnUpdateError - to capture a connection error, but that failed - IBDataSet1.Open never called either of these.

While using a highly patched application, I unplugged the network cable and tried to connect to a remote database. The application hung (was not responsive) for about 60 seconds before producing an error.

When the cable was reconnected a few seconds after the request was made, the application was eventually able to connect to the database without further processing and display the data.

This indicates that network interruptions need to last more than 60 seconds before they are a problem.

IBQuery - FOpen and Active

For TIBCustomDataSet, there is a big difference between FOpen and Active.

IBQuery.Close checks Active, which is False

function TDataSet.GetActive: Boolean;
begin
  Result := not (State in [dsInactive, dsOpening]); // In this case, *State* = dsInactive
end;

IBQuery.SQL.Text checks FOpen via CheckDatasetClosed. FOpen is normally set false in function TIBCustomDataSet.InternalClose, but this is not called when the connection is lost. In fact, there is no way to clear this parameter ... except to try and open the query, which fails, produces an error, and clears the flag (in that order).

Code examples

This is from the original Delphi 5 IBX components

function TIBDatabase.Call(ErrCode: ISC_STATUS;
  RaiseError: Boolean): ISC_STATUS;
begin
  result := ErrCode;
  FCanTimeout := False;
  if RaiseError and (ErrCode > 0) then
    IBDataBaseError;
end;

This is from Delphi 5 IBX 5.04

function TIBDatabase.Call(ErrCode: ISC_STATUS;
  RaiseError: Boolean): ISC_STATUS;
begin
  result := ErrCode;
  FCanTimeout := False;
  {Handle when the Error is due to a Database disconnect.  Call the
  OnConnectionLost if it exists.}
  if RaiseError and CheckStatusVector([isc_lost_db_connection]) then
    ForceClose;
  if RaiseError and (ErrCode > 0) then
    IBDataBaseError;
end;

I don't know what OnConnectionLost is - it is not defined.

This function is similar in Delphi 2006 - except that

It checks for 3 errors (not just one) - isc_lost_db_connection, isc_net_read_err, isc_net_write_err
It calls MonitorHook.SendError when the connection is lost

This is my attempt to fix the code (I wrote this before I realized that *call* is not virtual ... but since it is not virual, there was no reason to test this)

function TIBDatabase.Call(ErrCode: ISC_STATUS;
  RaiseError: Boolean): ISC_STATUS;
begin
  result := ErrCode;
  FCanTimeout := False;

  // Handle when the Error is due to a Database disconnect.
  if CheckStatusVector([isc_lost_db_connection]) or
     CheckStatusVector([isc_net_read_err]) or
     CheckStatusVector([isc_net_write_err])

  then begin
    if    Assigned(OnLostConnection)
    then           OnLostConnection(Self);    

    if DoForceCloseOnLostConnection then ForceClose;
  end;

  if RaiseError and (ErrCode > 0) then
    IBDataBaseError;
end;

Possible alternate code to check for all 3 errors (I have not verified that this works)

  if CheckStatusVector([isc_lost_db_connection, isc_net_read_err, isc_net_write_err])

The speed of this code is not important because it should happen less than once per day.

The purpose of CheckStatusVector is not clear

The usage above appears to check the error flag
The source implies that it returns true if any of the error codes is set (I'm not really sure that I am reading it correctly)
The help says that this function "Sets or unsets status vector checking"

This is the related transaction code in IBX 5.04, it calls the default database error routine

function TIBTransaction.Call(ErrCode: ISC_STATUS;
  RaiseError: Boolean): ISC_STATUS;
var
  i: Integer;
begin
  result := ErrCode;
  for i := 0 to FDatabases.Count - 1 do if FDatabases[i] <> nil then
    Databases[i].FCanTimeout := False;
  FCanTimeout := False;
  {Handle when the Error is due to a Database disconnect.  Pass it on to
   FDatabase so it can handle this}
  if CheckStatusVector([isc_lost_db_connection]) then
    FDefaultDatabase.Call(ErrCode, RaiseError)
  else
    if RaiseError and (result > 0) then
      IBDataBaseError;
end;

***

As currently implemented, there are a number of scenarios where the database still shows connected and some other component does not. In these cases, simply trying to access the database will generate additional errors. My solution is to always disconnect the database (via ForceClose) and then to continue.

The biggest problem with this pertains to open transactions - the database still thinks the transactions are active and the program thinks they aren't. As a result, there is no way to simply stop them. Actually, they need to be rolled back and there is no mechanism to do that.

Rules

Generally, I hate hard and fast rules, but, if you want a reliable application that doesn't crash a lot, you will need these.

Obviously, this list is not complete ... it is just a start. Good luck.

Testing the Database Connection

Never use

if not IBDatabase1.Connected then ...

This just tests if the database was connected sometime in the past. it is equivalent to

if not (IBDatabase1.FHandle <> nil) then ...

which does NOT indicate if there has been a network problem. Instead, you should always use

if not IBDatabase1.TestConnected then ...

which actually sends a command to the server to see if the connection is ok and automatically calls ForceClose if there is an error.

Disconnecting the Database

Never use

IBDatabase1.Connected := false

This is a complete disaster - it causes all types of problems because it executes

InternalClose(False);

Instead, you should always use

IBDatabase1.ForceClose

because it executes

InternalClose(True);

Comments / Rant

With respect to network connection issues, IBX is pretty bad - basically, there is no architecture to handle real world client-server database issues.

It isn't just that the components don't work, it's that I've heard lots of praise for Delphi and Firebird / Interbase ... but then when you use them, they really don't work.

I asked a few related questions in the newsgroups, and the basic response was

This has been discussed before

Well ... then ... how about fixing it - or at least posting a document explaining what can and can not be done to address this problem. (At least I am sharing my notes.)

With design deficiencies like these, they should have been placed right up front. Specifically, the Delphi help files should have warned the user to NEVER use IBDatabase1.Connected to test or break connections. It should only be used to start a connection.

For all I know, all client-server databases have the same types of issues. If so, I'm sorry for being so critical of the Delphi and the IBX components.

If I've been too hard, I'm sorry ... and if you can convince me that I am off base, I will tone it down a bit.

I spent over a week of hard work to figure this stuff out ...

References

Enhanced Interbase Express 6.08 components - this claims to provide better lost connection handling.

I have made some of my code showing how to handle "Lost Connection" errors available.

Author: Robert Clemenzi - clemenzi@cpcug.org