Description:
In MySql 5.7 the name of the stored procedure contain dot character is valid. BUT call the stored procedure from C# will get MySqlException, even if the "`" symbol be used in quoting the name.
Example:
The following SQL script is work in MySql 5.7.
/* ***
in SQL:
*/
USE `blackjack`;
CREATE PROCEDURE `app.create_game` (
INOUT _code CHAR(32) CHARSET latin1,
IN _table INT UNSIGNED,
IN _players TINYINT UNSIGNED,
IN _min_wager INT UNSIGNED,
OUT _result INT
)
...
But the following code will get exception.
/*
in C#
using System.Data;
*/
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "`app.create_game`";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@_code" , DBNull.Value);
command.Parameters.AddWithValue("@_table" , 1);
command.Parameters.AddWithValue("@_players" , 4);
command.Parameters.AddWithValue("@_min_wager" , 100);
command.Parameters.Add("@_result", MySqlDbType.Int32);
command.Parameters["@_code" ].Direction = ParameterDirection.InputOutput;
command.Parameters["@_result"].Direction = ParameterDirection.Output;
command.ExecuteNonQuery(); // <-- throw MySqlException:
// Procedure or function 'create_game' cannot
// be found in database 'app'.
result = (int) command.Parameters["@_result"].Value;
if (result == 0)
{
code = (string) command.Parameters["@_code"].Value;
}
How to repeat:
Let the "`" symbol can be resolved.
file: MySql.Data/StoredProcedure.cs
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -104,9
private string FixProcedureName(string name)
{
string[] parts = name.Split('.');
for (int i = 0; i < parts.Length; i++)
if (!parts[i].StartsWith("`", StringComparison.Ordinal))
parts[i] = String.Format("`{0}`", parts[i]);
if (parts.Length == 1) return parts[0];
return String.Format("{0}.{1}", parts[0], parts[1]);
}
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -154,15
public override void Resolve(bool preparing)
{
// check to see if we are already resolved
if (resolvedCommandText != null) return;
serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing;
// first retrieve the procedure definition from our
// procedure cache
string spName = commandText;
if (spName.IndexOf(".") == -1 && !String.IsNullOrEmpty(Connection.Database))
spName = Connection.Database + "." + spName;
spName = FixProcedureName(spName);
MySqlParameter returnParameter = GetReturnParameter();
Suggested fix:
file: MySql.Data/StoredProcedure.cs
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -104,9 +104,31 @@
- private string FixProcedureName(string name)
- {
- string[] parts = name.Split('.');
- for (int i = 0; i < parts.Length; i++)
- if (!parts[i].StartsWith("`", StringComparison.Ordinal))
- parts[i] = String.Format("`{0}`", parts[i]);
- if (parts.Length == 1) return parts[0];
- return String.Format("{0}.{1}", parts[0], parts[1]);
+ private string FixProcedureName(string spName)
+ {
+ string schema = null;
+ string name = spName;
+
+ int i = 0;
+ bool theCharWithinQuote = false;
+ while (i < spName.Length)
+ {
+ char ch = spName[i];
+ switch (ch)
+ {
+ case '`':
+ theCharWithinQuote = !theCharWithinQuote;
+ break;
+
+ case '.':
+ if (!theCharWithinQuote)
+ if (schema == null)
+ schema = spName.Substring(0, i);
+ break;
+ }
+
+ if (schema != null)
+ break;
+ i++;
+ }
+
+ if (!String.IsNullOrEmpty(schema))
+ schema = NormalizeName(schema);
+ else if (!String.IsNullOrEmpty(Connection.Database))
+ schema = NormalizeName(Connection.Database);
+
+ if (i < spName.Length)
+ name = NormalizeName(
+ spName.Substring(i + 1, spName.Length - i - 1));
+
+ if (schema == null)
+ return name;
+ return schema + "." + name;
}
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -154,15 +154,12 @@
public override void Resolve(bool preparing)
{
// check to see if we are already resolved
if (resolvedCommandText != null) return;
serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing;
// first retrieve the procedure definition from our
// procedure cache
- string spName = commandText;
- if (spName.IndexOf(".") == -1 && !String.IsNullOrEmpty(Connection.Database))
- spName = Connection.Database + "." + spName;
- spName = FixProcedureName(spName);
+ string spName = FixProcedureName(commandText);
MySqlParameter returnParameter = GetReturnParameter();
Description: In MySql 5.7 the name of the stored procedure contain dot character is valid. BUT call the stored procedure from C# will get MySqlException, even if the "`" symbol be used in quoting the name. Example: The following SQL script is work in MySql 5.7. /* *** in SQL: */ USE `blackjack`; CREATE PROCEDURE `app.create_game` ( INOUT _code CHAR(32) CHARSET latin1, IN _table INT UNSIGNED, IN _players TINYINT UNSIGNED, IN _min_wager INT UNSIGNED, OUT _result INT ) ... But the following code will get exception. /* in C# using System.Data; */ MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "`app.create_game`"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@_code" , DBNull.Value); command.Parameters.AddWithValue("@_table" , 1); command.Parameters.AddWithValue("@_players" , 4); command.Parameters.AddWithValue("@_min_wager" , 100); command.Parameters.Add("@_result", MySqlDbType.Int32); command.Parameters["@_code" ].Direction = ParameterDirection.InputOutput; command.Parameters["@_result"].Direction = ParameterDirection.Output; command.ExecuteNonQuery(); // <-- throw MySqlException: // Procedure or function 'create_game' cannot // be found in database 'app'. result = (int) command.Parameters["@_result"].Value; if (result == 0) { code = (string) command.Parameters["@_code"].Value; } How to repeat: Let the "`" symbol can be resolved. file: MySql.Data/StoredProcedure.cs <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @@ -104,9 private string FixProcedureName(string name) { string[] parts = name.Split('.'); for (int i = 0; i < parts.Length; i++) if (!parts[i].StartsWith("`", StringComparison.Ordinal)) parts[i] = String.Format("`{0}`", parts[i]); if (parts.Length == 1) return parts[0]; return String.Format("{0}.{1}", parts[0], parts[1]); } <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @@ -154,15 public override void Resolve(bool preparing) { // check to see if we are already resolved if (resolvedCommandText != null) return; serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing; // first retrieve the procedure definition from our // procedure cache string spName = commandText; if (spName.IndexOf(".") == -1 && !String.IsNullOrEmpty(Connection.Database)) spName = Connection.Database + "." + spName; spName = FixProcedureName(spName); MySqlParameter returnParameter = GetReturnParameter(); Suggested fix: file: MySql.Data/StoredProcedure.cs <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @@ -104,9 +104,31 @@ - private string FixProcedureName(string name) - { - string[] parts = name.Split('.'); - for (int i = 0; i < parts.Length; i++) - if (!parts[i].StartsWith("`", StringComparison.Ordinal)) - parts[i] = String.Format("`{0}`", parts[i]); - if (parts.Length == 1) return parts[0]; - return String.Format("{0}.{1}", parts[0], parts[1]); + private string FixProcedureName(string spName) + { + string schema = null; + string name = spName; + + int i = 0; + bool theCharWithinQuote = false; + while (i < spName.Length) + { + char ch = spName[i]; + switch (ch) + { + case '`': + theCharWithinQuote = !theCharWithinQuote; + break; + + case '.': + if (!theCharWithinQuote) + if (schema == null) + schema = spName.Substring(0, i); + break; + } + + if (schema != null) + break; + i++; + } + + if (!String.IsNullOrEmpty(schema)) + schema = NormalizeName(schema); + else if (!String.IsNullOrEmpty(Connection.Database)) + schema = NormalizeName(Connection.Database); + + if (i < spName.Length) + name = NormalizeName( + spName.Substring(i + 1, spName.Length - i - 1)); + + if (schema == null) + return name; + return schema + "." + name; } <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @@ -154,15 +154,12 @@ public override void Resolve(bool preparing) { // check to see if we are already resolved if (resolvedCommandText != null) return; serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing; // first retrieve the procedure definition from our // procedure cache - string spName = commandText; - if (spName.IndexOf(".") == -1 && !String.IsNullOrEmpty(Connection.Database)) - spName = Connection.Database + "." + spName; - spName = FixProcedureName(spName); + string spName = FixProcedureName(commandText); MySqlParameter returnParameter = GetReturnParameter();