MySQL Bugs: #41120: Cannot execute Stored Procedure from C#

Bug #41120	Cannot execute Stored Procedure from C#
Submitted:	29 Nov 2008 8:06	Modified:	2 Dec 2008 14:58
Reporter:	Chris Oosthuizen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.1.29-rc	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any

Description:
I have a simple database (test) with one table (TestTable). I created on stored procedure with 2 parameters to retrieve data from this table.

All table and stored procedure creation was done as root. The application connects to database as root using Connector/NET 5.1.24. MySql raises exception telling me that the stored procedure could not be found.

How to repeat:
C# Code Snippet:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace ParameterTest
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection conn = new MySqlConnection("Server=localhost;Port=3306;Database=Test;Uid=root;Pwd=xxxxxxxxx;");
            MySqlCommand cmd = conn.CreateCommand();
            try
            {

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "SP_Test";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("SNAME", MySqlDbType.VarChar, 255);
                cmd.Parameters["SNAME"].Value = "BOB";
                cmd.Parameters.Add("TXTLAST", MySqlDbType.Text);
                cmd.Parameters["TXTLAST"].Value = "JONES";

                conn.Open();

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}

Table DLL:

CREATE TABLE test.TestTable (
  FirstName VARCHAR(255) DEFAULT NULL,
  LastName TEXT DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Stored Procedure DLL:
CREATE DEFINER = 'root'@'localhost' 
PROCEDURE test.SP_Test(IN SNAME VARCHAR(255), IN TXTLAST TEXT)
BEGIN

    SELECT * FROM TestTable WHERE TestTable.FirstName = SNAME AND TestTable.LastName = TXTLAST;

END

CORRECTION: The Connector/NET version is 5.2.5

Looking at the definition of the SP, the database name starts with a lowercase 't' whereas in your connection string it starts with an uppercase 'T'. There was a change in behavior from 5.2.2 or 3 to 5.2.4/5, so that different cases don't work anymore (see http://bugs.mysql.com/bug.php?id=40751).

Hi Chris and thanks for your report.

As Dennis said, this could truly be the reason but without error message it's hard to tell. Please retest and inform me of result.

Dennis, thanks for your proactive involvement.

I have confirmed that the Database name in the connections string is indeed case sensitive. When changed to all lowercase, the error no longer occurs

Chris np, main thing is problem is gone. Please be more careful in the future as your other bug (BUG#40751) seems to be the product of same typo.