Bug #63074 Attempt to call stored function '`db`.`func`' without specifying a return parame

Submitted: 2 Nov 2011 9:44 Modified: 8 Nov 2011 12:16
Reporter: Lars Nymand Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.4.4.0 OS:Any
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: stored function return parame

[2 Nov 2011 9:44] Lars Nymand
Description:
I just updated from v6.2.20 to v6.4.4.0 and my program starts to get following exception:

System.InvalidOperationException: Attempt to call stored function '`db`.`updateUserInfo`' without specifying a return parameter
   at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, DataRow param, Boolean realAsFloat, MySqlParameter returnParameter)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Project.InsertUser() in D:\Project\UserInfo.cs:line 156

This function have worked just fine until now, and as you can see the return is defined in the stored function

C# code:
using (MySqlConnection conn = new MySqlConnection(binding.ConnectionString))
{
 conn.Open();

 String SQL = "updateUserInfo";
 using (MySqlCommand cmd = new MySqlCommand(SQL, conn))
 {
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.CommandTimeout = Program.Configuration.DatabaseCommandTimeout;
  cmd.Prepare();

  cmd.Parameters.Add(UserInfo.ParamAuthenticatedUsername);
  cmd.Parameters.Add(UserInfo.ParamName);

  foreach (UserInfo ui in userInfos)
  {
   ui.UpdateParameterValues();

   cmd.ExecuteNonQuery();
  }
 }
}

Stored function:
CREATE DEFINER=`root`@`localhost` FUNCTION `updateUserInfo`(`iusername` VARCHAR(64), `iname` VARCHAR(128))
	RETURNS int(10)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN

DECLARE tmpId Integer;
DECLARE tmpIUsername VARCHAR(64);
DECLARE tmpIName VARCHAR(128);
SET tmpId = -1;
SET tmpIUsername = iusername;
SET tmpIName = iname;

SET tmpId = IFNULL((SELECT id FROM `user` WHERE `username` = tmpIUsername LIMIT 1), -1);
IF tmpID = -1 THEN
	INSERT INTO `user` (username, name)
		VALUES(tmpIUsername, tmpIName);

	SELECT LAST_INSERT_ID() INTO tmpId;
END IF;
return tmpId;
END

How to repeat:
The code in the description is a draft from my code.
[8 Nov 2011 12:16] Bogdan Degtyariov
Lars,

Thank you for providing the stored function and C# function codes.

I do not understand how Connector/NET 6.2 worked without adding the return parameter to the function (ParameterDirection.ReturnValue).

After making the following changes in your C# code everything worked perfectly:

cmd.Parameters.Add("@ireturnvalue", MySqlDbType.Int32);
cmd.Parameters["@ireturnvalue"].Direction = ParameterDirection.ReturnValue;

This is not a bug in Connector/NET, please fix your code.
Thanks.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.