MySQL Bugs: #27632: VB and Stored Procedure - returning an OUT parameter causes error

Bug #27632	VB and Stored Procedure - returning an OUT parameter causes error
Submitted:	4 Apr 2007 5:18	Modified:	10 Jul 8:04
Reporter:	David Boccabella
Status:	Duplicate
Category:	Server: SP	Severity:	S2 (Serious)
Version:	5.4.1	OS:	Microsoft Windows (2k, XP, Vista, 2003, 2008)
Assigned to:		Target Version:
Tags:	Parameters, Visual Basic, stored procedure, ODBC, MySQL

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

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

Actually duplicate of Bug#17898.

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

Dave, it's still a duplicate of Bug#17898 which is also the master report of Bug#24724
from which this one derives.

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!)

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.

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