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

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 2

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