mysql 存储过程 out 调用_使用带有OUT参数的VB6调用MySQL存储过程

I have written this procedure in MySQL (Server 5.5)

DELIMITER $$

DROP PROCEDURE IF EXISTS `InsertList` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertList`(IN fName VARCHAR(20), IN fType VARCHAR(3), IN fFood varchar(20), Out fResult int)

BEGIN

insert into tblguest (firstname, confirm, food) values (fName, fType, fFood);

select count(id) from tblguest into fResult;

END $$

DELIMITER ;

When I call this procedure from MySQL Query Browser it returns as expected

Call InsertList ('V1', 'No', 'F1', @result);

Select @result;

--> It successfully returns the count of the ids in the table

I wrote the following code in VB6

Dim res As Integer

On Error GoTo chkErr

Set cmd = New ADODB.Command

cmd.ActiveConnection = cn

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "InsertList"

cmd.Parameters.Append cmd.CreateParameter("fName", adVarChar, adParamInput, 20, Text3.Text)

cmd.Parameters.Append cmd.CreateParameter("fType", adVarChar, adParamInput, 3, Text2.Text)

cmd.Parameters.Append cmd.CreateParameter("fFood", adVarChar, adParamInput, 20, Text1.Text)

cmd.Parameters.Append cmd.CreateParameter("fResult", adInteger,adParamOutput)

cmd.Execute

res = cmd("fResult")

MsgBox res

Exit Sub

chkErr:

Select Case Err.Number

Case Else

Text4.Text = Err.Number & " - " & Err.Description

End Select

But the moment it tries to run the statement cmd.execute it throws the following error :

-2147467259 - [MySQL][ODBC 5.1 Driver][mysqld-5.5.34]OUT or INOUT argument 4 for routine dbtest.InsertList is not a variable or NEW

pseudo-variable in BEFORE trigger

I have seen codes working for SQL, so is it that MySQL itself has an issue using Stored Procedure with an OUT Parameter?

解决方案

It seems a unsolved bug from MySQL ODBC and C/API

One solution is to execute that using a SQL command with prepared variables:

Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command

cmd.ActiveConnection = cn

cmd.CommandType = adCmdText

cmd.CommandText = "call InsertList(?,?,?,@fResult)"

cmd.Parameters.Append cmd.CreateParameter("fName", adVarChar, adParamInput, 20, Text3.Text)

cmd.Parameters.Append cmd.CreateParameter("fType", adVarChar, adParamInput, 3, Text2.Text)

cmd.Parameters.Append cmd.CreateParameter("fFood", adVarChar, adParamInput, 20, Text1.Text)

cmd.Execute

'And after that, using the same connection, get the value of

'@fResult from a single query:

Set rs = cn.Execute("select @fResult as fResult")

MsgBox rs!fResult

You will get the expected value.