From: Date: February 21 2008 10:38pm Subject: Connector/NET commit: r1186 - in branches/5.2: . Driver/Source TestSuite/Source List-Archive: http://lists.mysql.com/commits/42789 X-Bug: 34699 Message-Id: <200802212138.m1LLccNq030053@bk-internal.mysql.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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; + } + /// /// Serializes the given parameter to the given memory stream /// @@ -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); + } /// /// 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 /// @@ -967,37 +967,37 @@ #if !CF - /// - /// Bug #22452 MySql.Data.MySqlClient.MySqlException: - /// - [Test] - public void TurkishStoredProcs() - { + /// + /// Bug #22452 MySql.Data.MySqlClient.MySqlException: + /// + [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); } + + /// + /// + [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); + } + } + + /// + /// + [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); + } + } } }