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: | |
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
[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?