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.
我们有一个使用连接到Oracle DB的BDE的应用程序。我使用TQuery进行SQL查询,它连接到TDatabase,我们不是专业的程序员,我们不知道幕后发生了什么。
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.
这有什么解决方案吗?这似乎是我们的共同问题。
5 个解决方案
#1
I suggest the following on Database Component.
我在数据库组件上建议如下。
- do connect on every sql and close on completion.
- Use connection timeout & restart query if there is a timeout
- 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.
- Use a timer to check for central Database connectivity to do spooling of untransmitted data.
在每个sql上连接并在完成时关闭。
如果超时,请使用连接超时和重新启动查询
如果数据库已断开连接,请将数据假脱机到客户端上的本地数据库,并在再次连接后重新启动到*数据库的传输。这样您就不会丢失任何数据。
使用计时器检查*数据库连接以执行未传输数据的假脱机。
This problem is common for shopfloor data collection and the suggestion above is the only way I could effectively handle the problem.
这个问题在车间数据收集中很常见,上面的建议是我有效处理问题的唯一方法。
#2
Sorry for the short answer... fix your network. That is really the best option for you at this point.
对不起,简短的回答...修复你的网络。在这一点上,这真的是你最好的选择。
#3
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.
转向n层解决方案也可能有所帮助......尤其是如果中间层也在数据库服务器上,那么它的连接是本地的。 Delphi最近的大多数实现使用自定义HTTP服务器作为中间层,其优点是只需要对当前正在执行的请求进行连接。请求完成后,连接可以被切断而没有任何问题。
#4
Try to implement a "watchdog" thread that :
尝试实现一个“看门狗”线程:
- Pings the network (IP address of the database server) every X seconds
- If unavailable then disables the UI and try to connect again
每隔X秒ping网络(数据库服务器的IP地址)
如果不可用,则禁用UI并尝试再次连接
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.
考虑到任何事务可能根本不会成功,并将该信息保存在某处。然后,在通过“看门狗”线程连接时,如果事务的性质允许,则尝试再次预先形成该事务。
#5
Use DBExpress technology instead of ADO of anything else. The structure dbquery + provider + Clientdataset is "disconnected" by design.
使用DBExpress技术而不是其他任何ADO。结构dbquery + provider + Clientdataset按设计“断开连接”。
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.
顺便说一下,使用DBExpress可以很容易地将应用程序升级到n层方案。
#1
I suggest the following on Database Component.
我在数据库组件上建议如下。
- do connect on every sql and close on completion.
- Use connection timeout & restart query if there is a timeout
- 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.
- Use a timer to check for central Database connectivity to do spooling of untransmitted data.
在每个sql上连接并在完成时关闭。
如果超时,请使用连接超时和重新启动查询
如果数据库已断开连接,请将数据假脱机到客户端上的本地数据库,并在再次连接后重新启动到*数据库的传输。这样您就不会丢失任何数据。
使用计时器检查*数据库连接以执行未传输数据的假脱机。
This problem is common for shopfloor data collection and the suggestion above is the only way I could effectively handle the problem.
这个问题在车间数据收集中很常见,上面的建议是我有效处理问题的唯一方法。
#2
Sorry for the short answer... fix your network. That is really the best option for you at this point.
对不起,简短的回答...修复你的网络。在这一点上,这真的是你最好的选择。
#3
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.
转向n层解决方案也可能有所帮助......尤其是如果中间层也在数据库服务器上,那么它的连接是本地的。 Delphi最近的大多数实现使用自定义HTTP服务器作为中间层,其优点是只需要对当前正在执行的请求进行连接。请求完成后,连接可以被切断而没有任何问题。
#4
Try to implement a "watchdog" thread that :
尝试实现一个“看门狗”线程:
- Pings the network (IP address of the database server) every X seconds
- If unavailable then disables the UI and try to connect again
每隔X秒ping网络(数据库服务器的IP地址)
如果不可用,则禁用UI并尝试再次连接
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.
考虑到任何事务可能根本不会成功,并将该信息保存在某处。然后,在通过“看门狗”线程连接时,如果事务的性质允许,则尝试再次预先形成该事务。
#5
Use DBExpress technology instead of ADO of anything else. The structure dbquery + provider + Clientdataset is "disconnected" by design.
使用DBExpress技术而不是其他任何ADO。结构dbquery + provider + Clientdataset按设计“断开连接”。
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.
顺便说一下,使用DBExpress可以很容易地将应用程序升级到n层方案。