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

[2 Dec 2009 14:42] Cosmin Tudose
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
[2 Dec 2009 14:54] Tonci Grgin
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.
[2 Dec 2009 14:54] Tonci Grgin
Presuming we're talking of http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-stored.html, right?
[2 Dec 2009 16:44] Cosmin Tudose
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.
[3 Dec 2009 10:58] Tonci Grgin
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);
            }
[3 Dec 2009 11:01] Tonci Grgin
Ahh, the details...
W2K8SE x64, .NET 3.5, c/NET trunk against remote MySQL server 5.1.31x64 on OpenSolaris host.
[24 Mar 2010 23:42] Vince McDonald
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.
[25 Mar 2010 14:05] Vince McDonald
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.
[30 Mar 2010 15:43] Tony Bedford
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.
[29 Apr 2010 14:38] Tony Bedford
VB code has also now been added. (Tested in VS 2010).
[10 May 2012 0:52] Nicholas Tates
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.