#At file:///C:/Users/Reggie/work/connector-net/6.3/ based on revid:julio.casal@strippedmgqbmhf
977 Reggie Burnett 2011-05-04
Fixing bug #60366, Oracle bug #12425959 Calling stored procedure with output parameters may hugely decrease performance
This change is more significant than I would have liked in a GA but it fixes some significant performance issues and simplifies several execution paths in the code.
See release notes for a breakdown.
modified:
CHANGES
MySql.Data/Provider/Properties/Resources.Designer.cs
MySql.Data/Provider/Properties/Resources.resx
MySql.Data/Provider/Source/CommandBuilder.cs
MySql.Data/Provider/Source/Driver.cs
MySql.Data/Provider/Source/ISSchemaProvider.cs
MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs
MySql.Data/Provider/Source/ProcedureCache.cs
MySql.Data/Provider/Source/ResultSet.cs
MySql.Data/Provider/Source/StoredProcedure.cs
MySql.Data/Provider/Source/datareader.cs
MySql.Data/Provider/Source/parameter.cs
MySql.Data/Tests/Source/ConnectionStringBuilder.cs
MySql.Data/Tests/Source/ProcedureParameters.cs
MySql.Data/Tests/Source/StoredProcedure.cs
Release Notes.txt
=== modified file 'CHANGES'
=== modified file 'CHANGES'
--- a/CHANGES 2011-04-27 19:29:03 +0000
+++ b/CHANGES 2011-05-04 14:01:59 +0000
@@ -14,6 +14,8 @@
- Introduced workaround to unexpected query aborts (server 5.1+) when executing a datareader after a command.Cancel() (MySQL bug#60541).
- Fixed SetSite issues with Visual Studio 2010 integration (MySQL bug#60723, Oracle bug #12394470)
- modified schema1.sql in MySql.Web to remove ENGINE declaration on table creation (Oracle bug #12311974).
+- fixed performance issue with executing stored procedures with output parameters
+ (MySQL bug #60366, Oracle bug#12425959)
Version 6.3.6
- Fixed TracingDriver so that it normalizes long queries before truncation so we don't get exceptions
=== modified file 'MySql.Data/Provider/Properties/Resources.Designer.cs'
--- a/MySql.Data/Provider/Properties/Resources.Designer.cs 2010-04-27 18:31:24 +0000
+++ b/MySql.Data/Provider/Properties/Resources.Designer.cs 2011-05-04 14:01:59 +0000
@@ -1,7 +1,7 @@
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a tool.
-// Runtime Version:2.0.50727.4927
+// Runtime Version:4.0.30319.225
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
@@ -19,9 +19,9 @@
// class via a tool like ResGen or Visual Studio.
// To add or remove a member, edit your .ResX file then rerun ResGen
// with the /str option, or rebuild your VS project.
- [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "2.0.0.0")]
+#if !CF
+ [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "4.0.0.0")]
[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
-#if !CF
[global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]
#endif
public class Resources {
@@ -135,6 +135,15 @@
}
/// <summary>
+ /// Looks up a localized string similar to Parameters can only be derived for commands using the StoredProcedure command type..
+ /// </summary>
+ public static string CanNotDeriveParametersForTextCommands {
+ get {
+ return ResourceManager.GetString("CanNotDeriveParametersForTextCommands", resourceCulture);
+ }
+ }
+
+ /// <summary>
/// Looks up a localized string similar to MySqlCommandBuilder does not support multi-table statements.
/// </summary>
public static string CBMultiTableNotSupported {
@@ -776,6 +785,15 @@
}
/// <summary>
+ /// Looks up a localized string similar to Attempt to call stored function '{0}' without specifying a return parameter.
+ /// </summary>
+ public static string RoutineRequiresReturnParameter {
+ get {
+ return ResourceManager.GetString("RoutineRequiresReturnParameter", resourceCulture);
+ }
+ }
+
+ /// <summary>
/// Looks up a localized string similar to Connector/Net no longer supports server versions prior to 5.0.
/// </summary>
public static string ServerTooOld {
@@ -1091,7 +1109,7 @@
}
/// <summary>
- /// Looks up a localized string similar to Unable to retrieve stored routine parameters. Either grant access to the routine or add the 'Use Procedure Bodies=false' option to your connection string..
+ /// Looks up a localized string similar to Unable to retrieve stored procedure metadata for routine '{0}'. Either grant SELECT privilege to mysql.proc for this user or use "check parameters=false" with your connection string..
/// </summary>
public static string UnableToRetrieveParameters {
get {
@@ -1100,15 +1118,6 @@
}
/// <summary>
- /// Looks up a localized string similar to Unable to retrieve stored procedure metadata for routine '{0}'. Either grant SELECT privilege to mysql.proc for this user or use "use procedure bodies=false" with your connection string..
- /// </summary>
- public static string UnableToRetrieveSProcData {
- get {
- return ResourceManager.GetString("UnableToRetrieveSProcData", resourceCulture);
- }
- }
-
- /// <summary>
/// Looks up a localized string similar to Unable to start a second async operation while one is running..
/// </summary>
public static string UnableToStartSecondAsyncOp {
=== modified file 'MySql.Data/Provider/Properties/Resources.resx'
--- a/MySql.Data/Provider/Properties/Resources.resx 2010-03-11 21:04:56 +0000
+++ b/MySql.Data/Provider/Properties/Resources.resx 2011-05-04 14:01:59 +0000
@@ -300,8 +300,8 @@
<data name="UnableToConnectToHost" xml:space="preserve">
<value>Unable to connect to any of the specified MySQL hosts.</value>
</data>
- <data name="UnableToRetrieveSProcData" xml:space="preserve">
- <value>Unable to retrieve stored procedure metadata for routine '{0}'. Either grant SELECT privilege to mysql.proc for this user or use "use procedure bodies=false" with your connection string.</value>
+ <data name="UnableToRetrieveParameters" xml:space="preserve">
+ <value>Unable to retrieve stored procedure metadata for routine '{0}'. Either grant SELECT privilege to mysql.proc for this user or use "check parameters=false" with your connection string.</value>
</data>
<data name="NextResultIsClosed" xml:space="preserve">
<value>Invalid attempt to call NextResult when the reader is closed.</value>
@@ -348,9 +348,6 @@
<data name="UnableToDeriveParameters" xml:space="preserve">
<value>Unable to derive stored routine parameters. The 'Parameters' information schema table is not available and access to the stored procedure body has been disabled.</value>
</data>
- <data name="UnableToRetrieveParameters" xml:space="preserve">
- <value>Unable to retrieve stored routine parameters. Either grant access to the routine or add the 'Use Procedure Bodies=false' option to your connection string.</value>
- </data>
<data name="DefaultEncodingNotFound" xml:space="preserve">
<value>The default connection encoding was not found. Please report this as a bug along with your connection string and system details.</value>
</data>
@@ -453,10 +450,17 @@
<data name="UnableToEnableQueryAnalysis" xml:space="preserve">
<value>Unable to enable query analysis. Be sure the MySql.Data.EMTrace assembly is properly located and registered.</value>
</data>
+ <assembly alias="System.Windows.Forms" name="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<data name="keywords" type="System.Resources.ResXFileRef, System.Windows.Forms">
<value>keywords.txt;System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089;Windows-1252</value>
</data>
<data name="TraceQueryNormalized" xml:space="preserve">
<value>{0}: Query Normalized: {2}</value>
</data>
+ <data name="RoutineRequiresReturnParameter" xml:space="preserve">
+ <value>Attempt to call stored function '{0}' without specifying a return parameter</value>
+ </data>
+ <data name="CanNotDeriveParametersForTextCommands" xml:space="preserve">
+ <value>Parameters can only be derived for commands using the StoredProcedure command type.</value>
+ </data>
</root>
\ No newline at end of file
=== modified file 'MySql.Data/Provider/Source/CommandBuilder.cs'
--- a/MySql.Data/Provider/Source/CommandBuilder.cs 2010-08-18 19:52:04 +0000
+++ b/MySql.Data/Provider/Source/CommandBuilder.cs 2011-05-04 14:01:59 +0000
@@ -78,37 +78,45 @@
/// a valid stored procedure name.</exception>
public static void DeriveParameters(MySqlCommand command)
{
- // retrieve the proc definitino from the cache.
+ if (command.CommandType != CommandType.StoredProcedure)
+ throw new InvalidOperationException(Resources.CanNotDeriveParametersForTextCommands);
+
+ // retrieve the proc definition from the cache.
string spName = command.CommandText;
if (spName.IndexOf(".") == -1)
spName = command.Connection.Database + "." + spName;
- DataSet ds = command.Connection.ProcedureCache.GetProcedure(command.Connection, spName, null);
- if (!ds.Tables.Contains("Procedure Parameters"))
- throw new MySqlException(Resources.UnableToDeriveParameters);
- DataTable parameters = ds.Tables["Procedure Parameters"];
- DataTable procTable = ds.Tables["Procedures"];
- command.Parameters.Clear();
- foreach (DataRow row in parameters.Rows)
- {
- MySqlParameter p = new MySqlParameter();
- p.ParameterName = String.Format("@{0}", row["PARAMETER_NAME"]);
- if (row["ORDINAL_POSITION"].Equals(0) && p.ParameterName == "@")
- p.ParameterName = "@RETURN_VALUE";
- p.Direction = GetDirection(row);
- bool unsigned = StoredProcedure.GetFlags(row["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1;
- bool real_as_float = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
- p.MySqlDbType = MetaData.NameToType(row["DATA_TYPE"].ToString(),
- unsigned, real_as_float, command.Connection);
- if (!row["CHARACTER_MAXIMUM_LENGTH"].Equals(DBNull.Value))
- p.Size = (int)row["CHARACTER_MAXIMUM_LENGTH"];
- if (!row["NUMERIC_PRECISION"].Equals(DBNull.Value))
- p.Precision = Convert.ToByte(row["NUMERIC_PRECISION"]);
- if (!row["NUMERIC_SCALE"].Equals(DBNull.Value))
- p.Scale = Convert.ToByte(row["NUMERIC_SCALE"]);
- if (p.MySqlDbType == MySqlDbType.Set || p.MySqlDbType == MySqlDbType.Enum)
- p.PossibleValues = GetPossibleValues(row);
- command.Parameters.Add(p);
+ try
+ {
+ DataSet ds = command.Connection.ProcedureCache.GetProcedure(command.Connection, spName, null);
+ DataTable parameters = ds.Tables["Procedure Parameters"];
+ DataTable procTable = ds.Tables["Procedures"];
+ command.Parameters.Clear();
+ foreach (DataRow row in parameters.Rows)
+ {
+ MySqlParameter p = new MySqlParameter();
+ p.ParameterName = String.Format("@{0}", row["PARAMETER_NAME"]);
+ if (row["ORDINAL_POSITION"].Equals(0) && p.ParameterName == "@")
+ p.ParameterName = "@RETURN_VALUE";
+ p.Direction = GetDirection(row);
+ bool unsigned = StoredProcedure.GetFlags(row["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1;
+ bool real_as_float = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
+ p.MySqlDbType = MetaData.NameToType(row["DATA_TYPE"].ToString(),
+ unsigned, real_as_float, command.Connection);
+ if (!row["CHARACTER_MAXIMUM_LENGTH"].Equals(DBNull.Value))
+ p.Size = (int)row["CHARACTER_MAXIMUM_LENGTH"];
+ if (!row["NUMERIC_PRECISION"].Equals(DBNull.Value))
+ p.Precision = Convert.ToByte(row["NUMERIC_PRECISION"]);
+ if (!row["NUMERIC_SCALE"].Equals(DBNull.Value))
+ p.Scale = Convert.ToByte(row["NUMERIC_SCALE"]);
+ if (p.MySqlDbType == MySqlDbType.Set || p.MySqlDbType == MySqlDbType.Enum)
+ p.PossibleValues = GetPossibleValues(row);
+ command.Parameters.Add(p);
+ }
+ }
+ catch (InvalidOperationException ioe)
+ {
+ throw new MySqlException(Resources.UnableToDeriveParameters, ioe);
}
}
=== modified file 'MySql.Data/Provider/Source/Driver.cs'
--- a/MySql.Data/Provider/Source/Driver.cs 2011-04-08 17:03:15 +0000
+++ b/MySql.Data/Provider/Source/Driver.cs 2011-05-04 14:01:59 +0000
@@ -151,7 +151,7 @@
public bool SupportsOutputParameters
{
- get { return Version.isAtLeast(6,0,8); }
+ get { return Version.isAtLeast(5,5,0); }
}
public bool SupportsBatch
=== modified file 'MySql.Data/Provider/Source/ISSchemaProvider.cs'
--- a/MySql.Data/Provider/Source/ISSchemaProvider.cs 2010-11-17 22:57:36 +0000
+++ b/MySql.Data/Provider/Source/ISSchemaProvider.cs 2011-05-04 14:01:59 +0000
@@ -192,13 +192,13 @@
{
try
{
- if (connection.Settings.HasRootAccess)
+ if (connection.Settings.HasProcAccess)
return base.GetProcedures(restrictions);
}
catch (MySqlException ex)
{
if (ex.Number == (int)MySqlErrorCode.TableAccessDenied)
- connection.Settings.HasRootAccess = false;
+ connection.Settings.HasProcAccess = false;
else
throw;
}
@@ -344,17 +344,21 @@
public virtual DataTable GetProcedureParameters(string[] restrictions,
DataTable routines)
{
- if (connection.driver.Version.isAtLeast(6, 0, 6))
- return GetParametersFromIS(restrictions, routines);
+ bool is55 = connection.driver.Version.isAtLeast(5, 5, 0);
+
try
{
+ // we want to avoid using IS if we can as it is painfully slow
DataTable dt = CreateParametersTable();
GetParametersFromShowCreate(dt, restrictions, routines);
return dt;
}
- catch (InvalidOperationException ioe)
+ catch (Exception)
{
- throw new InvalidOperationException(Resources.UnableToRetrieveParameters, ioe);
+ if (!is55) throw;
+
+ // we get here by not having access and we are on 5.5 or later so just use IS
+ return GetParametersFromIS(restrictions, routines);
}
}
@@ -515,7 +519,7 @@
catch (SqlNullValueException snex)
{
throw new InvalidOperationException(
- String.Format(Resources.UnableToRetrieveSProcData, routine["ROUTINE_NAME"]), snex);
+ String.Format(Resources.UnableToRetrieveParameters, routine["ROUTINE_NAME"]), snex);
}
}
}
=== modified file 'MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs'
--- a/MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs 2011-03-03 18:02:14 +0000
+++ b/MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs 2011-05-04 14:01:59 +0000
@@ -41,7 +41,7 @@
new Dictionary<string, PropertyDefaultValue>(StringComparer.OrdinalIgnoreCase);
private Dictionary<string, object> values =
new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
- private bool hasRootAccess = true;
+ private bool hasProcAccess = true;
static MySqlConnectionStringBuilder()
{
@@ -475,11 +475,11 @@
[Description("Indicates if stored procedure bodies will be available for parameter detection.")]
[DefaultValue(true)]
[ValidKeywords("procedure bodies")]
- [RefreshProperties(RefreshProperties.All)]
+ [Obsolete("Use CheckParameters instead")]
public bool UseProcedureBodies
{
- get { return (bool)values["Use Procedure Bodies"]; }
- set { SetValue("Use Procedure Bodies", value); }
+ get { return (bool)values["Check Parameters"]; }
+ set { SetValue("Check Parameters", value); }
}
[Category("Advanced")]
@@ -592,6 +592,18 @@
set { SetValue("Sql Server Mode", value); }
}
+#if !CF
+ [Category("Advanced")]
+ [DisplayName("Check Parameters")]
+ [Description("Indicates if stored routine parameters should be checked against the server.")]
+ [DefaultValue(true)]
+#endif
+ public bool CheckParameters
+ {
+ get { return (bool)values["Check Parameters"]; }
+ set { SetValue("Check Parameters", value); }
+ }
+
#endregion
#region Pooling Properties
@@ -745,10 +757,10 @@
#endregion
- internal bool HasRootAccess
+ internal bool HasProcAccess
{
- get { return hasRootAccess; }
- set { hasRootAccess = value; }
+ get { return hasProcAccess; }
+ set { hasProcAccess = value; }
}
internal Regex GetBlobAsUTF8IncludeRegex()
@@ -852,10 +864,27 @@
val = ParseEnum(defaultValues[keyword].Type, (string)value, keyword);
else
val = ChangeType(value, defaultValues[keyword].Type);
+ HandleObsolete(keyword, val);
values[keyword] = val;
base[keyword] = val;
}
+ private void HandleObsolete(string keyword, object value)
+ {
+ if (String.Compare(keyword, "Use Old Syntax", true) == 0)
+ MySqlTrace.LogWarning(-1, "Use Old Syntax is now obsolete. Please see documentation");
+ else if (String.Compare(keyword, "Encrypt", true) == 0)
+ {
+ MySqlTrace.LogWarning(-1, "Encrypt is now obsolete. Use Ssl Mode instead");
+ Encrypt = (bool)value;
+ }
+ else if (String.Compare(keyword, "Use Procedure Bodies", true) == 0)
+ {
+ MySqlTrace.LogWarning(-1, "Use Procedure Bodies is now obsolete. Use Check Parameters instead");
+ CheckParameters = (bool)value;
+ }
+ }
+
private object ParseEnum(Type t, string requestedValue, string key)
{
try
@@ -887,10 +916,6 @@
string key = keyword.ToLower(CultureInfo.InvariantCulture);
if (!validKeywords.ContainsKey(key))
throw new ArgumentException(Resources.KeywordNotSupported, keyword);
- if (validKeywords[key] == "Use Old Syntax")
- MySqlTrace.LogWarning(-1, "Use Old Syntax is now obsolete. Please see documentation");
- if (validKeywords[key] == "Encrypt")
- MySqlTrace.LogWarning(-1, "Encrypt is now obsolete. Use Ssl Mode instead");
}
private static void Initialize()
=== modified file 'MySql.Data/Provider/Source/ProcedureCache.cs'
--- a/MySql.Data/Provider/Source/ProcedureCache.cs 2010-08-18 19:48:34 +0000
+++ b/MySql.Data/Provider/Source/ProcedureCache.cs 2011-05-04 14:01:59 +0000
@@ -158,12 +158,8 @@
// know the procedure we care about.
ISSchemaProvider isp = new ISSchemaProvider(connection);
string[] rest = isp.CleanRestrictions(restrictions);
- try
- {
- DataTable parametersTable = isp.GetProcedureParameters(rest, procTable);
- ds.Tables.Add(parametersTable);
- }
- catch (Exception) { }
+ DataTable parametersTable = isp.GetProcedureParameters(rest, procTable);
+ ds.Tables.Add(parametersTable);
return ds;
}
=== modified file 'MySql.Data/Provider/Source/ResultSet.cs'
--- a/MySql.Data/Provider/Source/ResultSet.cs 2010-08-18 19:52:04 +0000
+++ b/MySql.Data/Provider/Source/ResultSet.cs 2011-05-04 14:01:59 +0000
@@ -64,7 +64,7 @@
this.statementId = statementId;
rowIndex = -1;
LoadColumns(numCols);
- isOutputParameters = driver.HasStatus(ServerStatusFlags.OutputParameters);
+ isOutputParameters = IsOutputParameterResultSet();
hasRows = GetNextRow();
readDone = !hasRows;
}
@@ -258,6 +258,17 @@
values[i] = valueObject;
}
+ private bool IsOutputParameterResultSet()
+ {
+ if (driver.HasStatus(ServerStatusFlags.OutputParameters)) return true;
+
+ if (fields.Length == 0) return false;
+
+ for (int x = 0; x < fields.Length; x++)
+ if (!fields[x].ColumnName.StartsWith("@" + StoredProcedure.ParameterPrefix)) return false;
+ return true;
+ }
+
/// <summary>
/// Loads the column metadata for the current resultset
/// </summary>
=== modified file 'MySql.Data/Provider/Source/StoredProcedure.cs'
--- a/MySql.Data/Provider/Source/StoredProcedure.cs 2010-10-07 22:00:39 +0000
+++ b/MySql.Data/Provider/Source/StoredProcedure.cs 2011-05-04 14:01:59 +0000
@@ -37,6 +37,7 @@
private string outSelect;
private DataTable parametersTable;
private string resolvedCommandText;
+ private bool serverProvidingOutputParameters;
// Prefix used for to generate inout or output parameters names
internal const string ParameterPrefix = "_cnet_param_";
@@ -46,12 +47,12 @@
{
}
- private string GetReturnParameter()
+ private MySqlParameter GetReturnParameter()
{
if (Parameters != null)
foreach (MySqlParameter p in Parameters)
if (p.Direction == ParameterDirection.ReturnValue)
- return p.ParameterName.Substring(1);
+ return p;
return null;
}
@@ -85,67 +86,10 @@
{
string procCacheKey = GetCacheKey(procName);
DataSet ds = Connection.ProcedureCache.GetProcedure(Connection, procName, procCacheKey);
-
- if(ds.Tables.Count == 2)
+ lock (ds)
{
- // if we got our parameters and our user says it is ok to use proc bodies
- // then just return them
- if (Connection.Settings.UseProcedureBodies)
- {
- lock(ds)
- {
- proceduresTable = ds.Tables["procedures"];
- parametersTable = ds.Tables["procedure parameters"];
- return;
- }
- }
- }
-
- lock(ds)
- {
proceduresTable = ds.Tables["procedures"];
- }
- // we were not able to retrieve parameter data so we have to make do by
- // adding the parameters from the command object to our table
- // we use an internal method to create our procedure parameters table.
- ISSchemaProvider sp = new ISSchemaProvider(Connection);
- parametersTable = sp.CreateParametersTable();
-
- // now we run through the parameters that were set and fill in the parameters table
- // the best we can
- int pos = 1;
- foreach (MySqlParameter p in command.Parameters)
- {
- // in this mode, all parameters must have their type set
- if (!p.TypeHasBeenSet)
- throw new InvalidOperationException(Resources.NoBodiesAndTypeNotSet);
-
- DataRow row = parametersTable.NewRow();
- row["PARAMETER_NAME"] = p.ParameterName;
- row["PARAMETER_MODE"] = "IN";
- if (p.Direction == ParameterDirection.InputOutput)
- row["PARAMETER_MODE"] = "INOUT";
- else if (p.Direction == ParameterDirection.Output)
- row["PARAMETER_MODE"] = "OUT";
- else if (p.Direction == ParameterDirection.ReturnValue)
- {
- row["PARAMETER_MODE"] = "OUT";
- row["ORDINAL_POSITION"] = 0;
- }
- else
- row["ORDINAL_POSITION"] = pos++;
- parametersTable.Rows.Add(row);
- }
- if (Connection.Settings.UseProcedureBodies)
- {
- lock (ds)
- {
- // we got the parameters, but ignore them.
- if (ds.Tables.Contains("Procedure Parameters"))
- ds.Tables.Remove("Procedure Parameters");
-
- ds.Tables.Add(parametersTable);
- }
+ parametersTable = ds.Tables["procedure parameters"];
}
}
@@ -167,18 +111,20 @@
return String.Format("{0}.{1}", parts[0], parts[1]);
}
- private MySqlParameter GetAndFixParameter(DataRow param, bool realAsFloat, string returnParameter)
+ private MySqlParameter GetAndFixParameter(string spName, DataRow param, bool realAsFloat, MySqlParameter returnParameter)
{
string mode = (string)param["PARAMETER_MODE"];
string pName = (string)param["PARAMETER_NAME"];
if (param["ORDINAL_POSITION"].Equals(0))
- pName = returnParameter;
-
- if (pName == null) return null;
-
- // make sure the parameters given to us have an appropriate
- // type set if it's not already
+ {
+ if (returnParameter == null)
+ throw new InvalidOperationException(
+ String.Format(Resources.RoutineRequiresReturnParameter, spName));
+ pName = returnParameter.ParameterName;
+ }
+
+ // make sure the parameters given to us have an appropriate type set if it's not already
MySqlParameter p = command.Parameters.GetParameterFlexible(pName, true);
if (!p.TypeHasBeenSet)
{
@@ -189,6 +135,23 @@
return p;
}
+ private MySqlParameterCollection CheckParameters(string spName)
+ {
+ MySqlParameterCollection newParms = new MySqlParameterCollection(command);
+ MySqlParameter returnParameter = GetReturnParameter();
+
+ DataTable procTable;
+ GetParameters(spName, out procTable, out parametersTable);
+ if (procTable.Rows.Count == 0)
+ throw new InvalidOperationException(String.Format(Resources.RoutineNotFound, spName));
+
+ bool realAsFloat = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
+
+ foreach (DataRow param in parametersTable.Rows)
+ newParms.Add(GetAndFixParameter(spName, param, realAsFloat, returnParameter));
+ return newParms;
+ }
+
public override void Resolve(bool preparing)
{
// check to see if we are already resolved
@@ -201,135 +164,58 @@
spName = Connection.Database + "." + spName;
spName = FixProcedureName(spName);
- DataTable procTable;
- GetParameters(spName,out procTable, out parametersTable);
-
- if (procTable.Rows.Count == 0)
- throw new InvalidOperationException(String.Format(Resources.RoutineNotFound, spName));
-
- bool realAsFloat = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
- StringBuilder sqlStr = new StringBuilder();
- StringBuilder outSql = new StringBuilder();
- string sqlDelimiter = "";
- string outDelimiter = "";
-
- string retParm = GetReturnParameter();
- foreach (DataRow param in parametersTable.Rows)
+ MySqlParameter returnParameter = GetReturnParameter();
+
+ MySqlParameterCollection parms = command.Connection.Settings.CheckParameters ?
+ CheckParameters(spName) : Parameters;
+
+ StringBuilder setSql = new StringBuilder();
+ StringBuilder callSql = new StringBuilder();
+ StringBuilder selectSql = new StringBuilder();
+ string callDelimiter = String.Empty;
+ string selectDelimiter = String.Empty;
+ serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing;
+
+ foreach (MySqlParameter p in parms)
{
- MySqlParameter p = GetAndFixParameter(param, realAsFloat, retParm);
- if (p == null) continue;
-
- if (param["ORDINAL_POSITION"].Equals(0))
- continue;
-
- string baseName = p.ParameterName;
- string pName = baseName;
- if (baseName.StartsWith("@") || baseName.StartsWith("?"))
- baseName = baseName.Substring(1);
- else
+ if (p.Direction == ParameterDirection.ReturnValue) continue;
+
+ string pName = p.ParameterName;
+ if (!pName.StartsWith("@") && !pName.StartsWith("?"))
pName = "@" + pName;
-
- string inputVar = pName;
- if (p.Direction != ParameterDirection.Input &&
- !(Connection.driver.SupportsOutputParameters || preparing))
+ string parameterName = pName;
+ if (p.Direction != ParameterDirection.Input && !serverProvidingOutputParameters)
{
- // set a user variable to our current value
- string sql = String.Format("SET @{0}{1}={2}", ParameterPrefix, baseName, pName);
- MySqlCommand cmd = new MySqlCommand(sql, Connection);
-
- cmd.Parameters.Add(p);
- cmd.ExecuteNonQuery();
-
- inputVar = String.Format("@{0}{1}", ParameterPrefix, baseName);
-
- outSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", outDelimiter, inputVar);
- outDelimiter = ", ";
+ pName = String.Format("@{0}{1}", ParameterPrefix, p.BaseName);
+ if (p.Direction == ParameterDirection.InputOutput)
+ setSql.AppendFormat(CultureInfo.InvariantCulture, "SET {0}={1};", pName, parameterName);
+ selectSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", selectDelimiter, pName);
+ selectDelimiter = ", ";
}
- sqlStr.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", sqlDelimiter, inputVar);
- sqlDelimiter = ", ";
+ callSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", callDelimiter, pName);
+ callDelimiter = ", ";
}
- string sqlCmd = sqlStr.ToString().TrimEnd(' ', ',');
- outSelect = outSql.ToString().TrimEnd(' ', ',');
+ string sqlCmd = String.Empty;
- if (procTable.Rows[0]["ROUTINE_TYPE"].Equals("PROCEDURE"))
- sqlCmd = String.Format("call {0} ({1})", spName, sqlCmd);
+ if (returnParameter == null)
+ sqlCmd = String.Format("CALL {0} ({1})", spName, callSql.ToString());
else
{
- if (retParm == null)
- retParm = ParameterPrefix + "dummy";
- else
- outSelect = String.Format("@{0}{1}", ParameterPrefix, retParm);
- sqlCmd = String.Format("SET @{0}{1}={2}({3})", ParameterPrefix, retParm, spName, sqlCmd);
+ string returnParameterName = returnParameter.BaseName;
+ if (String.IsNullOrEmpty(returnParameterName))
+ returnParameterName = "dummy";
+
+ sqlCmd = String.Format("SET @{0}{1}={2}({3})", ParameterPrefix, returnParameterName, spName, callSql.ToString());
+ selectSql.AppendFormat(CultureInfo.InvariantCulture,
+ "{0}@{1}{2}", selectSql.ToString(), ParameterPrefix, returnParameterName);
}
+ if (setSql.Length > 0)
+ sqlCmd = String.Format("{0}{1}", setSql.ToString(), sqlCmd);
+ if (selectSql.Length > 0)
+ sqlCmd = String.Format("{0}; SELECT {1}", sqlCmd, selectSql.ToString());
resolvedCommandText = sqlCmd;
}
-
- private MySqlDataReader GetHackedOuputParameters()
- {
- if (outSelect.Length == 0) return null;
-
- MySqlCommand cmd = new MySqlCommand("SELECT " + outSelect, Connection);
-
- MySqlDataReader reader = cmd.ExecuteReader();
- // since MySQL likes to return user variables as strings
- // we reset the types of the readers internal value objects
- // this will allow those value objects to parse the string based
- // return values
- ResultSet results = reader.ResultSet;
- for (int i = 0; i < reader.FieldCount; i++)
- {
- string fieldName = reader.GetName(i);
- fieldName = fieldName.Remove(0, ParameterPrefix.Length + 1);
- MySqlParameter parameter = Parameters.GetParameterFlexible(fieldName, true);
-
- IMySqlValue v = MySqlField.GetIMySqlValue(parameter.MySqlDbType);
- if (v is MySqlBit)
- {
- MySqlBit bit = (MySqlBit)v;
- bit.ReadAsString = true;
- results.SetValueObject(i, bit);
- }
- else
- results.SetValueObject(i, v);
- }
- if (!reader.Read())
- {
- reader.Close();
- return null;
- }
- return reader;
- }
-
- public override void Close(MySqlDataReader reader)
- {
- base.Close(reader);
-
- ResultSet rs = reader.ResultSet;
- // if our closing reader doesn't have output parameters then we may have to
- // use the user variable hack
- if (rs == null || !rs.IsOutputParameters)
- {
- MySqlDataReader rdr = GetHackedOuputParameters();
- if (rdr == null) return;
- reader = rdr;
- }
-
- using (reader)
- {
- string prefix = "@" + ParameterPrefix;
-
- for (int i = 0; i < reader.FieldCount; i++)
- {
- string fieldName = reader.GetName(i);
- if (fieldName.StartsWith(prefix))
- fieldName = fieldName.Remove(0, prefix.Length);
- MySqlParameter parameter = Parameters.GetParameterFlexible(fieldName, true);
- parameter.Value = reader.GetValue(i);
- }
- reader.Close();
- }
- }
}
}
=== modified file 'MySql.Data/Provider/Source/datareader.cs'
--- a/MySql.Data/Provider/Source/datareader.cs 2011-04-08 17:03:15 +0000
+++ b/MySql.Data/Provider/Source/datareader.cs 2011-05-04 14:01:59 +0000
@@ -95,6 +95,11 @@
get { return resultSet; }
}
+ internal CommandBehavior CommandBehavior
+ {
+ get { return commandBehavior; }
+ }
+
/// <summary>
/// Gets a value indicating the depth of nesting for the current row. This method is not
/// supported currently and always returns 0.
@@ -899,8 +904,14 @@
{
resultSet = null;
resultSet = driver.NextResult(Statement.StatementId);
+
if (resultSet == null) return false;
- if (resultSet.IsOutputParameters) return false;
+
+ if (resultSet.IsOutputParameters)
+ {
+ ProcessOutputParameters();
+ return false;
+ }
if (resultSet.Size == 0)
{
@@ -989,6 +1000,55 @@
IDataReader reader = dummyCommand.ExecuteReader(); // ExecuteReader catches the exception and returns null, which is expected.
}
+ private void ProcessOutputParameters()
+ {
+ // if we are not 5.5 or later or we are not prepared then we are simulating output parameters
+ // with user variables and they are also string so we have to work some magic with out
+ // column types before we read the data
+ if (!driver.SupportsOutputParameters || !command.IsPrepared)
+ AdjustOutputTypes();
+
+ // now read the output parameters data row
+ if ((commandBehavior & System.Data.CommandBehavior.SchemaOnly) != 0) return;
+ resultSet.NextRow(commandBehavior);
+
+ string prefix = "@" + StoredProcedure.ParameterPrefix;
+
+ for (int i = 0; i < FieldCount; i++)
+ {
+ string fieldName = GetName(i);
+ if (fieldName.StartsWith(prefix))
+ fieldName = fieldName.Remove(0, prefix.Length);
+ MySqlParameter parameter = command.Parameters.GetParameterFlexible(fieldName, true);
+ parameter.Value = GetValue(i);
+ }
+ }
+
+ private void AdjustOutputTypes()
+ {
+ // since MySQL likes to return user variables as strings
+ // we reset the types of the readers internal value objects
+ // this will allow those value objects to parse the string based
+ // return values
+ for (int i = 0; i < FieldCount; i++)
+ {
+ string fieldName = GetName(i);
+ fieldName = fieldName.Remove(0, StoredProcedure.ParameterPrefix.Length + 1);
+ MySqlParameter parameter = command.Parameters.GetParameterFlexible(fieldName, true);
+
+ IMySqlValue v = MySqlField.GetIMySqlValue(parameter.MySqlDbType);
+ if (v is MySqlBit)
+ {
+ MySqlBit bit = (MySqlBit)v;
+ bit.ReadAsString = true;
+ resultSet.SetValueObject(i, bit);
+ }
+ else
+ resultSet.SetValueObject(i, v);
+ }
+ }
+
+
#region IEnumerator
/// <summary>
=== modified file 'MySql.Data/Provider/Source/parameter.cs'
--- a/MySql.Data/Provider/Source/parameter.cs 2010-08-18 19:48:34 +0000
+++ b/MySql.Data/Provider/Source/parameter.cs 2011-05-04 14:01:59 +0000
@@ -180,6 +180,16 @@
set { encoding = value; }
}
+ internal string BaseName
+ {
+ get
+ {
+ if (ParameterName.StartsWith("@") || ParameterName.StartsWith("?"))
+ return ParameterName.Substring(1);
+ return ParameterName;
+ }
+ }
+
/// <summary>
/// Gets or sets the <see cref="DbType"/> of the parameter.
/// </summary>
=== modified file 'MySql.Data/Tests/Source/ConnectionStringBuilder.cs'
--- a/MySql.Data/Tests/Source/ConnectionStringBuilder.cs 2011-03-03 18:01:04 +0000
+++ b/MySql.Data/Tests/Source/ConnectionStringBuilder.cs 2011-05-04 14:01:59 +0000
@@ -133,5 +133,19 @@
Assert.IsFalse(s.ContainsKey("badkey"));
}
#endif
+
+ [Test]
+ public void UseProcedureBodiesSettingCheckParameters()
+ {
+ MySqlConnectionStringBuilder s = new MySqlConnectionStringBuilder("server=localhost;use procedure bodies=false");
+ Assert.IsFalse(s.CheckParameters);
+ }
+
+ [Test]
+ public void EncrpytSslmode()
+ {
+ MySqlConnectionStringBuilder s = new MySqlConnectionStringBuilder("server=localhost;encrypt=true");
+ Assert.AreEqual(s.SslMode, MySqlSslMode.Preferred);
+ }
}
}
=== modified file 'MySql.Data/Tests/Source/ProcedureParameters.cs'
--- a/MySql.Data/Tests/Source/ProcedureParameters.cs 2010-08-18 19:48:34 +0000
+++ b/MySql.Data/Tests/Source/ProcedureParameters.cs 2011-05-04 14:01:59 +0000
@@ -30,12 +30,6 @@
[TestFixture]
public class ProcedureParameterTests : BaseTest
{
- public override void Setup()
- {
- accessToMySqlDb = true;
- base.Setup();
- }
-
[Test]
public void ProcedureParameters()
{
=== modified file 'MySql.Data/Tests/Source/StoredProcedure.cs'
--- a/MySql.Data/Tests/Source/StoredProcedure.cs 2010-10-06 20:39:51 +0000
+++ b/MySql.Data/Tests/Source/StoredProcedure.cs 2011-05-04 14:01:59 +0000
@@ -912,8 +912,14 @@
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?p_kiosk", 2);
cmd.Parameters.AddWithValue("?p_user", 4);
- cmd.ExecuteNonQuery();
- Assert.AreEqual(2, cmd.Parameters.Count);
+ try
+ {
+ cmd.ExecuteNonQuery();
+ Assert.Fail();
+ }
+ catch (InvalidOperationException)
+ {
+ }
}
/// <summary>
=== modified file 'Release Notes.txt'
--- a/Release Notes.txt 2010-09-01 17:50:11 +0000
+++ b/Release Notes.txt 2011-05-04 14:01:59 +0000
@@ -11,7 +11,6 @@
- The ability to enable query analysis at runtime
- New SQL editor
-
What we know is broken
----------------------
- Documentation has not been integrated into VS2010 yet. We are having trouble with silent integration
@@ -20,6 +19,31 @@
- The Generated Database Wizard will save the file with the extension .sql. You will need to
manually change the extension to .mysql to use the new editor.
+What we changed in 6.3.7
+------------------------
+We introduced a slight change in behavior with 6.3.7. We did this as part of a larger change to address a performance
+issue. It's a relatively minor change in behavior however we encourage you to read this section carefully and review your
+application for areas that might be affected.
+
+We have made the Use Procedure Bodies flag obsolete and introduced the Check Parameters option. They server mainly the
+same purpose. The Check Parameters option is true by default. Setting to false tells
+Connector/Net to not fetch any routine or parameter metadata and to simply trust what the user has specified. This can greatly
+increase performance but it also puts significant pressure on the application developer to give the parameters in the right
+order.
+
+The next change in behavior is that Connector/Net no longer allows you to call a stored function without specifying a return value.
+We are now using the presence of a return value parameter as a signal that we should execute the routine as a stored function. If
+you don't care about the return value, then give a place holder value.
+
+The next change is that if you user has insufficient permissions to retrieve parameter metadata,
+you are not connecting to server 5.5 or later, and you have specified Check Parameters then the connector will throw an
+InvalidOperationException indicating that you have insufficient privileges to retrieve routine parameter metadata. In the past the
+connector would silently use the parameters you gave on the command.
+
+We apologize for introducing these changes in a GA product but we felt it was important as these changes greatly increase the
+speed that we execute stored routines.
+
+
Attachment: [text/bzr-bundle] bzr/reggie.burnett@oracle.com-20110504140159-zct7s2wljm2e4ndn.bundle
| Thread |
|---|
| • bzr commit into connector-net-6.3 branch (reggie.burnett:977) Bug#60366Bug#12425959 | Reggie Burnett | 4 May |
