How can I hold back the database connection when the network is unstable?

Question

We have an application using BDE connected to Oracle DB.
I use TQuery for SQL queries and connect to TDatabase, we are not professional programmers, and we do not know what is happening under the hood.

Our network is unstable, we have a problem with packet loss.

If a problem occurs, our application disconnects from the database server or does not complete the current request.
What is the best way to handle this?

Our network team is currently working on a major issue, and we changed the code to reconnect to the database when we have a failure. We are faced with the problem of the number of open sessions on our database server.

Is there any solution for this?
This is a common problem for us.

Answer 1

I suggest the following in a database component.

• connect to each sql and complete its completion.
• Use connection timeout and restart the request if there is a timeout
• If the database is down, collapse the data to the local database on the client and restart the transfer to the central database as soon as it reconnects. This way you are not losing any data.
• Use a timer to verify connectivity to a central database to buffer unused data.

This problem is a common occurrence for collecting data on stores, and the above suggestion is the only way I could effectively deal with this problem.

Answer 2

Sorry for the short answer ... fix your network. This is really the best option for you at the moment.

Answer 3

Moving to an n-tier solution can also help ... especially if the middle tier is also on the database server, so its connection is local. Most recent Delphi implementations use a custom HTTP server as a middle tier, the advantage is that a connection is required only for the current request. When the request is completed, the connection can be disconnected without any problems.

Answer 4

Try implementing a watchdog thread that:

• Sets the network (database server IP address) every X seconds
• If it is unavailable, disables the user interface and tries to reconnect

Please note that any transaction may not succeed at all, and save this information somewhere. Then, when connecting through the watchdog stream, try again to complete this transaction, if the transaction allows it.

Answer 5

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

You can check simply by opening the data set, reset the connection, reconnect and publish the data.

By the way, with DBExpress it is easy to upgrade the application to an n-level script.