MySQL Bugs: #49352: Procedure or function cannot be found in database

Bug #49352	Procedure or function cannot be found in database
Submitted:	2 Dec 2009 14:42	Modified:	29 Apr 2010 14:38
Reporter:	Cosmin Tudose	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S2 (Serious)
Version:	6.2	OS:	Windows (Windows 7)
Assigned to:	Tony Bedford	CPU Architecture:	Any

Description:
I use your c# example "Accessing Stored Procedures with Connector/NET".
After the procedure add_emp was created i try to execut the second example.
After cmd.ExecuteNonQuery(); i receive the next error: 
"Procedure or function '`add_emp`' cannot be found in database '`test`'".
The procedure exist in the database procedure.

How to repeat:
Server version: 5.1.36-community-log

Hi Cosmin and thanks for your report.

Probably you've created SP in different database or did not create it at all. Please check with mysql command line client or with query browser that procedure is there and in proper database.

If all seems in place, attach small but complete test case demonstrating this behavior.

Presuming we're talking of http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-stored.html, right?

Hi, i find the problem. When my database name start with number(ex: 1Test)  i receive the error. If i change the name of the database (ex:Test) the example works.

Cosmin, unfortunately I was unable to confirm your problems. For me, it just works.

Tony, samples from http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-stored.html just do not work:
  o Can't parse DateTime (DateTime.Parse("12/13/1977 2:17:36 PM"));)
  o Parameter Add is deprecated, use AddWithValue
  o There is no DDL for table emp
  o There is no check if stuff exists

Now, I propose to change samples on that page to something like:
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "blah blah";
            MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DROP TABLE IF EXISTS emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE PROCEDURE add_emp(" +
                    "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " +
                    "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
                    "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";

                cmd.ExecuteNonQuery();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();

            try
            {
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "add_emp";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@lname", "Jones");
                cmd.Parameters["@lname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@fname", "Tom");
                cmd.Parameters["@fname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@bday", DateTime.UtcNow.ToUniversalTime());
                cmd.Parameters["@bday"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32);
                cmd.Parameters["@empno"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                MessageBox.Show(cmd.Parameters["@empno"].Value.ToString());
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                  "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

Ahh, the details...
W2K8SE x64, .NET 3.5, c/NET trunk against remote MySQL server 5.1.31x64 on OpenSolaris host.

I'm encountering a similar scenario, using my own stored procedures and code, which I can also reproduce when following the steps in the previously-mentioned article:  http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-stored.html

I've confirmed that I'm able to run the stored procedure from the MySql command line, using the same user account that my .NET application uses.  This suggests to me that the issue is in the Connector/Net code, rather than in the documentation.

Environment details:
Windows XP Home, .NET 2.0, C#, programming against local MySql server 5.1.45 using Connector/NET 6.2.2.0.

I found the cause of my previous issue.  It was due to how I set up my connection string.  The database needs to be specified in all lower-case, to match the name of the database as stored in MySQL.  I didn't expect that to occur, since MySQL is otherwise case-insensitive on Windows.

I'll file this separately.  With any luck, this information may assist the original poster.

I've reworked the offending page completely (in XML sources). The new C# code has been simplified and tested, and is presented as a complete working example, rather than various bits and pieces that don't work. For now I have removed the VB code (to be added later). I will keep this bug in 'Documenting' until the VB code has been added.

VB code has also now been added. (Tested in VS 2010).

Thanks Vince!  Even with 6.5.4 Connector and 5.5 MySQL this still seems to be an issue.  I guess I'm just used to SQL Server and case not really mattering.