How can I keep my DB connection from failing when the network is unstable?

Question

We have a application that uses BDE connected to an Oracle DB.
I use TQuery for the SQL queries, and it connects to TDatabase, we are not professional programmers, and we don't know what happens under the hood.

Our network is unstable, we have an issue with packet loss.

When the problem occurs, our application disconnects from the DB server, or fails to finish the current query.
What is the best way to handle this?

Our networking team is currently working to fix the root issue, and we have changed the code to reconnect to the database when we have a failure. We are running into an issue with the number of open sessions on our database server.

Is there any solution for this?
It appears to be a common issue for us.

Answer 1

Sorry for the short answer... fix your network. That is really the best option for you at this point.

Answer 2

I suggest the following on Database Component.

1. do connect on every sql and close on completion.
2. Use connection timeout & restart query if there is a timeout
3. If database is disconnected, spool the data to a local database on the client and restart transmission to the central database once there is connectivity again. This way you do not loose any data.
4. Use a timer to check for central Database connectivity to do spooling of untransmitted data.

This problem is common for shopfloor data collection and the suggestion above is the only way I could effectively handle the problem.

Answer 3

Try to implement a "watchdog" thread that :

1. Pings the network (IP address of the database server) every X seconds
2. If unavailable then disables the UI and try to connect again

Take into account that any transaction might not succeed at all, and save that information somewhere. Then, upon connecting via "watchdog" thread, try to preform that transaction again, if the nature of transaction allows so.

Answer 4

Going to an n-tier solution might also help... especially if the middle tier is also on the database server so its connection is local. Most of the recent implementations for Delphi use a custom HTTP server as the middle tier, the advantage of such is that the connection is only required for the current request being performed..once the request is complete, the connection can be severed without any problems.

Answer 5

Use DBExpress technology instead of ADO of anything else. The structure dbquery + provider + Clientdataset is "disconnected" by design.

You can test simply opening a dataset, drop the connection, reconnect and post the data.

By the way, with DBExpress it's easy to upgrade an application to a n-tier scenario.