List:Commits« Previous MessageNext Message »
From:rburnett Date:May 19 2006 6:44pm
Subject:Connector/NET commit: r241 - in branches/1.0: . TestSuite mysqlclient
View as plain text  
Modified:
   branches/1.0/CHANGES
   branches/1.0/TestSuite/StoredProcedure.cs
   branches/1.0/mysqlclient/StoredProcedure.cs
   branches/1.0/mysqlclient/parameter.cs
Log:
Bug #17814 Stored procedure fails unless DbType set explicitly

The problem here is that our current implementation of output variables relies on user variables that don't preserve type.
The current code did have discovery capabililty but that was only being used for the DeriveParameters method.
The fix here is to call DiscoverParameters on a copy of the command only in the case that the user did not 
fully specify the types of all the parameters.  This is suboptimal and it is suggested that the user either call
DeriveParameters on their own or fully specify the types to prevent the additional work.



Modified: branches/1.0/CHANGES
===================================================================
--- branches/1.0/CHANGES	2006-05-19 16:35:21 UTC (rev 240)
+++ branches/1.0/CHANGES	2006-05-19 18:44:12 UTC (rev 241)
@@ -9,6 +9,7 @@
     Bug #15077 Error MySqlCommandBuilder.DeriveParameters for sp without parameters. [fixed]
     Bug #16934 Unsigned values > 2^63 (UInt64) cannot be used in prepared statements
 	Bug #19515 DiscoverParameters fails on numeric datatype [fixed]
+    Bug #17814 Stored procedure fails unless DbType set explicitly
 	
 x-xx-05 - Version 1.0.7
 

Modified: branches/1.0/TestSuite/StoredProcedure.cs
===================================================================
--- branches/1.0/TestSuite/StoredProcedure.cs	2006-05-19 16:35:21 UTC (rev 240)
+++ branches/1.0/TestSuite/StoredProcedure.cs	2006-05-19 18:44:12 UTC (rev 241)
@@ -106,32 +106,49 @@
 			}
 		}
 
-		[Test()]
+        /// <summary>
+        /// Bug #17814  	Stored procedure fails unless DbType set explicitly
+        /// </summary>
+		[Test]
 		[Category("5.0")]
 		public void OutputParameters()
 		{
 			// create our procedure
 			execSQL( "DROP PROCEDURE IF EXISTS spCount" );
-			execSQL( "CREATE PROCEDURE spCount( out value VARCHAR(50), OUT intVal INT, OUT dateVal TIMESTAMP, OUT floatVal FLOAT ) " + 
-				"BEGIN  SET value='42';  SET intVal=33; SET dateVal='2004-06-05 07:58:09'; SET floatVal = 1.2; END" );
+			execSQL( "CREATE PROCEDURE spCount(out value VARCHAR(50), OUT intVal INT, " +
+                "OUT dateVal TIMESTAMP, OUT floatVal FLOAT, OUT noTypeVarChar VARCHAR(20), " +
+                "OUT noTypeInt INT) " + 
+				"BEGIN  SET value='42';  SET intVal=33; SET dateVal='2004-06-05 07:58:09'; " +
+                "SET floatVal = 1.2; SET noTypeVarChar='test'; SET noTypeInt=66; END" );
 
 			MySqlCommand cmd = new MySqlCommand("spCount", conn);
 			cmd.CommandType = CommandType.StoredProcedure;
-			cmd.Parameters.Add( new MySqlParameter("?value", MySqlDbType.VarChar));
-			cmd.Parameters.Add( new MySqlParameter( "?intVal", MySqlDbType.Int32 ) );
-			cmd.Parameters.Add( new MySqlParameter( "?dateVal", MySqlDbType.Datetime ) );
-			cmd.Parameters.Add( new MySqlParameter( "?floatVal", MySqlDbType.Float ) );
-			cmd.Parameters[0].Direction = ParameterDirection.Output;
+			cmd.Parameters.Add(new MySqlParameter("?value", MySqlDbType.VarChar));
+			cmd.Parameters.Add(new MySqlParameter("?intVal", MySqlDbType.Int32));
+			cmd.Parameters.Add(new MySqlParameter("?dateVal", MySqlDbType.Datetime));
+			cmd.Parameters.Add(new MySqlParameter("?floatVal", MySqlDbType.Float));
+            MySqlParameter vcP = new MySqlParameter();
+            vcP.ParameterName = "noTypeVarChar";
+            vcP.Direction = ParameterDirection.Output;
+            cmd.Parameters.Add(vcP);
+            MySqlParameter vcI = new MySqlParameter();
+            vcI.ParameterName = "noTypeInt";
+            vcI.Direction = ParameterDirection.Output;
+            cmd.Parameters.Add(vcI);
+            cmd.Parameters[0].Direction = ParameterDirection.Output;
 			cmd.Parameters[1].Direction = ParameterDirection.Output;
 			cmd.Parameters[2].Direction = ParameterDirection.Output;
 			cmd.Parameters[3].Direction = ParameterDirection.Output;
 			int rowsAffected = cmd.ExecuteNonQuery();
 
-			Assert.AreEqual( 0, rowsAffected );
-			Assert.AreEqual( "42", cmd.Parameters[0].Value );
-			Assert.AreEqual( 33, cmd.Parameters[1].Value );
-			Assert.AreEqual( new DateTime(2004, 6, 5, 7, 58, 9), Convert.ToDateTime(cmd.Parameters[2].Value) );
-			Assert.AreEqual( 1.2, cmd.Parameters[3].Value );
+			Assert.AreEqual(0, rowsAffected);
+			Assert.AreEqual("42", cmd.Parameters[0].Value);
+			Assert.AreEqual(33, cmd.Parameters[1].Value);
+			Assert.AreEqual(new DateTime(2004, 6, 5, 7, 58, 9), 
+                Convert.ToDateTime(cmd.Parameters[2].Value));
+			Assert.AreEqual(1.2, cmd.Parameters[3].Value);
+            Assert.AreEqual("test", cmd.Parameters[4].Value);
+            Assert.AreEqual(66, cmd.Parameters[5].Value);
 
 			execSQL("DROP PROCEDURE spCount");
 		}

Modified: branches/1.0/mysqlclient/StoredProcedure.cs
===================================================================
--- branches/1.0/mysqlclient/StoredProcedure.cs	2006-05-19 16:35:21 UTC (rev 240)
+++ branches/1.0/mysqlclient/StoredProcedure.cs	2006-05-19 18:44:12 UTC (rev 241)
@@ -190,6 +190,29 @@
 			return name;
 		}
 
+        private void CheckParameterTypes(MySqlCommand cmd)
+        {
+            bool setTypes = false;
+            foreach (MySqlParameter p in cmd.Parameters)
+            {
+                if (!p.TypeHasBeenSet)
+                {
+                    setTypes = true;
+                    break;
+                }
+            }
+            if (!setTypes) return;
+            MySqlCommand cmd2 = new MySqlCommand(cmd.CommandText, cmd.Connection);
+            cmd2.CommandType = cmd.CommandType;
+            DiscoverParameters(cmd2, "");
+            foreach (MySqlParameter p in cmd.Parameters)
+            {
+                if (p.TypeHasBeenSet) continue;
+                MySqlParameter parmWithType = cmd2.Parameters[p.ParameterName];
+                p.MySqlDbType = parmWithType.MySqlDbType;
+            }
+        }
+
 		/// <summary>
 		/// Creates the proper command text for executing the given stored procedure
 		/// </summary>
@@ -197,6 +220,7 @@
 		/// <returns></returns>
 		public string Prepare(MySqlCommand cmd)
 		{
+            CheckParameterTypes(cmd);
 			MySqlParameter returnParameter = GetReturnParameter(cmd);
 
 			string returnDef;

Modified: branches/1.0/mysqlclient/parameter.cs
===================================================================
--- branches/1.0/mysqlclient/parameter.cs	2006-05-19 16:35:21 UTC (rev 240)
+++ branches/1.0/mysqlclient/parameter.cs	2006-05-19 18:44:12 UTC (rev 241)
@@ -43,7 +43,6 @@
 		private DataRowVersion		sourceVersion = DataRowVersion.Current;
 		private int					size;
 		private byte				precision;
-		private bool				isUnsigned;
 		private byte				scale;
 		private MySqlDbType			mySqlDbType;
 		private DbType				dbType;
@@ -147,6 +146,11 @@
 
 		#region Properties
 
+        internal bool TypeHasBeenSet
+        {
+            get { return inferType == false; }
+        }
+
 		/// <summary>
 		/// Gets or sets the <see cref="DbType"/> of the parameter.
 		/// </summary>
@@ -394,7 +398,6 @@
 				case DbType.Byte:
 				case DbType.SByte:
 					mySqlDbType = dbType == DbType.Byte ? MySqlDbType.UByte : MySqlDbType.Byte; 
-					isUnsigned = dbType == DbType.Byte;
 					break;
 
 				case DbType.Date: mySqlDbType = MySqlDbType.Date; break;

Thread
Connector/NET commit: r241 - in branches/1.0: . TestSuite mysqlclientrburnett19 May