Bug StoredProcedure in dotConnect for MySQL 5.80.152

Bug StoredProcedure in dotConnect for MySQL 5.80.152

Postby python » Fri 30 Jul 2010 12:57

Hello,

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

The name you set in
Code: Select all
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();
            }
python
 
Posts: 7
Joined: Wed 23 Jun 2010 09:19

Postby Shalex » 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/docs/?Parameters.html, the Using parameters with stored procedures in synchronization mode section.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby python » 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 ;-)
python
 
Posts: 7
Joined: Wed 23 Jun 2010 09:19


Return to dotConnect for MySQL