Problem with MySQL stored procedure

Jassim Rahma

Hi, What's the problem with this code?:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

IF NOT EXISTS (SELECT member\_channel\_id FROM member\_channels WHERE volow\_member\_id = param\_volow\_member\_id) THEN
BEGIN
    UPDATE volow\_members SET is\_channel\_created = FALSE;

    SET param\_is\_channel\_created = FALSE;
END;
ELSE SET param\_is\_channel\_created = TRUE;
END IF;

END

I am not getting any error on mysql neither on the .net application?!! here is my .net code:

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();

sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_volow_member_id", Convert.ToInt32(Session["volow_member_id"])).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;

// execute the query;
int result_row = sql_command.ExecuteNonQuery();

if (Convert.ToBoolean(sql_command.Parameters["param_is_channel_created"].Value) != true)
{ Session["is_channel_created"] = false; }

Jorgen Andersson

Not getting any errors could sometimes be thought of as a good thing. What's the problem?

Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

Jassim Rahma

that's what's making me crazy! no error in Mysql nor C# code but the MySQL code is not getting invoke. DELETE is not happening!

Bernhard Hiller

How do you know that the DELETE did not happen? Did you check in the database? What do you expect to get at int result_row = sql_command.ExecuteNonQuery();?

Jassim Rahma

yes i checked the database itself. records were not deleted and no error were thrown

Lost User

.. Try 'debugging'. What does the row-count return? What happens if you put a 'SELECT 1' in there, without any parameters, does that execute?

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

Lost User

This line

sql_command.Parameters.AddWithValue("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;

is wrong. AddWithValue expects a value as the second parameter but you are providing it a data type (did you mean to call Add method?). Anyway, you should not be providing a value to an output parameter. I'm not sure if this is what is the cause of your problem.

Jassim Rahma

how can i proivide an value?? it's an OUT parameter

Jassim Rahma

I even tried this simple delete but still not working. No error and records not getting deleted!!!

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();

sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
int result_row = sql_command.ExecuteNonQuery();

and SQL:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

IF NOT EXISTS (SELECT member\_channel\_id FROM member\_channels WHERE volow\_member\_id = param\_volow\_member\_id) THEN
BEGIN
    UPDATE volow\_members SET is\_channel\_created = FALSE;

    SET param\_is\_channel\_created = FALSE;
END;
ELSE SET param\_is\_channel\_created = TRUE;
END IF;

END