英文:
Intermittent Connection fails
问题
我有一个子程序，用于测试是否存在与MySQL数据库的连接。如果没有，它会打开连接。但它随机失败，不如预期。
我的函数：
Public oConn As ADODB.Connection
Function InitConn()
' 这个函数用于测试数据库的活动连接，如果尚未打开，它将打开连接
' 它返回布尔值，以便调用它的程序可以决定如何处理。
On Error Resume Next
If oConn Is Nothing Then
Dim str As String
str = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=xxx.xxx.xxx.xxx;DATABASE=mysqldatabase;PORT=3306;UID=myid;PWD=mypassword;"
Set oConn = New ADODB.Connection
oConn.Open str
Application.CalculateUntilAsyncQueriesDone
End If
If Err.Number <> 0 Then
InitConn = False
Else
InitConn = True
End If
On Error GoTo 0
End Function
调用该函数的常见程序：
If Not InitConn Then
msgbox "一些适当的消息"
Exit Sub
End If
SQLstr = "一些SQL语句"
Set rs = CreateObject("ADODB.Recordset")
rs.Open SQLstr, oConn, adOpenStatic
该函数总是返回True，但调用子例程的SQL调用失败，错误消息显示ODBC驱动程序不支持所请求的属性。立即再次尝试通常成功。
考虑到这可能是与数据库的响应时间有关的问题，我添加了Application.CalculateUntilAsyncQueriesDone。
没有效果。
我确信我必须在这里犯了一个初学者的错误。非常感谢任何帮助。
Bob
英文:
I have a subroutine that tests to see if a connection to a mysql database is active. If it is not, it opens the connection. But it randomly fails not as expected.
My function:
Public oConn As ADODB.Connection
Function InitConn()
' this tests for an active connection to the database and opens it if not already open
' it returns boolean so the calling routine can decide what to do.
On Error Resume Next
If oConn Is Nothing Then
Dim str As String
str = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=xxx.xxx.xxx.xxx;DATABASE=mysqldatabase;PORT=3306;UID=myid;PWD=mypassword;"
Set oConn = New ADODB.Connection
oConn.Open str
Application.CalculateUntilAsyncQueriesDone
End If
If Err.Number <> 0 Then
InitConn = False
Else
InitConn = True
End If
On Error GoTo 0
End Function
The calling routine is most often:
If Not InitConn Then
msgbox "some appropriate message"
Exit Sub '
endif
SQLstr = "some SQL statement"
Set rs = CreateObject("ADODB.Recordset")
rs.Open SQLstr, oConn, adOpenStatic
The function always returns True, but the calling subroutine's sql call fails with the error message that the odbc driver does not support the requested properties. Immediately trying again succeeds.
Thinking maybe it was a timing issue with a response from the database, I added
Application.CalculateUntilAsyncQueriesDone
No effect.
I'm sure I must have a rookie mistake in here somewhere. Any help would be greatly appreciated.
Bob
答案1
得分: 0
这个项目从我的局域网上的32位Mariadb服务器迁移到了共享环境上的64位MySQL数据库。所以，我无法启用日志记录。
但我检查了我的驱动程序。它说是64位的ODBC驱动程序，但为了确保，我下载并卸载了32位版本，然后重新安装了64位版本。现在一切都正常了！我不知道有多少帖子警告要确保你有正确的驱动程序。我以为我有，但在这种情况下，确保旧的驱动程序已被移除是明智之举。
英文:
This project was moved from a 32 bit Mariadb server on my lan to a 64 bit MySQL database on a shared environment. So, I cannot turn on logs.
But I checked my driver. It said it was a 64 bit ODBC driver, but to be sure, I downloaded and uninstalled the 32 bit version, then reinstalled the 64 bit. Everything now works! I don't know how many posts warn about making sure you have the correct driver. I thought I did, but in this case, it paid to be sure the old drivers had been removed.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论