I have an Excel worksheet that needs to make a number of calls out to a MySQL server. Here's the VBA code, simplified to illustrate this issue:
Public Function Connect() As ADODB.Connection
Dim cn As New ADODB.Connection
With cn
.ConnectionString = "<Server>"
.Properties("Initial Catalog").Value = "<DB>"
.Properties("User ID").Value = "<User>"
.Properties("Password").Value = "<Password>"
.CommandTimeout = 1500
End With
Set Connect = cn
End Function
Public Sub TestSProc()
Dim cxn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim p1 As ADODB.Parameter
Dim p2 As ADODB.Parameter
Set cxn = Connect()
cxn.Open
Set cmd.ActiveConnection = cxn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "OutParamTest"
Set p1 = cmd.CreateParameter(, adBoolean, adParamOutput)
Set p2 = cmd.CreateParameter(, adVarChar, adParamOutput, 255)
cmd.Parameters.Append p1
cmd.Parameters.Append p2
cmd.Execute
MsgBox "Result: " & p1.Value & " / " & p2.Value
End Sub
This executes the following stored procedure:
CREATE PROCEDURE `OutParamTest`(
OUT `isAvailable` BIT,
OUT `remarks` VARCHAR(255)
)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET isAvailable = false;
SET remarks = 'The quick brown fox jumps over the lazy dog.';
END
On my machine, and for almost all users, this returns:
Result: False / The quick brown fox jumps over the lazy dog.
But for one user, it returns:
Result: True /
The only difference I can find between my machine, and the user's is the MySQL ODBC 5.3 ANSI and Unicode Driver versions. I have version 5.03.02.00, and originally this user had an older version. I asked him to upgrade, and he now has version 5.03.09.00 (newer than mine), but the problem still exists.
Is there something I'm missing? Is there some kind of machine-level configuration variable that could cause OUT parameters to stop working? Or do OUT parameters only work in some select driver versions?
UPDATE: I modified the SP as follows:
CREATE PROCEDURE `OutParamTest`(
OUT `num` INT,
OUT `remarks` VARCHAR(255)
)
BEGIN
SET num = 12345;
SET remarks = 'The quick brown fox jumps over the lazy dog.';
END
And made the corresponding changes in VBA. Now on my machine it returns:
Result: 12345 / The quick brown fox jumps over the lazy dog.
And on this user's machine it returns:
Result: 232718680 /
