Added:
trunk/TestSuite/PerfMonTests.cs
trunk/mysqlclient/PerformanceMonitor.cs
trunk/mysqlclient/ProcedureCache.cs
Modified:
trunk/CHANGES
trunk/MySql.Data.2005.csproj
trunk/TestSuite/BaseTest.cs
trunk/TestSuite/MySql.Data.Tests.2005.csproj
trunk/TestSuite/StoredProcedure.cs
trunk/mysqlclient/AssemblyInfo.cs
trunk/mysqlclient/Connection.cs
trunk/mysqlclient/ConnectionString.cs
trunk/mysqlclient/ISSchemaProvider.cs
trunk/mysqlclient/MySqlPool.cs
trunk/mysqlclient/MySqlPoolManager.cs
trunk/mysqlclient/Statement.cs
trunk/mysqlclient/StoredProcedure.cs
trunk/mysqlclient/common/DBConnectionString.cs
trunk/mysqlclient/common/StreamCreator.cs
trunk/mysqlclient/docs/MySqlConnection.xml
Log:
First push of stored procedure cache with perf mon hooks
This change includes the hard query and soft query counters
Not all stored procs are working yet (since I changed to using GetSchema) but the rest should be easy to bring online
This patch also include various other bits that I've been working on
Modified: trunk/CHANGES
===================================================================
--- trunk/CHANGES 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/CHANGES 2006-04-14 17:12:18 UTC (rev 217)
@@ -9,6 +9,8 @@
Added internal implemention of SHA1 so we don't have to distribute the OpenNetCF on mobile devices
Added usage advisor warnings for requesting column values by the wrong type
Reworked connection string classes to be simpler and faster
+ Added cache for stored procedure definitions
+ Added perfmon hooks for stored procedure cache hits and misses
8-24-05 - Version 1.0.5
Modified: trunk/MySql.Data.2005.csproj
===================================================================
--- trunk/MySql.Data.2005.csproj 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/MySql.Data.2005.csproj 2006-04-14 17:12:18 UTC (rev 217)
@@ -135,7 +135,6 @@
<Compile Include="MySqlClient\common\Platform.cs">
<SubType>Code</SubType>
</Compile>
- <Compile Include="MySqlClient\common\ProcedureCache.cs" />
<Compile Include="MySqlClient\common\Resources.cs" />
<Compile Include="MySqlClient\common\SHA1.cs" />
<Compile Include="MySqlClient\common\SocketStream.cs" />
@@ -214,10 +213,12 @@
<Compile Include="MySqlClient\parameter_collection.cs">
<SubType>Code</SubType>
</Compile>
+ <Compile Include="MySqlClient\PerformanceMonitor.cs" />
<Compile Include="MySqlClient\PreparedStatement.cs">
<SubType>Code</SubType>
</Compile>
<Compile Include="MySqlClient\ISSchemaProvider.cs" />
+ <Compile Include="MySqlClient\ProcedureCache.cs" />
<Compile Include="MySqlClient\SchemaProvider.cs" />
<Compile Include="MySqlClient\SharedMemoryStream.cs">
<SubType>Code</SubType>
Modified: trunk/TestSuite/BaseTest.cs
===================================================================
--- trunk/TestSuite/BaseTest.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/TestSuite/BaseTest.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -62,7 +62,6 @@
user = "root";
password = "";
otherkeys = ConfigurationSettings.AppSettings["otherkeys"];
- otherkeys += ";driver=client";
string connString = GetConnectionString(true);
conn = new MySqlConnection( connString );
conn.Open();
Modified: trunk/TestSuite/MySql.Data.Tests.2005.csproj
===================================================================
--- trunk/TestSuite/MySql.Data.Tests.2005.csproj 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/TestSuite/MySql.Data.Tests.2005.csproj 2006-04-14 17:12:18 UTC (rev 217)
@@ -144,6 +144,7 @@
<Compile Include="ParameterTests.cs">
<SubType>Code</SubType>
</Compile>
+ <Compile Include="PerfMonTests.cs" />
<Compile Include="PoolingTests.cs">
<SubType>Code</SubType>
</Compile>
Added: trunk/TestSuite/PerfMonTests.cs
===================================================================
--- trunk/TestSuite/PerfMonTests.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/TestSuite/PerfMonTests.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -0,0 +1,103 @@
+// Copyright (C) 2004-2005 MySQL AB
+//
+// This program is free software; you can redistribute it and/or modify
+// it under the terms of the GNU General Public License version 2 as published by
+// the Free Software Foundation
+//
+// There are special exceptions to the terms and conditions of the GPL
+// as it is applied to this software. View the full text of the
+// exception in file EXCEPTIONS in the directory of this software
+// distribution.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+using System;
+using System.Data;
+using MySql.Data.MySqlClient;
+using NUnit.Framework;
+using System.Diagnostics;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ /// <summary>
+ /// Summary description for StoredProcedure.
+ /// </summary>
+ [TestFixture]
+ public class PerfMonTests : BaseTest
+ {
+
+ [TestFixtureSetUp]
+ public void FixtureSetup()
+ {
+ CounterCreationDataCollection ccd = new CounterCreationDataCollection();
+ string categoryName = ".NET Data Provider for MySQL";
+ try
+ {
+ if (PerformanceCounterCategory.Exists(categoryName))
+ PerformanceCounterCategory.Delete(categoryName);
+ CounterCreationData procHardQuery = new CounterCreationData(
+ "HardProcedureQueries", "Number of stored procedure metadata queries that are sent to MySQL",
+ PerformanceCounterType.NumberOfItems32);
+ ccd.Add(procHardQuery);
+ CounterCreationData procSoftQuery = new CounterCreationData(
+ "SoftProcedureQueries", "Number of stored procedure metadata queries that are handled by cache",
+ PerformanceCounterType.NumberOfItems32);
+ ccd.Add(procSoftQuery);
+
+ PerformanceCounterCategory.Create(categoryName, "Performance counters for the .NET provider for MySQL", ccd);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ csAdditions = ";pooling=false;use performance monitor=true";
+ Open();
+ execSQL("DROP TABLE IF EXISTS Test; CREATE TABLE Test (id INT, name VARCHAR(100))");
+ }
+
+ [TestFixtureTearDown]
+ public void TestFixtureTearDown()
+ {
+ Close();
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void ProcedureFromCache()
+ {
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ execSQL("CREATE PROCEDURE spTest(id int) BEGIN END");
+
+ PerformanceCounter hardQuery = new PerformanceCounter(
+ ".NET Data Provider for MySQL", "HardProcedureQueries", true);
+ PerformanceCounter softQuery = new PerformanceCounter(
+ ".NET Data Provider for MySQL", "SoftProcedureQueries", true);
+ long hardCount = hardQuery.RawValue;
+ long softCount = softQuery.RawValue;
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?id", 1);
+ cmd.ExecuteScalar();
+
+ Assert.AreEqual(hardCount + 1, hardQuery.RawValue);
+ Assert.AreEqual(softCount, softQuery.RawValue);
+ hardCount = hardQuery.RawValue;
+
+ MySqlCommand cmd2 = new MySqlCommand("spTest", conn);
+ cmd2.CommandType = CommandType.StoredProcedure;
+ cmd2.Parameters.Add("?id", 1);
+ cmd2.ExecuteScalar();
+
+ Assert.AreEqual(hardCount, hardQuery.RawValue);
+ Assert.AreEqual(softCount+1, softQuery.RawValue);
+ }
+
+ }
+}
Modified: trunk/TestSuite/StoredProcedure.cs
===================================================================
--- trunk/TestSuite/StoredProcedure.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/TestSuite/StoredProcedure.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -35,7 +35,7 @@
[TestFixtureSetUp]
public void FixtureSetup()
{
- csAdditions = ";pooling=false";
+ csAdditions = ";logging=true;pooling=false";
Open();
execSQL("DROP TABLE IF EXISTS Test; CREATE TABLE Test (id INT, name VARCHAR(100))");
}
@@ -66,7 +66,7 @@
p.Value = 21;
object id = cmd.ExecuteScalar();
- Assert.AreEqual( 21, id );
+ Assert.AreEqual(21, id);
}
}
Modified: trunk/mysqlclient/AssemblyInfo.cs
===================================================================
--- trunk/mysqlclient/AssemblyInfo.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/AssemblyInfo.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -33,7 +33,7 @@
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("MySQL AB")]
[assembly: AssemblyProduct("")]
-[assembly: AssemblyCopyright("Copyright 2004-2005, MySQL AB")]
+[assembly: AssemblyCopyright("Copyright 2004-2006, MySQL AB")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: ComVisible(false)]
@@ -50,7 +50,7 @@
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
-[assembly: AssemblyVersion("1.0.5.*")]
+[assembly: AssemblyVersion("5.0.0.*")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
Modified: trunk/mysqlclient/Connection.cs
===================================================================
--- trunk/mysqlclient/Connection.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/Connection.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -45,6 +45,7 @@
private bool hasBeenOpen;
private SchemaProvider schemaProvider;
private ProcedureCache procedureCache;
+ private PerformanceMonitor perfMonitor;
/// <include file='docs/MySqlConnection.xml' path='docs/InfoMessage/*'/>
public event MySqlInfoMessageEventHandler InfoMessage;
@@ -95,6 +96,11 @@
}
}
+ internal PerformanceMonitor PerfMonitor
+ {
+ get { return perfMonitor; }
+ }
+
#endregion
#region Properties
@@ -344,6 +350,7 @@
schemaProvider = new ISSchemaProvider(this);
else
schemaProvider = new NonISSchemaProvider(this);
+ perfMonitor = new PerformanceMonitor(this);
hasBeenOpen = true;
}
Modified: trunk/mysqlclient/ConnectionString.cs
===================================================================
--- trunk/mysqlclient/ConnectionString.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/ConnectionString.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -64,6 +64,7 @@
private DriverType driverType;
private bool allowZeroDateTime;
private bool convertZeroDateTime;
+ private bool usePerformanceMonitor;
public MySqlConnectionString() : base()
{
@@ -80,10 +81,12 @@
driverType = DriverType.Native;
allowZeroDateTime = true;
convertZeroDateTime = true;
+ usePerformanceMonitor = false;
}
- public MySqlConnectionString(string connectString) : base(connectString)
+ public MySqlConnectionString(string connectString) : this()
{
+ this.connectString = connectString;
Parse();
}
@@ -243,6 +246,16 @@
get { return procedureCacheSize; }
}
+#if DESIGN
+ [Category("Other")]
+ [Description("Should performance metrics be generated.")]
+ [DefaultValue(false)]
+#endif
+ public bool UsePerformanceMonitor
+ {
+ get { return usePerformanceMonitor; }
+ }
+
#endregion
/// <summary>
@@ -354,6 +367,11 @@
case "procedurecache":
procedureCacheSize = Int32.Parse(value);
return true;
+
+ case "useperformancemonitor":
+ case "use performance monitor":
+ usePerformanceMonitor = boolVal;
+ return true;
}
if (! base.ConnectionParameterParsed(key, value))
Modified: trunk/mysqlclient/ISSchemaProvider.cs
===================================================================
--- trunk/mysqlclient/ISSchemaProvider.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/ISSchemaProvider.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -113,7 +113,9 @@
query.Append(" WHERE ");
query.Append(where);
}
- return GetTable(query.ToString());
+ DataTable table = GetTable(query.ToString());
+ table.TableName = "Procedures";
+ return table;
}
/// <summary>
@@ -229,7 +231,7 @@
private DataTable CreateParametersTable()
{
- DataTable dt = new DataTable("ProcedureParameters");
+ DataTable dt = new DataTable("Procedure Parameters");
dt.Columns.Add("specific_schema", typeof(string));
dt.Columns.Add("specific_name", typeof(string));
dt.Columns.Add("parameter_name", typeof(string));
Modified: trunk/mysqlclient/MySqlPool.cs
===================================================================
--- trunk/mysqlclient/MySqlPool.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/MySqlPool.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -33,7 +33,8 @@
private ArrayList idlePool;
private MySqlConnectionString settings;
private int minSize;
- private int maxSize;
+ private int maxSize;
+ private ProcedureCache procedureCache;
public MySqlPool(MySqlConnectionString settings)
{
@@ -45,14 +46,21 @@
// prepopulate the idle pool to minSize
for (int i=0; i < minSize; i++)
- CreateNewPooledConnection();
+ CreateNewPooledConnection();
+
+ procedureCache = new ProcedureCache(settings.ProcedureCacheSize);
}
public MySqlConnectionString Settings
{
get { return settings; }
set { settings = value; }
- }
+ }
+
+ public ProcedureCache ProcedureCache
+ {
+ get { return procedureCache; }
+ }
private int CheckConnections()
{
Modified: trunk/mysqlclient/MySqlPoolManager.cs
===================================================================
--- trunk/mysqlclient/MySqlPoolManager.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/MySqlPoolManager.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -43,7 +43,7 @@
pools = new Hashtable();
}
- public static Driver GetConnection(MySqlConnectionString settings)
+ public static MySqlPool GetPool(MySqlConnectionString settings)
{
// make sure the manager is initialized
if (MySqlPoolManager.pools == null)
@@ -51,21 +51,21 @@
string text = settings.GetConnectionString(true);
- lock( pools.SyncRoot )
+ lock(pools.SyncRoot)
{
MySqlPool pool;
- if (!pools.Contains( text ))
+ if (!pools.Contains(text))
{
- pool = new MySqlPool( settings );
- pools.Add( text, pool );
+ pool = new MySqlPool(settings);
+ pools.Add(text, pool);
}
else
{
pool = (pools[text] as MySqlPool);
pool.Settings = settings;
- }
-
- return pool.GetConnection();
+ }
+
+ return pool;
}
}
@@ -74,10 +74,10 @@
lock (pools.SyncRoot)
{
string key = driver.Settings.GetConnectionString(true);
- MySqlPool pool = (MySqlPool)pools[ key ];
+ MySqlPool pool = (MySqlPool)pools[key];
if (pool == null)
throw new MySqlException("Pooling exception: Unable to find original pool for connection");
- pool.ReleaseConnection( driver );
+ pool.ReleaseConnection(driver);
}
}
}
Added: trunk/mysqlclient/PerformanceMonitor.cs
===================================================================
--- trunk/mysqlclient/PerformanceMonitor.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/PerformanceMonitor.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -0,0 +1,44 @@
+using System;
+using System.Diagnostics;
+
+
+namespace MySql.Data.MySqlClient
+{
+ class PerformanceMonitor
+ {
+ private MySqlConnection connection;
+ private static PerformanceCounter procedureHardQueries;
+ private static PerformanceCounter procedureSoftQueries;
+ private static string categoryName;
+
+ public PerformanceMonitor(MySqlConnection connection)
+ {
+ this.connection = connection;
+
+ if (categoryName == null)
+ {
+ categoryName = ".NET Data Provider for MySQL";
+ if (PerformanceCounterCategory.CounterExists("HardProcedureQueries",
+ categoryName))
+ procedureHardQueries = new PerformanceCounter(categoryName,
+ "HardProcedureQueries", false);
+ if (PerformanceCounterCategory.CounterExists("SoftProcedureQueries",
+ categoryName))
+ procedureSoftQueries = new PerformanceCounter(categoryName,
+ "SoftProcedureQueries", false);
+ }
+ }
+
+ public void AddHardProcedureQuery()
+ {
+ if (!connection.Settings.UsePerformanceMonitor) return;
+ procedureHardQueries.Increment();
+ }
+
+ public void AddSoftProcedureQuery()
+ {
+ if (!connection.Settings.UsePerformanceMonitor) return;
+ procedureSoftQueries.Increment();
+ }
+ }
+}
Added: trunk/mysqlclient/ProcedureCache.cs
===================================================================
--- trunk/mysqlclient/ProcedureCache.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/ProcedureCache.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -0,0 +1,77 @@
+using MySql.Data.MySqlClient;
+using System.Data;
+using System.Collections.Generic;
+using System.Collections;
+
+namespace MySql.Data.MySqlClient
+{
+ class ProcedureCache
+ {
+ private Hashtable procHash;
+ private Queue<int> hashQueue;
+ private int maxSize;
+
+ public ProcedureCache(int size)
+ {
+ maxSize = size;
+ hashQueue = new Queue<int>(maxSize);
+ procHash = new Hashtable(maxSize);
+ }
+
+ public DataSet GetProcedure(MySqlConnection conn, string spName)
+ {
+ int hash = spName.GetHashCode();
+ DataSet ds = (DataSet)procHash[hash];
+ if (ds == null)
+ {
+ ds = AddNew(conn, spName);
+ conn.PerfMonitor.AddHardProcedureQuery();
+ }
+ else
+ conn.PerfMonitor.AddSoftProcedureQuery();
+ return ds;
+ }
+
+ private DataSet AddNew(MySqlConnection connection, string spName)
+ {
+ DataSet procData = GetProcData(connection, spName);
+ if (maxSize > 0)
+ {
+ if (procHash.Keys.Count == maxSize)
+ TrimHash();
+ int hash = spName.GetHashCode();
+ procHash.Add(hash, procData);
+ hashQueue.Enqueue(hash);
+ }
+ return procData;
+ }
+
+ private void TrimHash()
+ {
+ int oldestHash = hashQueue.Dequeue();
+ procHash.Remove(oldestHash);
+ }
+
+ private DataSet GetProcData(MySqlConnection connection, string spName)
+ {
+ int dotIndex = spName.IndexOf(".");
+ string schema = spName.Substring(0, dotIndex);
+ string name = spName.Substring(dotIndex+1, spName.Length-dotIndex-1);
+
+ string[] restrictions = new string[4];
+ restrictions[1] = schema;
+ restrictions[2] = name;
+ DataTable procTable = connection.GetSchema("procedures", restrictions);
+
+ DataTable parametersTable = connection.GetSchema("procedure parameters",
+ restrictions);
+
+ DataSet ds = new DataSet();
+ ds.Tables.Add(procTable);
+ ds.Tables.Add(parametersTable);
+ return ds;
+ }
+
+
+ }
+}
Modified: trunk/mysqlclient/Statement.cs
===================================================================
--- trunk/mysqlclient/Statement.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/Statement.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -32,6 +32,7 @@
protected string commandText;
private ArrayList buffers;
protected int statementId;
+ protected MySqlParameterCollection parameters;
private Statement(MySqlConnection connection)
{
@@ -53,7 +54,9 @@
public virtual void Execute(MySqlParameterCollection parameters)
{
- BindParameters(parameters);
+ // we keep a reference to this until we are done
+ this.parameters = parameters;
+ BindParameters();
ExecuteNext();
}
@@ -68,12 +71,13 @@
return true;
}
- protected void BindParameters(MySqlParameterCollection parameters)
+ protected virtual void BindParameters()
{
// tokenize the sql
ArrayList tokenArray = TokenizeSql(commandText);
- MySqlStreamWriter writer = new MySqlStreamWriter(new MemoryStream(), driver.Encoding);
+ MySqlStreamWriter writer = new MySqlStreamWriter(new MemoryStream(),
+ driver.Encoding);
writer.Version = driver.Version;
// make sure our token array ends with a ;
Modified: trunk/mysqlclient/StoredProcedure.cs
===================================================================
--- trunk/mysqlclient/StoredProcedure.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/StoredProcedure.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -21,6 +21,7 @@
using System;
using System.Data;
using MySql.Data.Common;
+using System.Text;
namespace MySql.Data.MySqlClient
{
@@ -32,61 +33,17 @@
private string hash;
private string outSelect;
- public StoredProcedure(MySqlConnection connection, string text) : base(connection, text)
+ public StoredProcedure(MySqlConnection connection, string text) :
+ base(connection, text)
{
uint code = (uint)DateTime.Now.GetHashCode();
hash = code.ToString();
this.connection = connection;
}
- private string GetParameterList(string spName, bool isProc)
+ private string GetReturnParameter()
{
- MySqlCommand cmd = new MySqlCommand();
- cmd.Connection = connection;
-
- int dotIndex = spName.IndexOf(".");
- // query the mysql.proc table for the procedure parameter list
- // if our spname as a dot in it, then we assume the first part is the
- // 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() ";
- 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 " +
- "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.Parameters.Add("db", connection.Database);
- cmd.Parameters.Add("name", spName);
- MySqlDataReader reader = null;
-
- try
- {
- reader = cmd.ExecuteReader();
- if (!reader.Read()) return null;
- return reader.GetString(0);
- }
- catch (Exception ex)
- {
- Logger.LogException( ex );
- throw;
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- private string GetReturnParameter(MySqlCommand cmd)
- {
- foreach (MySqlParameter p in cmd.Parameters)
+ foreach (MySqlParameter p in parameters)
if (p.Direction == ParameterDirection.ReturnValue)
return hash + p.ParameterName;
return null;
@@ -99,77 +56,65 @@
public override bool ExecuteNext()
{
- return false;
+ bool returnVal = base.ExecuteNext();
+ if (!returnVal)
+ UpdateParameters();
+ return returnVal;
}
- /// <summary>
- /// Creates the proper command text for executing the given stored procedure
- /// </summary>
- /// <param name="cmd"></param>
- /// <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;
+ protected override void BindParameters()
+ {
+ // first retrieve the procedure definition from our
+ // procedure cache
+ string spName = commandText;
+ if (spName.IndexOf(".") == -1)
+ spName = connection.Database + "." + spName;
+ DataSet ds = connection.ProcedureCache.GetProcedure(connection, spName);
- string setStr = String.Empty;
- string sqlStr = String.Empty;
-
- outSelect = String.Empty;
- try
- {
- string param_list = GetParameterList(cmd.CommandText, isProc);
+ DataTable procTable = ds.Tables["procedures"];
+ DataTable paramTable = ds.Tables["procedure parameters"];
- if (param_list != null && param_list.Length > 0)
- {
- string[] paramDefs = Utility.ContextSplit( param_list, ",", "()" );
- foreach (string paramDef in paramDefs)
- {
- 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 sqlStr = String.Empty;
+ string setStr = String.Empty;
- string pName = connection.ParameterMarker + vName;
- vName = "@" + hash + vName;
+ string retParm = GetReturnParameter();
+ foreach (DataRow param in paramTable.Rows)
+ {
+ if (param["ordinal_position"].Equals(0)) continue;
+ string mode = (string)param["parameter_mode"];
+ string name = (string)param["parameter_name"];
+ string pName = connection.ParameterMarker + name;
+ string vName = "@" + hash + name;
- if (direction.Equals("in"))
- sqlStr += pName + ", ";
- else if (direction == "out")
- {
- sqlStr += vName + ", ";
- outSelect += vName + ", ";
- }
- else if (direction == "inout")
- {
- setStr += "set " + vName + "=" + pName + ";";
- sqlStr += vName + ", ";
- outSelect += vName + ", ";
- }
- }
- }
- sqlStr = sqlStr.TrimEnd(' ', ',');
- outSelect = outSelect.TrimEnd(' ', ',');
- if (isProc)
- sqlStr = "call " + cmd.CommandText + "(" + sqlStr + ")";
- else
- {
- sqlStr = "set @" + retParm + "=" + cmd.CommandText + "(" + sqlStr + ")";
- outSelect = "@" + retParm;
- }
- if (setStr.Length > 0)
- sqlStr = setStr + sqlStr;
- return sqlStr;
- }
- catch (Exception ex)
+ sqlStr += pName + ", ";
+ if (mode == "OUT")
+ outSelect += vName + ", ";
+ else
+ {
+ setStr += "SET " + vName + "=" + pName + ";";
+ outSelect += vName + ", ";
+ }
+
+ }
+
+ sqlStr = sqlStr.TrimEnd(' ', ',');
+ outSelect = outSelect.TrimEnd(' ', ',');
+ if (procTable.Rows[0]["ROUTINE_TYPE"].Equals("PROCEDURE"))
+ sqlStr = "call " + commandText + "(" + sqlStr + ")";
+ else
{
- throw new MySqlException("Exception trying to retrieve parameter info for " + cmd.CommandText + ": " + ex.Message, ex);
+ sqlStr = "set @" + retParm + "=" + commandText + "(" + sqlStr + ")";
+ outSelect = "@" + retParm;
}
+ if (setStr.Length > 0)
+ sqlStr = setStr + sqlStr;
+ commandText = sqlStr;
+
+ // now call our base version
+ base.BindParameters();
}
- public void UpdateParameters(MySqlParameterCollection parameters)
+ public void UpdateParameters()
{
if (outSelect.Length == 0) return;
Modified: trunk/mysqlclient/common/DBConnectionString.cs
===================================================================
--- trunk/mysqlclient/common/DBConnectionString.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/common/DBConnectionString.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -1,32 +1,32 @@
-// Copyright (C) 2004 MySQL AB
-//
-// This program is free software; you can redistribute it and/or modify
-// it under the terms of the GNU General Public License version 2 as published by
-// the Free Software Foundation
-//
-// There are special exceptions to the terms and conditions of the GPL
-// as it is applied to this software. View the full text of the
-// exception in file EXCEPTIONS in the directory of this software
-// distribution.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program; if not, write to the Free Software
-// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-
-using System;
-
-namespace MySql.Data.Common
-{
- /// <summary>
- /// Summary description for Utility.
- /// </summary>
- internal abstract class DBConnectionString
- {
+// Copyright (C) 2004 MySQL AB
+//
+// This program is free software; you can redistribute it and/or modify
+// it under the terms of the GNU General Public License version 2 as published by
+// the Free Software Foundation
+//
+// There are special exceptions to the terms and conditions of the GPL
+// as it is applied to this software. View the full text of the
+// exception in file EXCEPTIONS in the directory of this software
+// distribution.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+using System;
+
+namespace MySql.Data.Common
+{
+ /// <summary>
+ /// Summary description for Utility.
+ /// </summary>
+ internal abstract class DBConnectionString
+ {
protected string connectString;
protected bool persistSecurityInfo;
protected string userId;
@@ -38,10 +38,10 @@
protected bool pooling;
protected int minPoolSize;
protected int maxPoolSize;
- protected int poolLifeTime;
-
- public DBConnectionString()
- {
+ protected int poolLifeTime;
+
+ public DBConnectionString()
+ {
persistSecurityInfo = false;
connectionTimeout = 15;
pooling = true;
@@ -171,95 +171,95 @@
}
#endregion
-
- protected string RemoveKeys(string value, string[] keys)
- {
- System.Text.StringBuilder sb = new System.Text.StringBuilder();
- string[] pairs = Utility.ContextSplit(value, ";", "\"'");
- foreach (string keyvalue in pairs)
- {
- string test = keyvalue.Trim().ToLower();
- if (test.StartsWith("pwd") || test.StartsWith("password"))
- continue;
- sb.Append(keyvalue);
- sb.Append(";");
- }
- sb.Remove(sb.Length-1, 1); // remove the trailing ;
- return sb.ToString();
- }
-
- protected virtual bool ConnectionParameterParsed(string key, string value)
- {
- string lowerKey = key.ToLower(System.Globalization.CultureInfo.InvariantCulture);
-
- switch (lowerKey)
- {
+
+ protected string RemoveKeys(string value, string[] keys)
+ {
+ System.Text.StringBuilder sb = new System.Text.StringBuilder();
+ string[] pairs = Utility.ContextSplit(value, ";", "\"'");
+ foreach (string keyvalue in pairs)
+ {
+ string test = keyvalue.Trim().ToLower();
+ if (test.StartsWith("pwd") || test.StartsWith("password"))
+ continue;
+ sb.Append(keyvalue);
+ sb.Append(";");
+ }
+ sb.Remove(sb.Length-1, 1); // remove the trailing ;
+ return sb.ToString();
+ }
+
+ protected virtual bool ConnectionParameterParsed(string key, string value)
+ {
+ string lowerKey = key.ToLower(System.Globalization.CultureInfo.InvariantCulture);
+
+ switch (lowerKey)
+ {
case "persist security info":
- persistSecurityInfo = value.ToLower() == "yes" || value.ToLower() == "true";
- return true;
-
- case "uid":
- case "username":
- case "user id":
- case "user name":
+ persistSecurityInfo = value.ToLower() == "yes" || value.ToLower() == "true";
+ return true;
+
+ case "uid":
+ case "username":
+ case "user id":
+ case "user name":
case "userid":
- userId = value;
- return true;
-
- case "password":
+ userId = value;
+ return true;
+
+ case "password":
case "pwd":
- password = value;
- return true;
-
- case "host":
- case "server":
- case "data source":
- case "datasource":
- case "address":
- case "addr":
+ password = value;
+ return true;
+
+ case "host":
+ case "server":
+ case "data source":
+ case "datasource":
+ case "address":
+ case "addr":
case "network address":
- host = value;
- return true;
-
- case "initial catalog":
+ host = value;
+ return true;
+
+ case "initial catalog":
case "database":
- database = value;
- return true;
-
- case "connection timeout":
- case "connect timeout":
- connectionTimeout = Int32.Parse(value,
- System.Globalization.NumberFormatInfo.InvariantInfo);
- return true;
-
- case "port":
- port = UInt32.Parse(value,
- System.Globalization.NumberFormatInfo.InvariantInfo);
- return true;
-
- case "pooling":
- pooling = value.ToLower() == "yes" || value.ToLower() == "true";
- return true;
-
- case "min pool size":
- minPoolSize = Int32.Parse(value,
- System.Globalization.NumberFormatInfo.InvariantInfo);
- return true;
-
- case "max pool size":
- maxPoolSize = Int32.Parse(value,
- System.Globalization.NumberFormatInfo.InvariantInfo);
- return true;
-
- case "connection lifetime":
- poolLifeTime = Int32.Parse(value,
- System.Globalization.NumberFormatInfo.InvariantInfo);
- return true;
- }
- return false;
- }
-
- protected virtual void Parse()
+ database = value;
+ return true;
+
+ case "connection timeout":
+ case "connect timeout":
+ connectionTimeout = Int32.Parse(value,
+ System.Globalization.NumberFormatInfo.InvariantInfo);
+ return true;
+
+ case "port":
+ port = UInt32.Parse(value,
+ System.Globalization.NumberFormatInfo.InvariantInfo);
+ return true;
+
+ case "pooling":
+ pooling = value.ToLower() == "yes" || value.ToLower() == "true";
+ return true;
+
+ case "min pool size":
+ minPoolSize = Int32.Parse(value,
+ System.Globalization.NumberFormatInfo.InvariantInfo);
+ return true;
+
+ case "max pool size":
+ maxPoolSize = Int32.Parse(value,
+ System.Globalization.NumberFormatInfo.InvariantInfo);
+ return true;
+
+ case "connection lifetime":
+ poolLifeTime = Int32.Parse(value,
+ System.Globalization.NumberFormatInfo.InvariantInfo);
+ return true;
+ }
+ return false;
+ }
+
+ protected virtual void Parse()
{
String[] keyvalues = connectString.Split(';');
String[] newkeyvalues = new String[keyvalues.Length];
@@ -299,7 +299,7 @@
ConnectionParameterParsed(parts[0], parts[1]);
}
- }
-
- }
-}
+ }
+
+ }
+}
Modified: trunk/mysqlclient/common/StreamCreator.cs
===================================================================
--- trunk/mysqlclient/common/StreamCreator.cs 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/common/StreamCreator.cs 2006-04-14 17:12:18 UTC (rev 217)
@@ -1,162 +1,163 @@
-// Copyright (C) 2004-2005 MySQL AB
-//
-// This program is free software; you can redistribute it and/or modify
-// it under the terms of the GNU General Public License version 2 as published by
-// the Free Software Foundation
-//
-// There are special exceptions to the terms and conditions of the GPL
-// as it is applied to this software. View the full text of the
-// exception in file EXCEPTIONS in the directory of this software
-// distribution.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program; if not, write to the Free Software
-// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-
-using System;
-using System.IO;
-using System.Net;
-using System.Net.Sockets;
-using System.Collections;
-using System.Threading;
+// Copyright (C) 2004-2005 MySQL AB
+//
+// This program is free software; you can redistribute it and/or modify
+// it under the terms of the GNU General Public License version 2 as published by
+// the Free Software Foundation
+//
+// There are special exceptions to the terms and conditions of the GPL
+// as it is applied to this software. View the full text of the
+// exception in file EXCEPTIONS in the directory of this software
+// distribution.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+using System;
+using System.IO;
+using System.Net;
+using System.Net.Sockets;
+using System.Collections;
+using System.Threading;
using MySql.Data.MySqlClient;
-using System.Reflection;
-
-namespace MySql.Data.Common
-{
- /// <summary>
- /// Summary description for StreamCreator.
- /// </summary>
- internal class StreamCreator
- {
- private const uint FIONBIO = 0x8004667e;
- string hostList;
- uint port;
- string pipeName;
- int timeOut;
-
- public StreamCreator( string hosts, uint port, string pipeName)
- {
- hostList = hosts;
- if (hostList == null || hostList.Length == 0)
- hostList = "localhost";
- this.port = port;
- this.pipeName = pipeName;
- }
-
- public Stream GetStream(int timeOut)
- {
- this.timeOut = timeOut;
-
- if (hostList.StartsWith("/"))
- return CreateSocketStream(null, 0, true);
-
- string [] dnsHosts = hostList.Split('&');
- ArrayList ipAddresses = new ArrayList();
- ArrayList hostNames = new ArrayList();
-
- //
- // Each host name specified may contain multiple IP addresses
- // Lets look at the DNS entries for each host name
- foreach (string h in dnsHosts)
- {
- IPHostEntry hostAddress = Dns.GetHostByName(h);
- foreach (IPAddress addr in hostAddress.AddressList)
- {
- ipAddresses.Add( addr );
- hostNames.Add( hostAddress.HostName );
- }
- }
-
- System.Random random = new Random((int)DateTime.Now.Ticks);
- int index = random.Next(ipAddresses.Count);
-
- bool usePipe = (pipeName != null && pipeName.Length != 0);
- Stream stream = null;
- for (int i=0; i < ipAddresses.Count; i++)
- {
- if ( pipeName != null )
- stream = CreateNamedPipeStream( (string)hostNames[index] );
- else
- stream = CreateSocketStream( (IPAddress)ipAddresses[index], port, false );
- if (stream != null) return stream;
-
- index++;
- if (index == ipAddresses.Count) index = 0;
- }
-
- return stream;
- }
-
- private Stream CreateNamedPipeStream( string hostname )
- {
- string pipePath;
- if (0 == String.Compare(hostname, "localhost", true))
- pipePath = @"\\.\pipe\" + pipeName;
- else
- pipePath = String.Format(@"\\{0}\pipe\{1}", hostname.ToString(), pipeName);
- return new NamedPipeStream(pipePath, FileAccess.ReadWrite);
- }
-
- private EndPoint CreateUnixEndPoint(string host)
- {
- // first we need to load the Mono.posix assembly
- Assembly a = Assembly.LoadWithPartialName("Mono.Posix");
-
- // then we need to construct a UnixEndPoint object
- EndPoint ep = (EndPoint)a.CreateInstance("Mono.Posix.UnixEndPoint",
- false, BindingFlags.CreateInstance, null,
- new object[1] { host }, null, null);
- return ep;
- }
-
- private Stream CreateSocketStream( IPAddress ip, uint port, bool unix )
- {
- SocketStream ss = null;
- try
- {
- //
- // Lets try to connect
- EndPoint endPoint;
-
- if (!Platform.IsWindows() && unix)
- endPoint = CreateUnixEndPoint(hostList);
- else
- endPoint = new IPEndPoint(ip, (int)port);
-
- ss = unix ?
- new SocketStream(AddressFamily.Unix, SocketType.Stream, ProtocolType.IP) :
- new SocketStream(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
- ss.Connect(endPoint, timeOut);
- ss.Socket.SetSocketOption(SocketOptionLevel.Tcp, SocketOptionName.NoDelay, 1);
- return ss;
- }
- catch (ArgumentOutOfRangeException are)
- {
- Logger.LogException(are);
- ss = null;
- }
- catch (SocketException se)
- {
- Logger.LogException(se);
- ss = null;
- }
- catch (ObjectDisposedException ode)
- {
- Logger.LogException(ode);
- ss = null;
- }
- catch (MySqlException me)
- {
- Logger.LogException(me);
- ss = null;
- }
- return ss;
- }
- }
-}
+using System.Reflection;
+
+namespace MySql.Data.Common
+{
+ /// <summary>
+ /// Summary description for StreamCreator.
+ /// </summary>
+ internal class StreamCreator
+ {
+ private const uint FIONBIO = 0x8004667e;
+ string hostList;
+ uint port;
+ string pipeName;
+ int timeOut;
+
+ public StreamCreator( string hosts, uint port, string pipeName)
+ {
+ hostList = hosts;
+ if (hostList == null || hostList.Length == 0)
+ hostList = "localhost";
+ this.port = port;
+ this.pipeName = pipeName;
+ }
+
+ public Stream GetStream(int timeOut)
+ {
+ this.timeOut = timeOut;
+
+ if (hostList.StartsWith("/"))
+ return CreateSocketStream(null, 0, true);
+
+ string [] dnsHosts = hostList.Split('&');
+ ArrayList ipAddresses = new ArrayList();
+ ArrayList hostNames = new ArrayList();
+
+ //
+ // Each host name specified may contain multiple IP addresses
+ // Lets look at the DNS entries for each host name
+ foreach (string h in dnsHosts)
+ {
+ IPHostEntry hostAddress = Dns.GetHostByName(h);
+ foreach (IPAddress addr in hostAddress.AddressList)
+ {
+ ipAddresses.Add( addr );
+ hostNames.Add( hostAddress.HostName );
+ }
+ }
+
+ System.Random random = new Random((int)DateTime.Now.Ticks);
+ int index = random.Next(ipAddresses.Count);
+
+ bool usePipe = (pipeName != null && pipeName.Length != 0);
+ Stream stream = null;
+ for (int i=0; i < ipAddresses.Count; i++)
+ {
+ if ( pipeName != null )
+ stream = CreateNamedPipeStream( (string)hostNames[index] );
+ else
+ stream = CreateSocketStream( (IPAddress)ipAddresses[index], port, false );
+ if (stream != null) return stream;
+
+ index++;
+ if (index == ipAddresses.Count) index = 0;
+ }
+
+ return stream;
+ }
+
+ private Stream CreateNamedPipeStream( string hostname )
+ {
+ string pipePath;
+ if (0 == String.Compare(hostname, "localhost", true))
+ pipePath = @"\\.\pipe\" + pipeName;
+ else
+ pipePath = String.Format(@"\\{0}\pipe\{1}", hostname.ToString(), pipeName);
+ return new NamedPipeStream(pipePath, FileAccess.ReadWrite);
+ }
+
+ private EndPoint CreateUnixEndPoint(string host)
+ {
+ // first we need to load the Mono.posix assembly
+ Assembly a = Assembly.LoadWithPartialName("Mono.Posix");
+
+ // then we need to construct a UnixEndPoint object
+ EndPoint ep = (EndPoint)a.CreateInstance("Mono.Posix.UnixEndPoint",
+ false, BindingFlags.CreateInstance, null,
+ new object[1] { host }, null, null);
+ return ep;
+ }
+
+ private Stream CreateSocketStream( IPAddress ip, uint port, bool unix )
+ {
+ SocketStream ss = null;
+ try
+ {
+ //
+ // Lets try to connect
+ EndPoint endPoint;
+
+ if (!Platform.IsWindows() && unix)
+ endPoint = CreateUnixEndPoint(hostList);
+ else
+ endPoint = new IPEndPoint(ip, (int)port);
+
+ ss = unix ?
+ new SocketStream(AddressFamily.Unix, SocketType.Stream, ProtocolType.IP) :
+ new SocketStream(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
+ ss.Connect(endPoint, timeOut);
+ ss.Socket.SetSocketOption(SocketOptionLevel.Tcp, SocketOptionName.NoDelay, 1);
+ return ss;
+ }
+ catch (ArgumentOutOfRangeException are)
+ {
+ Logger.LogException(are);
+ ss = null;
+ }
+ catch (SocketException se)
+ {
+ Logger.LogException(se);
+ ss = null;
+ }
+ catch (ObjectDisposedException ode)
+ {
+ Logger.LogException(ode);
+ ss = null;
+ }
+ catch (MySqlException me)
+ {
+ Logger.LogException(me);
+ ss = null;
+ }
+ return ss;
+ }
+
+ }
+}
Modified: trunk/mysqlclient/docs/MySqlConnection.xml
===================================================================
--- trunk/mysqlclient/docs/MySqlConnection.xml 2006-04-14 04:54:27 UTC (rev 216)
+++ trunk/mysqlclient/docs/MySqlConnection.xml 2006-04-14 17:12:18 UTC (rev 217)
@@ -833,7 +833,25 @@
use the new '?' parameter marker.</note>
</td>
</tr>
- </table>
+ <tr>
+ <td>
+ Use Performance Monitor<para> -or- </para>UsePerformanceMonitor
+ </td>
+ <td>false</td>
+ <td>
+ Posts performance data that can be tracked using perfmon
+ </td>
+ </tr>
+ <tr>
+ <td>
+ Procedure Cache Size
+ </td>
+ <td>25</td>
+ <td>
+ How many stored procedure definitions can be held in the cache
+ </td>
+ </tr>
+ </table>
</div>
<para>
The following table lists the valid names for connection pooling values within
| Thread |
|---|
| • Connector/NET commit: r217 - in trunk: . TestSuite mysqlclient mysqlclient/common mysqlclient/docs | rburnett | 14 Apr |
