通过MS Access（VBA）中的ADODB实现的MySQL存储过程的输出参数在一台计算机上正确，而在另一台计算机上随机 [英] Output parameter of MySQL stored procedures via ADODB in MS Access (VBA) correct on one computer and random on another
问题描述
（几乎）我已尽一切努力找出问题所在，但我迷路了。
I have tried (nearly) everything to isolate the problem, but I am lost.
我有一个使用ADODB连接到本地MySQL数据库的MS Access应用程序。我将其复制到新计算机上，但是现在存储过程的输出参数每次都包含一个随机值（如果通过ADODB完成）。在MySQL WorkBench中执行时，输出参数正确。
I have an MS Access application that uses ADODB to interface to a local MySQL database. I copied it to a new computer, but now the output parameters of the stored procedures contain a random value each time (if done via ADODB). When executed in MySQL WorkBench, the output parameters are correct.
以下是两台计算机的规格：
Here are the specs of the 2 computers:
- 旧：Windows 7 Pro，Office 2010 ProPlus，MySQL ODBC 5.3.4，MySQL服务器5.6.22（均为64位；还安装了32位ODBC）；
- 新：Windows 10 Pro，Office 2016 ProPlus，MySQL ODBC 5.3.6，MySQL服务器5.7.16（除MS Office以外，其余均为64位；还安装了32位ODBC）。 / li>
- old: Windows 7 Pro, Office 2010 ProPlus, MySQL ODBC 5.3.4, MySQL server 5.6.22 (all are 64-bit; 32-bit ODBC is also installed);
- new: Windows 10 Pro, Office 2016 ProPlus, MySQL ODBC 5.3.6, MySQL server 5.7.16 (all are 64-bit, except MS Office; 32-bit ODBC is also installed).
为找出问题，我使用了以下简单的存储过程：
To isolate the problem, I used the following simple stored procedure:
CREATE PROCEDURE `testit`(
OUT iTest INT(11))
BEGIN
SET iTest = 123;
END
和仅包含以下VBA代码的测试MS Access数据库（和参考Microsoft ActiveX Data Objects 6.1库）：
And a test MS Access database containing only the following VBA code (and a reference to the Microsoft ActiveX Data Objects 6.1 library):
Public Function dbTestIt() As Long
Dim dbConn As ADODB.Connection
Dim dbCmd As ADODB.Command
'Open new connection
Set dbConn = New ADODB.Connection
dbConn.ConnectionString = "Driver={MySQL ODBC 5.3 Ansi Driver};option=3;database=xxx;user=root;password=yyy;"
dbConn.Open
'Execute new command
Set dbCmd = New ADODB.Command
With dbCmd
Set .ActiveConnection = dbConn
.CommandTimeout = 0
.CommandType = adCmdStoredProc
.CommandText = "testit"
.Parameters.Append dbCmd.CreateParameter("iTest", adInteger, adParamOutput)
.Execute
dbTestIt = dbCmd.Parameters.Item(0).Value
End With
'Close Connection
dbConn.Close
End Function
以下是测试结果：
- 在旧计算机上，dbTestIt（）始终返回123；
- 在新计算机上，dbTestIt（）返回随机值（例如51、1936020585、1）；
- 如果我从新计算机连接到旧上的MySQL服务器， >计算机（在连接字符串中使用server = 192.168.1.x），它总是重新启动还123；
- 这告诉我问题是（仅）在新计算机上的 MySQL服务器中？ ）;
- 但是，如果我随后从旧计算机连接到新计算机上的MySQL服务器，它也会始终返回123！
- On the old computer, dbTestIt() always returns 123;
- On the new computer, dbTestIt() returns random values (e.g. 51, 1936020585, 1);
- And if I connect from the new computer to the MySQL server on the old computer (using server=192.168.1.x in the connection string), it always returns 123 as well;
- This tells me the problem is (only) in the MySQL server on the new computer (right?);
- However, if I then connect from the old computer to the MySQL server on the new computer, it also always returns 123!
所以问题似乎出在新组件的组合上strong>电脑，但是，为什么？以及如何测试呢？
So the problem seems to be in the combination of the components on the new computer, but which and why? And how to test it?
有人有什么好主意吗？
推荐答案
进一步查找原因问题的解决方案，我将新计算机上的MySQL ODBC驱动程序5.3.6降级到版本5.3.4（在旧计算机上），现在可以使用！为了确认这确实是原因，我再次升级到版本5.3.6，但再次失败。
To further locate the cause of the problem, I downgraded the MySQL ODBC driver 5.3.6 on the new computer to version 5.3.4 (which is on the old computer) and now it works! To confirm that this is indeed the cause, I upgraded to version 5.3.6 again and it failed again.
看起来MySQL ODBC驱动程序5.3.6（8个月前发布！）在处理（本地）MySQL Server 5.7.16的输出参数时遇到问题结合ADODB和Windows 10。
So it looks like MySQL ODBC driver 5.3.6 (released 8 months ago!) has problems handling output parameters from a (local) MySQL Server 5.7.16, maybe only in combination with ADODB and Windows 10.
因此，目前，我将使用5.3.4版本并将错误报告给MySQL。
So for the moment I will use the 5.3.4 version and report a bug to MySQL.
这篇关于通过MS Access（VBA）中的ADODB实现的MySQL存储过程的输出参数在一台计算机上正确，而在另一台计算机上随机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
