Description:
When a MySQL stored routine has a parameter whose name contains parentheses, it's not possible to execute the routine with MySqlCommand.
If the parameter is named "TEST(1)" and you add a MySqlParameter named "@TEST(1)" you get the following exception:
MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@TEST' must be defined.
at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
at MySql.Data.MySqlClient.Statement.BindParameters()
at MySql.Data.MySqlClient.PreparableStatement.Execute()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
If you add a parameter named "@TEST" instead, you get:
System.ArgumentException: Parameter 'TEST(1)' not found in the collection.
at MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(String parameterName, Boolean throwOnNotFound)
at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, MySqlSchemaRow param, Boolean realAsFloat, MySqlParameter returnParameter)
at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
If you add parameters named "@TEST" and "@TEST(1)" you get:
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1));SELECT @_cnet_param_Parameter3' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
How to repeat:
Execute the following C# code:
using (var connection = new MySqlConnection("..."))
{
connection.Open();
using (var cmd = new MySqlCommand(@"DROP FUNCTION IF EXISTS echo;
CREATE FUNCTION echo(`TEST(1)` VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
RETURN `TEST(1)`;
END", connection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySqlCommand("echo", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
// uncomment one or both lines to get different behaviour
cmd.Parameters.AddWithValue("@TEST(1)", "a test");
// cmd.Parameters.AddWithValue("@TEST", "a test 2");
var returnValue = cmd.Parameters.Add(new MySqlParameter { Direction = ParameterDirection.ReturnValue });
cmd.ExecuteScalar();
Console.WriteLine(returnValue.Value); // expected: "a test"
}
}
Description: When a MySQL stored routine has a parameter whose name contains parentheses, it's not possible to execute the routine with MySqlCommand. If the parameter is named "TEST(1)" and you add a MySqlParameter named "@TEST(1)" you get the following exception: MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@TEST' must be defined. at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex) at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet) at MySql.Data.MySqlClient.Statement.BindParameters() at MySql.Data.MySqlClient.PreparableStatement.Execute() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar() If you add a parameter named "@TEST" instead, you get: System.ArgumentException: Parameter 'TEST(1)' not found in the collection. at MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(String parameterName, Boolean throwOnNotFound) at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, MySqlSchemaRow param, Boolean realAsFloat, MySqlParameter returnParameter) at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName) at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar() If you add parameters named "@TEST" and "@TEST(1)" you get: MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1));SELECT @_cnet_param_Parameter3' at line 1 at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar() How to repeat: Execute the following C# code: using (var connection = new MySqlConnection("...")) { connection.Open(); using (var cmd = new MySqlCommand(@"DROP FUNCTION IF EXISTS echo; CREATE FUNCTION echo(`TEST(1)` VARCHAR(20)) RETURNS VARCHAR(20) BEGIN RETURN `TEST(1)`; END", connection)) { cmd.ExecuteNonQuery(); } using (var cmd = new MySqlCommand("echo", connection)) { cmd.CommandType = CommandType.StoredProcedure; // uncomment one or both lines to get different behaviour cmd.Parameters.AddWithValue("@TEST(1)", "a test"); // cmd.Parameters.AddWithValue("@TEST", "a test 2"); var returnValue = cmd.Parameters.Add(new MySqlParameter { Direction = ParameterDirection.ReturnValue }); cmd.ExecuteScalar(); Console.WriteLine(returnValue.Value); // expected: "a test" } }