计算机进入睡眠状态后，vb6 应用程序与 SQL Server 数据库的连接丢失

发布于 2024-12-28 15:59:32 字数 974 浏览 1 评论 0原文

我有一个旧的 Visual Basic 6 应用程序，当计算机从睡眠状态返回时，一些用户向我报告错误。并非每台客户端计算机都会出现此问题（我会说某些 Windows 7）。如果 vb6 应用程序仍然打开，那么如果他们尝试使用此应用程序，则会崩溃并显示以下错误消息。

在此处输入图像描述

我调试后发现了问题：我有一个全局变量来保持与数据库的连接。该变量仅在应用程序开始时初始化一次。当计算机进入睡眠状态并在一段时间后返回时，该变量的状态仍然是“OPEN”，但实际上连接已丢失！如果我“关闭”然后“打开”这个变量连接，我就可以查询数据库。

我想知道我失去了数据库连接这是否正常？！

这是一些代码：

 ' This is my global variable
 Global cn As New ADODB.Connection

 ' Set connection properties for sql server.
 cn.ConnectionTimeout = 25
 cn.Provider = "sqloledb"
 cn.Properties("Data Source").Value = ".\SQL2008"
 cn.Properties("Initial Catalog").Value = DB_INITIAL_CATALOG
 cn.Properties("User ID").Value = DB_USERNAME
 cn.Properties("Password").Value = DB_PASSWORD
 cn.Open

 ' This is a typical query on my database
 Set rs = New ADODB.Recordset
 strSql = "SELECT * FROM tblUsers"
 rs.Open strSql, cn, adOpenKeyset

知道吗？

谢谢。

原文

I have an old visual basic 6 application when some users reported me errors when the computer going back from sleep. This problem did not occurs on every client computer, (I would say some Windows 7). If the vb6 application was still open then if they try to use this application it crashes with the following error message.

enter image description here

I debugged and I found the problem: I have a global variable that keep the connection to the database. This variable is initialized only once in the beginning of the application. When the computer going to sleep and go back some times later, the status of this variable is still "OPEN" but in fact the connection is lost! If I "CLOSE" and then "OPEN" this variable connection I am able to query the database.

I wonder if this is normal that I lost my database connection?!

Here is some code:

 ' This is my global variable
 Global cn As New ADODB.Connection

 ' Set connection properties for sql server.
 cn.ConnectionTimeout = 25
 cn.Provider = "sqloledb"
 cn.Properties("Data Source").Value = ".\SQL2008"
 cn.Properties("Initial Catalog").Value = DB_INITIAL_CATALOG
 cn.Properties("User ID").Value = DB_USERNAME
 cn.Properties("Password").Value = DB_PASSWORD
 cn.Open

 ' This is a typical query on my database
 Set rs = New ADODB.Recordset
 strSql = "SELECT * FROM tblUsers"
 rs.Open strSql, cn, adOpenKeyset

Any idea?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

莫多说 2025-01-04 15:59:32

是的，我以前见过这种事。连接字符串上的设置可能有助于减少连接，但超时和/或网络丢失可能会破坏与数据库服务器的底层（通常是 TCP）连接。然后，您会在数据库的下一个 I/O 中看到错误清单本身。

我建议包装对共享连接的访问，以便您可以透明地捕获该特定错误并重试。在类或模块中保持连接私有，并具有以下方法：

'Open is called to set the args to connect, these should be saved for reconnect
Public Sub Open(connect params here)

    'save arsg to prive members to reconnect

    'connect to db

End Sub

Public Function OpenKeyset(sql) As RecordSet

   Set rs = New ADODB.Recordset  

   On Error Resume Next
   rs.Open strSql, privateConn, adOpenKeyset

   'if the error is the disconnect
   If Error.Number = xxx Then 'or inspect the error message or error collection

      'turn of error trap
      Err.Clear
      On Error Goto 0

      'reopen db conn

      'then retry
      rs.Open strSql, privateConn, adOpenKeyset

   End If

   OpenKeyset = rs

End Function

您甚至可以定期执行无操作数据库操作，例如查询目录或其他保持连接活动并主动重新连接的操作。您还可以观察计算机休眠时发生的时间大幅跳跃。

Yes, I've seen this kind of thing before. There may be settings on the connect string that can help reduce it, but time outs and/or network drops can break the underlying (often TCP) connection to the DB server. You then see the error manifest itself in the next I/O to the database.

I recommend wrapping access to the shared connection so you can transparently catch that specific error and retry. Keep the connection private in a class or module and have methods such as:

'Open is called to set the args to connect, these should be saved for reconnect
Public Sub Open(connect params here)

    'save arsg to prive members to reconnect

    'connect to db

End Sub

Public Function OpenKeyset(sql) As RecordSet

   Set rs = New ADODB.Recordset  

   On Error Resume Next
   rs.Open strSql, privateConn, adOpenKeyset

   'if the error is the disconnect
   If Error.Number = xxx Then 'or inspect the error message or error collection

      'turn of error trap
      Err.Clear
      On Error Goto 0

      'reopen db conn

      'then retry
      rs.Open strSql, privateConn, adOpenKeyset

   End If

   OpenKeyset = rs

End Function

You could even periodically perform a no-op db operation like quering the catalog or whatever to keep the connection live and proactivle reconnect. You couild also watch for large jumps in time that happen if a computer hibernates.

回复收藏 0 原文

魔 2025-01-04 15:59:32

您应该在 vb6 代码中捕获睡眠和唤醒事件，任何长时间运行的查询都应该在睡眠时关闭，以便可以关闭 dB 连接。醒来时，你会做相反的事情。你需要听
WM_POWERBROADCAST 消息。https://msdn .microsoft.com/en-us/library/windows/desktop/aa373247(v=vs.85).aspx

祝你好运
杰佩

回复收藏 0 原文

~没有更多了~