I have tried (nearly) everything to isolate the problem, but I am lost.
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:
- 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
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
Here are the test results:
- 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!
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?
Anybody any bright ideas?
