使用带有OUT参数的VB6调用MySQL存储过程 [英] Calling MySQL Stored Procedure using VB6 with OUT Parameter
问题描述
我已经在MySQL(服务器5.5)中编写了此过程
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 ;
当我从MySQL查询浏览器调用此过程时,它会按预期返回
When I call this procedure from MySQL Query Browser it returns as expected
Call InsertList ('V1', 'No', 'F1', @result);
Select @result;
->它成功返回表中ID的数量
--> It successfully returns the count of the ids in the table
我在VB6中编写了以下代码
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
但是当它尝试运行语句cmd.execute
时,会引发以下错误:
But the moment it tries to run the statement cmd.execute
it throws the following error :
-2147467259-[MySQL] [ODBC 5.1驱动程序] [mysqld-5.5.34]例程dbtest.InsertList的OUT或INOUT参数4不是变量或NEW 触发前的伪变量
-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
我已经看到了适用于SQL的代码,那么MySQL本身在使用带有OUT参数的存储过程时是否会遇到问题?
I have seen codes working for SQL, so is it that MySQL itself has an issue using Stored Procedure with an OUT Parameter?
推荐答案
这似乎是来自MySQL ODBC和C/API的未解决的错误
It seems a unsolved bug from MySQL ODBC and C/API
一种解决方案是使用带有准备好的变量的SQL命令执行该操作:
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
您将获得期望值.
这篇关于使用带有OUT参数的VB6调用MySQL存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!