The Rhapsody 'Database Message Extraction' filter is a bit pants but you have to use it anyway

We were in a situation where we needed generic patient information to be displayed in an application. How do we get this info and maybe make it so the process can be reused by other applications? Everything else in the hospital goes through Rhapsody, so can we do this in Rhapsody? Seems easy enough; send a message to Rhapsody and say 'hey, I want data for ward ABC', then Rhapsody runs a query against the database and sends back all the required information as an XML message. Man, that was so easy, this integration stuff is a piece of cake.

The Rhapsody Database Components Best Practice Guide says "If you want to retrieve data from a database table for every message it receives, use the Database Message Extraction filter."

I'm a good little developer. I read the documentation. I believe in the documentation. So, when we use the Database Message Extraction filter we are following best practice. Good for us!

Unfortunately the Database Message Extraction filter has some problems. It seems that when the Rhapsody route that holds the DB Message Extraction filter starts up it connects to the database at that point. It then holds that connection open. If that connection is disrupted for some reason (network issues, database goes offline then comes back online) the DB Message Extraction filter WILL NOT be able to recover that connection. It will remain in a broken state until the Rhapsody route is restarted. When this happens you will see errors like this:

We have a lot of outages. We have upgrades, we have server moves, we had a server room move. The connect gets broken between the Rhapsody server and the database server a lot. We have backups that can potentially make servers unavailable for very short periods and so for a while we were in a situation where every night the Rhapsody route was breaking and having to be restarted. The system engineers managed to solve the problem with the backups but we still have the problem every time the database becomes unavailable.

So, I don't have a good solution to this. If you want to follow the pattern of extracting and returning data for each message sent then you have no choice but to use the DB Message Extraction filter (according to Rhapsody support). I reckon you should avoid using it if at all possible. If not, you might be able to work around the problem by scheduling a route restart (if your version of Rhapsody will allow this - ours doesn't). If you have this problem I don't really have any help for you, I'm just letting you know I share your pain and advising everyone else not to use this filter.