Bug #27632 VB and Stored Procedure - returning an OUT parameter causes error
Submitted: 4 Apr 2007 3:18 Modified: 10 Jul 2009 6:04
Reporter: David Boccabella Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.4.1 OS:Windows (2k, XP, Vista, 2003, 2008)
Assigned to: CPU Architecture:Any
Tags: MySQL, ODBC, Parameters, stored procedure, Visual Basic

[4 Apr 2007 3:18] David Boccabella
Description:

When I run a stored procedure that has an OUT variable in Visual Basic I get this error.

[MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt]OUT or INOUT argument 2 for routine sap2k_dat.next_rec3 is not a variable or NEW pseudo-variable in BEFORE trigger 

I am running MyODBC 3.51.16 (Latest)

How to repeat:
Create a Table

CREATE TABLE `next_id` (
  `id1` int(11) NOT NULL,
  `keytype` int(11) NOT NULL,
  `keydesc` varchar(30) default NULL,
  PRIMARY KEY  (`keytype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Create the following stored Proc

DELIMITER $$ 

DROP PROCEDURE IF EXISTS `sap2k_dat`.`next_rec3`$$ 

CREATE DEFINER=`root`@`localhost` PROCEDURE `next_rec3`(in _param1 INT, out _param2 INT) 
BEGIN 
DECLARE nextnum INT; 
start transaction; 
if (select count(*) from next_id where keytype = _param1) =0 then 
insert into next_id values (1,_param1); 
else 
update next_id set id1 = id1 + 1 where keytype = _param1; 
end if; 
select id1 into _param2 from next_id where keytype = _param1; 
commit; 
END$$ 

DELIMITER ; 

---------------------------------------------------------

You can test the stored Proceudre like this

call next_rec3(3,@c); 
select @c; 

And it will return back 1,2,3 depending on how often it is run.

Now in VB create the following.. Note that the ODBC connection string you will use with be different from mine

--------------------------------------------------

Function GetNextNum3(iKeyType As Long) 
On Error GoTo GetNextNum3Error 

Dim MyConnection As ADODB.Connection 
Dim MyCommand As ADODB.Command 
Dim MyParamters As ADODB.Parameters 
Dim MyP As ADODB.Parameter 
Dim MySQLString As String 
Dim MyODBCConnectString As String 

Set MyConnection = New ADODB.Connection 
Set MyCommand = New ADODB.Command 

' set gDBCONNECTSAP as Your connection string 

MyODBCConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & gDBConnectSAP & ";" 
MyConnection.Open MyODBCConnectString 
With MyCommand 
.ActiveConnection = MyConnection 
.CommandText = "next_rec3" 
.CommandType = adCmdStoredProc 
.Parameters.Append .CreateParameter("_param1", adInteger, adParamInput) 
.Parameters.Append .CreateParameter("_param2", adInteger, adParamOutput) 
End With 

MyCommand("_param1") = 3 

MyCommand.Execute 

debug.print MyCommand("_param2") 

MyRecordset.Close 
Set MyRecordset = Nothing 

MyConnection.Close 
Set MyConnection = Nothing 

GetNextNum3Exit: 
Exit Function 

GetNextNum3Error: 
MsgBox Error$ 

Resume GetNextNum3Exit 
End Function 

---------------------------------------

The system errors with the following on the OUT param

[MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt]OUT or INOUT argument 2 for routine sap2k_dat.next_rec3 is not a variable or NEW pseudo-variable in BEFORE trigger 

Please Help.. 

Dave
[4 Apr 2007 12:31] Tonci Grgin
David thanks for complete test case.

I have tried several ways of running it in VS2005 but everything ends with 
[ERROR][set_stmt_error][.\error.c][206]message: OUT or INOUT argument 2 for routine test.procbug27632 is not a variable or NEW pseudo-variable in BEFORE trigger.
The error is, I think, due to wrong way of passing OUT parameter to SP (like it's IN):
070404 14:16:34	     45 Connect     root@localhost on test
		     45 Query       SET SQL_AUTO_IS_NULL=0
		     45 Query       select database()
		     45 Query       call procbug27632(8, 0)

Verified as described on:
 - MySQL 5.0.38BK on WinXP Pro SP2 localhost
 - MyODBC 3.51.14GA & connector/ODBC v5 20070322

DDL:
CREATE TABLE `bug27632` (
  `id1` int(11) NOT NULL,
  `keytype` int(11) NOT NULL,
  `keydesc` varchar(30) default NULL,
  PRIMARY KEY  (`keytype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `procbug27632`$$ 
CREATE DEFINER=`root`@`localhost` PROCEDURE `procbug27632`(in _param1 INT, out _param2 INT) 
BEGIN 
DECLARE nextnum INT; 
start transaction; 
if (select count(*) from bug27632 where keytype = _param1) =0 then 
insert into bug27632 values (1,_param1, "test"); 
else 
update bug27632 set id1 = id1 + 1 where keytype = _param1; 
end if; 
select id1 into _param2 from bug27632 where keytype = _param1; 
commit; 
END$$ 
DELIMITER ;

Test case:
  Dim p_conn As New ADODB.Connection
  'p_conn.Open("DRIVER={MySQL Connector/ODBC v5};SERVER=localhost;OPTION=3;PWD=;DATABASE=test;UID=root") '1+2+32   16387
  p_conn.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PWD=;OPTION=16427")

  Dim p_cmd As New ADODB.Command
  Dim paramIn As ADODB.Parameter
  Dim paramOut As ADODB.Parameter

  p_cmd.ActiveConnection = p_conn
  On Error Resume Next
  p_cmd.CommandType = CommandTypeEnum.adCmdStoredProc
  p_cmd.CommandText = "procbug27632"

  paramIn = p_cmd.CreateParameter("_param1", DataTypeEnum.adInteger, ParameterDirectionEnum.adParamInput)
  paramIn.Value = 8
  paramOut = p_cmd.CreateParameter("_param2", DataTypeEnum.adInteger, ParameterDirectionEnum.adParamOutput) 'adParamReturnValue - doesn't help

  p_cmd.Parameters.Append(paramIn)
  p_cmd.Parameters.Append(paramOut)
  'p_cmd.Prepared = True
  p_cmd.Execute()
  Console.WriteLine("--- Out param value: " & p_cmd("_param2").Value & "---")
  Console.WriteLine("--- Out param value: " & paramOut.Value.ToString() & "---")
  p_conn.Close()
  p_conn = Nothing
[4 Apr 2007 16:45] Tonci Grgin
Actually duplicate of Bug#17898.
[9 Jul 2009 2:07] David Boccabella
Hi.
I have tried the same tests with MySQl v5.4.1 with bth  ODBC 3.51.24 and  5.01.05

My Mysql Version is 5.4.1-beta-community

Can we please fix this for this version.  I have been waiting for a solution for nearly 3 years.

Many thanks
Dave
[9 Jul 2009 7:23] Tonci Grgin
Dave, it's still a duplicate of Bug#17898 which is also the master report of Bug#24724 from which this one derives.
[9 Jul 2009 23:11] David Boccabella
Dear Tonci
Yes - This bug is a duplicate of several other bugs.. And what does that tell us.  That this issue has been reported by many users, all who are waiting for some sort of resolution (Oh it might be in version 6, or 7 , or 8 or MySQl)

Meanwhile more and more developers who are using MySQL Stored Procedure's are being inflicted by this fundmental difference between MySQL and  the other major players in the market.

Its a Bug.. Yes..  Complaining that we are using 'closed bugs' to report this, and also reminding us - the users - that it is a duplicate of Bug A, B, or C does not fix the issue.

Only our constant reminders that the users are waiting for a fix to us keeps the issue alive.

Fixing the bug does fix the issue, and it stops the user's complaints.

New storage engines are nice.  But lets fix the problems before adding glitz.

Dave  (Waiting for over 3 years now... And MsSQL is starting to look better all of the time!)
[10 Jul 2009 5:45] Tonci Grgin
David, the fact that this report is a duplicate of several others still stays. I can not let that happen as BugsDB would become useless.

All I can do is to try to escalate Bug#17898 to engineering. Please post your comments there urging for back-port of the fix to all versions of MySQL server.
[10 Jul 2009 6:04] David Boccabella
Tonci
Bug  17898 is a CLOSED bug.  Posters there ware told to either raise a new issue or  buy and Enterprize license and complin that way.

If an issue is classified as CLOSED then there will be no additional work done on it.

So - shall I raise a whole new issue, complete with test code, examples, explainations, and wait another 3 years for a fix?

Your input grately appreciated.

Dave
[25 Jan 2012 2:43] Tomas Bobovsky
Hi,
I'm using mysql ODBC connector 5.1 with the 5.5.2 mysql server under windows 7 and I'm running into the problem David has been complaining since 2007, it's now 2012.  

This bug is duplicate of Bug 17898 which is closed but the problem still persists.    

I'm migrating my database from mssql to mysql (starting to 2nd guess my decission now), and I have close to 100 stored procedures, many with output parameters.

I bind all my params and call SQLExecDirect() I receive the following error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.5.20]OUT or INOUT argument 1 for routine sonixhub.SP_UpdatePatient is not a variable or NEW pseudo-variable in BEFORE trigger"	wchar_t [512]

The identical code works fine with mssql.

Has there been a resolution to this problem and so is there a chance something has to be modified on my side or is this problem still persisting in mysql/ODBC connector?