I will start by saying that I know this exact question has been asked before but it was incorrectly marked as a duplicate question and never answered.
The first key point about this question is that the InvalidCastExcpetion is thrown, before attempting to use the return from the stored procedure. So all of the suggested solutions, which are to deal with that are not applicable. It fails on the line that executes the SQL command.
The second key point is that the exact same code works successfully when connecting as root but not when connecting as a different user. Therefore, it is not a problem with the code that can be solved with any of the previously given solutions.
Does anyone have any ideas why this would work for the root user but not for another and why the message in the exception has nothing to do with privileges? By the way, the user in question has execute privileges and can call this procedure from within MySQL Workbench.
I do not think the code is particularly relevant, given that it works for root but I am including it anyway.
My C# code is as follows:
using (MySqlConnection conn = new MySqlConnection(SqlConnectionString)) {
using (MySqlCommand cmd = conn.CreateCommand()) {
try {
conn.Open();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = @"open_application";
cmd.Parameters.AddWithValue("?p_opr_id", organisationID);
cmd.Parameters.AddWithValue("?p_app_id", applicationID);
MySqlParameter returnValue = new MySqlParameter();
returnValue.Direction = System.Data.ParameterDirection.ReturnValue;
returnValue.MySqlDbType = MySqlDbType.Int32;
cmd.Parameters.Add(returnValue);
cmd.ExecuteNonQuery();
//Deal with the return value
} catch (Exception ex) {
message = "Failed to record application opening: " + ex.Message;
}
}
}
The InvalidCastException is thrown from cmd.ExecuteNonQuery();
The MySQL stored function is as follows:
DELIMITER $$
DROP FUNCTION IF EXISTS `open_application` $$
CREATE FUNCTION `open_application`(p_opr_id int, p_app_id int) RETURNS tinyint
BEGIN
DECLARE v_opened int;
DECLARE cur CURSOR FOR SELECT opened FROM operator_application WHERE opr_id = p_opr_id AND app_id = p_app_id;
OPEN cur;
FETCH cur INTO v_opened;
CLOSE cur;
IF v_opened = 1 THEN
RETURN 0;
END IF;
UPDATE operator_application SET opened = 1 WHERE opr_id = p_opr_id AND app_id = p_app_id;
RETURN 1;
END $$
DELIMITER ;
** UPDATE **
I have rewritten the stored function as a stored procedure. Instead of using RETURN, I am using SELECT and then invoking the stored procedure using MySQLCommand.ExecuteScalar, instead of ExecuteNonQuery. It is now working. I still have no idea why it wasn't working as a function.
