Thanks a bunch for the *very* thorough walk-through of all the things
I need to consider. You are probably right that I should take a few
steps back and throw away the threads and focus on understanding the
objects and handling the errors. But you know, my boss is waiting for
results and I have to churn the code :-)
The service will most probably be running on the same computer as the
database server but I like to cover most possible bases before I
release the software, and a little extra error-handling never hurt
anyone :)
FWIW, my new employer uses FB in quite a few legacy projects so it
definitely won't be my last brush with it.
I'll have to go through your post more thorougly tomorrow but thanks
ever so much so far.

regards from Norway,
Håvard

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> 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
www.ibobjects.com/TechInfo.html 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
> begin
> if not connected then
> try
> connect;
> except
> [ trap the exception, interpret it and handle it according to
the fault ]
> end
> 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
> else
> .... 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.
>
> Helen
>