Subject Re: [IBO] Reconnecting in a windows service
Author Helen Borrie
At 07:08 AM 10/12/2007, HÃ¥vard wrote:
>I've got a Windows service which connects to a firebird
>database. To do this I'm currently using a TIB_Connection. I've got a
>guard clause procedure which does the following:
> if conn.Connected then
> Result := true
> else
> begin
> try
> conn.Connect;
>below this I've tried various exception handling but the Connect call
>always throws an exception which "lands" way outside this procedure.
>My service needs to have a valid connection before it can do any
>processing and so what I need to do is:
>1. Check for connection
>2. If no valid connection, sleep and try to reconnect.
>3. If valid connection continue and do the work.
>I have to mention that my service uses a couple of
>threads but my thread syncronization seems to work nicely when the
>connection is up and valid.

[ ... ]
> Are there other objects I should use instead of the TIB_Connection?

I'll comment on this first, as it is extremely important. A "connection" is a logical object, representing a live connection between one thread of your application and one database. Within this connection object are transactions and statements. Encompassing all of these parts is a TIB_Session object.

The default behaviour of IBO is to create an internal IB_Session as soon as a data access object is created. That IB_Session then becomes the default IB_Session object for all data access objects subsequently created. If you do things in threads, you must explicitly create (and afterwards destroy) a complete "package" consisting of a TIB_Session, a TIB_Connection, transactions and statements for the thread. The explicit IB_Session object must be the first object in the creation order and the last destroyed. References to an IB_Session and its objects should NEVER cross session boundaries.

It looks possible that your experience described above has to do with these crossed references, causing an AV and perhaps crashing your service, i.e., a cause not necessarily related to a network fault. I strongly recommend that you put aside your experiments with threaded connections until you get your head around the mechanics of a database connection and trapping errors in Delphi.

>Since I know that the server that the
>service will run on have dodgy connection lines I should ideally be
>able to handle a broken connection in the middle of the processing
>also, but this is secondary.

It seems an *odd thing* to be running a service on one machine to connect to and operate on a database on another....but....

Even if the connection has died, testing Connected subsequent to a successful Connect call will continue to return True unless something explicitly calls Disconnect. Actually, on a dirty network, it is a primary concern, not secondary at all. Even if the first attempt to connect is successful, the conditions make it uncertain whether the connection will stay "alive" long enough even to complete an operation.

Your application will "discover" the lost connection only by attempting a data access request - which might be simply a call from a scrolling control for the ib_query to fetch more records into the dataset buffer. The IB_Query (or the IB_Connection object itself) will return an exception if it cannot satisfy the request. Here is where your application must intercept the exception and work out what has happened.

The TIB_Connection has some methods available that you can call, to enable your app to try to re-establish the connection. Consult the Help file for VerifyConnection and ForceDisconnect. Your application also has to be prepared to invalidate everything pertaining to the buffers, since you don't have any way of knowing at what point the connection was broken.

> What is the best way to do this? I saw the OnError event mentioned on
> this forum but since I'm fairly new to Delphi I don't know how to use this.

Exception handling is absolutely essential, even if you are not working in a dirty network environment. There are lots of articles around the Web about Delphi's error-handling facilities so, if it wasn't an issue for you before, it certainly is now.

The Delphi help covers exception handling to some extent but, like Borland's help files in general, it assumes you know what you're looking for. The printed manual is better: if it is not on your CD, you can usually download it from the Codegear site.

Once you understand how it works, you might get some more enlightenment from the Tech Info sheets at and more from the various sample applications that are under your IBO root. Study the IBO helpfile for the custom exception handling layer (EIB_Error class) that IBO provides, both for database exceptions (EIB_IscError subclass) and for various internal errors in the IBO components (including EIB_ConnectionError).

The OnError event can be used as a "sieve" catching errors and dispatching them for handling. The most elegant way to use it is to have your own unit containing handlers for the "most likely" errors your application will encounter and have your OnError event sift out the exceptions that can be percolated upwards from those that it can dispatch to an appropriate handler. It's up to you to decide which errors you want to handle in which way. Firebird and IB have ~700 possible exception conditions - you can study all of them with their codes in a PDF paper downloadable via the Documentation Index at the Firebird website. Obviously you won't want a handler for every one of them! although the EIB_IscError class would enable you to do it if you really wanted to! :-)

Now, getting back to testing a connection on this flaky network. Well, the first rule is the one of diminishing returns: the more flaky the network, the less useful it will be for 2-tier client/server. So, if you are designing for a network you know is unreliable, forget 2-tier. Go for an n-tier architecture, an intranet or internet system for example. (2-tier using SSH is a "middle way" that can mitigate the effects of slowness across a WAN - another story...)

As with all network database systems, plan to pull the minimum amount of data from the server to the client and to keep it "live" for the least possible time.

with conn do
if not connected then
[ trap the exception, interpret it and handle it according to the fault ]
else // conn thinks it is connected
if not VerifyConnection then // is the connection alive?
if ConnectionWasLost then // VerifyConnection has flagged it as lost
Connected := False
.... etc.

If the database server is running, but network problems randomly interfere with the ability to make the initial connection, there's no way to get fine-grained knowledge of what is wrong, i.e., whether it's a misbehaving router, badly configured DHCP, radio interference, an over-muscled firewall, or whatever, it's enough to know that the connection couldn't be made. It might be worth writing your connection procedure so that it does a limited number of retries before giving up and logging a connection failure. Keep in mind that VerifyConnection, like Connect and Disconnect, is a network operation. If you do gazillions of them, you'll have a lot of wire traffic.

If the database server isn't running, you'll get a different message and your only recourse is to give up and make a later attempt, since a connection attempt can't start/restart a crashed server and you're dependent on factors beyond your control.

If the connection scenario is a hopeless jumble of many problems, including connections being lost or server crashes, the First Aid kit will need to be more complex. Your connection procedure should probably carry arguments that can cater for both non-connection and broken connection scenarios.

Sorry not to be of explicit help for your case...there's too much "unknown" here, not least of which appears to be your Delphi learning curve. Get in amongst the sample apps and tweak them in places where your knowledge of the environment you're working in tells you that you have to do more than the usual amount of bending and stretching.