MySQL Bugs: #44349: Connector/Net 5.2.5 ignores parameter names

Bug #44349	Connector/Net 5.2.5 ignores parameter names
Submitted:	17 Apr 2009 20:07	Modified:	30 Apr 2009 13:35
Reporter:	Julian Beckensall	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.5	OS:	Any
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	connector, parameter names

Description:
I've just installed and started to use version 5.2.5 of the ADO.Net connector on an ASP.Net 2.0 Win2003 web server linked to a linux MySQL database on another server.  These are both hosted by FastHosts.

Locally, on my XP PC, everything is fine.  BUT, in the new environment, the parameter names seem to be ignored in stored procs, and they are taken in the order the parameters were added to the parameters collection instead.

E.g.  My ASP.Net app sends Param3='C', Param2='B', Param1='A'.
My stored procedure then translates them to Param1='C', Param2='B', Param3='A'

This may have been an issue on previous versions of the connector - I've  only just uploaded to FastHosts.

Any help much appreciated - I'm losing hair with this connector.

How to repeat:
Must be Windows to Linux

Suggested fix:
Other than re-writing all my ASP.Net code, no idea!

Hi Julian and thanks for your report.

I can't gather much about the problem from your words so if you can come up with simple test case (incl. DDL/DML) that I can load into VS2005/8 I will take a look.

Hi Tonci.  Sorry - should have supplied this originally. 

Stored proc: -
----------------------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `XXXXXX`.`spParamTest` $$
CREATE PROCEDURE `XXXXXX`.`spParamTest` (p_1 varchar(5), p_2 varchar(5))
BEGIN

  select p_1 as P1, p_2 as P2;

END $$

DELIMITER ;
----------------------------------------

VS2005 C# Code: -
----------------------------------------
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient; // Version 5.2.5.0  (Runtime = v2.0.50727)

namespace paramTest
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            // Connection String
            // Need "Use Procedure Bodies =false" as I don't have access to the "procs" table
            // Server is a remote, Linux server
            // ASP.Net code running on my XP PC but behaves the same on Win2003 server
            string connstr = "Database=XXXXX;Data Source=###.###.###.###;User Id=XXXXX;Password=XXXXX; Use Procedure Bodies =false;";

            // Open connection
            MySqlConnection conn = new MySqlConnection(connstr);
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("spParamTest", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Params in the 'wrong' order
            cmd.Parameters.AddWithValue("p_2", "World");
            cmd.Parameters.AddWithValue("p_1", "Hello");

            // Get Adapter
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            // Create + Populate Dataset
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);

            // Show return values - should be "P1: Hello", "P2: World"
            Response.Write("P1: " + dataset.Tables[0].Rows[0]["P1"].ToString());
            Response.Write("<br/>");  
            Response.Write("P2: " + dataset.Tables[0].Rows[0]["P2"].ToString());

            // Tidy up
            conn.Close();
            conn = null;
            dataset = null;

        }
    }
}

Julian, your parameters have no sense of direction :-) Have you tried defining:
  o in SP: IN p_1 varchar(5), IN p_2 varchar(5)
  o in C#: cmd.Parameters[0].Direction = ParameterDirection.Input;
           cmd.Parameters[1].Direction = ParameterDirection.Input;

Does the test work if all is defined like this? How does, if you can test it, "Use Procedure Bodies = true" change things?

This works as expected:
      cmdCreateTable.CommandText = ("CREATE PROCEDURE `SP_BUG44349`(" + "\n" +
      "IN p_1 VARCHAR(5), \n" +
      "IN p_2 VARCHAR(5) \n" +
      ") \n" +
      "BEGIN SELECT p_1 AS P1, p_2 AS P2; END\n");
      try
      {
            cmdCreateTable.ExecuteNonQuery();
            MySqlCommand cmd = new MySqlCommand("SP_BUG44349", conn);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("?p_1", ("Hello"));
            cmd.Parameters[0].DbType = DbType.AnsiString;
            cmd.Parameters[0].Direction = ParameterDirection.Input;
            cmd.Parameters.AddWithValue("?p_2", ("world"));
            cmd.Parameters[1].DbType = DbType.AnsiString;
            cmd.Parameters[1].Direction = ParameterDirection.Input;

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Console.WriteLine("P1: " + dataset.Tables[0].Rows[0]["P1"].ToString());
            Console.WriteLine("P2: " + dataset.Tables[0].Rows[0]["P2"].ToString());
            dataset = null;
against remote MySQL server 5.1.31 on OpenSolaris host. .NET FW used is 3.5.

Hi again Tonci. 

The code you supplied does work, but only because you create the parameters in the correct order in the ASP code.

If I change your code to this (adding p_2, THEN p_1), it doesn't: -

            cmd.Parameters.AddWithValue("?p_2", ("world"));
            cmd.Parameters[0].DbType = DbType.AnsiString;
            cmd.Parameters[0].Direction = ParameterDirection.Input;

            cmd.Parameters.AddWithValue("?p_1", ("Hello"));
            cmd.Parameters[1].DbType = DbType.AnsiString;
            cmd.Parameters[1].Direction = ParameterDirection.Input;

In other words, it seems to ignore the parameter names and goes by the order in which they were added to the Command object.

I have tried to connect without the "Use Procedure Bodies = false", but I get this error: "SELECT command denied to user 'XXXXX'@'XXXXX.com' for table 'proc'".  As this database is controlled by the ISP, I don't seem to have any choice but to use it.

Thanks for your help.

Julian.

Julian, you are right. Verified as described with test case already pasted. Replacing                 
  cmd.Parameters.AddWithValue("?p_1", ("Hello"));
and
  cmd.Parameters.AddWithValue("?p_2", ("Hello"));

shows the problem:
[20.4.2009 16:48:48] - Executing command QUERY with text ='call SP_BUG44349 ('world', 'Hello')'
P1: world
P2: Hello

This is expected behavior when you use 'use procedure bodies=false'.  The reason is that when we don't have access to the procedure bodies the connector cannot determine what the parameters for a proc are.  In that case, the connector has no choice but to use the parameters in the order they are added to the command.  

this is actually a fairly nice optimization.  As long as you add the parameters in the right order you avoid a round trip to the server to get parameter names which speeds things up.