Connector/NET commit: r192 - in branches/1.0: . TestSuite mysqlclient

[rburnett]

Modified:
   branches/1.0/CHANGES
   branches/1.0/TestSuite/BlobTests.cs
   branches/1.0/TestSuite/StoredProcedure.cs
   branches/1.0/mysqlclient/CommandBuilder.cs
   branches/1.0/mysqlclient/Driver.cs
   branches/1.0/mysqlclient/MysqlDefs.cs
   branches/1.0/mysqlclient/StoredProcedure.cs
Log:
Bug #13632  	the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5
[fixed]

Driver.cs - Added Property method to retrieve server properties
MysqlDefs.cs - Added unsigned types
StoredProcedure.cs - Many changes.  Reworked how parameters are handled so that
DeriveParameters could be ipmlemented.
TestSuite/StoredProcedure.cs - Added DeriveParameters test suites

Modified: branches/1.0/CHANGES
===================================================================
--- branches/1.0/CHANGES	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/CHANGES	2005-10-13 20:47:54 UTC (rev 192)
@@ -7,9 +7,12 @@
 	Bug #13662 Prepare() truncates accented character input [already fixed, added test]
 	Bug #11947 MySQLCommandBuilder mishandling CONCAT() aliased column [updated test case]
 	Bug #13541 Prepare breaks if a parameter is used more than once [fixed]
+	Bug #13632 the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5	
 	
 	Other changes
 	-------------------------    
+	Failure to provide parameters for out and inout values is now detected
+
     
 9-27-05 - Version 1.0.6
  

Modified: branches/1.0/TestSuite/BlobTests.cs
===================================================================
--- branches/1.0/TestSuite/BlobTests.cs	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/TestSuite/BlobTests.cs	2005-10-13 20:47:54 UTC (rev 192)
@@ -54,6 +54,12 @@
 			int lenIn = 400000;
 			byte[] dataIn = Utils.CreateBlob(lenIn);
 
+
+			MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM test", conn);
+			MySqlDataReader reader2 = cmd2.ExecuteReader();
+			reader2.Read();
+			reader2.Close();
+
 			MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE Test", conn);
 			cmd.ExecuteNonQuery();
 

Modified: branches/1.0/TestSuite/StoredProcedure.cs
===================================================================
--- branches/1.0/TestSuite/StoredProcedure.cs	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/TestSuite/StoredProcedure.cs	2005-10-13 20:47:54 UTC (rev 192)
@@ -471,6 +471,83 @@
 			Assert.IsTrue(val is System.Int32, "Checking type");
 		}
 
+		/// <summary>
+		/// Bug #13632  	the MySQLCommandBuilder.deriveparameters has not been updated for
MySQL 5
+		/// </summary>
+		[Category("5.0")]
+		[Test]
+		public void DeriveParameters()
+		{
+			execSQL("CREATE PROCEDURE spTest(IN \r\nvalin DECIMAL(10,2), " +
+				"\nIN val2 INT, INOUT val3 FLOAT, OUT val4 DOUBLE, INOUT val5 BIT, " +
+				"val6 VARCHAR(155), val7 SET('a','b')) BEGIN SELECT 1; END");
 
+			MySqlCommand cmd = new MySqlCommand("spTest", conn);
+			cmd.CommandType = CommandType.StoredProcedure;
+			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
+			MySqlCommandBuilder.DeriveParameters(cmd);
+
+			Assert.AreEqual(7, cmd.Parameters.Count);
+			Assert.AreEqual("valin", cmd.Parameters[0].ParameterName);
+			Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[0].Direction);
+			Assert.AreEqual(MySqlDbType.NewDecimal, cmd.Parameters[0].MySqlDbType);
+
+			Assert.AreEqual("val2", cmd.Parameters[1].ParameterName);
+			Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[1].Direction);
+			Assert.AreEqual(MySqlDbType.Int32, cmd.Parameters[1].MySqlDbType);
+
+			Assert.AreEqual("val3", cmd.Parameters[2].ParameterName);
+			Assert.AreEqual(ParameterDirection.InputOutput, cmd.Parameters[2].Direction);
+			Assert.AreEqual(MySqlDbType.Float, cmd.Parameters[2].MySqlDbType);
+
+			Assert.AreEqual("val4", cmd.Parameters[3].ParameterName);
+			Assert.AreEqual(ParameterDirection.Output, cmd.Parameters[3].Direction);
+			Assert.AreEqual(MySqlDbType.Double, cmd.Parameters[3].MySqlDbType);
+
+			Assert.AreEqual("val5", cmd.Parameters[4].ParameterName);
+			Assert.AreEqual(ParameterDirection.InputOutput, cmd.Parameters[4].Direction);
+			Assert.AreEqual(MySqlDbType.Bit, cmd.Parameters[4].MySqlDbType);
+
+			Assert.AreEqual("val6", cmd.Parameters[5].ParameterName);
+			Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[5].Direction);
+			Assert.AreEqual(MySqlDbType.VarChar, cmd.Parameters[5].MySqlDbType);
+
+			Assert.AreEqual("val7", cmd.Parameters[6].ParameterName);
+			Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[6].Direction);
+			Assert.AreEqual(MySqlDbType.Set, cmd.Parameters[6].MySqlDbType);
+		}
+
+		/// <summary>
+		/// Bug #13632  	the MySQLCommandBuilder.deriveparameters has not been updated for
MySQL 5
+		/// </summary>
+		[Category("5.0")]
+		[Test]
+		public void DeriveParametersForFunction()
+		{
+			try 
+			{
+				execSQL("DROP FUNCTION IF EXISTS spTest");
+				execSQL("CREATE FUNCTION spTest(v1 DATETIME) RETURNS INT " +
+					" BEGIN RETURN 1; END");
+
+				MySqlCommand cmd = new MySqlCommand("spTest", conn);
+				cmd.CommandType = CommandType.StoredProcedure;
+				MySqlDataAdapter da = new MySqlDataAdapter(cmd);
+				MySqlCommandBuilder.DeriveParameters(cmd, false);
+
+				Assert.AreEqual(2, cmd.Parameters.Count);
+				Assert.AreEqual("v1", cmd.Parameters[0].ParameterName);
+				Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[0].Direction);
+				Assert.AreEqual(MySqlDbType.Datetime, cmd.Parameters[0].MySqlDbType);
+
+				Assert.AreEqual(ParameterDirection.ReturnValue, cmd.Parameters[1].Direction);
+				Assert.AreEqual(MySqlDbType.Int32, cmd.Parameters[1].MySqlDbType);
+			}
+			catch (Exception ex)
+			{
+				Assert.Fail(ex.Message);
+			}
+		}
+
 	}
 }

Modified: branches/1.0/mysqlclient/CommandBuilder.cs
===================================================================
--- branches/1.0/mysqlclient/CommandBuilder.cs	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/mysqlclient/CommandBuilder.cs	2005-10-13 20:47:54 UTC (rev 192)
@@ -120,17 +120,33 @@
 
 		#region Public Methods
 		/// <summary>
-		/// Retrieves parameter information from the stored procedure specified in the
MySqlCommand and populates the Parameters collection of the specified MySqlCommand
object.
-		/// This method is not currently supported since stored procedures are not available in
MySql.
+		/// Retrieves parameter information from the stored procedure specified 
+		/// in the MySqlCommand and populates the Parameters collection of the 
+		/// specified MySqlCommand object.
+		/// This method is not currently supported since stored procedures are 
+		/// not available in MySql.
 		/// </summary>
-		/// <param name="command">The MySqlCommand referencing the stored procedure from
which the parameter information is to be derived. The derived parameters are added to the
Parameters collection of the MySqlCommand.</param>
-		/// <exception cref="InvalidOperationException">The command text is not a valid
stored procedure name.</exception>
+		/// <param name="command">The MySqlCommand referencing the stored 
+		/// procedure from which the parameter information is to be derived. 
+		/// The derived parameters are added to the Parameters collection of the 
+		/// MySqlCommand.</param>
+		/// <exception cref="InvalidOperationException">The command text is not 
+		/// a valid stored procedure name.</exception>
 		public static void DeriveParameters(MySqlCommand command)
 		{
-			// this is just to make FxCop happy until we support this routine
-			throw new MySqlException("DeriveParameters is not supported (due to MySql not
supporting SP)");
+			if (!command.Connection.driver.Version.isAtLeast(5,0,0))
+				throw new MySqlException("DeriveParameters is not supported on versions " +
+					"prior to 5.0");
+			StoredProcedure sp = new StoredProcedure(command.Connection);
+			sp.DiscoverParameters(command, "");
 		}
 
+		public static void DeriveParameters(MySqlCommand command, bool useProc)
+		{
+			StoredProcedure sp = new StoredProcedure(command.Connection);
+			sp.DiscoverParameters(command, useProc ? "PROCEDURE" : "FUNCTION");
+		}
+
 		/// <include file='docs/MySqlCommandBuilder.xml' path='docs/GetDeleteCommand/*'/>
 		public MySqlCommand GetDeleteCommand()
 		{

Modified: branches/1.0/mysqlclient/Driver.cs
===================================================================
--- branches/1.0/mysqlclient/Driver.cs	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/mysqlclient/Driver.cs	2005-10-13 20:47:54 UTC (rev 192)
@@ -96,6 +96,11 @@
 
 		#endregion
 
+		public string Property(string key)
+		{
+			return (string)serverProps[key];
+		}
+
 		public bool IsTooOld() 
 		{
 			TimeSpan ts = DateTime.Now.Subtract( creationTime );

Modified: branches/1.0/mysqlclient/MysqlDefs.cs
===================================================================
--- branches/1.0/mysqlclient/MysqlDefs.cs	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/mysqlclient/MysqlDefs.cs	2005-10-13 20:47:54 UTC (rev 192)
@@ -151,7 +151,17 @@
 		/// <summary><b>Obsolete</b>  Use VarChar type</summary>
 		String      = 254,
 		/// <summary></summary>
-		Geometry	= 255
+		Geometry	= 255,
+		/// <summary></summary>
+		UByte		= 1024,
+		/// <summary></summary>
+		UInt16		= 1025,
+		/// <summary></summary>
+		UInt24		= 1026,
+		/// <summary></summary>
+		UInt32		= 1027,
+		/// <summary></summary>
+		UInt64		= 1028
 	};
 
 

Modified: branches/1.0/mysqlclient/StoredProcedure.cs
===================================================================
--- branches/1.0/mysqlclient/StoredProcedure.cs	2005-10-04 15:02:46 UTC (rev 191)
+++ branches/1.0/mysqlclient/StoredProcedure.cs	2005-10-13 20:47:54 UTC (rev 192)
@@ -20,6 +20,7 @@
 
 using System;
 using System.Data;
+using System.Text;
 using MySql.Data.Common;
 
 namespace MySql.Data.MySqlClient
@@ -40,10 +41,21 @@
 			connection = conn;
 		}
 
-		private string GetParameterList(string spName, bool isProc) 
+		private MySqlParameter GetReturnParameter(MySqlCommand cmd)
 		{
+			foreach (MySqlParameter p in cmd.Parameters)
+				if (p.Direction == ParameterDirection.ReturnValue)
+					return p;
+			return null;
+		}
+
+		private string GetParameterList(MySqlCommand procCmd, out string returns,
+			string procType)
+		{
 			MySqlCommand cmd = new MySqlCommand();
+			string spName = procCmd.CommandText;
 			cmd.Connection = connection;
+			returns = null;
 
 			int dotIndex = spName.IndexOf(".");
 			// query the mysql.proc table for the procedure parameter list
@@ -51,28 +63,39 @@
 			// database name.  If there is no dot, then we use database() as 
 			// the current database.
 			if (dotIndex == -1)
-				cmd.CommandText = "SELECT param_list FROM mysql.proc WHERE db=database() ";
+				cmd.CommandText = "SELECT param_list, returns FROM mysql.proc " +
+					"WHERE db=database() ";
 			else
 			{
 				string db = spName.Substring(0, dotIndex);
 				cmd.Parameters.Add("db", db);
 				spName = spName.Substring(dotIndex+1, spName.Length - dotIndex-1);
-				cmd.CommandText = String.Format("SELECT param_list FROM mysql.proc " + 
+				cmd.CommandText = String.Format("SELECT param_list, returns FROM mysql.proc " + 
 					"WHERE db=_latin1 {0}db ", connection.ParameterMarker);
 			}
 
-			cmd.CommandText += String.Format("AND name=_latin1 {0}name AND type='{1}'",
-				connection.ParameterMarker, isProc ? "PROCEDURE" : "FUNCTION");
+			cmd.CommandText += String.Format("AND name=_latin1 {0}name",
+				connection.ParameterMarker);
+				
+			if (procType.Length > 0)
+				cmd.CommandText += " AND type='" + procType + "'";
 
-			//cmd.Parameters.Add("db", connection.Database);
-			cmd.Parameters.Add("name", spName);
+			cmd.Parameters.Add(connection.ParameterMarker + "name", spName);
 			MySqlDataReader reader = null;
 
 			try 
 			{
 				reader = cmd.ExecuteReader();
 				if (!reader.Read()) return null;
-				return reader.GetString(0);
+				if (!reader.IsDBNull(1))
+					returns = reader.GetString(1);
+				if (returns != null && returns.Length == 0)
+					returns = null;
+				string return_val = reader.GetString(0);
+				if (reader.Read())
+					throw new MySqlException("More than one procedure or function matches " +
+						"the name '" + spName + "'");
+				return return_val;
 			}
 			catch (Exception ex) 
 			{
@@ -85,14 +108,79 @@
 			}
 		}
 
-		private string GetReturnParameter(MySqlCommand cmd)
+		private string CleanType(string type)
 		{
-			foreach (MySqlParameter p in cmd.Parameters)
-				if (p.Direction == ParameterDirection.ReturnValue)
-					return hash + p.ParameterName;
-			return null;
+			int paren_index = type.IndexOf("(");
+			if (paren_index != -1)
+				type = type.Substring(0, paren_index);
+			return type;
 		}
 
+		private string[] GetParameterParts(string parameterDef)
+		{
+			int pos = 0;
+			string[] parts = new string[3];
+
+			string[] split = Utility.ContextSplit(parameterDef.ToLower(), " \t\r\n", "");
+			if (split.Length == 0) return null;
+
+			if (split[0] == "in" || split[0] == "out" || split[0] == "inout")
+				parts[0] = split[pos++];
+			else
+				parts[0] = "in";
+
+			parts[1] = split[pos++];
+			parts[2] = CleanType(split[pos++]);
+			return parts;
+		}
+
+		private string[] GetParameterDefs(MySqlCommand cmd, out string returns, 
+			string procType)
+		{
+			string sig = GetParameterList(cmd, out returns, procType);
+
+			if (sig == null || sig.Length == 0) 
+				return null;
+
+			string[] paramDefs = Utility.ContextSplit(sig, ",", "()");
+			return paramDefs;
+		}
+
+		public void DiscoverParameters(MySqlCommand cmd, string procType)
+		{
+			string returns = String.Empty;
+			string[] defs = GetParameterDefs(cmd, out returns, procType);
+
+			foreach (string def in defs)
+			{
+				string[] parts = GetParameterParts(def);
+				if (parts == null) continue;
+				MySqlParameter p = new MySqlParameter(parts[1], GetType(parts[2]));
+				if (parts[0] == "out")
+					p.Direction = ParameterDirection.Output;
+				else if (parts[0] == "inout")
+					p.Direction = ParameterDirection.InputOutput;
+				else
+					p.Direction = ParameterDirection.Input;
+				cmd.Parameters.Add(p);
+			}
+
+			if (returns != null && returns.Length != 0)
+			{
+				MySqlParameter p = new MySqlParameter();
+				p.MySqlDbType = GetType(CleanType(returns));
+				p.Direction = ParameterDirection.ReturnValue;
+				cmd.Parameters.Add(p);
+			}
+		}
+
+		private string CleanParameterName(string name)
+		{
+			if (name[0] == connection.ParameterMarker)
+				return name.Remove(0,1);
+			return name;
+		}
+
 		/// <summary>
 		/// Creates the proper command text for executing the given stored procedure
 		/// </summary>
@@ -100,56 +188,56 @@
 		/// <returns></returns>
 		public string Prepare(MySqlCommand cmd)
 		{
-			// if we have a return value paramter, then we treat it as a 
-			// stored function
-			string retParm = GetReturnParameter(cmd);
-			bool isProc = retParm == null;
+			MySqlParameter returnParameter = GetReturnParameter(cmd);
 
+			string returnDef;
+			string[] defs = GetParameterDefs(cmd, out returnDef, 
+				returnParameter == null ? "PROCEDURE" : "FUNCTION");
+
+			string sqlStr = String.Empty;
 			string setStr = String.Empty;
-			string sqlStr = String.Empty;
-			
 			outSelect = String.Empty;
+
 			try 
 			{
-				string param_list = GetParameterList(cmd.CommandText, isProc);
-
-				if (param_list != null && param_list.Length > 0)
+				if (defs != null)
 				{
-					string[] paramDefs = Utility.ContextSplit( param_list, ",", "()" );
-					foreach (string paramDef in paramDefs) 
+					foreach (string def in defs)
 					{
-						string[] parts = Utility.ContextSplit(paramDef.ToLower(), " \t\r\n", "");
-						if (parts.Length == 0) continue;
-						string direction = parts.Length >= 3 ? parts[0] : "in";
-						string vName = parts.Length >= 3 ? parts[1] : parts[0];
+						string[] parts = GetParameterParts(def);
+						if (parts == null) continue;
 
-						string pName = connection.ParameterMarker + vName;
-						vName = "@" + hash + vName;
+						int index = cmd.Parameters.IndexOf(parts[1]);
+						if (index == -1)
+							throw new MySqlException("Parameter '" + parts[1] + "' is not defined");
 
-						if (direction.Equals("in"))
-							sqlStr += pName + ", ";
-						else if (direction == "out") 
+						MySqlParameter p = cmd.Parameters[index];
+						//string pName = connection.ParameterMarker + p.ParameterName;
+						string vName = "@" + hash + CleanParameterName(p.ParameterName);
+						if (p.Direction == ParameterDirection.Input)
 						{
-							sqlStr += vName + ", ";
-							outSelect += vName + ", ";
+							sqlStr += p.ParameterName + ", ";
+							continue;
 						}
-						else if (direction == "inout")
-						{
-							setStr += "set " + vName + "=" + pName + ";";
-							sqlStr += vName + ", ";
-							outSelect += vName + ", ";
-						}
+						else if (p.Direction == ParameterDirection.InputOutput)
+							setStr += "set " + vName + "=" + p.ParameterName + ";";
+						sqlStr += vName + ", ";
+						outSelect += vName + ", ";
 					}
 				}
-				sqlStr = sqlStr.TrimEnd(' ', ',');
-				outSelect = outSelect.TrimEnd(' ', ',');
-				if (isProc)
-					sqlStr = "call " + cmd.CommandText + "(" + sqlStr + ")";
+
+				if (returnParameter == null)
+					sqlStr = "call " + cmd.CommandText + "(" + sqlStr;
 				else
 				{
-					sqlStr = "set @" + retParm + "=" + cmd.CommandText + "(" + sqlStr + ")";
-					outSelect = "@" + retParm;
+					string vname = "@" + hash + CleanParameterName(returnParameter.ParameterName);
+					sqlStr = "set " + vname + "=" + cmd.CommandText + "(" + sqlStr;
+					outSelect = vname + outSelect;
 				}
+
+				sqlStr = sqlStr.TrimEnd(' ', ',');
+				outSelect = outSelect.TrimEnd(' ', ',');
+				sqlStr += ")";
 				if (setStr.Length > 0)
 					sqlStr = setStr + sqlStr;
 				return sqlStr;
@@ -185,5 +273,71 @@
 			}
 			reader.Close();
 		}
+
+		private MySqlDbType GetType(string typename)
+		{
+			typename = typename.ToLower();
+			bool isUnsigned = typename.IndexOf("unsigned") != -1;
+			string sqlmode = connection.driver.Property("sql_mode");
+			bool real_as_float = sqlmode.IndexOf("REAL_AS_FLOAT") != -1;
+
+			int index = typename.IndexOf("(");
+			if (index != -1)
+				typename = typename.Substring(0, index);
+
+			switch (typename)
+			{
+				case "varchar": return MySqlDbType.VarChar;
+				case "date": return MySqlDbType.Date;
+				case "datetime": return MySqlDbType.Datetime;
+				case "decimal": 
+				case "dec":
+				case "fixed":
+					if (connection.driver.Version.isAtLeast(5,0,3))
+						return MySqlDbType.NewDecimal;
+					else
+						return MySqlDbType.Decimal;
+				case "year":
+					return MySqlDbType.Year;
+				case "time":
+					return MySqlDbType.Time;
+				case "timestamp":
+					return MySqlDbType.Timestamp;
+				case "set": return MySqlDbType.Set;
+				case "enum": return MySqlDbType.Enum;
+				case "bit": return MySqlDbType.Bit;
+				case "tinyint":
+				case "bool":
+				case "boolean": 
+					return MySqlDbType.Byte;
+				case "smallint": 
+					return isUnsigned ? MySqlDbType.UInt16 : MySqlDbType.Int16;
+				case "mediumint": 
+					return isUnsigned ? MySqlDbType.UInt24 : MySqlDbType.Int24;
+				case "int" : 
+				case "integer":
+					return isUnsigned ? MySqlDbType.UInt32 : MySqlDbType.Int32;
+				case "bigint": 
+					return isUnsigned ? MySqlDbType.UInt64 : MySqlDbType.Int64;
+				case "float": return MySqlDbType.Float;
+				case "double": return MySqlDbType.Double;
+				case "real": return 
+					 real_as_float ? MySqlDbType.Float : MySqlDbType.Double;
+				case "blob":
+				case "text":
+					return MySqlDbType.Blob;
+				case "longblob":
+				case "longtext":
+					return MySqlDbType.LongBlob;
+				case "mediumblob":
+				case "mediumtext":
+					return MySqlDbType.MediumBlob;
+				case "tinyblob":
+				case "tinytext":
+					return MySqlDbType.TinyBlob;
+			}
+			throw new MySqlException("Unhandled type encountered");
+		}
+
 	}
 }