Devart Forums

Posted: **Fri 30 Jul 2010 12:57**

Hello,

there is a little bug with the parameters of a stored procedure.

The name you set in

cmd.Parameters.Add("b"

is ignored for the stored procedure.

Also the order of the cmd.Parameters.Add()-Commands is important and has to match with the order of parameters of the stored procedure. (also if you give the same name like in the stored procedure)

This means the parameterName of the cmd.Parameters.Add-Command is completely ignored in the sql server.
The original MySQL Connector does it correctly.

Tried it with dotConnect for MySQL 5.80.152 Express Version.

Thanks for that great free

connector

See attached a little bug script.

Code: Select all

DROP PROCEDURE spDevartBug;
DELIMITER //
CREATE PROCEDURE spDevartBug (IN var_1 VARCHAR(50), IN var_2 VARCHAR(50))
BEGIN
  SELECT var_1 as var1, var_2 as var2;
END //
DELIMITER ;

Code: Select all

            using (MySqlConnection con = new MySqlConnection("Server=localhost;Database=test;Uid=test;Pwd=test;")) {
                con.Open();
                MySqlCommand cmd = new MySqlCommand(
                    "spDevartBug", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                
                cmd.Parameters.Add("b", MySqlType.VarChar, 50);
                cmd.Parameters.Add("a", MySqlType.VarChar, 50);

                cmd.Parameters["a"].Value = "1";
                cmd.Parameters["b"].Value = "2";

                MySqlDataReader readerData = cmd.ExecuteReader();
                readerData.Read();
                Console.WriteLine((string)readerData["var1"]);
                Console.WriteLine((string)readerData["var2"]);
                readerData.Close();
            }

Posted: **Tue 03 Aug 2010 12:14**

If parameters are added to the command collection in the order that is different from the function parameters order in database, it is necessary to describe the command by setting MySqlCommand.ParameterCheck to true to reoder parameters in a proper way.

Please set the names of parameters from the cmd.Parameters collection so that they will be the same as function parameters names, and set cmd.ParameterCheck = true; :

Code: Select all

            using (MySqlConnection con = new MySqlConnection("Server=localhost;Database=test;Uid=test;Pwd=test;")) {
                con.Open();
                MySqlCommand cmd = new MySqlCommand(
                    "spDevartBug", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
               
                cmd.Parameters.Add("var_2", MySqlType.VarChar, 50);
                cmd.Parameters.Add("var_1", MySqlType.VarChar, 50);

                cmd.Parameters["var_1"].Value = "1";
                cmd.Parameters["var_2"].Value = "2";
                cmd.ParameterCheck = true;

                MySqlDataReader readerData = cmd.ExecuteReader();
                readerData.Read();
                Console.WriteLine((string)readerData["var1"]);
                Console.WriteLine((string)readerData["var2"]);
                readerData.Close();
            }

For more information, please refer to http://www.devart.com/dotconnect/mysql/ ... eters.html, the Using parameters with stored procedures in synchronization mode section.

Posted: **Wed 04 Aug 2010 16:32**

Great. Thank you.

I think this option should be set to true as default.
But as long as this option exists its ok

Devart Forums

Bug StoredProcedure in dotConnect for MySQL 5.80.152

Bug StoredProcedure in dotConnect for MySQL 5.80.152