使用VBS和ADODB调用MySQL带参存储过程，OUT参数返回异常随机值

环境信息 #

• 数据库：MySQL v5.7.44
• 开发环境：基于VBS的Classic ASP，使用ADODB组件

问题现象 #

在phpMyAdmin中执行存储过程usp_SimpleTest_IN_OUT时，输出完全符合预期：输入111、222、333，返回结果如下：

但在ASP页面通过ADODB参数化调用时，出现异常：

• 数值型(INT)OUT参数返回0到99999999之间的随机值
• VARCHAR类型OUT参数始终返回空字符串
• 错误检测返回0，无错误抛出

存储过程代码 #

DELIMITER $$
CREATE PROCEDURE `usp_SimpleTest_IN_OUT`(
      IN `pi_1_int` INT
    , IN `pi_2_int` INT
    , IN `pi_3_int` INT
    , OUT `po_1_int` INT
    , OUT `po_2_int` INT
    , OUT `po_3_string` VARCHAR(100))
    NO SQL
    DETERMINISTIC

BEGIN
    SET po_1_int = 12;
    SELECT pi_2_int INTO po_2_int;
    SET po_3_string = CONCAT("Concat: ", pi_1_int, ", ", pi_2_int, ", " , pi_3_int);
END$$

DELIMITER ;

ASP页面VBS代码 #

Dim o_Cx                ' Object
    Set o_Cx = CreateObject("ADODB.Connection")
Dim o_Cmd               ' Object
    Set o_Cmd = CreateObject("ADODB.Command")
Dim o_Prm               ' Object
    o_Prm = CreateObject("ADODB.Parameter")

o_Cx.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};" _
            & "PORT=3306;" _
            & "Server=***.***.***.***;" _
            & "Database=*******;" _
            & "User=********;" _
            & "Password=********;" _
            & "Option=3;"

o_Cx.CommandTimeout = 1000
o_Cmd.CommandType = 4         ' adCmdStoredProc
o_Cmd.ActiveConnection = o_Cx
'o_Cmd.Parameters.Refresh     ' <<<< Tried here but made no difference

dim o_PARAM
' INPUTS:
' Create anonymous input parameter placeholders ([name], dataType, direction, width [,value]):
set o_PARAM = o_Cmd.CreateParameter(, 3, 1)  ' 3 = adInteger, 1 = adParamInput
    o_Cmd.Parameters.Append o_PARAM
set o_PARAM = o_Cmd.CreateParameter(, 3, 1)
    o_Cmd.Parameters.Append o_PARAM
set o_PARAM = o_Cmd.CreateParameter(, 3, 1)
    o_Cmd.Parameters.Append o_PARAM
    
' OUTPUTS:
' Create anonymous output parameter placeholders:
set o_PARAM = o_Cmd.CreateParameter(, 3, 2)          ' 2 = adParamOutput
    o_Cmd.Parameters.Append o_PARAM
set o_PARAM = o_Cmd.CreateParameter(, 3, 2)
    o_Cmd.Parameters.Append o_PARAM
set o_PARAM = o_Cmd.CreateParameter(, 200, 2, 100)   ' 2 = adParamOutput, 200 = adVarChar
    o_Cmd.Parameters.Append o_PARAM

' POPULATE INPUTS (These 3 values are hard coded for this example):
o_Cmd.Parameters.Item(0).Value =    18              ' (0) = the first (IN) parameter in this procedure's params list (ie: 'pi_1_int')
o_Cmd.Parameters.Item(1).Value =    503326          ' (1) = the second... (ie: 'pi_2_int') etc
o_Cmd.Parameters.Item(2).Value =    12              ' (2) = the last input... (ie: 'pi_3_int') etc
   
o_Cmd.CommandText = "usp_SimpleTest_IN_OUT"
'o_Cmd.Parameters.Refresh          ' <<<< This was tried here too but made no difference
o_Cmd.Execute , , 128             '  128 = adExecuteNoRecords

' Final testing; 'for/next' loop to display output into to ASP webpage:
dim s
dim i_prm
for i_prm = 0 to o_Cmd.Parameters.Count - 1
    s = o_Cmd.Parameters(i_prm).value
    response.write("<br />#" & i_prm & " = '" & s & "' - varType() = '" & varType(s) & "'")
next ' i_prm
response.write("<br />Error: '" & err.number & "'")

set o_Cx = nothing
set o_cmd = nothing
set o_Prm = nothing

' RESULTS as displayed in ASP webpage:
'   #0 = '18' - varType() = '3'                    // Expected value as hard coded input
'   #1 = '503326' - varType() = '3'                // ditto 
'   #2 = '12' - varType() = '3'                    // ditto
'   #3 = '94014952' - varType() = '3'              // Expected '18', but unexpected random value returned; changes on each page refresh
'   #4 = '51' - varType() = '3'                    // Expected '503326' but unexpected... ditto
'   #5 = '' - varType() = '8'                      // Expected 'Concat: 18, 503326, 12' but consistent zero length string returned
' Error: '0'            

' Footnote:
' Also tried up the page in setting and getting the value of the first IN and OUT param, but same random result as above:
'   o_Cmd.Parameters.Append o_Cmd.CreateParameter("@po_1_int", 3, 2)
'   s = o_Cmd.Parameters("@po_1_int").value

补充尝试 #

• 调用o_Cmd.Parameters.Refresh后填充IN参数，执行后获取OUT参数时触发500错误，推测是代码问题或主机不支持该方法
• 移除存储过程和ADODB参数中的宽度参数，问题仍存在，部分随机值重复出现

内容的提问来源于stack exchange，提问作者AndyAccess