Friday, December 12, 2008

Calling MySQL stored procedures from C# with Connector/Net

Have ever got this error when calling a stored procedure?
MySql.Data.MySqlClient.MySqlException: SELECT command denied to user 'your_user'@'localhost' for table 'proc'
  at MySql.Data.MySqlClient.MySqlStream.OpenPacket () [0x00000] 
  at MySql.Data.MySqlClient.NativeDriver.ReadResult (System.UInt64& affectedRows, System.Int64& lastInsertId) [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet () [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult () [0x00000] 
Let me explain what is going on here. The first thing Connector/Net does when you call a stored procedure is select the full text of the stored procedure from the server, parse it, and cache information about the parameters. It then uses that information to build the statement for the actual stored procedure call.

It does this by either calling
SELECT * FROM mysql.proc WHERE db LIKE 'mydb_name' AND name LIKE 'MyProcedure';

or by calling
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'mydb_name' AND ROUTINE_NAME LIKE 'MyProcedure';

Why does it do this?

Let me give you a little background. Microsoft SQL Server has a nice feature in that it gives you a lot of flexibility when you call stored procedure. The syntax is
EXEC MyProcedure @MyParam1=123, @MyParam2='blah';

The parameters can appear in any order and any of the parameters can be specified as optional in the stored procedure. This is nice when you want to add a new parameter to a stored procedure and release it to your website without taking downtime. First you run an ALTER statement to release the new stored procedure with a new optional parameter, then you release the new application code which passes the new parameter to the stored procedure. Everything works fine when the application code and stored procedure are not in sync because the new parameter is optional.

MySQL's syntax is different. It is
CALL MyProcedure(123, 'blah');

The parameters have to appear in the right order and there is no support for optional parameters.

Now let's look at how stored procedures are called from the application code. Calling Microsoft SQL Server from C# code looks like this
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) {
  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("MyProcedure", conn);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.Add("@MyParam1", System.Data.SqlDbType.Int).Value = 123;
  cmd.Parameters.Add("@MyParam2", System.Data.SqlDbType.VarChar, 40).Value = "blah";
  conn.Open();
  cmd.ExecuteNonQuery();
}
MySQL Connector/Net mimics this syntax fairly closely.
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr)) {
  MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("MyProcedure", conn);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.Add("@MyParam1", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 123;
  cmd.Parameters.Add("@MyParam2", MySql.Data.MySqlClient.MySqlDbType.String).Value = "blah";
  conn.Open();
  cmd.ExecuteNonQuery();
}
So in order for Connector/Net to figure out what order to put the parameters when it creates the CALL statement it gets the stored procedure definition from the server and looks at the order that the parameters appear in the original CREATE PROCEDURE statement.

There are a couple of problems with this.
  1. The account the application code is running under may not have select permissions on mysql.proc and if you are using shared web hosting it may not be possible to give your account these permissions.
  2. There is a performance penalty for retrieving and storing the stored procedure bodies.
So what I recommend doing is building the CALL statement yourself as a parameterized query.

My recommended way to call MySQL stored procedures
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr)) {
  MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
  cmd.Connection = conn;
  cmd.CommandText = "CALL MyProcedure(@MyParam1, @MyParam2);";
  cmd.Parameters.AddWithValue("@MyParam1", 123);
  cmd.Parameters.AddWithValue("@MyParam2", "blah");
  // Or if you prefer this style:
  //cmd.Parameters.Add("@MyParam1", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 123;
  //cmd.Parameters.Add("@MyParam2", MySql.Data.MySqlClient.MySqlDbType.String).Value = "blah";
  conn.Open();
  cmd.ExecuteNonQuery();
}
Connector/Net will go through the CommandText and replace @MyParam1 and @MyParam2 with the values you provide so the statement that gets sent to the MySQL server is just CALL MyProcedure(123, 'blah'); It's a good idea to do it with parameters because the library code will properly escape single quote characters. If you don't do this, the SQL statement could end up with syntax errors, or worse, end users might be able to run malicious SQL queries against your MySQL server.

Output parameters

Connector/Net does not handle parameters with Direction = System.Data.ParameterDirection.Output unless CommandType = System.Data.CommandType.StoredProcedure. In our case we're using CommandType.Text so we can only use ParameterDirection.Input.

So what do we do if we want to call a stored procedure that has an output parameter?

We use MySQL User Variables. This is the statement we will send to the MySQL server.
CALL MyProcedure(@MyOutputParam1, @MyOutputParam2, 123, 'blah');
SELECT @MyOutputParam1, @MyOutputParam2;


But before we can make this work there are a couple things we need to take care of. One is that we need to include Allow User Variables=true; in the connection string.

Server=localhost; Port=3306; Database=your_database; Uid=your_user; Pwd=your_password; Connect Timeout=30; Allow User Variables=true;

If we don't do this Connector/Net will throw an exception. This is because it checks that the parameter names in the CommandText match up with the parameters that have been added to the list and it doesn't have a way to distinguish between user variables and parameters. If we set Allow User Variables=true; it will skip this check.

The other thing we have to take care of is the data type of the user variables. Connector/Net does not handle result sets with user variables in them properly.

To see for yourself, try running this query:
SET @a = true, @b = 222, @c = -1, @d = 'abcd';
SELECT @a, @b, @c, @d, true, 222, -1, 'abcd';
Connector/Net returns the first three columns in the result set as byte[] and sets the contents of the byte array to be the raw bytes sent back by the MySQL server. The fourth column is a normal string and the rest of the columns are all handled properly.

The MySQL Command Line Client, on the other hand, handles user variables properly. You can see it here.
mysql> SET @a = true, @b = 222, @c = -1, @d = 'abcd';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a, @b, @c, @d, true, 222, -1, 'abcd';
+------+------+------+------+------+-----+----+------+
| @a   | @b   | @c   | @d   | TRUE | 222 | -1 | abcd |
+------+------+------+------+------+-----+----+------+
| 1    | 222  | -1   | abcd |    1 | 222 | -1 | abcd |
+------+------+------+------+------+-----+----+------+
1 row in set (0.00 sec)
I was curious what was going on so I intercepted the response sent back by the MySQL server to see if I could tell why Connector/Net was failing. It sends the column definitions first and then the contents of each row. I could see that the row data was the same for column @a and column TRUE, column @b and column 222, and so on, but the header containing the column definitions were different. I didn't investigate further and instead looked for a workaround.

Here is what I did to make it work.

CALL MyProcedure(@MyOutputNum1, @MyOutputString2, 123, 'blah');
SELECT CAST(@MyOutputNum1 AS SIGNED), @MyOutputString2;


If your parameters are not signed integers you will need to cast them as something else. Read about using CAST and CONVERT. Note that SIGNED and UNSIGNED are both 64 bit integers.

Now that I know what to do I can create the application code.
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr)) {
  MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
  cmd.Connection = conn;
  cmd.CommandText = "CALL MyProcedure(@MyOutputNum1, @MyOutputString2, ?MyParam1, ?MyParam2); SELECT CAST(@MyOutputNum1 AS SIGNED), @MyOutputString2;";
  // I am using the ?param style to make it easy to differentiate between user variables and parameters. @MyParam1 would also work.
  cmd.Parameters.AddWithValue("?MyParam1", 123);
  cmd.Parameters.AddWithValue("?MyParam2", "blah");
  conn.Open();
  MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader();
  // If MyProcedure returns a result set that will come first so you will need: 
  //   while (rdr.Read()) {...} 
  //   and rdr.NextResult();
  // Now get the output parameters.
  long myOutputNum1 = -1;
  string myOutputString2 = null;
  if (rdr.Read()) {
    int i = 0;
    if (rdr.FieldCount > i && !rdr.IsDBNull(i)) myOutputNum1 = rdr.GetInt64(i);
    i++;
    if (rdr.FieldCount > i && !rdr.IsDBNull(i)) myOutputString2 = rdr.GetString(i);
  }
}
That worked.

6 comments:

hyp0th3rmi4 said...

Version 5.2 (MySQL 5.1) has a switch in the connection string that is called "Use Procedure Bodies". This is normally set to true, and causes the driver to connect the server and query the proc table that is NOT in the databases where the user has created them. This causes the problem cited in the post. Because the user may not have the rights to perform a select operation on that table
If we set the Use Procedure Bodies to false we simply get rid of this problem. Setting this value to false implies that driver cannot derive the types of the parameters and the user has to specifically set the type of each parameter.

The reason in which MySQL handles procedures is quite weird and strongly limits the portability of code...

Mark Cordell said...

The "Use Procedure Bodies" does not work. I have tried it and it does not make a difference.

Here is what the documentation says:

Instructs the provider to attempt to call the procedure without first resolving the metadata. Thjis is useful in situations where the calling user does not have access to the mysql.proc table. To use this mode, the parameters for the procedure must be added to the command in the same order as they appear in the procedure definition and their types must be explicitly set.

Here is what actually happens:

Using the latest version of Connector/Net, which is 5.2.5, call a stored procedure with no parameters and monitor the network traffic. You will see it doing a select to retrieve the definition of the stored procedure.

Maybe at some point in the future they will fix this so that it works as described in the documentation, but even with this there is still the issue that you have to add the parameters with types in the proper order, which is not going to be obvious to someone who comes along later and looks at your code.

Donna B. Isobel said...

There is a performance penalty for retrieving and storing the stored procedure bodies.

I don't know why but i really like this sentence. Makes me think. If you take it out the context of what your saying... its a real mind buster.

:)

Lars Ole Avery Simonsen said...

Thanks for the cast workaround to return types getting messed up when selecting a @variable value through the connector. I am seeing some truly weird behavior, but this workaround allows me to move on at least.

Anonymous said...

if (my_con.State == System.Data.ConnectionState.Open)
{
try
{
MySqlCommand my_comm = new MySqlCommand();
my_comm.Connection = my_con;
my_comm.CommandType = System.Data.CommandType.StoredProcedure;
my_comm.CommandText = "CALL pr_view_peop";

MySqlDataAdapter my_adapter = new MySqlDataAdapter(my_comm.CommandText, my_con);

DataTable dt = new DataTable();
my_adapter.Fill(dt);

dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
//!!!!!!!!!!!!!!!
}

chimarrao said...

Solution: In the Connection String set parameter "UseProcedureBodies=false".
No more problem!