How to deal with an invalid pooled connection?

Data Abstract
1

Hi,

I’m trying to make my server as resilient as possible. Part of this is dealing with the scenario where the database engine itself is inaccessible for some reason. This could be because the server is stopped or rebooted or connectivity is lost for some reason.

Obviously while the server cannot access the database, nothing is going to work and I’ll get errors, but what I want is for the server to continue normal operations when the database returns to service, and not have to restart the server.

The problem I’m having here is with connection pooling. If there’s an active connection in the pool and the database engine is restarted or the connection terminated, then the server thinks it has a valid connection in the pool but there is no longer an underlying connection to the database server.

Any attempt to use this pooled connection thus results in an exception. If no attempt is made to use the pooled connection for the duration of the configured timeout, then the pooled connection expires and subsequent access creates a new connection and everything is then fine.

What I’m wondering is how I can deal with this? What I want is to somehow trap this condition and then invalidate the entire connection pool, forcing it to start again. I can’t even simply wait until the pooled connection expires as every time the server attempts to use it, the timeout is reset and starts again.

2

It seems that, when this happens, the FireDAC driver first throws an EMSSQLNativeException exception, then the RO framework throws an EROUnregisteredServerException.

I’m try to trap one of these but without much success. Firstly, I can see the exceptions when running the server in the Delphi IDE, but when run outside the IDE, I don’t see any exception passed back to the client.

I’ve tried adding the code detailed in this item: How catch & log exceptions (Delphi)

But no ExceptObject is ever created.

UPDATE:

Sorry my mistake - had some old code which was eating the exception. The OnDeactivate handler now works as per the other article, although there appears to be no easy way to determine if the exception was due to a dead connection in the pool. I’ve had to resort to checking if the exception message contains the text SQL_ERROR as this is what the FireDAC driver seems to produce in this situation. If it does then I call ClearPool on the connection manager and then subsequent calls re-create the connection and all seems to work.