MySQL Bugs: #48117: bug when using OUT variable.

Bug #48117	bug when using OUT variable.
Submitted:	16 Oct 2009 13:43	Modified:	19 Oct 2009 17:56
Reporter:	Andre Cassar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.1.2	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	INOUT, OUT

Description:
I had a piece of code that was working before ( it is being used in production) I was using the 5.2.3 .Net connector version. Now what I did is that I updated the project to make use of the latest .Net connector 6.1.2 however I'm encountering this problem. I have a stored procedure which takes an OUT parameter. I return in it the LAST_INSERT_ID value. 

The stored procedure is this:

CREATE PROCEDURE table1.ADDUSER(IN _USERNAME char(50),IN _PASSWORD char(30), IN _DISPLAYNAME VARCHAR(25),IN _USERTYPEPREFERENCE TINYINT,OUT IDNO INT) BEGIN DECLARE TMPID INT; INSERT INTO table1(USERNAME,PASSWORD,DISPLAYNAME,CREATED,USERTYPEPREFERENCE) VALUES(_USERNAME,_PASSWORD,_DISPLAYNAME,NOW(),_USERTYPEPREFERENCE);
SET TMPID=LAST_INSERT_ID(); SET IDNO=TMPID; 
INSERT INTO table2(ID) VALUES(TMPID); END;

and then I'm using .Net connector to call this SP using this code:

MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = m_connection;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddUser";

            cmd.Parameters.Add("_USERNAME", MySqlDbType.String);
            cmd.Parameters["_USERNAME"].Value = NewUser.UserName;
            cmd.Parameters.Add("_PASSWORD", MySqlDbType.String);
            cmd.Parameters["_PASSWORD"].Value = NewUser.Password;
            cmd.Parameters.Add("_DISPLAYNAME", MySqlDbType.String);
            cmd.Parameters["_DISPLAYNAME"].Value = NewUser.DisplayName;
            cmd.Parameters.Add("_USERTYPEPREFERENCE", MySqlDbType.Int32);
            cmd.Parameters["_USERTYPEPREFERENCE"].Value =(int) NewUser.UserType;

            cmd.Parameters.Add("IDNO", MySqlDbType.Int32);
            cmd.ExecuteNonQuery();

When I execute this piece of code using version 6.1.2 I get the following exception: 

OUT or INOUT argument 5 for routine table1.ADDUSER is not a variable or NEW pseudo-variable in BEFORE trigger

How to repeat:
As described above

Suggested fix:
revert to the old version.

Hi Andre and thanks for your report.

Please use Parameter.AddWithValue as Parameter.Add is deprecated. Also, do say explicitly the direction of parameter:
  mySqlCommand.Parameters["?ReturnValue"].Direction = ParameterDirection.Output;
Retest and get back to me with results.

Which version of MySQL server do you use?

ok using the output direction parameter seems like it solved the problem. Thanks. Sorry for the inconvenience caused :) but as I said this was something that was working before. Any I'm using the latest MySQL server 5.1

Andre, glad things work now.

As for "it worked before", please read numerous bug reports in BugsDB to get up to speed. The origin of problem (and the reason I asked for server version) is in Bug#17898, take it from there. The principal reason for changing c/NET version to 6.1 were numerous changes that could break old behavior (there is no such changes from 5.1.x to 5.1.x+1 of course). So it is a good policy to
  o read changelog provided with every connector version
  o pay attention to IntelliSense (if you're using VS) as it warns you that Parameter.Add is obsolete and even tells you what to use.

I checked for the intellisense obsolete message, but at no point my Visual Studio pointed this out. I'm using VS 2008.