MySQL stored procedure call using VB6 with OUT parameter

Question

I wrote 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 number of identifiers 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 when he tries to run the cmd.execute , he throws the following error:

-2147467259 - [MySQL] [ODBC driver 5.1] [mysqld-5.5.34] OUT or INOUT argument 4 for regular dbtest.InsertList is not a variable or NEW pseudo-variable in a BEFORE trigger

I saw codes that work on SQL, is it true that MySQL has a problem using a stored procedure with an OUT parameter?

Answer 1

Seems to be an unresolved error from MySQL ODBC and C / API

One solution is to execute using an 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.

Answer 2

Dim query as string

query = "call InsertList ('V25', 'no', 'Both', @fResult)"

cmd.CommandText = request

- According to your code, if I write cn.execute

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

- msgbox rs! fResult throws error 94 - Invalid use of null

- If I use cmd.execute

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

--- 2147217900 - [MySQL] [ODBC driver 5.1] [mysqld-5.5.34] You have an error in the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to 'call InsertList (' V25 ',' no ',' Both ', @fResult)' on line 1