Modified:
branches/5.2/CHANGES
branches/5.2/Driver/Source/Statement.cs
branches/5.2/Driver/Source/StoredProcedure.cs
branches/5.2/TestSuite/Source/StoredProcedure.cs
Log:
- Fixed new parameter handling code so that procedures and functions now work
(bug #34699).
The problem was that to implement output parameters with MySQL we have to use user variables but executing a sproc or function on a connection that is not allowing user variables would throw a missing parameter exception. We fixed that by adding code that will check to see if the parameter starts with the sproc hash and ignoring it if so.
Modified: branches/5.2/CHANGES
===================================================================
--- branches/5.2/CHANGES 2008-02-19 16:16:18 UTC (rev 1185)
+++ branches/5.2/CHANGES 2008-02-21 21:38:37 UTC (rev 1186)
@@ -1,5 +1,7 @@
Version 5.2.1
- Tons of fixes in providers. The actually work now. :)
+- Fixed new parameter handling code so that procedures and functions now work
+ (bug #34699)
Version 5.2 - 2/11/2008
. Added ClearPool and ClearAllPools features
Modified: branches/5.2/Driver/Source/Statement.cs
===================================================================
--- branches/5.2/Driver/Source/Statement.cs 2008-02-19 16:16:18 UTC (rev 1185)
+++ branches/5.2/Driver/Source/Statement.cs 2008-02-21 21:38:37 UTC (rev 1186)
@@ -23,6 +23,7 @@
using System.IO;
using System.Text;
using MySql.Data.Common;
+using System.Data;
namespace MySql.Data.MySqlClient
{
@@ -204,6 +205,12 @@
return parameters[index];
}
*/
+
+ protected virtual bool ShouldIgnoreMissingParameter(string parameterName)
+ {
+ return Connection.Settings.AllowUserVariables;
+ }
+
/// <summary>
/// Serializes the given parameter to the given memory stream
/// </summary>
@@ -221,7 +228,7 @@
{
// if we are allowing user variables and the parameter name starts with @
// then we can't throw an exception
- if (parmName.StartsWith("@") && Connection.Settings.AllowUserVariables)
+ if (parmName.StartsWith("@") && ShouldIgnoreMissingParameter(parmName))
return false;
throw new MySqlException(
String.Format(Resources.ParameterMustBeDefined, parmName));
Modified: branches/5.2/Driver/Source/StoredProcedure.cs
===================================================================
--- branches/5.2/Driver/Source/StoredProcedure.cs 2008-02-19 16:16:18 UTC (rev 1185)
+++ branches/5.2/Driver/Source/StoredProcedure.cs 2008-02-21 21:38:37 UTC (rev 1186)
@@ -55,6 +55,12 @@
return null;
}
+ protected override bool ShouldIgnoreMissingParameter(string parameterName)
+ {
+ if (parameterName.StartsWith("@" + hash)) return true;
+ return base.ShouldIgnoreMissingParameter(parameterName);
+ }
+
public override string ResolvedCommandText
{
get { return resolvedCommandText; }
@@ -179,7 +185,7 @@
retParm = hash + "dummy";
else
outSelect = String.Format("@{0}", retParm);
- sqlCmd = String.Format("set @{0}={1}({2})", retParm, commandText, sqlCmd);
+ sqlCmd = String.Format("SET @{0}={1}({2})", retParm, commandText, sqlCmd);
}
if (setStr.Length > 0)
@@ -194,32 +200,41 @@
if (outSelect.Length == 0) return;
- 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
- for (int i = 0; i < reader.FieldCount; i++)
+ bool allowUserVar = Connection.Settings.AllowUserVariables;
+ Connection.Settings.AllowUserVariables = true;
+ try
{
- string fieldName = reader.GetName(i);
- fieldName = fieldName.Remove(0, hash.Length + 1);
- MySqlParameter parameter = Parameters.GetParameterFlexible(fieldName, true);
- reader.values[i] = MySqlField.GetIMySqlValue(parameter.MySqlDbType);
- }
+ MySqlCommand cmd = new MySqlCommand("SELECT " + outSelect, Connection);
+ using (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
+ for (int i = 0; i < reader.FieldCount; i++)
+ {
+ string fieldName = reader.GetName(i);
+ fieldName = fieldName.Remove(0, hash.Length + 1);
+ MySqlParameter parameter = Parameters.GetParameterFlexible(fieldName, true);
+ reader.values[i] = MySqlField.GetIMySqlValue(parameter.MySqlDbType);
+ }
- if (reader.Read())
- {
- for (int i = 0; i < reader.FieldCount; i++)
- {
- string fieldName = reader.GetName(i);
- fieldName = fieldName.Remove(0, hash.Length + 1);
- MySqlParameter parameter = Parameters.GetParameterFlexible(fieldName, true);
- parameter.Value = reader.GetValue(i);
+ if (reader.Read())
+ {
+ for (int i = 0; i < reader.FieldCount; i++)
+ {
+ string fieldName = reader.GetName(i);
+ fieldName = fieldName.Remove(0, hash.Length + 1);
+ MySqlParameter parameter = Parameters.GetParameterFlexible(fieldName, true);
+ parameter.Value = reader.GetValue(i);
+ }
+ }
}
}
- reader.Close();
+ finally
+ {
+ Connection.Settings.AllowUserVariables = allowUserVar;
+ }
}
}
}
Modified: branches/5.2/TestSuite/Source/StoredProcedure.cs
===================================================================
--- branches/5.2/TestSuite/Source/StoredProcedure.cs 2008-02-19 16:16:18 UTC (rev 1185)
+++ branches/5.2/TestSuite/Source/StoredProcedure.cs 2008-02-21 21:38:37 UTC (rev 1186)
@@ -546,9 +546,9 @@
"val6 VARCHAR(155), val7 SET('a','b'), val8 CHAR, val9 NUMERIC(10,2)) " +
"BEGIN SELECT 1; END");
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- MySqlCommandBuilder.DeriveParameters(cmd);
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ MySqlCommandBuilder.DeriveParameters(cmd);
Assert.AreEqual(9, cmd.Parameters.Count);
Assert.AreEqual("@valin", cmd.Parameters[0].ParameterName);
@@ -587,14 +587,14 @@
Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[8].Direction);
Assert.AreEqual(MySqlDbType.NewDecimal, cmd.Parameters[8].MySqlDbType);
- execSQL("DROP PROCEDURE spTest");
- execSQL("CREATE PROCEDURE spTest() BEGIN END");
- cmd.CommandText = "spTest";
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Clear();
- MySqlCommandBuilder.DeriveParameters(cmd);
- Assert.AreEqual(0, cmd.Parameters.Count);
- }
+ execSQL("DROP PROCEDURE spTest");
+ execSQL("CREATE PROCEDURE spTest() BEGIN END");
+ cmd.CommandText = "spTest";
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Clear();
+ MySqlCommandBuilder.DeriveParameters(cmd);
+ Assert.AreEqual(0, cmd.Parameters.Count);
+ }
/// <summary>
/// Bug #13632 the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5
@@ -609,9 +609,9 @@
execSQL("CREATE FUNCTION fnTest(v1 DATETIME) RETURNS INT " +
" LANGUAGE SQL DETERMINISTIC BEGIN RETURN 1; END");
- MySqlCommand cmd = new MySqlCommand("fnTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- MySqlCommandBuilder.DeriveParameters(cmd);
+ MySqlCommand cmd = new MySqlCommand("fnTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ MySqlCommandBuilder.DeriveParameters(cmd);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual("@v1", cmd.Parameters[0].ParameterName);
@@ -818,54 +818,54 @@
#if !CF
[Explicit]
- [Test]
- public void ProcedureCache()
- {
+ [Test]
+ public void ProcedureCache()
+ {
if (version < new Version(5, 0)) return;
// open a new connection using a procedure cache
- string connStr = GetConnectionString(true);
- connStr += ";procedure cache size=25;logging=true";
- MySqlConnection c = new MySqlConnection(connStr);
- try
- {
- c.Open();
+ string connStr = GetConnectionString(true);
+ connStr += ";procedure cache size=25;logging=true";
+ MySqlConnection c = new MySqlConnection(connStr);
+ try
+ {
+ c.Open();
- // install our custom trace listener
- GenericListener myListener = new GenericListener();
- System.Diagnostics.Trace.Listeners.Add(myListener);
+ // install our custom trace listener
+ GenericListener myListener = new GenericListener();
+ System.Diagnostics.Trace.Listeners.Add(myListener);
- for (int x = 0; x < 10; x++)
- {
- execSQL("DROP PROCEDURE IF EXISTS spTest" + x);
- execSQL("CREATE PROCEDURE spTest" + x + "() BEGIN SELECT 1; END");
- MySqlCommand cmd = new MySqlCommand("spTest" + x, c);
- cmd.CommandType = CommandType.StoredProcedure;
- for (int y = 0; y < 20; y++)
- {
- cmd.ExecuteNonQuery();
- }
- }
+ for (int x = 0; x < 10; x++)
+ {
+ execSQL("DROP PROCEDURE IF EXISTS spTest" + x);
+ execSQL("CREATE PROCEDURE spTest" + x + "() BEGIN SELECT 1; END");
+ MySqlCommand cmd = new MySqlCommand("spTest" + x, c);
+ cmd.CommandType = CommandType.StoredProcedure;
+ for (int y = 0; y < 20; y++)
+ {
+ cmd.ExecuteNonQuery();
+ }
+ }
- // remove our custom trace listener
- System.Diagnostics.Trace.Listeners.Remove(myListener);
+ // remove our custom trace listener
+ System.Diagnostics.Trace.Listeners.Remove(myListener);
- // now see how many times our listener recorded a cache hit
- Assert.AreEqual(190, myListener.Find("from procedure cache"));
- Assert.AreEqual(10, myListener.Find("from server"));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (c != null)
- c.Close();
- for (int x = 0; x < 10; x++)
- execSQL("DROP PROCEDURE IF EXISTS spTest" + x);
- }
- }
+ // now see how many times our listener recorded a cache hit
+ Assert.AreEqual(190, myListener.Find("from procedure cache"));
+ Assert.AreEqual(10, myListener.Find("from server"));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (c != null)
+ c.Close();
+ for (int x = 0; x < 10; x++)
+ execSQL("DROP PROCEDURE IF EXISTS spTest" + x);
+ }
+ }
#endif
/// <summary>
@@ -967,37 +967,37 @@
#if !CF
- /// <summary>
- /// Bug #22452 MySql.Data.MySqlClient.MySqlException:
- /// </summary>
- [Test]
- public void TurkishStoredProcs()
- {
+ /// <summary>
+ /// Bug #22452 MySql.Data.MySqlClient.MySqlException:
+ /// </summary>
+ [Test]
+ public void TurkishStoredProcs()
+ {
if (version < new Version(5, 0)) return;
execSQL("CREATE PROCEDURE spTest(IN p_paramname INT) BEGIN SELECT p_paramname; END");
- CultureInfo uiCulture = Thread.CurrentThread.CurrentUICulture;
- CultureInfo culture = Thread.CurrentThread.CurrentCulture;
- Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR");
- Thread.CurrentThread.CurrentUICulture = new CultureInfo("tr-TR");
+ CultureInfo uiCulture = Thread.CurrentThread.CurrentUICulture;
+ CultureInfo culture = Thread.CurrentThread.CurrentCulture;
+ Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR");
+ Thread.CurrentThread.CurrentUICulture = new CultureInfo("tr-TR");
- try
- {
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.Parameters.AddWithValue("?p_paramname", 2);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.ExecuteScalar();
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- Thread.CurrentThread.CurrentCulture = culture;
- Thread.CurrentThread.CurrentUICulture = uiCulture;
- }
- }
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.Parameters.AddWithValue("?p_paramname", 2);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.ExecuteScalar();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ Thread.CurrentThread.CurrentCulture = culture;
+ Thread.CurrentThread.CurrentUICulture = uiCulture;
+ }
+ }
#endif
@@ -1386,5 +1386,60 @@
double balance = Convert.ToDouble(command.Parameters["?Balance"].Value);
Assert.AreEqual(1.0, balance);
}
+
+ /// <summary>
+ /// </summary>
+ [Test]
+ public void OutputParametersWithNewParamHandling()
+ {
+ if (version < new Version(5, 0)) return;
+
+ // create our procedure
+ execSQL("CREATE PROCEDURE spTest(out val1 VARCHAR(350)) " +
+ "BEGIN SET val1 = '42'; END");
+
+ string connStr = GetConnectionString(true);
+ connStr = connStr.Replace("allow user variables=true", "allow user variables=false");
+ using (MySqlConnection c = new MySqlConnection(connStr))
+ {
+ c.Open();
+
+ MySqlCommand cmd = new MySqlCommand("spTest", c);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add(new MySqlParameter("@val1", MySqlDbType.VarChar)).Direction = ParameterDirection.Output;
+ int rowsAffected = cmd.ExecuteNonQuery();
+
+ Assert.AreEqual(0, rowsAffected);
+ Assert.AreEqual("42", cmd.Parameters[0].Value);
+ }
+ }
+
+ /// <summary>
+ /// </summary>
+ [Test]
+ public void FunctionWithNewParamHandling()
+ {
+ if (version < new Version(5, 0)) return;
+
+ // create our procedure
+ execSQL("CREATE FUNCTION spTest(`value` INT) RETURNS INT " +
+ "BEGIN RETURN value; END");
+
+ string connStr = GetConnectionString(true);
+ connStr = connStr.Replace("allow user variables=true", "allow user variables=false");
+ using (MySqlConnection c = new MySqlConnection(connStr))
+ {
+ c.Open();
+
+ MySqlCommand cmd = new MySqlCommand("spTest", c);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add(new MySqlParameter("@value", MySqlDbType.Int32)).Value = 22;
+ cmd.Parameters.Add(new MySqlParameter("@returnvalue", MySqlDbType.Int32)).Direction = ParameterDirection.ReturnValue;
+ int rowsAffected = cmd.ExecuteNonQuery();
+
+ Assert.AreEqual(0, rowsAffected);
+ Assert.AreEqual(22, cmd.Parameters[1].Value);
+ }
+ }
}
}
| Thread |
|---|
| • Connector/NET commit: r1186 - in branches/5.2: . Driver/Source TestSuite/Source | rburnett | 21 Feb |
