Modified:
trunk/TestSuite/BaseTest.cs
trunk/TestSuite/CommandBuilderTests.cs
trunk/TestSuite/ConnectionTests.cs
trunk/TestSuite/CultureTests.cs
trunk/TestSuite/DataAdapterTests.cs
trunk/TestSuite/DataReaderTests.cs
trunk/TestSuite/DataTypeTests.cs
trunk/TestSuite/DateTimeTests.cs
trunk/TestSuite/GetSchemaTests.cs
trunk/TestSuite/MySql.Data.Tests.2005.csproj
trunk/TestSuite/ParameterTests.cs
trunk/TestSuite/PreparedStatements.cs
trunk/TestSuite/StoredProcedure.cs
trunk/TestSuite/StressTests.cs
trunk/TestSuite/Syntax.cs
Log:
BaseTest
small fix to KillConnection
CommandBuilderTests
added appropriate ConflictOption setting
removed the logging csAddition
ConnectionTests
Fixed ConnectInVariousWays and ConnectionTimeout
CultureTests
Removing logging setting
DataAdapterTests
Fixed TestUpdate to reflect the fact that the CommandBuilder no longer includes a select statement
Fixed AutoIncrementColumns to reflect the fact that you can't jump values any longer.
DataReaderTests
Removed the check in ConsecutiveNulls for return of null (not possible)
DataTypeTests
Refactored BytesAndBooleans into two test cases
Fixed BitAndDecimal and DecimalTests tests after failed merge.
DateTimeTests
Overrode Setup to recreate the table for every test.
Cleaned up white space around DateAdd
GetSchemaTests
Implemented some more tests (still more to go)
ParameterTests
Overrode Setup to recreate table for every test
PreparedStatements
Added necessary drop table query in ParameterLengths
StoredProcedure
Several fixes, most of the related to making sure the right table exists at the right time in the test.
StressTests
Syntax
Whitespace removal
Modified: trunk/TestSuite/BaseTest.cs
===================================================================
--- trunk/TestSuite/BaseTest.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/BaseTest.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -117,7 +117,7 @@
bool exists = reader.Read();
reader.Close();
if (exists)
- execSQL( "TRUNCATE TABLE Test" );
+ execSQL("TRUNCATE TABLE Test");
if (Is50)
{
execSQL("DROP PROCEDURE IF EXISTS spTest");
@@ -126,7 +126,7 @@
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
}
@@ -143,8 +143,15 @@
protected void KillConnection(MySqlConnection c)
{
int threadId = c.ServerThread;
- MySqlCommand cmd = new MySqlCommand("KILL " + threadId, c);
- cmd.ExecuteNonQuery();
+ MySqlCommand cmd = new MySqlCommand("KILL " + threadId, conn);
+ try
+ {
+ cmd.ExecuteNonQuery();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
c.Ping(); // this final ping will cause MySQL to clean up the killed thread
}
Modified: trunk/TestSuite/CommandBuilderTests.cs
===================================================================
--- trunk/TestSuite/CommandBuilderTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/CommandBuilderTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -30,7 +30,6 @@
[TestFixtureSetUp]
public void FixtureSetup()
{
- csAdditions += ";logging=true";
Open();
execSQL("DROP TABLE IF EXISTS Test");
@@ -105,7 +104,8 @@
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- DataTable dt = new DataTable();
+ cb.ConflictOption = ConflictOption.CompareAllSearchableValues;
+ DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
@@ -114,7 +114,7 @@
try
{
dt.Rows[0]["name"] = "Test3";
- da.Update( dt );
+ da.Update(dt);
Assert.Fail("This should not work");
}
catch (DBConcurrencyException)
@@ -122,9 +122,9 @@
}
dt.Rows.Clear();
- da.Fill( dt );
- Assert.AreEqual( 1, dt.Rows.Count );
- Assert.AreEqual( "Test2", dt.Rows[0]["name"] );
+ da.Fill(dt);
+ Assert.AreEqual(1, dt.Rows.Count);
+ Assert.AreEqual("Test2", dt.Rows[0]["name"]);
}
/// <summary>
Modified: trunk/TestSuite/ConnectionTests.cs
===================================================================
--- trunk/TestSuite/ConnectionTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/ConnectionTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -1,407 +1,407 @@
-// Copyright (C) 2004-2006 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.Configuration;
-
-namespace MySql.Data.MySqlClient.Tests
-{
- /// <summary>
- /// Summary description for ConnectionTests.
- /// </summary>
- [TestFixture]
- public class ConnectionTests : BaseTest
- {
- [TestFixtureSetUp]
- public void FixtureSetup()
- {
- Open();
- }
-
- [TestFixtureTearDown]
- public void FixtureTearDown()
- {
- Close();
- }
-
- [Test]
- public void ConnectionStringBuilder()
- {
- MySqlConnectionStringBuilder sb = null;
- try
- {
- sb = new MySqlConnectionStringBuilder();
- sb.ConnectionString = "server=localhost;uid=reggie;pwd=pass;port=1111;" +
- "connection timeout=23; pooling=true; min pool size=33; " +
- "max pool size=66";
- }
- catch (ArgumentException ex)
- {
- Assert.Fail(ex.Message);
- }
- Assert.AreEqual("localhost", sb.Server);
- Assert.AreEqual("reggie", sb.UserID);
- Assert.AreEqual("pass", sb.Password);
- Assert.AreEqual(1111, sb.Port);
- Assert.AreEqual(23, sb.ConnectionTimeout);
- Assert.IsTrue(sb.Pooling);
- Assert.AreEqual(33, sb.MinimumPoolSize);
- Assert.AreEqual(66, sb.MaximumPoolSize);
- string s = sb.ConnectionString;
-
- try
- {
- sb.ConnectionString = "server=localhost;badkey=badvalue";
- Assert.Fail("This should not work");
- }
- catch (ArgumentException)
- {
- }
- catch (Exception)
- {
- Assert.Fail("Wrong exception type");
- }
-
- sb.Clear();
- Assert.AreEqual(15, sb.ConnectionTimeout);
- Assert.AreEqual(true, sb.Pooling);
- Assert.AreEqual(3306, sb.Port);
- Assert.AreEqual(String.Empty, sb.Server);
- Assert.AreEqual(false, sb.PersistSecurityInfo);
- Assert.AreEqual(0, sb.ConnectionLifeTime);
- Assert.IsFalse(sb.ConnectionReset);
- Assert.AreEqual(0, sb.MinimumPoolSize);
- Assert.AreEqual(100, sb.MaximumPoolSize);
- Assert.AreEqual(String.Empty, sb.UserID);
- Assert.AreEqual(String.Empty, sb.Password);
- Assert.AreEqual(false, sb.UseUsageAdvisor);
- Assert.AreEqual(String.Empty, sb.CharacterSet);
- Assert.AreEqual(false, sb.UseCompression);
- Assert.AreEqual("MYSQL", sb.PipeName);
- Assert.IsFalse(sb.Logging);
- Assert.IsFalse(sb.UseOldSyntax);
- Assert.IsTrue(sb.AllowBatch);
- Assert.IsFalse(sb.ConvertZeroDateTime);
- Assert.AreEqual("MYSQL", sb.SharedMemoryName);
- Assert.AreEqual(String.Empty, sb.Database);
- Assert.AreEqual(MySqlDriverType.Native, sb.DriverType);
- Assert.AreEqual(MySqlConnectionProtocol.Sockets, sb.ConnectionProtocol);
- Assert.IsFalse(sb.AllowZeroDateTime);
- Assert.IsFalse(sb.UsePerformanceMonitor);
- Assert.AreEqual(25, sb.ProcedureCacheSize);
- }
-
- [Test]
- public void TestConnectionStrings()
- {
- MySqlConnection c = new MySqlConnection();
-
- // public properties
- Assert.AreEqual(15, c.ConnectionTimeout, "ConnectionTimeout");
- Assert.AreEqual("", c.Database, "Database");
- Assert.AreEqual(String.Empty, c.DataSource, "DataSource");
- Assert.AreEqual(false, c.UseCompression, "Use Compression");
- Assert.AreEqual(System.Data.ConnectionState.Closed, c.State, "State");
-
- c = new MySqlConnection("connection timeout=25; user id=myuser; " +
- "password=mypass; database=Test;server=myserver; use compression=true; " +
- "pooling=false;min pool size=5; max pool size=101");
- // public properties
- Assert.AreEqual(25, c.ConnectionTimeout, "ConnectionTimeout");
- Assert.AreEqual("Test", c.Database, "Database");
- Assert.AreEqual("myserver", c.DataSource, "DataSource");
- Assert.AreEqual(true, c.UseCompression, "Use Compression");
- Assert.AreEqual(System.Data.ConnectionState.Closed, c.State, "State");
-
- c.ConnectionString = "connection timeout=15; user id=newuser; " +
- "password=newpass; port=3308; database=mydb; data source=myserver2; " +
- "use compression=true; pooling=true; min pool size=3; max pool size=76";
-
- // public properties
- Assert.AreEqual(15, c.ConnectionTimeout, "ConnectionTimeout");
- Assert.AreEqual("mydb", c.Database, "Database");
- Assert.AreEqual("myserver2", c.DataSource, "DataSource");
- Assert.AreEqual(true, c.UseCompression, "Use Compression");
- Assert.AreEqual(System.Data.ConnectionState.Closed, c.State, "State");
- }
-
- [Test]
- [ExpectedException(typeof(MySqlException))]
- public void TestConnectingSocketBadUserName()
- {
- execSQL("DELETE FROM mysql.user WHERE length(user) = 0");
- execSQL("FLUSH PRIVILEGES");
-
- string connStr = "server={0};user id=dummy;password=;database=Test;pooling=false";
- MySqlConnection c = new MySqlConnection(
- String.Format(connStr, host));
- c.Open();
- c.Close();
- }
-
- [Test]
- [ExpectedException(typeof(MySqlException))]
- public void TestConnectingSocketBadDbName()
- {
- string connStr = "server={0};user id={1};password={2};database=dummy; " +
- "pooling=false";
- MySqlConnection c = new MySqlConnection(
- String.Format(connStr, host, this.user, this.password));
- c.Open();
- c.Close();
- }
-
- [Test]
- public void TestPersistSecurityInfoCachingPasswords()
- {
- string connStr = String.Format("database=test;server={0};user id={1};Password={2}; pooling=false",
- host, this.user, this.password );
- MySqlConnection c = new MySqlConnection( connStr );
- c.Open();
- c.Close();
-
- // this shouldn't work
- connStr = String.Format("database=test;server={0};user id={1};Password={2}; pooling=false",
- host, this.user, "bad_password" );
- c = new MySqlConnection( connStr );
- try
- {
- c.Open();
- Assert.Fail("Thn is should not work");
- c.Close();
- return;
- }
- catch (MySqlException)
- {
- }
-
- // this should work
- connStr = String.Format("database=test;server={0};user id={1};Password={2}; pooling=false",
- host, this.user, this.password);
- c = new MySqlConnection( connStr );
- c.Open();
- c.Close();
- }
-
- [Test]
- public void ChangeDatabase()
- {
- string connStr = GetConnectionString(true);
- MySqlConnection c = new MySqlConnection(connStr + ";pooling=false");
- c.Open();
- Assert.IsTrue(c.State == ConnectionState.Open);
-
- Assert.AreEqual("test", c.Database.ToLower());
-
- c.ChangeDatabase("mysql");
-
- Assert.AreEqual("mysql", c.Database.ToLower());
-
- c.Close();
- }
-
- [Test]
- public void ConnectionTimeout()
- {
- MySqlConnection c = new MySqlConnection(
- "server=1.1.1.1;user id=bogus;pwd=bogus;Connection timeout=5;" +
- "pooling=false");
- DateTime start = DateTime.Now;
- try
- {
- c.Open();
- }
- catch (Exception)
- {
- TimeSpan diff = DateTime.Now.Subtract(start);
- Assert.IsTrue(diff.TotalSeconds < 6, "Timeout exceeded");
- }
- }
-
- [Test]
- public void ConnectInVariousWays()
- {
- try
- {
- string connStr = conn.ConnectionString;
-
- // connect with no db
- string connStr2 = connStr.Replace("database=test;","");
- MySqlConnection c = new MySqlConnection( connStr2 );
- c.Open();
- c.Close();
-
- // TODO: make anonymous login work
- execSQL("CREATE USER ''");
-
- // connect with all defaults
- if (connStr.IndexOf("localhost") != -1)
- {
- c = new MySqlConnection(String.Empty);
- c.Open();
- c.Close();
- }
-
- execSQL("GRANT ALL ON *.* to 'nopass'@'localhost'");
- execSQL("FLUSH PRIVILEGES");
-
- // connect with no password
- connStr2 = "server=" + host + ";user id=nopass";
- c = new MySqlConnection( connStr2 );
- c.Open();
- c.Close();
-
- connStr2 += ";password=;";
- c = new MySqlConnection(connStr2);
- c.Open();
- c.Close();
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- execSQL("DELETE FROM mysql.user WHERE length(user) = 0");
- execSQL("DELETE FROM mysql.user WHERE user='nopass'");
- execSQL("FLUSH PRIVILEGES");
- }
- }
-
- [Test]
- [Category("4.1")]
- public void ConnectingAsUTF8()
- {
- execSQL("CREATE Database IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8");
-
- string connStr = String.Format("server={0};user id={1}; password={2}; database=test2;pooling=false;charset=utf8",
- host, user, password);
- MySqlConnection c = new MySqlConnection(connStr);
- c.Open();
-
- MySqlCommand cmd = new MySqlCommand("DROP TABLE IF EXISTS test;CREATE TABLE test (id varbinary(16), active bit)", c);
- cmd.ExecuteNonQuery();
- cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x1234567890 AS Binary), true)";
- cmd.ExecuteNonQuery();
- cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x123456789a AS Binary), true)";
- cmd.ExecuteNonQuery();
- cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x123456789b AS Binary), true)";
- cmd.ExecuteNonQuery();
- c.Close();
-
- MySqlConnection d = new MySqlConnection(connStr);
- d.Open();
-
- MySqlCommand cmd2 = new MySqlCommand("SELECT id, active FROM test", d);
- MySqlDataReader reader = null;
- try
- {
- reader = cmd2.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.IsTrue(reader.GetBoolean(1));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- }
-
- d.Close();
-
- execSQL("DROP DATABASE IF EXISTS test2");
- }
-
- /// <summary>
- /// Bug #10281 Clone issue with MySqlConnection
- /// </summary>
- [Test]
- public void TestConnectionClone()
- {
- MySqlConnection c = new MySqlConnection();
- MySqlConnection clone = (MySqlConnection) ((ICloneable)c).Clone();
- clone.ToString();
- }
-
- /// <summary>
- /// Bug #13321 Persist security info does not woek
- /// </summary>
- [Test]
- public void PersistSecurityInfo()
- {
- string s = GetConnectionString(true).ToLower();
- int start = s.IndexOf("persist security info");
- int end = s.IndexOf(";", start);
- string newConnStr = s.Substring(0, start);
- newConnStr += s.Substring(end, s.Length - (end));
- newConnStr += ";persist security info=false";
-
- MySqlConnection conn2 = new MySqlConnection(newConnStr);
- string p = "password";
- if (conn2.ConnectionString.IndexOf("pwd") != -1)
- p = "pwd";
- else if (conn2.ConnectionString.IndexOf("passwd") != -1)
- p = "passwd";
-
- Assert.IsTrue(conn2.ConnectionString.IndexOf(p) != -1);
- conn2.Open();
- conn2.Close();
- Assert.IsTrue(conn2.ConnectionString.IndexOf(p) == -1);
- }
-
- /// <summary>
- /// Bug #13658 connection.state does not update on Ping()
- /// </summary>
- [Test]
- public void PingUpdatesState()
- {
- MySqlConnection conn2 = new MySqlConnection(GetConnectionString(true));
- conn2.Open();
- KillConnection(conn2);
- Assert.IsFalse(conn2.Ping());
- Assert.IsTrue(conn2.State == ConnectionState.Closed);
- }
-
- /// <summary>
- /// Bug #16659 Can't use double quotation marks(") as password access server by Connector/NET
- /// </summary>
- [Test]
- public void ConnectWithQuotePassword()
- {
- execSQL("GRANT ALL ON *.* to 'test'@'localhost' IDENTIFIED BY '\"'");
- MySqlConnection c = new MySqlConnection("server=" + host + ";uid=test;pwd='\"';pooling=false");
- try
- {
- c.Open();
- c.Close();
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- execSQL("DELETE FROM mysql.user WHERE user='test'");
- }
- }
-}
+// Copyright (C) 2004-2006 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.Configuration;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ /// <summary>
+ /// Summary description for ConnectionTests.
+ /// </summary>
+ [TestFixture]
+ public class ConnectionTests : BaseTest
+ {
+ [TestFixtureSetUp]
+ public void FixtureSetup()
+ {
+ Open();
+ }
+
+ [TestFixtureTearDown]
+ public void FixtureTearDown()
+ {
+ Close();
+ }
+
+ [Test]
+ public void ConnectionStringBuilder()
+ {
+ MySqlConnectionStringBuilder sb = null;
+ try
+ {
+ sb = new MySqlConnectionStringBuilder();
+ sb.ConnectionString = "server=localhost;uid=reggie;pwd=pass;port=1111;" +
+ "connection timeout=23; pooling=true; min pool size=33; " +
+ "max pool size=66";
+ }
+ catch (ArgumentException ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ Assert.AreEqual("localhost", sb.Server);
+ Assert.AreEqual("reggie", sb.UserID);
+ Assert.AreEqual("pass", sb.Password);
+ Assert.AreEqual(1111, sb.Port);
+ Assert.AreEqual(23, sb.ConnectionTimeout);
+ Assert.IsTrue(sb.Pooling);
+ Assert.AreEqual(33, sb.MinimumPoolSize);
+ Assert.AreEqual(66, sb.MaximumPoolSize);
+ string s = sb.ConnectionString;
+
+ try
+ {
+ sb.ConnectionString = "server=localhost;badkey=badvalue";
+ Assert.Fail("This should not work");
+ }
+ catch (ArgumentException)
+ {
+ }
+ catch (Exception)
+ {
+ Assert.Fail("Wrong exception type");
+ }
+
+ sb.Clear();
+ Assert.AreEqual(15, sb.ConnectionTimeout);
+ Assert.AreEqual(true, sb.Pooling);
+ Assert.AreEqual(3306, sb.Port);
+ Assert.AreEqual(String.Empty, sb.Server);
+ Assert.AreEqual(false, sb.PersistSecurityInfo);
+ Assert.AreEqual(0, sb.ConnectionLifeTime);
+ Assert.IsFalse(sb.ConnectionReset);
+ Assert.AreEqual(0, sb.MinimumPoolSize);
+ Assert.AreEqual(100, sb.MaximumPoolSize);
+ Assert.AreEqual(String.Empty, sb.UserID);
+ Assert.AreEqual(String.Empty, sb.Password);
+ Assert.AreEqual(false, sb.UseUsageAdvisor);
+ Assert.AreEqual(String.Empty, sb.CharacterSet);
+ Assert.AreEqual(false, sb.UseCompression);
+ Assert.AreEqual("MYSQL", sb.PipeName);
+ Assert.IsFalse(sb.Logging);
+ Assert.IsFalse(sb.UseOldSyntax);
+ Assert.IsTrue(sb.AllowBatch);
+ Assert.IsFalse(sb.ConvertZeroDateTime);
+ Assert.AreEqual("MYSQL", sb.SharedMemoryName);
+ Assert.AreEqual(String.Empty, sb.Database);
+ Assert.AreEqual(MySqlDriverType.Native, sb.DriverType);
+ Assert.AreEqual(MySqlConnectionProtocol.Sockets, sb.ConnectionProtocol);
+ Assert.IsFalse(sb.AllowZeroDateTime);
+ Assert.IsFalse(sb.UsePerformanceMonitor);
+ Assert.AreEqual(25, sb.ProcedureCacheSize);
+ }
+
+ [Test]
+ public void TestConnectionStrings()
+ {
+ MySqlConnection c = new MySqlConnection();
+
+ // public properties
+ Assert.AreEqual(15, c.ConnectionTimeout, "ConnectionTimeout");
+ Assert.AreEqual("", c.Database, "Database");
+ Assert.AreEqual(String.Empty, c.DataSource, "DataSource");
+ Assert.AreEqual(false, c.UseCompression, "Use Compression");
+ Assert.AreEqual(System.Data.ConnectionState.Closed, c.State, "State");
+
+ c = new MySqlConnection("connection timeout=25; user id=myuser; " +
+ "password=mypass; database=Test;server=myserver; use compression=true; " +
+ "pooling=false;min pool size=5; max pool size=101");
+ // public properties
+ Assert.AreEqual(25, c.ConnectionTimeout, "ConnectionTimeout");
+ Assert.AreEqual("Test", c.Database, "Database");
+ Assert.AreEqual("myserver", c.DataSource, "DataSource");
+ Assert.AreEqual(true, c.UseCompression, "Use Compression");
+ Assert.AreEqual(System.Data.ConnectionState.Closed, c.State, "State");
+
+ c.ConnectionString = "connection timeout=15; user id=newuser; " +
+ "password=newpass; port=3308; database=mydb; data source=myserver2; " +
+ "use compression=true; pooling=true; min pool size=3; max pool size=76";
+
+ // public properties
+ Assert.AreEqual(15, c.ConnectionTimeout, "ConnectionTimeout");
+ Assert.AreEqual("mydb", c.Database, "Database");
+ Assert.AreEqual("myserver2", c.DataSource, "DataSource");
+ Assert.AreEqual(true, c.UseCompression, "Use Compression");
+ Assert.AreEqual(System.Data.ConnectionState.Closed, c.State, "State");
+ }
+
+ [Test]
+ [ExpectedException(typeof(MySqlException))]
+ public void TestConnectingSocketBadUserName()
+ {
+ execSQL("DELETE FROM mysql.user WHERE length(user) = 0");
+ execSQL("FLUSH PRIVILEGES");
+
+ string connStr = "server={0};user id=dummy;password=;database=Test;pooling=false";
+ MySqlConnection c = new MySqlConnection(
+ String.Format(connStr, host));
+ c.Open();
+ c.Close();
+ }
+
+ [Test]
+ [ExpectedException(typeof(MySqlException))]
+ public void TestConnectingSocketBadDbName()
+ {
+ string connStr = "server={0};user id={1};password={2};database=dummy; " +
+ "pooling=false";
+ MySqlConnection c = new MySqlConnection(
+ String.Format(connStr, host, this.user, this.password));
+ c.Open();
+ c.Close();
+ }
+
+ [Test]
+ public void TestPersistSecurityInfoCachingPasswords()
+ {
+ string connStr = String.Format("database=test;server={0};user id={1};Password={2}; pooling=false",
+ host, this.user, this.password );
+ MySqlConnection c = new MySqlConnection( connStr );
+ c.Open();
+ c.Close();
+
+ // this shouldn't work
+ connStr = String.Format("database=test;server={0};user id={1};Password={2}; pooling=false",
+ host, this.user, "bad_password" );
+ c = new MySqlConnection( connStr );
+ try
+ {
+ c.Open();
+ Assert.Fail("Thn is should not work");
+ c.Close();
+ return;
+ }
+ catch (MySqlException)
+ {
+ }
+
+ // this should work
+ connStr = String.Format("database=test;server={0};user id={1};Password={2}; pooling=false",
+ host, this.user, this.password);
+ c = new MySqlConnection( connStr );
+ c.Open();
+ c.Close();
+ }
+
+ [Test]
+ public void ChangeDatabase()
+ {
+ string connStr = GetConnectionString(true);
+ MySqlConnection c = new MySqlConnection(connStr + ";pooling=false");
+ c.Open();
+ Assert.IsTrue(c.State == ConnectionState.Open);
+
+ Assert.AreEqual("test", c.Database.ToLower());
+
+ c.ChangeDatabase("mysql");
+
+ Assert.AreEqual("mysql", c.Database.ToLower());
+
+ c.Close();
+ }
+
+ [Test]
+ public void ConnectionTimeout()
+ {
+ MySqlConnection c = new MySqlConnection(
+ "server=1.1.1.1;user id=bogus;pwd=bogus;Connection timeout=5;" +
+ "pooling=false");
+ DateTime start = DateTime.Now;
+ try
+ {
+ c.Open();
+ }
+ catch (Exception)
+ {
+ TimeSpan diff = DateTime.Now.Subtract(start);
+ Assert.IsTrue(diff.TotalSeconds < 30, "Timeout exceeded");
+ }
+ }
+
+ [Test]
+ public void ConnectInVariousWays()
+ {
+ try
+ {
+ string connStr = conn.ConnectionString;
+
+ // connect with no db
+ string connStr2 = GetConnectionString(false);
+ MySqlConnection c = new MySqlConnection(connStr2);
+ c.Open();
+ c.Close();
+
+ // TODO: make anonymous login work
+ execSQL("GRANT ALL ON *.* to '' IDENTIFIED BY ''");
+
+ // connect with all defaults
+ if (connStr.IndexOf("localhost") != -1)
+ {
+ c = new MySqlConnection(String.Empty);
+ c.Open();
+ c.Close();
+ }
+
+ execSQL("GRANT ALL ON *.* to 'nopass'@'localhost'");
+ execSQL("FLUSH PRIVILEGES");
+
+ // connect with no password
+ connStr2 = "server=" + host + ";user id=nopass";
+ c = new MySqlConnection( connStr2 );
+ c.Open();
+ c.Close();
+
+ connStr2 += ";password=;";
+ c = new MySqlConnection(connStr2);
+ c.Open();
+ c.Close();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ execSQL("DELETE FROM mysql.user WHERE length(user) = 0");
+ execSQL("DELETE FROM mysql.user WHERE user='nopass'");
+ execSQL("FLUSH PRIVILEGES");
+ }
+ }
+
+ [Test]
+ [Category("4.1")]
+ public void ConnectingAsUTF8()
+ {
+ execSQL("CREATE Database IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8");
+
+ string connStr = String.Format("server={0};user id={1}; password={2}; database=test2;pooling=false;charset=utf8",
+ host, user, password);
+ MySqlConnection c = new MySqlConnection(connStr);
+ c.Open();
+
+ MySqlCommand cmd = new MySqlCommand("DROP TABLE IF EXISTS test;CREATE TABLE test (id varbinary(16), active bit)", c);
+ cmd.ExecuteNonQuery();
+ cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x1234567890 AS Binary), true)";
+ cmd.ExecuteNonQuery();
+ cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x123456789a AS Binary), true)";
+ cmd.ExecuteNonQuery();
+ cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x123456789b AS Binary), true)";
+ cmd.ExecuteNonQuery();
+ c.Close();
+
+ MySqlConnection d = new MySqlConnection(connStr);
+ d.Open();
+
+ MySqlCommand cmd2 = new MySqlCommand("SELECT id, active FROM test", d);
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd2.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.IsTrue(reader.GetBoolean(1));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+
+ d.Close();
+
+ execSQL("DROP DATABASE IF EXISTS test2");
+ }
+
+ /// <summary>
+ /// Bug #10281 Clone issue with MySqlConnection
+ /// </summary>
+ [Test]
+ public void TestConnectionClone()
+ {
+ MySqlConnection c = new MySqlConnection();
+ MySqlConnection clone = (MySqlConnection) ((ICloneable)c).Clone();
+ clone.ToString();
+ }
+
+ /// <summary>
+ /// Bug #13321 Persist security info does not woek
+ /// </summary>
+ [Test]
+ public void PersistSecurityInfo()
+ {
+ string s = GetConnectionString(true).ToLower();
+ int start = s.IndexOf("persist security info");
+ int end = s.IndexOf(";", start);
+ string newConnStr = s.Substring(0, start);
+ newConnStr += s.Substring(end, s.Length - (end));
+ newConnStr += ";persist security info=false";
+
+ MySqlConnection conn2 = new MySqlConnection(newConnStr);
+ string p = "password";
+ if (conn2.ConnectionString.IndexOf("pwd") != -1)
+ p = "pwd";
+ else if (conn2.ConnectionString.IndexOf("passwd") != -1)
+ p = "passwd";
+
+ Assert.IsTrue(conn2.ConnectionString.IndexOf(p) != -1);
+ conn2.Open();
+ conn2.Close();
+ Assert.IsTrue(conn2.ConnectionString.IndexOf(p) == -1);
+ }
+
+ /// <summary>
+ /// Bug #13658 connection.state does not update on Ping()
+ /// </summary>
+ [Test]
+ public void PingUpdatesState()
+ {
+ MySqlConnection conn2 = new MySqlConnection(GetConnectionString(true));
+ conn2.Open();
+ KillConnection(conn2);
+ Assert.IsFalse(conn2.Ping());
+ Assert.IsTrue(conn2.State == ConnectionState.Closed);
+ }
+
+ /// <summary>
+ /// Bug #16659 Can't use double quotation marks(") as password access server by Connector/NET
+ /// </summary>
+ [Test]
+ public void ConnectWithQuotePassword()
+ {
+ execSQL("GRANT ALL ON *.* to 'test'@'localhost' IDENTIFIED BY '\"'");
+ MySqlConnection c = new MySqlConnection("server=" + host + ";uid=test;pwd='\"';pooling=false");
+ try
+ {
+ c.Open();
+ c.Close();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ execSQL("DELETE FROM mysql.user WHERE user='test'");
+ }
+ }
+}
Modified: trunk/TestSuite/CultureTests.cs
===================================================================
--- trunk/TestSuite/CultureTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/CultureTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -33,7 +33,6 @@
[TestFixtureSetUp]
public void FixtureSetup()
{
- csAdditions += ";logging=true";
Open();
execSQL("DROP TABLE IF EXISTS Test");
@@ -68,7 +67,7 @@
CultureInfo c = new CultureInfo("de-DE");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
-
+
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (fl FLOAT, db DOUBLE, dec1 DECIMAL(5,2))");
Modified: trunk/TestSuite/DataAdapterTests.cs
===================================================================
--- trunk/TestSuite/DataAdapterTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/DataAdapterTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -1,480 +1,477 @@
-// Copyright (C) 2004-2006 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 MySql.Data.MySqlClient;
-using System.Data;
-using NUnit.Framework;
-
-namespace MySql.Data.MySqlClient.Tests
-{
- /// <summary>
- /// Summary description for ConnectionTests.
- /// </summary>
- [TestFixture]
- public class DataAdapterTests : BaseTest
- {
-
- [TestFixtureSetUp]
- public void FixtureSetup()
- {
- Open();
- }
-
- [SetUp]
- protected override void Setup()
- {
- base.Setup();
-
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id INT NOT NULL AUTO_INCREMENT, id2 INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, ts TIMESTAMP, OriginalId INT, PRIMARY KEY(id, id2))");
- }
-
-
- [Test]
- public void TestFill()
- {
- FillImpl(false);
- FillImpl(true);
- }
-
- private void FillImpl(bool prepare)
- {
- execSQL("TRUNCATE TABLE Test");
- execSQL("INSERT INTO Test (id, id2, name, dt) VALUES (NULL, 1, 'Name 1', Now())");
- execSQL("INSERT INTO Test (id, id2, name, dt) VALUES (NULL, 2, NULL, Now())");
- execSQL("INSERT INTO Test (id, id2, name, dt) VALUES (NULL, 3, '', Now())");
-
- MySqlDataAdapter da = new MySqlDataAdapter("select * from Test", conn);
- if (prepare) da.SelectCommand.Prepare();
- DataSet ds = new DataSet();
- da.Fill(ds, "Test");
-
- Assert.AreEqual(1, ds.Tables.Count);
- Assert.AreEqual(3, ds.Tables[0].Rows.Count);
-
- Assert.AreEqual(1, ds.Tables[0].Rows[0]["id2"]);
- Assert.AreEqual(2, ds.Tables[0].Rows[1]["id2"]);
- Assert.AreEqual(3, ds.Tables[0].Rows[2]["id2"]);
-
- Assert.AreEqual("Name 1", ds.Tables[0].Rows[0]["name"]);
- Assert.AreEqual(DBNull.Value, ds.Tables[0].Rows[1]["name"]);
- Assert.AreEqual(String.Empty, ds.Tables[0].Rows[2]["name"]);
- }
-
- [Test]
- public void TestUpdate()
- {
- MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE Test", conn);
- cmd.ExecuteNonQuery();
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- DataRow dr = dt.NewRow();
- dr["id2"] = 2;
- dr["name"] = "TestName1";
- dt.Rows.Add( dr );
-
- int count = da.Update(dt);
-
- // make sure our refresh of auto increment values worked
- Assert.AreEqual( 1, count, "checking insert count" );
- Assert.IsNotNull( dt.Rows[ dt.Rows.Count-1 ]["id"], "Checking auto increment column" );
-
- dt.Rows[0]["id2"] = 3;
- dt.Rows[0]["name"] = "TestName2";
- dt.Rows[0]["ts"] = DBNull.Value;
- DateTime day1 = new DateTime(2003, 1, 16, 12, 24, 0);
- dt.Rows[0]["dt"] = day1;
- dt.Rows[0]["tm"] = day1.TimeOfDay;
- count = da.Update( dt );
-
- Assert.IsNotNull( dt.Rows[0]["ts"], "checking refresh of record" );
- Assert.AreEqual( 3, dt.Rows[0]["id2"], "checking refresh of primary column" );
-
- dt.Rows.Clear();
- da.Fill( dt );
-
- Assert.AreEqual( 1, count, "checking update count" );
- DateTime dateTime = (DateTime)dt.Rows[0]["dt"];
- Assert.AreEqual( day1, dateTime, "checking date" );
- Assert.AreEqual( day1.TimeOfDay, dt.Rows[0]["tm"], "checking time" );
-
- dt.Rows[0].Delete();
- count = da.Update( dt );
-
- Assert.AreEqual( 1, count, "checking insert count" );
-
- dt.Rows.Clear();
- da.Fill(dt);
- Assert.AreEqual( 0, dt.Rows.Count, "checking row count" );
- }
-
- [Test]
- public void OriginalInName()
- {
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- DataRow row = dt.NewRow();
- row["id"] = DBNull.Value;
- row["id2"] = 1;
- row["name"] = "Test";
- row["dt"] = DBNull.Value;
- row["tm"] = DBNull.Value;
- row["ts"] = DBNull.Value;
- row["OriginalId"] = 2;
- dt.Rows.Add( row );
- da.Update(dt);
-
- Assert.AreEqual( 1, dt.Rows.Count );
- Assert.AreEqual( 2, dt.Rows[0]["OriginalId"] );
- }
-
- [Test]
- public void UseAdapterPropertyOfCommandBuilder()
- {
- execSQL("INSERT INTO Test (id, id2, name) VALUES (NULL, 1, 'Test')");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder();
- cb.DataAdapter = da;
-
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- dt.Rows[0]["name"] = "Test Update";
- int updateCnt = da.Update(dt);
-
- Assert.AreEqual( 1, updateCnt );
-
- dt.Rows.Clear();
- da.Fill(dt);
-
- Assert.AreEqual( 1, dt.Rows.Count );
- Assert.AreEqual( "Test Update", dt.Rows[0]["name"] );
- }
-
- [Test]
- public void UpdateNullTextFieldToEmptyString()
- {
- execSQL("INSERT INTO Test (id, id2, name) VALUES (1, 1, NULL)");
-
- try
- {
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
-
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- dt.Rows[0]["name"] = "";
- int updateCnt = da.Update(dt);
-
- Assert.AreEqual( 1, updateCnt );
-
- dt.Rows.Clear();
- da.Fill(dt);
-
- Assert.AreEqual( 1, dt.Rows.Count );
- Assert.AreEqual( "", dt.Rows[0]["name"] );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- }
-
- [Test]
- public void UpdateExtendedTextFields()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL( "CREATE TABLE Test (id int, notes MEDIUMTEXT, PRIMARY KEY(id))" );
- execSQL("INSERT INTO Test VALUES(1, 'This is my note')" );
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- dt.Rows[0]["notes"] = "This is my new note";
- da.Update( dt );
-
- dt.Clear();
- da.Fill(dt);
- Assert.AreEqual( "This is my new note", dt.Rows[0]["notes"]);
- }
-
- [Test]
- public void SelectMoreThan252Rows()
- {
- for (int i=0; i < 500; i++)
- execSQL("INSERT INTO Test(id, id2) VALUES(NULL, " + i + ")");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- Assert.AreEqual( 500, dt.Rows.Count );
- }
-
-/* [Test]
- [Explicit]
- public void UpdateManyRows()
- {
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- for (int i=0; i < 100000; i++)
- {
- DataRow dr = dt.NewRow();
- dr["id"] = DBNull.Value;
- dr["id2"] = i;
- dt.Rows.Add( dr );
- DataTable changes = dt.GetChanges();
- da.Update( changes );
- dt.AcceptChanges();
- }
-
- dt.Clear();
- da.Fill(dt);
- Assert.AreEqual(100000, dt.Rows.Count);
- }
-*/
- [Test]
- public void DiscreteValues()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id int, name varchar(200), dt DATETIME, b1 TEXT)");
- execSQL("INSERT INTO Test VALUES (1, 'Test', '2004-08-01', 'Text 1')");
- execSQL("INSERT INTO Test VALUES (2, 'Test 1', '2004-07-02', 'Text 2')");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- Assert.AreEqual( "Test", dt.Rows[0]["name"] );
- Assert.AreEqual( "Test 1", dt.Rows[1]["name"] );
-
- Assert.AreEqual( "Text 1", dt.Rows[0]["b1"] );
- Assert.AreEqual( "Text 2", dt.Rows[1]["b1"] );
-
- Assert.AreEqual( new DateTime(2004, 8, 1, 0, 0, 0).ToString(), dt.Rows[0]["dt"].ToString() );
- Assert.AreEqual( new DateTime(2004, 7, 2, 0, 0, 0).ToString(), dt.Rows[1]["dt"].ToString() );
- }
-
- [Test]
- public void Bug5798()
- {
- execSQL("INSERT INTO Test (id, id2, name) VALUES (1, 1, '')");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
- DataTable dt = new DataTable();
- da.Fill( dt );
-
- Assert.AreEqual( String.Empty, dt.Rows[0]["name"] );
-
- dt.Rows[0]["name"] = "Test";
- da.Update( dt );
-
- dt.Clear();
- da.Fill(dt);
- Assert.AreEqual( "Test", dt.Rows[0]["name"] );
- }
-
- [Test]
- public void ColumnMapping()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id int, dcname varchar(100), primary key(id))");
- execSQL("INSERT INTO Test VALUES (1, 'Test1')");
- execSQL("INSERT INTO Test VALUES (2, 'Test2')");
- execSQL("INSERT INTO Test VALUES (3, 'Test3')");
- execSQL("INSERT INTO Test VALUES (4, 'Test4')");
-
-// MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
-// MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
-// DataTable dt = new DataTable();
- }
-
- [Test]
- public void TestFillWithHelper()
- {
- execSQL("DROP TABLE IF EXISTS table1");
- execSQL("DROP TABLE IF EXISTS table2");
- execSQL( "CREATE TABLE table1 (`key` INT, PRIMARY KEY(`key`))" );
- execSQL( "CREATE TABLE table2 (`key` INT, PRIMARY KEY(`key`))" );
- execSQL("INSERT INTO table1 VALUES (1)");
- execSQL("INSERT INTO table2 VALUES (1)");
-
- string sql = "SELECT table1.key FROM table1 WHERE table1.key=1; " +
- "SELECT table2.key FROM table2 WHERE table2.key=1";
- DataSet ds = MySqlHelper.ExecuteDataset( conn, sql, null );
- Assert.AreEqual( 2, ds.Tables.Count );
- Assert.AreEqual( 1, ds.Tables[0].Rows.Count );
- Assert.AreEqual( 1, ds.Tables[1].Rows.Count );
- Assert.AreEqual( 1, ds.Tables[0].Rows[0]["key"] );
- Assert.AreEqual( 1, ds.Tables[1].Rows[0]["key"] );
- }
-
- /// <summary>
- /// Bug #8509 - MySqlDataAdapter.FillSchema does not interpret unsigned integer
- /// </summary>
- [Test]
- public void AutoIncrementColumns()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (id int(10) unsigned NOT NULL auto_increment primary key)");
- execSQL("INSERT INTO test VALUES(NULL)");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
- DataSet ds = new DataSet();
- da.Fill(ds);
- Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
- ds.Tables[0].Rows[0]["id"] = 2;
- DataRow row = ds.Tables[0].NewRow();
- row["id"] = 4;
- ds.Tables[0].Rows.Add(row);
-
- // add a null id. This should be auto'ed to 5
- row = ds.Tables[0].NewRow();
- row["id"] = DBNull.Value;
- ds.Tables[0].Rows.Add(row);
-
- try
- {
- da.Update(ds);
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
-
- ds.Clear();
- da.Fill(ds);
- Assert.AreEqual(2, ds.Tables[0].Rows[0]["id"]);
- Assert.AreEqual(4, ds.Tables[0].Rows[1]["id"]);
- Assert.AreEqual(5, ds.Tables[0].Rows[2]["id"]);
- }
-
- /// <summary>
- /// Bug #8292 GROUP BY / WITH ROLLUP with DataSet causes System.Data.ConstraintException
- /// </summary>
- [Test]
- [Category("4.1")]
- public void Rollup()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test ( id INT NOT NULL, amount INT )");
- execSQL("INSERT INTO test VALUES (1, 44)");
- execSQL("INSERT INTO test VALUES (2, 88)");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test GROUP BY id WITH ROLLUP", conn);
- DataSet ds = new DataSet();
- da.Fill(ds);
-
- Assert.AreEqual(1, ds.Tables.Count);
- Assert.AreEqual(3, ds.Tables[0].Rows.Count);
- Assert.AreEqual(88, ds.Tables[0].Rows[2]["amount"]);
- Assert.AreEqual(DBNull.Value, ds.Tables[0].Rows[2]["id"]);
- }
-
- /// <summary>
- /// Bug #8514 CURRENT_TIMESTAMP default not respected
- /// </summary>
- [Test]
- [Category("NotWorking")]
- public void DefaultValues()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (id int, name VARCHAR(20) NOT NULL DEFAULT 'abc', dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- MySqlCommand insCmd = new MySqlCommand("INSERT INTO test VALUES (?id, ?name, ?dt)", conn);
- insCmd.Parameters.Add("?id", MySqlDbType.Int32, 0, "id");
- insCmd.Parameters.Add("?name", MySqlDbType.VarChar, 20, "name");
- insCmd.Parameters.Add("?dt", MySqlDbType.Datetime, 0, "dt");
- da.InsertCommand = insCmd;
-
- DataTable dt = new DataTable();
-
- //da.FillSchema(ds, SchemaType.Source);//, "test");
- da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
- try
- {
- da.Fill(dt);
- }
- catch (Exception ex)
- {
- Console.WriteLine(ex.Message);
- }
-
-
- DataRow row = dt.NewRow();
- row["id"] = 1;
- row["name"] = "xyz";
- dt.Rows.Add(row);
-
- DataRow row2 = dt.NewRow();
- row2["id"] = 2;
- row2["name"] = DBNull.Value;
- dt.Rows.Add(row2);
-
- da.Update(dt);
-
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", conn);
- try
- {
- using (MySqlDataReader reader = cmd.ExecuteReader())
- {
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(1, reader["id"]);
- Assert.AreEqual("xyz", reader["name"]);
- Assert.AreEqual(DateTime.Now.Year, reader.GetDateTime(2).Year);
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(2, reader["id"]);
- Assert.AreEqual("abc", reader["name"]);
- Assert.AreEqual(DateTime.Now.Year, reader.GetDateTime(2).Year);
- Assert.IsFalse(reader.Read());
- }
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- }
-
- }
-}
+// Copyright (C) 2004-2006 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 MySql.Data.MySqlClient;
+using System.Data;
+using NUnit.Framework;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ /// <summary>
+ /// Summary description for ConnectionTests.
+ /// </summary>
+ [TestFixture]
+ public class DataAdapterTests : BaseTest
+ {
+
+ [TestFixtureSetUp]
+ public void FixtureSetup()
+ {
+ Open();
+ }
+
+ [SetUp]
+ protected override void Setup()
+ {
+ base.Setup();
+
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT NOT NULL AUTO_INCREMENT, " +
+ "id2 INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, " +
+ "ts TIMESTAMP, OriginalId INT, PRIMARY KEY(id, id2))");
+ }
+
+
+ [Test]
+ public void TestFill()
+ {
+ FillImpl(false);
+ FillImpl(true);
+ }
+
+ private void FillImpl(bool prepare)
+ {
+ execSQL("TRUNCATE TABLE Test");
+ execSQL("INSERT INTO Test (id, id2, name, dt) VALUES (NULL, 1, 'Name 1', Now())");
+ execSQL("INSERT INTO Test (id, id2, name, dt) VALUES (NULL, 2, NULL, Now())");
+ execSQL("INSERT INTO Test (id, id2, name, dt) VALUES (NULL, 3, '', Now())");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("select * from Test", conn);
+ if (prepare) da.SelectCommand.Prepare();
+ DataSet ds = new DataSet();
+ da.Fill(ds, "Test");
+
+ Assert.AreEqual(1, ds.Tables.Count);
+ Assert.AreEqual(3, ds.Tables[0].Rows.Count);
+
+ Assert.AreEqual(1, ds.Tables[0].Rows[0]["id2"]);
+ Assert.AreEqual(2, ds.Tables[0].Rows[1]["id2"]);
+ Assert.AreEqual(3, ds.Tables[0].Rows[2]["id2"]);
+
+ Assert.AreEqual("Name 1", ds.Tables[0].Rows[0]["name"]);
+ Assert.AreEqual(DBNull.Value, ds.Tables[0].Rows[1]["name"]);
+ Assert.AreEqual(String.Empty, ds.Tables[0].Rows[2]["name"]);
+ }
+
+ [Test]
+ public void TestUpdate()
+ {
+ MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE Test", conn);
+ cmd.ExecuteNonQuery();
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ DataRow dr = dt.NewRow();
+ dr["id2"] = 2;
+ dr["name"] = "TestName1";
+ dt.Rows.Add(dr);
+ int count = da.Update(dt);
+
+ // make sure our refresh of auto increment values worked
+ Assert.AreEqual(1, count, "checking insert count");
+ Assert.IsNotNull(dt.Rows[ dt.Rows.Count-1 ]["id"],
+ "Checking auto increment column");
+
+ dt.Rows.Clear();
+ da.Fill(dt);
+ dt.Rows[0]["id2"] = 3;
+ dt.Rows[0]["name"] = "TestName2";
+ dt.Rows[0]["ts"] = DBNull.Value;
+ DateTime day1 = new DateTime(2003, 1, 16, 12, 24, 0);
+ dt.Rows[0]["dt"] = day1;
+ dt.Rows[0]["tm"] = day1.TimeOfDay;
+ count = da.Update(dt);
+
+ Assert.IsNotNull(dt.Rows[0]["ts"], "checking refresh of record");
+ Assert.AreEqual(3, dt.Rows[0]["id2"], "checking refresh of primary column");
+
+ dt.Rows.Clear();
+ da.Fill(dt);
+
+ Assert.AreEqual(1, count, "checking update count");
+ DateTime dateTime = (DateTime)dt.Rows[0]["dt"];
+ Assert.AreEqual(day1, dateTime, "checking date");
+ Assert.AreEqual(day1.TimeOfDay, dt.Rows[0]["tm"], "checking time");
+
+ dt.Rows[0].Delete();
+ count = da.Update(dt);
+
+ Assert.AreEqual(1, count, "checking insert count");
+
+ dt.Rows.Clear();
+ da.Fill(dt);
+ Assert.AreEqual(0, dt.Rows.Count, "checking row count");
+ }
+
+ [Test]
+ public void OriginalInName()
+ {
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ cb.ToString(); // keep the compiler happy
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ DataRow row = dt.NewRow();
+ row["id"] = DBNull.Value;
+ row["id2"] = 1;
+ row["name"] = "Test";
+ row["dt"] = DBNull.Value;
+ row["tm"] = DBNull.Value;
+ row["ts"] = DBNull.Value;
+ row["OriginalId"] = 2;
+ dt.Rows.Add( row );
+ da.Update(dt);
+
+ Assert.AreEqual( 1, dt.Rows.Count );
+ Assert.AreEqual( 2, dt.Rows[0]["OriginalId"] );
+ }
+
+ [Test]
+ public void UseAdapterPropertyOfCommandBuilder()
+ {
+ execSQL("INSERT INTO Test (id, id2, name) VALUES (NULL, 1, 'Test')");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder();
+ cb.DataAdapter = da;
+
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ dt.Rows[0]["name"] = "Test Update";
+ int updateCnt = da.Update(dt);
+
+ Assert.AreEqual( 1, updateCnt );
+
+ dt.Rows.Clear();
+ da.Fill(dt);
+
+ Assert.AreEqual( 1, dt.Rows.Count );
+ Assert.AreEqual( "Test Update", dt.Rows[0]["name"] );
+ }
+
+ [Test]
+ public void UpdateNullTextFieldToEmptyString()
+ {
+ execSQL("INSERT INTO Test (id, id2, name) VALUES (1, 1, NULL)");
+
+ try
+ {
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ cb.ToString(); // keep the compiler happy
+
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ dt.Rows[0]["name"] = "";
+ int updateCnt = da.Update(dt);
+
+ Assert.AreEqual( 1, updateCnt );
+
+ dt.Rows.Clear();
+ da.Fill(dt);
+
+ Assert.AreEqual( 1, dt.Rows.Count );
+ Assert.AreEqual( "", dt.Rows[0]["name"] );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ }
+
+ [Test]
+ public void UpdateExtendedTextFields()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL( "CREATE TABLE Test (id int, notes MEDIUMTEXT, PRIMARY KEY(id))" );
+ execSQL("INSERT INTO Test VALUES(1, 'This is my note')" );
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ cb.ToString(); // keep the compiler happy
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ dt.Rows[0]["notes"] = "This is my new note";
+ da.Update( dt );
+
+ dt.Clear();
+ da.Fill(dt);
+ Assert.AreEqual( "This is my new note", dt.Rows[0]["notes"]);
+ }
+
+ [Test]
+ public void SelectMoreThan252Rows()
+ {
+ for (int i=0; i < 500; i++)
+ execSQL("INSERT INTO Test(id, id2) VALUES(NULL, " + i + ")");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ Assert.AreEqual( 500, dt.Rows.Count );
+ }
+
+/* [Test]
+ [Explicit]
+ public void UpdateManyRows()
+ {
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ for (int i=0; i < 100000; i++)
+ {
+ DataRow dr = dt.NewRow();
+ dr["id"] = DBNull.Value;
+ dr["id2"] = i;
+ dt.Rows.Add( dr );
+ DataTable changes = dt.GetChanges();
+ da.Update( changes );
+ dt.AcceptChanges();
+ }
+
+ dt.Clear();
+ da.Fill(dt);
+ Assert.AreEqual(100000, dt.Rows.Count);
+ }
+*/
+ [Test]
+ public void DiscreteValues()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id int, name varchar(200), dt DATETIME, b1 TEXT)");
+ execSQL("INSERT INTO Test VALUES (1, 'Test', '2004-08-01', 'Text 1')");
+ execSQL("INSERT INTO Test VALUES (2, 'Test 1', '2004-07-02', 'Text 2')");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ Assert.AreEqual( "Test", dt.Rows[0]["name"] );
+ Assert.AreEqual( "Test 1", dt.Rows[1]["name"] );
+
+ Assert.AreEqual( "Text 1", dt.Rows[0]["b1"] );
+ Assert.AreEqual( "Text 2", dt.Rows[1]["b1"] );
+
+ Assert.AreEqual( new DateTime(2004, 8, 1, 0, 0, 0).ToString(), dt.Rows[0]["dt"].ToString() );
+ Assert.AreEqual( new DateTime(2004, 7, 2, 0, 0, 0).ToString(), dt.Rows[1]["dt"].ToString() );
+ }
+
+ [Test]
+ public void Bug5798()
+ {
+ execSQL("INSERT INTO Test (id, id2, name) VALUES (1, 1, '')");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ cb.ToString(); // keep the compiler happy
+ DataTable dt = new DataTable();
+ da.Fill( dt );
+
+ Assert.AreEqual( String.Empty, dt.Rows[0]["name"] );
+
+ dt.Rows[0]["name"] = "Test";
+ da.Update( dt );
+
+ dt.Clear();
+ da.Fill(dt);
+ Assert.AreEqual( "Test", dt.Rows[0]["name"] );
+ }
+
+ [Test]
+ public void ColumnMapping()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id int, dcname varchar(100), primary key(id))");
+ execSQL("INSERT INTO Test VALUES (1, 'Test1')");
+ execSQL("INSERT INTO Test VALUES (2, 'Test2')");
+ execSQL("INSERT INTO Test VALUES (3, 'Test3')");
+ execSQL("INSERT INTO Test VALUES (4, 'Test4')");
+
+// MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+// MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+// DataTable dt = new DataTable();
+ }
+
+ [Test]
+ public void TestFillWithHelper()
+ {
+ execSQL("DROP TABLE IF EXISTS table1");
+ execSQL("DROP TABLE IF EXISTS table2");
+ execSQL( "CREATE TABLE table1 (`key` INT, PRIMARY KEY(`key`))" );
+ execSQL( "CREATE TABLE table2 (`key` INT, PRIMARY KEY(`key`))" );
+ execSQL("INSERT INTO table1 VALUES (1)");
+ execSQL("INSERT INTO table2 VALUES (1)");
+
+ string sql = "SELECT table1.key FROM table1 WHERE table1.key=1; " +
+ "SELECT table2.key FROM table2 WHERE table2.key=1";
+ DataSet ds = MySqlHelper.ExecuteDataset( conn, sql, null );
+ Assert.AreEqual( 2, ds.Tables.Count );
+ Assert.AreEqual( 1, ds.Tables[0].Rows.Count );
+ Assert.AreEqual( 1, ds.Tables[1].Rows.Count );
+ Assert.AreEqual( 1, ds.Tables[0].Rows[0]["key"] );
+ Assert.AreEqual( 1, ds.Tables[1].Rows[0]["key"] );
+ }
+
+ /// <summary>
+ /// Bug #8509 - MySqlDataAdapter.FillSchema does not interpret unsigned integer
+ /// </summary>
+ [Test]
+ public void AutoIncrementColumns()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (id int(10) unsigned NOT NULL auto_increment primary key)");
+ execSQL("INSERT INTO test VALUES(NULL)");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ DataSet ds = new DataSet();
+ da.Fill(ds);
+ Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
+
+ // add a null id. This should be auto'ed to 2
+ DataRow row = ds.Tables[0].NewRow();
+ row["id"] = DBNull.Value;
+ ds.Tables[0].Rows.Add(row);
+
+ try
+ {
+ da.Update(ds);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+
+ ds.Clear();
+ da.Fill(ds);
+ Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
+ Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
+ }
+
+ /// <summary>
+ /// Bug #8292 GROUP BY / WITH ROLLUP with DataSet causes System.Data.ConstraintException
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void Rollup()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test ( id INT NOT NULL, amount INT )");
+ execSQL("INSERT INTO test VALUES (1, 44)");
+ execSQL("INSERT INTO test VALUES (2, 88)");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test GROUP BY id WITH ROLLUP", conn);
+ DataSet ds = new DataSet();
+ da.Fill(ds);
+
+ Assert.AreEqual(1, ds.Tables.Count);
+ Assert.AreEqual(3, ds.Tables[0].Rows.Count);
+ Assert.AreEqual(88, ds.Tables[0].Rows[2]["amount"]);
+ Assert.AreEqual(DBNull.Value, ds.Tables[0].Rows[2]["id"]);
+ }
+
+ /// <summary>
+ /// Bug #8514 CURRENT_TIMESTAMP default not respected
+ /// </summary>
+ [Test]
+ [Category("NotWorking")]
+ public void DefaultValues()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (id int, name VARCHAR(20) NOT NULL DEFAULT 'abc', dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ MySqlCommand insCmd = new MySqlCommand("INSERT INTO test VALUES (?id, ?name, ?dt)", conn);
+ insCmd.Parameters.Add("?id", MySqlDbType.Int32, 0, "id");
+ insCmd.Parameters.Add("?name", MySqlDbType.VarChar, 20, "name");
+ insCmd.Parameters.Add("?dt", MySqlDbType.Datetime, 0, "dt");
+ da.InsertCommand = insCmd;
+
+ DataTable dt = new DataTable();
+
+ //da.FillSchema(ds, SchemaType.Source);//, "test");
+ da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
+ try
+ {
+ da.Fill(dt);
+ }
+ catch (Exception ex)
+ {
+ Console.WriteLine(ex.Message);
+ }
+
+
+ DataRow row = dt.NewRow();
+ row["id"] = 1;
+ row["name"] = "xyz";
+ dt.Rows.Add(row);
+
+ DataRow row2 = dt.NewRow();
+ row2["id"] = 2;
+ row2["name"] = DBNull.Value;
+ dt.Rows.Add(row2);
+
+ da.Update(dt);
+
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", conn);
+ try
+ {
+ using (MySqlDataReader reader = cmd.ExecuteReader())
+ {
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(1, reader["id"]);
+ Assert.AreEqual("xyz", reader["name"]);
+ Assert.AreEqual(DateTime.Now.Year, reader.GetDateTime(2).Year);
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(2, reader["id"]);
+ Assert.AreEqual("abc", reader["name"]);
+ Assert.AreEqual(DateTime.Now.Year, reader.GetDateTime(2).Year);
+ Assert.IsFalse(reader.Read());
+ }
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ }
+
+ }
+}
Modified: trunk/TestSuite/DataReaderTests.cs
===================================================================
--- trunk/TestSuite/DataReaderTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/DataReaderTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -386,8 +386,7 @@
Assert.AreEqual("Test", reader.GetString(1));
reader.Read();
Assert.AreEqual(2, reader.GetValue(0));
- Assert.AreEqual(DBNull.Value, reader.GetValue(1));
- Assert.AreEqual(null, reader.GetString(1));
+ Assert.AreEqual(DBNull.Value, reader.GetValue(1));
reader.Read();
Assert.AreEqual(3, reader.GetValue(0));
Assert.AreEqual("Test2", reader.GetValue(1));
Modified: trunk/TestSuite/DataTypeTests.cs
===================================================================
--- trunk/TestSuite/DataTypeTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/DataTypeTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -1,700 +1,691 @@
-// Copyright (C) 2004-2006 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 MySql.Data.MySqlClient;
-using System.Data;
-using NUnit.Framework;
-
-namespace MySql.Data.MySqlClient.Tests
-{
- /// <summary>
- /// Summary description for ConnectionTests.
- /// </summary>
- [TestFixture]
- public class DataTypeTests : BaseTest
- {
- [TestFixtureSetUp]
- public void TestFixtureSetup()
- {
- Open();
- }
-
- [TestFixtureTearDown()]
- public void TestFixtureTearDown()
- {
- Close();
- }
-
- [SetUp()]
- protected override void Setup()
- {
- base.Setup();
-
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), d DATE, dt DATETIME, tm TIME, PRIMARY KEY(id))");
- }
-
- [Test]
- public void BytesAndBooleans()
- {
- InternalBytesAndBooleans( false );
- if (! Is41 && ! Is50) return;
- InternalBytesAndBooleans( true );
- }
-
- private void InternalBytesAndBooleans( bool prepare )
- {
- execSQL( "DROP TABLE IF EXISTS Test" );
- execSQL( "CREATE TABLE Test (id TINYINT, idu TINYINT UNSIGNED, i INT UNSIGNED)" );
- execSQL( "INSERT INTO Test VALUES (-98, 140, 20)" );
- execSQL( "INSERT INTO Test VALUES (0, 0, 0)" );
-
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
- if (prepare) cmd.Prepare();
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( -98, (sbyte)reader.GetByte(0) );
- Assert.AreEqual( 140, reader.GetByte(1) );
- Assert.IsTrue( reader.GetBoolean(1) );
- Assert.AreEqual( 20, reader.GetUInt32(2) );
- Assert.AreEqual( 20, reader.GetInt32(2) );
-
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( 0, reader.GetByte(0) );
- Assert.AreEqual( 0, reader.GetByte(1) );
- Assert.IsFalse( reader.GetBoolean(1) );
-
- Assert.IsFalse( reader.Read() );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void TestFloats()
- {
- InternalTestFloats(false);
- if (! Is41 && ! Is50) return;
- InternalTestFloats(true);
- }
-
- private void InternalTestFloats(bool prepared)
- {
- execSQL( "DROP TABLE IF EXISTS Test" );
- execSQL( "CREATE TABLE Test (fl FLOAT, db DOUBLE, dec1 DECIMAL(5,2))" );
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?fl, ?db, ?dec)", conn);
- cmd.Parameters.Add( "?fl", MySqlDbType.Float );
- cmd.Parameters.Add( "?db", MySqlDbType.Double );
- cmd.Parameters.Add( "?dec", MySqlDbType.Decimal );
- cmd.Parameters[0].Value = 2.3;
- cmd.Parameters[1].Value = 4.6;
- cmd.Parameters[2].Value = 23.82;
- if (prepared)
- cmd.Prepare();
- int count = cmd.ExecuteNonQuery();
- Assert.AreEqual( 1, count );
-
- cmd.Parameters[0].Value = 1.5;
- cmd.Parameters[1].Value = 47.85;
- cmd.Parameters[2].Value = 123.85;
- count = cmd.ExecuteNonQuery();
- Assert.AreEqual( 1, count );
-
- MySqlDataReader reader = null;
- try
- {
- cmd.CommandText = "SELECT * FROM Test";
- if (prepared) cmd.Prepare();
- reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( 2.3, reader.GetFloat(0) );
- Assert.AreEqual( 4.6, reader.GetDouble(1) );
- Assert.AreEqual( 23.82, reader.GetDecimal(2) );
-
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( 1.5, reader.GetFloat(0) );
- Assert.AreEqual( 47.85, reader.GetDouble(1) );
- Assert.AreEqual( 123.85, reader.GetDecimal(2) );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void TestGuid()
- {
- MySqlDataReader reader =null;
-
- try
- {
- MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE Test", conn);
- cmd.ExecuteNonQuery();
-
- Guid g = Guid.NewGuid();
- cmd.CommandText = "INSERT INTO Test VALUES (?id, ?guid, NULL, NULL, NULL)";
- cmd.Parameters.Add( new MySqlParameter("?id", 1));
- cmd.Parameters.Add( new MySqlParameter( "?guid", g ));
- cmd.ExecuteNonQuery();
-
- cmd.Parameters[0].Value = 2;
- cmd.Parameters[1].Value = g.ToString("N");
- cmd.ExecuteNonQuery();
-
- cmd.Parameters[0].Value = 3;
- cmd.Parameters[1].Value = g.ToString("D");
- cmd.ExecuteNonQuery();
-
- cmd.Parameters[0].Value = 4;
- cmd.Parameters[1].Value = g.ToString("B");
- cmd.ExecuteNonQuery();
-
- cmd.Parameters[0].Value = 5;
- cmd.Parameters[1].Value = g.ToString("P");
- cmd.ExecuteNonQuery();
-
- cmd.CommandText = "SELECT * FROM Test";
- reader = cmd.ExecuteReader();
-
- Assert.AreEqual( true, reader.Read() );
- Guid newG = reader.GetGuid(1);
- Assert.AreEqual( g, newG );
-
- Assert.AreEqual( true, reader.Read() );
- newG = reader.GetGuid(1);
- Assert.AreEqual( g, newG );
-
- Assert.AreEqual( true, reader.Read() );
- newG = reader.GetGuid(1);
- Assert.AreEqual( g, newG );
-
- Assert.AreEqual( true, reader.Read() );
- newG = reader.GetGuid(1);
- Assert.AreEqual( g, newG );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test()]
- public void TestTime()
- {
- MySqlDataReader reader = null;
-
- try
- {
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, tm) VALUES (1, '00:00')", conn);
- cmd.ExecuteNonQuery();
- cmd.CommandText = "INSERT INTO Test (id, tm) VALUES (2, '512:45:17')";
- cmd.ExecuteNonQuery();
-
- cmd.CommandText = "SELECT * FROM Test";
- reader = cmd.ExecuteReader();
- reader.Read();
-
- object value = reader["tm"];
- Assert.AreEqual( value.GetType(), typeof(TimeSpan));
- TimeSpan ts = (TimeSpan)reader["tm"];
- Assert.AreEqual( 0, ts.Hours );
- Assert.AreEqual( 0, ts.Minutes );
- Assert.AreEqual( 0, ts.Seconds );
-
- reader.Read();
- value = reader["tm"];
- Assert.AreEqual( value.GetType(), typeof(TimeSpan));
- ts = (TimeSpan)reader["tm"];
- Assert.AreEqual( 21, ts.Days );
- Assert.AreEqual( 8, ts.Hours );
- Assert.AreEqual( 45, ts.Minutes );
- Assert.AreEqual( 17, ts.Seconds );
-
- reader.Close();
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test()]
- public void YearType()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (yr YEAR)");
- execSQL("INSERT INTO Test VALUES (98)");
- execSQL("INSERT INTO Test VALUES (1990)");
- execSQL("INSERT INTO Test VALUES (2004)");
- execSQL("INSERT INTO Test VALUES (111111111111111111111)");
-
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- reader.Read();
- Assert.AreEqual( 1998, reader.GetUInt32(0) );
- reader.Read();
- Assert.AreEqual( 1990, reader.GetUInt32(0) );
- reader.Read();
- Assert.AreEqual( 2004, reader.GetUInt32(0) );
- reader.Read();
- Assert.AreEqual( 0, reader.GetUInt32(0) );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test()]
- public void TypeCoercion()
- {
- MySqlParameter p = new MySqlParameter("?test", 1);
- Assert.AreEqual( DbType.Int32, p.DbType );
- Assert.AreEqual( MySqlDbType.Int32, p.MySqlDbType );
-
- p.DbType = DbType.Int64;
- Assert.AreEqual( DbType.Int64, p.DbType );
- Assert.AreEqual( MySqlDbType.Int64, p.MySqlDbType );
-
- p.MySqlDbType = MySqlDbType.Int16;
- Assert.AreEqual( DbType.Int16, p.DbType );
- Assert.AreEqual( MySqlDbType.Int16, p.MySqlDbType );
- }
-
- /// <summary>
- /// Bug #7951 - Error reading timestamp column
- /// </summary>
- [Test]
- public void Timestamp()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (id int, dt DATETIME, ts2 TIMESTAMP(2), ts4 TIMESTAMP(4), " +
- "ts6 TIMESTAMP(6), ts8 TIMESTAMP(8), ts10 TIMESTAMP(10), ts12 TIMESTAMP(12), " +
- "ts14 TIMESTAMP(14))");
- execSQL("INSERT INTO test (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
- "VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
-
- MySqlDataAdapter da = new MySqlDataAdapter( "SELECT * FROM test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- DateTime now = (DateTime)dt.Rows[0]["dt"];
- Assert.AreEqual( 1, dt.Rows[0]["id"] );
-
- DateTime ts2 = (DateTime)dt.Rows[0]["ts2"];
- Assert.AreEqual( now.Year, ts2.Year );
-
- DateTime ts4 = (DateTime)dt.Rows[0]["ts4"];
- Assert.AreEqual( now.Year, ts4.Year );
- Assert.AreEqual( now.Month, ts4.Month );
-
- DateTime ts6 = (DateTime)dt.Rows[0]["ts6"];
- Assert.AreEqual( now.Year, ts6.Year );
- Assert.AreEqual( now.Month, ts6.Month );
- Assert.AreEqual( now.Day, ts6.Day );
-
- DateTime ts8 = (DateTime)dt.Rows[0]["ts8"];
- Assert.AreEqual( now.Year, ts8.Year );
- Assert.AreEqual( now.Month, ts8.Month );
- Assert.AreEqual( now.Day, ts8.Day );
-
- DateTime ts10 = (DateTime)dt.Rows[0]["ts10"];
- Assert.AreEqual( now.Year, ts10.Year );
- Assert.AreEqual( now.Month, ts10.Month );
- Assert.AreEqual( now.Day, ts10.Day );
- Assert.AreEqual( now.Hour, ts10.Hour );
- Assert.AreEqual( now.Minute, ts10.Minute );
-
- DateTime ts12 = (DateTime)dt.Rows[0]["ts12"];
- Assert.AreEqual( now.Year, ts12.Year );
- Assert.AreEqual( now.Month, ts12.Month );
- Assert.AreEqual( now.Day, ts12.Day );
- Assert.AreEqual( now.Hour, ts12.Hour );
- Assert.AreEqual( now.Minute, ts12.Minute );
- Assert.AreEqual( now.Second, ts12.Second );
-
- DateTime ts14 = (DateTime)dt.Rows[0]["ts14"];
- Assert.AreEqual( now.Year, ts14.Year );
- Assert.AreEqual( now.Month, ts14.Month );
- Assert.AreEqual( now.Day, ts14.Day );
- Assert.AreEqual( now.Hour, ts14.Hour );
- Assert.AreEqual( now.Minute, ts14.Minute );
- Assert.AreEqual( now.Second, ts14.Second );
- }
-
-
- [Test]
- public void AggregateTypesTest()
- {
- execSQL( "DROP TABLE IF EXISTS foo" );
- execSQL( "CREATE TABLE foo (abigint bigint, aint int)");
- execSQL( "INSERT INTO foo VALUES (1, 2)");
- execSQL( "INSERT INTO foo VALUES (2, 3)");
- execSQL( "INSERT INTO foo VALUES (3, 4)");
- execSQL( "INSERT INTO foo VALUES (3, 5)");
-
- // Try a normal query
- string NORMAL_QRY = "SELECT abigint, aint FROM foo WHERE abigint = {0}";
- string qry = String.Format(NORMAL_QRY, 3);
- MySqlCommand cmd = new MySqlCommand(qry, conn);
- MySqlDataReader reader = null;
-
- try
- {
- reader = cmd.ExecuteReader();
- while (reader.Read())
- {
- reader.GetInt64(0);
- reader.GetInt32(1); // <--- aint... this succeeds
- }
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
-
- cmd.CommandText = "SELECT abigint, max(aint) FROM foo GROUP BY abigint";
- try
- {
- reader = cmd.ExecuteReader();
- while (reader.Read())
- {
- reader.GetInt64(0);
- reader.GetInt64(1); // <--- max(aint)... this fails
- }
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- execSQL( "DROP TABLE IF EXISTS foo");
- }
-
-/* [Test]
- public void TypeBoundaries()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test ( MaxDouble DOUBLE, MinDouble DOUBLE, MaxFloat FLOAT, MinFloat FLOAT )");
-
- MySqlCommand cmd = new MySqlCommand(
- "INSERT Test (MaxDouble, MinDouble, MaxFloat, MinFloat) VALUES " +
- "(?maxDouble, ?minDouble, ?maxFloat, ?minFloat)", conn);
- cmd.Parameters.Add("?maxDouble", MySqlDouble.MaxValue);
- cmd.Parameters.Add("?minDouble", MySqlDouble.MinValue);
- cmd.Parameters.Add("?maxFloat", MySqlFloat.MaxValue);
- cmd.Parameters.Add("?minFloat", MySqlFloat.MinValue);
- cmd.ExecuteNonQuery();
-
- cmd.CommandText = "SELECT * FROM Test";
- try
- {
- using (MySqlDataReader reader = cmd.ExecuteReader())
- {
- reader.Read();
- Assert.AreEqual(MySqlDouble.MaxValue, reader.GetDouble(0));
- Assert.AreEqual(MySqlDouble.MinValue, reader.GetDouble(1));
- Assert.AreEqual(MySqlFloat.MaxValue, reader.GetFloat(2));
- Assert.AreEqual(MySqlFloat.MinValue, reader.GetFloat(3));
- }
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- }*/
-
- [Test]
- public void BitAndDecimal()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (bt1 BIT, bt4 BIT(4), bt11 BIT(11), bt23 BIT(23), bt32 BIT(32)) engine=myisam");
- execSQL("INSERT INTO test VALUES (12, 2, 120, 240, 1000)");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- DataTable dt = new DataTable();
- da.Fill(dt);
- DataRow row = dt.NewRow();
- row["id"] = DBNull.Value;
- row["dec1"] = 23.4;
- dt.Rows.Add(row);
- da.Update(dt);
-
- dt.Clear();
- da.Fill(dt);
- Assert.AreEqual(1, dt.Rows.Count);
- Assert.AreEqual(1, dt.Rows[0]["id"]);
- Assert.AreEqual(23.4, dt.Rows[0]["dec1"]);
- }
-
- [Test]
- public void DecimalTests()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (val decimal(10,1))");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(?dec)", conn);
- cmd.Parameters.Add("?dec", (decimal)2.4);
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
-
- cmd.Prepare();
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
-
- cmd.CommandText = "SELECT * FROM test";
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.AreEqual( 1, reader.GetInt32(0));
- Assert.AreEqual( 2, reader.GetInt32(1));
- Assert.AreEqual( 120, reader.GetInt32(2));
- Assert.AreEqual( 1000, reader.GetInt32(3));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- }
-
- execSQL("CREATE TABLE test (bt1 BIT, bt4 BIT(4), bt8 BIT(8), bt32 BIT(32)) engine=myisam");
- execSQL("INSERT INTO test VALUES (12, 2, 120, 1000)");
- cmd.CommandText = "SELECT * FROM test";
- reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(1, reader.GetInt32(0));
- Assert.AreEqual(2, reader.GetInt32(1));
- Assert.AreEqual(120, reader.GetInt32(2));
- Assert.AreEqual(1000, reader.GetInt32(3));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- execSQL("DROP TABLE IF EXISTS test2");
- if (reader != null) reader.Close();
- }
-
- }
-
- /// <summary>
- /// Bug #10486 MySqlDataAdapter.Update error for decimal column
- /// </summary>
- [Test]
- public void UpdateDecimalColumns()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (id int not null auto_increment primary key, " +
- "dec1 decimal(10,1))");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- DataTable dt = new DataTable();
- da.Fill(dt);
- DataRow row = dt.NewRow();
- row["id"] = DBNull.Value;
- row["dec1"] = 23.4;
- dt.Rows.Add(row);
- da.Update(dt);
-
- dt.Clear();
- da.Fill(dt);
- Assert.AreEqual(1, dt.Rows.Count);
- Assert.AreEqual(1, dt.Rows[0]["id"]);
- Assert.AreEqual(23.4, dt.Rows[0]["dec1"]);
- }
-
- [Test]
- public void DecimalTests2()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (val decimal(10,1))");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(?dec)", conn);
- cmd.Parameters.Add("?dec", (decimal)2.4);
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
-
- cmd.Prepare();
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
-
- cmd.CommandText = "SELECT * FROM test";
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.IsTrue(reader[0] is Decimal);
- Assert.AreEqual(2.4, reader[0]);
-
- Assert.IsTrue(reader.Read());
- Assert.IsTrue(reader[0] is Decimal);
- Assert.AreEqual(2.4, reader[0]);
-
- Assert.IsFalse(reader.Read());
- Assert.IsFalse(reader.NextResult());
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- [Category("5.0")]
- public void Bit()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (bit1 BIT, bit2 BIT(5), bit3 BIT(10))");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (?b1, ?b2, ?b3)", conn);
- cmd.Parameters.Add(new MySqlParameter("?b1", MySqlDbType.Bit));
- cmd.Parameters.Add(new MySqlParameter("?b2", MySqlDbType.Bit));
- cmd.Parameters.Add(new MySqlParameter("?b3", MySqlDbType.Bit));
- cmd.Prepare();
- cmd.Parameters[0].Value = 1;
- cmd.Parameters[1].Value = 2;
- cmd.Parameters[2].Value = 3;
- cmd.ExecuteNonQuery();
-
- MySqlDataReader reader = null;
- try
- {
- cmd.CommandText = "SELECT * FROM test";
- cmd.Prepare();
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(1, reader[0]);
- Assert.AreEqual(2, reader[1]);
- Assert.AreEqual(3, reader[2]);
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- /// <summary>
- /// Bug #17375 CommandBuilder ignores Unsigned flag at Parameter creation
- /// Bug #15274 Use MySqlDbType.UInt32, throwed exception 'Only byte arrays can be serialize'
- /// </summary>
- [Test]
- public void UnsignedTypes()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (b TINYINT UNSIGNED PRIMARY KEY)");
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
-
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- DataView dv = new DataView(dt);
- DataRowView row;
-
- row = dv.AddNew();
- row["b"] = 120;
- row.EndEdit();
- da.Update(dv.Table);
-
- row = dv.AddNew();
- row["b"] = 135;
- row.EndEdit();
- da.Update(dv.Table);
-
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (b MEDIUMINT UNSIGNED PRIMARY KEY)");
- execSQL("INSERT INTO test VALUES(20)");
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM test WHERE (b > ?id)", conn);
- cmd.Parameters.Add("?id", MySqlDbType.UInt16).Value = 10;
- MySqlDataReader dr = null;
- try
- {
- dr = cmd.ExecuteReader();
- dr.Read();
- Assert.AreEqual(20, dr.GetUInt16(0));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (dr != null)
- dr.Close();
- }
- }
- }
-}
+// Copyright (C) 2004-2006 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 MySql.Data.MySqlClient;
+using System.Data;
+using NUnit.Framework;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ /// <summary>
+ /// Summary description for ConnectionTests.
+ /// </summary>
+ [TestFixture]
+ public class DataTypeTests : BaseTest
+ {
+ [TestFixtureSetUp]
+ public void TestFixtureSetup()
+ {
+ Open();
+ }
+
+ [TestFixtureTearDown()]
+ public void TestFixtureTearDown()
+ {
+ Close();
+ }
+
+ [SetUp()]
+ protected override void Setup()
+ {
+ base.Setup();
+
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), d DATE, dt DATETIME, tm TIME, PRIMARY KEY(id))");
+ }
+
+ [Test]
+ public void BytesAndBooleans()
+ {
+ InternalBytesAndBooleans(false);
+ }
+
+ [Category("4.1")]
+ [Test]
+ public void BytesAndBooleansPrepared()
+ {
+ InternalBytesAndBooleans(true);
+ }
+
+ private void InternalBytesAndBooleans( bool prepare )
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id TINYINT, idu TINYINT UNSIGNED, i INT UNSIGNED)");
+ execSQL("INSERT INTO Test VALUES (-98, 140, 20)");
+ execSQL("INSERT INTO Test VALUES (0, 0, 0)");
+
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
+ if (prepare) cmd.Prepare();
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(-98, (sbyte)reader.GetByte(0));
+ Assert.AreEqual(140, reader.GetByte(1));
+ Assert.IsTrue(reader.GetBoolean(1));
+ Assert.AreEqual(20, reader.GetUInt32(2));
+ Assert.AreEqual(20, reader.GetInt32(2));
+
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(0, reader.GetByte(0));
+ Assert.AreEqual(0, reader.GetByte(1));
+ Assert.IsFalse(reader.GetBoolean(1));
+
+ Assert.IsFalse(reader.Read());
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void TestFloats()
+ {
+ InternalTestFloats(false);
+ if (! Is41 && ! Is50) return;
+ InternalTestFloats(true);
+ }
+
+ private void InternalTestFloats(bool prepared)
+ {
+ execSQL( "DROP TABLE IF EXISTS Test" );
+ execSQL( "CREATE TABLE Test (fl FLOAT, db DOUBLE, dec1 DECIMAL(5,2))" );
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?fl, ?db, ?dec)", conn);
+ cmd.Parameters.Add( "?fl", MySqlDbType.Float );
+ cmd.Parameters.Add( "?db", MySqlDbType.Double );
+ cmd.Parameters.Add( "?dec", MySqlDbType.Decimal );
+ cmd.Parameters[0].Value = 2.3;
+ cmd.Parameters[1].Value = 4.6;
+ cmd.Parameters[2].Value = 23.82;
+ if (prepared)
+ cmd.Prepare();
+ int count = cmd.ExecuteNonQuery();
+ Assert.AreEqual( 1, count );
+
+ cmd.Parameters[0].Value = 1.5;
+ cmd.Parameters[1].Value = 47.85;
+ cmd.Parameters[2].Value = 123.85;
+ count = cmd.ExecuteNonQuery();
+ Assert.AreEqual( 1, count );
+
+ MySqlDataReader reader = null;
+ try
+ {
+ cmd.CommandText = "SELECT * FROM Test";
+ if (prepared) cmd.Prepare();
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue( reader.Read() );
+ Assert.AreEqual( 2.3, reader.GetFloat(0) );
+ Assert.AreEqual( 4.6, reader.GetDouble(1) );
+ Assert.AreEqual( 23.82, reader.GetDecimal(2) );
+
+ Assert.IsTrue( reader.Read() );
+ Assert.AreEqual( 1.5, reader.GetFloat(0) );
+ Assert.AreEqual( 47.85, reader.GetDouble(1) );
+ Assert.AreEqual( 123.85, reader.GetDecimal(2) );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void TestGuid()
+ {
+ MySqlDataReader reader =null;
+
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE Test", conn);
+ cmd.ExecuteNonQuery();
+
+ Guid g = Guid.NewGuid();
+ cmd.CommandText = "INSERT INTO Test VALUES (?id, ?guid, NULL, NULL, NULL)";
+ cmd.Parameters.Add( new MySqlParameter("?id", 1));
+ cmd.Parameters.Add( new MySqlParameter( "?guid", g ));
+ cmd.ExecuteNonQuery();
+
+ cmd.Parameters[0].Value = 2;
+ cmd.Parameters[1].Value = g.ToString("N");
+ cmd.ExecuteNonQuery();
+
+ cmd.Parameters[0].Value = 3;
+ cmd.Parameters[1].Value = g.ToString("D");
+ cmd.ExecuteNonQuery();
+
+ cmd.Parameters[0].Value = 4;
+ cmd.Parameters[1].Value = g.ToString("B");
+ cmd.ExecuteNonQuery();
+
+ cmd.Parameters[0].Value = 5;
+ cmd.Parameters[1].Value = g.ToString("P");
+ cmd.ExecuteNonQuery();
+
+ cmd.CommandText = "SELECT * FROM Test";
+ reader = cmd.ExecuteReader();
+
+ Assert.AreEqual( true, reader.Read() );
+ Guid newG = reader.GetGuid(1);
+ Assert.AreEqual( g, newG );
+
+ Assert.AreEqual( true, reader.Read() );
+ newG = reader.GetGuid(1);
+ Assert.AreEqual( g, newG );
+
+ Assert.AreEqual( true, reader.Read() );
+ newG = reader.GetGuid(1);
+ Assert.AreEqual( g, newG );
+
+ Assert.AreEqual( true, reader.Read() );
+ newG = reader.GetGuid(1);
+ Assert.AreEqual( g, newG );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void TestTime()
+ {
+ MySqlDataReader reader = null;
+
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, tm) VALUES (1, '00:00')", conn);
+ cmd.ExecuteNonQuery();
+ cmd.CommandText = "INSERT INTO Test (id, tm) VALUES (2, '512:45:17')";
+ cmd.ExecuteNonQuery();
+
+ cmd.CommandText = "SELECT * FROM Test";
+ reader = cmd.ExecuteReader();
+ reader.Read();
+
+ object value = reader["tm"];
+ Assert.AreEqual( value.GetType(), typeof(TimeSpan));
+ TimeSpan ts = (TimeSpan)reader["tm"];
+ Assert.AreEqual( 0, ts.Hours );
+ Assert.AreEqual( 0, ts.Minutes );
+ Assert.AreEqual( 0, ts.Seconds );
+
+ reader.Read();
+ value = reader["tm"];
+ Assert.AreEqual( value.GetType(), typeof(TimeSpan));
+ ts = (TimeSpan)reader["tm"];
+ Assert.AreEqual( 21, ts.Days );
+ Assert.AreEqual( 8, ts.Hours );
+ Assert.AreEqual( 45, ts.Minutes );
+ Assert.AreEqual( 17, ts.Seconds );
+
+ reader.Close();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void YearType()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (yr YEAR)");
+ execSQL("INSERT INTO Test VALUES (98)");
+ execSQL("INSERT INTO Test VALUES (1990)");
+ execSQL("INSERT INTO Test VALUES (2004)");
+ execSQL("INSERT INTO Test VALUES (111111111111111111111)");
+
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ reader.Read();
+ Assert.AreEqual( 1998, reader.GetUInt32(0) );
+ reader.Read();
+ Assert.AreEqual( 1990, reader.GetUInt32(0) );
+ reader.Read();
+ Assert.AreEqual( 2004, reader.GetUInt32(0) );
+ reader.Read();
+ Assert.AreEqual( 0, reader.GetUInt32(0) );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void TypeCoercion()
+ {
+ MySqlParameter p = new MySqlParameter("?test", 1);
+ Assert.AreEqual( DbType.Int32, p.DbType );
+ Assert.AreEqual( MySqlDbType.Int32, p.MySqlDbType );
+
+ p.DbType = DbType.Int64;
+ Assert.AreEqual( DbType.Int64, p.DbType );
+ Assert.AreEqual( MySqlDbType.Int64, p.MySqlDbType );
+
+ p.MySqlDbType = MySqlDbType.Int16;
+ Assert.AreEqual( DbType.Int16, p.DbType );
+ Assert.AreEqual( MySqlDbType.Int16, p.MySqlDbType );
+ }
+
+ /// <summary>
+ /// Bug #7951 - Error reading timestamp column
+ /// </summary>
+ [Test]
+ public void Timestamp()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (id int, dt DATETIME, ts2 TIMESTAMP(2), ts4 TIMESTAMP(4), " +
+ "ts6 TIMESTAMP(6), ts8 TIMESTAMP(8), ts10 TIMESTAMP(10), ts12 TIMESTAMP(12), " +
+ "ts14 TIMESTAMP(14))");
+ execSQL("INSERT INTO test (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
+ "VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
+
+ MySqlDataAdapter da = new MySqlDataAdapter( "SELECT * FROM test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ DateTime now = (DateTime)dt.Rows[0]["dt"];
+ Assert.AreEqual( 1, dt.Rows[0]["id"] );
+
+ DateTime ts2 = (DateTime)dt.Rows[0]["ts2"];
+ Assert.AreEqual( now.Year, ts2.Year );
+
+ DateTime ts4 = (DateTime)dt.Rows[0]["ts4"];
+ Assert.AreEqual( now.Year, ts4.Year );
+ Assert.AreEqual( now.Month, ts4.Month );
+
+ DateTime ts6 = (DateTime)dt.Rows[0]["ts6"];
+ Assert.AreEqual( now.Year, ts6.Year );
+ Assert.AreEqual( now.Month, ts6.Month );
+ Assert.AreEqual( now.Day, ts6.Day );
+
+ DateTime ts8 = (DateTime)dt.Rows[0]["ts8"];
+ Assert.AreEqual( now.Year, ts8.Year );
+ Assert.AreEqual( now.Month, ts8.Month );
+ Assert.AreEqual( now.Day, ts8.Day );
+
+ DateTime ts10 = (DateTime)dt.Rows[0]["ts10"];
+ Assert.AreEqual( now.Year, ts10.Year );
+ Assert.AreEqual( now.Month, ts10.Month );
+ Assert.AreEqual( now.Day, ts10.Day );
+ Assert.AreEqual( now.Hour, ts10.Hour );
+ Assert.AreEqual( now.Minute, ts10.Minute );
+
+ DateTime ts12 = (DateTime)dt.Rows[0]["ts12"];
+ Assert.AreEqual( now.Year, ts12.Year );
+ Assert.AreEqual( now.Month, ts12.Month );
+ Assert.AreEqual( now.Day, ts12.Day );
+ Assert.AreEqual( now.Hour, ts12.Hour );
+ Assert.AreEqual( now.Minute, ts12.Minute );
+ Assert.AreEqual( now.Second, ts12.Second );
+
+ DateTime ts14 = (DateTime)dt.Rows[0]["ts14"];
+ Assert.AreEqual( now.Year, ts14.Year );
+ Assert.AreEqual( now.Month, ts14.Month );
+ Assert.AreEqual( now.Day, ts14.Day );
+ Assert.AreEqual( now.Hour, ts14.Hour );
+ Assert.AreEqual( now.Minute, ts14.Minute );
+ Assert.AreEqual( now.Second, ts14.Second );
+ }
+
+
+ [Test]
+ public void AggregateTypesTest()
+ {
+ execSQL( "DROP TABLE IF EXISTS foo" );
+ execSQL( "CREATE TABLE foo (abigint bigint, aint int)");
+ execSQL( "INSERT INTO foo VALUES (1, 2)");
+ execSQL( "INSERT INTO foo VALUES (2, 3)");
+ execSQL( "INSERT INTO foo VALUES (3, 4)");
+ execSQL( "INSERT INTO foo VALUES (3, 5)");
+
+ // Try a normal query
+ string NORMAL_QRY = "SELECT abigint, aint FROM foo WHERE abigint = {0}";
+ string qry = String.Format(NORMAL_QRY, 3);
+ MySqlCommand cmd = new MySqlCommand(qry, conn);
+ MySqlDataReader reader = null;
+
+ try
+ {
+ reader = cmd.ExecuteReader();
+ while (reader.Read())
+ {
+ reader.GetInt64(0);
+ reader.GetInt32(1); // <--- aint... this succeeds
+ }
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+
+ cmd.CommandText = "SELECT abigint, max(aint) FROM foo GROUP BY abigint";
+ try
+ {
+ reader = cmd.ExecuteReader();
+ while (reader.Read())
+ {
+ reader.GetInt64(0);
+ reader.GetInt64(1); // <--- max(aint)... this fails
+ }
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ execSQL( "DROP TABLE IF EXISTS foo");
+ }
+
+/* [Test]
+ public void TypeBoundaries()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test ( MaxDouble DOUBLE, MinDouble DOUBLE, MaxFloat FLOAT, MinFloat FLOAT )");
+
+ MySqlCommand cmd = new MySqlCommand(
+ "INSERT Test (MaxDouble, MinDouble, MaxFloat, MinFloat) VALUES " +
+ "(?maxDouble, ?minDouble, ?maxFloat, ?minFloat)", conn);
+ cmd.Parameters.Add("?maxDouble", MySqlDouble.MaxValue);
+ cmd.Parameters.Add("?minDouble", MySqlDouble.MinValue);
+ cmd.Parameters.Add("?maxFloat", MySqlFloat.MaxValue);
+ cmd.Parameters.Add("?minFloat", MySqlFloat.MinValue);
+ cmd.ExecuteNonQuery();
+
+ cmd.CommandText = "SELECT * FROM Test";
+ try
+ {
+ using (MySqlDataReader reader = cmd.ExecuteReader())
+ {
+ reader.Read();
+ Assert.AreEqual(MySqlDouble.MaxValue, reader.GetDouble(0));
+ Assert.AreEqual(MySqlDouble.MinValue, reader.GetDouble(1));
+ Assert.AreEqual(MySqlFloat.MaxValue, reader.GetFloat(2));
+ Assert.AreEqual(MySqlFloat.MinValue, reader.GetFloat(3));
+ }
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ }*/
+
+ [Test]
+ public void BitAndDecimal()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (bt1 BIT, bt4 BIT(4), bt11 BIT(11), bt23 BIT(23), bt32 BIT(32)) engine=myisam");
+ execSQL("INSERT INTO test VALUES (12, 2, 120, 240, 1000)");
+
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", conn);
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(1, reader.GetInt32(0));
+ Assert.AreEqual(2, reader.GetInt32(1));
+ Assert.AreEqual(120, reader.GetInt32(2));
+ Assert.AreEqual(240, reader.GetInt32(3));
+ Assert.AreEqual(1000, reader.GetInt32(4));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ /// <summary>
+ /// Bug #10486 MySqlDataAdapter.Update error for decimal column
+ /// </summary>
+ [Test]
+ public void UpdateDecimalColumns()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (id int not null auto_increment primary key, " +
+ "dec1 decimal(10,1))");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+ DataRow row = dt.NewRow();
+ row["id"] = DBNull.Value;
+ row["dec1"] = 23.4;
+ dt.Rows.Add(row);
+ da.Update(dt);
+
+ dt.Clear();
+ da.Fill(dt);
+ Assert.AreEqual(1, dt.Rows.Count);
+ Assert.AreEqual(1, dt.Rows[0]["id"]);
+ Assert.AreEqual(23.4, dt.Rows[0]["dec1"]);
+ }
+
+ [Test]
+ public void DecimalTests()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (val decimal(10,1))");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(?dec)", conn);
+ cmd.Parameters.Add("?dec", (decimal)2.4);
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+
+ cmd.Prepare();
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+
+ cmd.CommandText = "SELECT * FROM test";
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.IsTrue(reader[0] is Decimal);
+ Assert.AreEqual(2.4, reader[0]);
+
+ Assert.IsTrue(reader.Read());
+ Assert.IsTrue(reader[0] is Decimal);
+ Assert.AreEqual(2.4, reader[0]);
+
+ Assert.IsFalse(reader.Read());
+ Assert.IsFalse(reader.NextResult());
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void DecimalTests2()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (val decimal(10,1))");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(?dec)", conn);
+ cmd.Parameters.Add("?dec", (decimal)2.4);
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+
+ cmd.Prepare();
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+
+ cmd.CommandText = "SELECT * FROM test";
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.IsTrue(reader[0] is Decimal);
+ Assert.AreEqual(2.4, reader[0]);
+
+ Assert.IsTrue(reader.Read());
+ Assert.IsTrue(reader[0] is Decimal);
+ Assert.AreEqual(2.4, reader[0]);
+
+ Assert.IsFalse(reader.Read());
+ Assert.IsFalse(reader.NextResult());
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void Bit()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (bit1 BIT, bit2 BIT(5), bit3 BIT(10))");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (?b1, ?b2, ?b3)", conn);
+ cmd.Parameters.Add(new MySqlParameter("?b1", MySqlDbType.Bit));
+ cmd.Parameters.Add(new MySqlParameter("?b2", MySqlDbType.Bit));
+ cmd.Parameters.Add(new MySqlParameter("?b3", MySqlDbType.Bit));
+ cmd.Prepare();
+ cmd.Parameters[0].Value = 1;
+ cmd.Parameters[1].Value = 2;
+ cmd.Parameters[2].Value = 3;
+ cmd.ExecuteNonQuery();
+
+ MySqlDataReader reader = null;
+ try
+ {
+ cmd.CommandText = "SELECT * FROM test";
+ cmd.Prepare();
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(1, reader[0]);
+ Assert.AreEqual(2, reader[1]);
+ Assert.AreEqual(3, reader[2]);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ /// <summary>
+ /// Bug #17375 CommandBuilder ignores Unsigned flag at Parameter creation
+ /// Bug #15274 Use MySqlDbType.UInt32, throwed exception 'Only byte arrays can be serialize'
+ /// </summary>
+ [Test]
+ public void UnsignedTypes()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (b TINYINT UNSIGNED PRIMARY KEY)");
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ DataView dv = new DataView(dt);
+ DataRowView row;
+
+ row = dv.AddNew();
+ row["b"] = 120;
+ row.EndEdit();
+ da.Update(dv.Table);
+
+ row = dv.AddNew();
+ row["b"] = 135;
+ row.EndEdit();
+ da.Update(dv.Table);
+
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (b MEDIUMINT UNSIGNED PRIMARY KEY)");
+ execSQL("INSERT INTO test VALUES(20)");
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM test WHERE (b > ?id)", conn);
+ cmd.Parameters.Add("?id", MySqlDbType.UInt16).Value = 10;
+ MySqlDataReader dr = null;
+ try
+ {
+ dr = cmd.ExecuteReader();
+ dr.Read();
+ Assert.AreEqual(20, dr.GetUInt16(0));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (dr != null)
+ dr.Close();
+ }
+ }
+ }
+}
Modified: trunk/TestSuite/DateTimeTests.cs
===================================================================
--- trunk/TestSuite/DateTimeTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/DateTimeTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -1,409 +1,416 @@
-// Copyright (C) 2004-2006 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 System.Data.Common;
-using System.Threading;
-using MySql.Data.MySqlClient;
-using System.Globalization;
-using NUnit.Framework;
-using MySql.Data.Types;
-using System.Text;
-
-namespace MySql.Data.MySqlClient.Tests
-{
- [NUnit.Framework.TestFixture]
- public class DateTimeTests : BaseTest
- {
- [TestFixtureSetUp]
- public void TestFixtureSetUp()
- {
- Open();
-
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id INT NOT NULL, dt DATETIME, d DATE, t TIME, ts TIMESTAMP, PRIMARY KEY(id))");
- }
-
- [TestFixtureTearDown]
- public void TestFixtureTearDown()
- {
- Close();
- }
-
- [Test]
- public void ConvertZeroDateTime()
- {
- execSQL("INSERT INTO Test VALUES(1, '0000-00-00', '0000-00-00', '00:00:00', NULL)");
-
- MySqlConnection c;
- MySqlDataReader reader = null;
-
- string connStr = this.GetConnectionString(true);
- connStr += ";convert zero datetime=yes";
- c = new MySqlConnection(connStr);
-
- try
- {
- c.Open();
-
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", c);
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(DateTime.MinValue.Date, reader.GetDateTime(1).Date);
- Assert.AreEqual(DateTime.MinValue.Date, reader.GetDateTime(2).Date);
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- c.Close();
- }
- }
-
- [Test]
- public void TestNotAllowZerDateAndTime()
- {
- execSQL("INSERT INTO Test VALUES(1, 'Test', '0000-00-00', '0000-00-00', '00:00:00')");
- execSQL("INSERT INTO Test VALUES(2, 'Test', '2004-11-11', '2004-11-11', '06:06:06')");
-
- MySqlDataReader reader = null;
- try
- {
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
-
- MySqlDateTime testDate = reader.GetMySqlDateTime(2);
- Assert.IsFalse(testDate.IsValidDateTime, "IsZero is false");
-
- try
- {
- reader.GetValue(2);
- Assert.Fail("This should not work");
- }
- catch (MySqlConversionException) { }
-
- Assert.IsTrue( reader.Read() );
-
- DateTime dt2 = (DateTime)reader.GetValue(2);
- Assert.AreEqual( new DateTime(2004,11,11).Date, dt2.Date );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
-
- }
-
- [Test]
- public void DateAdd()
- {
- MySqlCommand cmd = new MySqlCommand( "select date_add(?someday, interval 1 hour)", conn);
- DateTime now = DateTime.Now;
- DateTime later = now.AddHours(1);
- later = later.AddMilliseconds( later.Millisecond * -1 );
- cmd.Parameters.Add("?someday", now );
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- DateTime dt = reader.GetDateTime(0);
- Assert.AreEqual( later.Date, dt.Date );
- Assert.AreEqual( later.Hour, dt.Hour );
- Assert.AreEqual( later.Minute, dt.Minute );
- Assert.AreEqual( later.Second, dt.Second );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void TestAllowZeroDateTime()
- {
- execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
-
- MySqlConnection c = new MySqlConnection(
- conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true" );
- c.Open();
- MySqlDataReader reader = null;
- try
- {
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c);
- reader = cmd.ExecuteReader();
- reader.Read();
-
- Assert.IsTrue( reader.GetValue(1) is MySqlDateTime );
- Assert.IsTrue( reader.GetValue(2) is MySqlDateTime );
-
- Assert.IsFalse( reader.GetMySqlDateTime(1).IsValidDateTime );
- Assert.IsFalse( reader.GetMySqlDateTime(2).IsValidDateTime );
-
- try
- {
- reader.GetDateTime(1);
- Assert.Fail("This should not succeed");
- }
- catch (MySqlConversionException) {}
-
-
- }
- catch (MySqlException ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- c.Close();
- }
- }
-
- [Test]
- public void InsertDateTimeValue()
- {
- MySqlConnection c = new MySqlConnection( conn.ConnectionString +
- ";allow zero datetime=yes");
- try
- {
- c.Open();
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, dt FROM Test", c);
- MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
- cb.ToString(); // keep the compiler happy
-
- DataTable dt = new DataTable();
- dt.Columns.Add(new DataColumn("id", typeof(int)));
- dt.Columns.Add(new DataColumn("dt", typeof(DateTime)));
-
- da.Fill(dt);
-
- DateTime now = DateTime.Now;
- DataRow row = dt.NewRow();
- row["id"] = 1;
- row["dt"] = now;
- dt.Rows.Add(row);
- da.Update(dt);
-
- dt.Clear();
- da.Fill(dt);
-
- Assert.AreEqual(1, dt.Rows.Count);
- Assert.AreEqual(now.Date, ((DateTime)dt.Rows[0]["dt"]).Date );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- c.Close();
- }
- }
-
-
- [Test]
- public void SortingMySqlDateTimes()
- {
- execSQL("INSERT INTO Test (id, dt) VALUES (1, '2004-10-01')");
- execSQL("INSERT INTO Test (id, dt) VALUES (2, '2004-10-02')");
- execSQL("INSERT INTO Test (id, dt) VALUES (3, '2004-11-01')");
- execSQL("INSERT INTO Test (id, dt) VALUES (4, '2004-11-02')");
-
- CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
- CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
- CultureInfo cul = new CultureInfo("en-GB");
- Thread.CurrentThread.CurrentCulture = cul;
- Thread.CurrentThread.CurrentUICulture = cul;
-
- using( MySqlConnection c = new MySqlConnection( conn.ConnectionString + ";allow zero datetime=yes" ))
- {
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT dt FROM Test", c);
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- DataView dv = dt.DefaultView;
- dv.Sort = "dt ASC";
-
- Assert.AreEqual( new DateTime(2004, 10, 1).Date, Convert.ToDateTime(dv[0]["dt"]).Date );
- Assert.AreEqual( new DateTime(2004, 10, 2).Date, Convert.ToDateTime(dv[1]["dt"]).Date );
- Assert.AreEqual( new DateTime(2004, 11, 1).Date, Convert.ToDateTime(dv[2]["dt"]).Date );
- Assert.AreEqual( new DateTime(2004, 11, 2).Date, Convert.ToDateTime(dv[3]["dt"]).Date );
-
- Thread.CurrentThread.CurrentCulture = curCulture;
- Thread.CurrentThread.CurrentUICulture = curUICulture;
- }
- }
-
- [Test]
- public void TestZeroDateTimeException()
- {
- execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
-
- MySqlDataReader reader = null;
- try
- {
- MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
- reader = cmd.ExecuteReader();
- reader.Read();
- reader.GetDateTime(2);
- Assert.Fail("Should throw an exception");
- }
- catch (MySqlConversionException)
- {
- }
- catch (MySqlException ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- /// <summary>
- /// Bug #8929 Timestamp values with a date > 10/29/9997 cause problems
- /// </summary>
- [Test]
- public void LargeDateTime()
- {
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, dt) VALUES(?id,?dt)", conn);
- cmd.Parameters.Add(new MySqlParameter("?id", 1));
- cmd.Parameters.Add(new MySqlParameter("?dt", DateTime.Parse("9997-10-29")));
- cmd.ExecuteNonQuery();
- cmd.Parameters[0].Value = 2;
- cmd.Parameters[1].Value = DateTime.Parse("9997-10-30");
- cmd.ExecuteNonQuery();
- cmd.Parameters[0].Value = 3;
- cmd.Parameters[1].Value = DateTime.Parse("9999-12-31");
- cmd.ExecuteNonQuery();
-
- cmd.CommandText = "SELECT id,dt FROM Test";
- using (MySqlDataReader reader = cmd.ExecuteReader())
- {
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(DateTime.Parse("9997-10-29").Date, reader.GetDateTime(1).Date);
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(DateTime.Parse("9997-10-30").Date, reader.GetDateTime(1).Date);
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(DateTime.Parse("9999-12-31").Date, reader.GetDateTime(1).Date);
- }
- }
-
- [Test]
- public void UsingDatesAsStrings()
- {
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test (id, dt) VALUES (1, ?dt)", conn);
- cmd.Parameters.Add("?dt", MySqlDbType.Date);
- cmd.Parameters[0].Value = "2005-03-04";
- cmd.ExecuteNonQuery();
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
- Assert.AreEqual(1, dt.Rows.Count);
- DateTime date = (DateTime)dt.Rows[0]["dt"];
- Assert.AreEqual(2005, date.Year);
- Assert.AreEqual(3, date.Month);
- Assert.AreEqual(4, date.Day);
- }
-
- /// <summary>
- /// Bug #19481 Where clause with datetime throws exception [any warning causes the exception]
- /// </summary>
- [Test]
- public void Bug19481()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test(ID INT NOT NULL AUTO_INCREMENT, " +
- "SATELLITEID VARCHAR(3) NOT NULL, ANTENNAID INT, AOS_TIMESTAMP DATETIME NOT NULL, " +
- "TEL_TIMESTAMP DATETIME, LOS_TIMESTAMP DATETIME, PRIMARY KEY (ID))");
- execSQL("INSERT INTO test VALUES (NULL,'224','0','2005-07-24 00:00:00'," +
- "'2005-07-24 00:02:00','2005-07-24 00:22:00')");
- execSQL("INSERT INTO test VALUES (NULL,'155','24','2005-07-24 03:00:00'," +
- "'2005-07-24 03:02:30','2005-07-24 03:20:00')");
- execSQL("INSERT INTO test VALUES (NULL,'094','34','2005-07-24 09:00:00'," +
- "'2005-07-24 09:00:30','2005-07-24 09:15:00')");
- execSQL("INSERT INTO test VALUES (NULL,'224','54','2005-07-24 12:00:00'," +
- "'2005-07-24 12:01:00','2005-07-24 12:33:00')");
- execSQL("INSERT INTO test VALUES (NULL,'155','25','2005-07-24 15:00:00'," +
- "'2005-07-24 15:02:00','2005-07-24 15:22:00')");
- execSQL("INSERT INTO test VALUES (NULL,'094','0','2005-07-24 17:00:00'," +
- "'2005-07-24 17:02:12','2005-07-24 17:20:00')");
- execSQL("INSERT INTO test VALUES (NULL,'224','24','2005-07-24 19:00:00'," +
- "'2005-07-24 19:02:00','2005-07-24 19:27:00')");
- execSQL("INSERT INTO test VALUES (NULL,'155','34','2005-07-24 21:00:00'," +
- "'2005-07-24 21:02:33','2005-07-24 21:22:55')");
- execSQL("INSERT INTO test VALUES (NULL,'094','55','2005-07-24 23:00:00'," +
- "'2005-07-24 23:00:45','2005-07-24 23:22:23')");
-
- DateTime date = DateTime.Parse("7/24/2005");
- StringBuilder sql = new StringBuilder();
- sql.AppendFormat("SELECT ID, ANTENNAID, TEL_TIMESTAMP, LOS_TIMESTAMP FROM test " +
- "WHERE TEL_TIMESTAMP >= '{0}'", date.ToString("u"));
- MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), conn);
- DataSet dataSet = new DataSet();
- da.Fill(dataSet);
- }
-
- /// <summary>
- /// Bug #17736 Selecting a row with with empty date '0000-00-00' results in Read() hanging.
- /// </summary>
- [Category("4.1")]
- [Test]
- public void PreparedZeroDateTime()
- {
- execSQL("INSERT INTO test VALUES(1, Now(), '0000-00-00', NULL, NULL)");
- MySqlCommand cmd = new MySqlCommand("SELECT d FROM test WHERE id=?id", conn);
- cmd.Parameters.Add("?id", 1);
- cmd.Prepare();
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- reader.Read();
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null)
- reader.Close();
- }
- }
- }
-
-}
+// Copyright (C) 2004-2006 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 System.Data.Common;
+using System.Threading;
+using MySql.Data.MySqlClient;
+using System.Globalization;
+using NUnit.Framework;
+using MySql.Data.Types;
+using System.Text;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ [NUnit.Framework.TestFixture]
+ public class DateTimeTests : BaseTest
+ {
+ [TestFixtureSetUp]
+ public void TestFixtureSetUp()
+ {
+ Open();
+ }
+
+ [TestFixtureTearDown]
+ public void TestFixtureTearDown()
+ {
+ Close();
+ }
+
+ protected override void Setup()
+ {
+ base.Setup();
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT NOT NULL, dt DATETIME, d DATE, " +
+ "t TIME, ts TIMESTAMP, PRIMARY KEY(id))");
+ }
+
+ [Test]
+ public void ConvertZeroDateTime()
+ {
+ execSQL("INSERT INTO Test VALUES(1, '0000-00-00', '0000-00-00', " +
+ "'00:00:00', NULL)");
+
+ MySqlConnection c;
+ MySqlDataReader reader = null;
+
+ string connStr = this.GetConnectionString(true);
+ connStr += ";convert zero datetime=yes";
+ c = new MySqlConnection(connStr);
+
+ try
+ {
+ c.Open();
+
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", c);
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(DateTime.MinValue.Date, reader.GetDateTime(1).Date);
+ Assert.AreEqual(DateTime.MinValue.Date, reader.GetDateTime(2).Date);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ c.Close();
+ }
+ }
+
+ [Test]
+ public void TestNotAllowZerDateAndTime()
+ {
+ execSQL("INSERT INTO Test VALUES(1, 'Test', '0000-00-00', '0000-00-00', '00:00:00')");
+ execSQL("INSERT INTO Test VALUES(2, 'Test', '2004-11-11', '2004-11-11', '06:06:06')");
+
+ MySqlDataReader reader = null;
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+
+ MySqlDateTime testDate = reader.GetMySqlDateTime(2);
+ Assert.IsFalse(testDate.IsValidDateTime, "IsZero is false");
+
+ try
+ {
+ reader.GetValue(2);
+ Assert.Fail("This should not work");
+ }
+ catch (MySqlConversionException) { }
+
+ Assert.IsTrue( reader.Read() );
+
+ DateTime dt2 = (DateTime)reader.GetValue(2);
+ Assert.AreEqual( new DateTime(2004,11,11).Date, dt2.Date );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+
+ }
+
+ [Test]
+ public void DateAdd()
+ {
+ MySqlCommand cmd = new MySqlCommand( "select date_add(?someday, interval 1 hour)",
+ conn);
+ DateTime now = DateTime.Now;
+ DateTime later = now.AddHours(1);
+ later = later.AddMilliseconds(later.Millisecond * -1);
+ cmd.Parameters.Add("?someday", now );
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ DateTime dt = reader.GetDateTime(0);
+ Assert.AreEqual(later.Date, dt.Date);
+ Assert.AreEqual(later.Hour, dt.Hour);
+ Assert.AreEqual(later.Minute, dt.Minute);
+ Assert.AreEqual(later.Second, dt.Second);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void TestAllowZeroDateTime()
+ {
+ execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
+
+ MySqlConnection c = new MySqlConnection(
+ conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true" );
+ c.Open();
+ MySqlDataReader reader = null;
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c);
+ reader = cmd.ExecuteReader();
+ reader.Read();
+
+ Assert.IsTrue( reader.GetValue(1) is MySqlDateTime );
+ Assert.IsTrue( reader.GetValue(2) is MySqlDateTime );
+
+ Assert.IsFalse( reader.GetMySqlDateTime(1).IsValidDateTime );
+ Assert.IsFalse( reader.GetMySqlDateTime(2).IsValidDateTime );
+
+ try
+ {
+ reader.GetDateTime(1);
+ Assert.Fail("This should not succeed");
+ }
+ catch (MySqlConversionException) {}
+
+
+ }
+ catch (MySqlException ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ c.Close();
+ }
+ }
+
+ [Test]
+ public void InsertDateTimeValue()
+ {
+ MySqlConnection c = new MySqlConnection( conn.ConnectionString +
+ ";allow zero datetime=yes");
+ try
+ {
+ c.Open();
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, dt FROM Test", c);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ cb.ToString(); // keep the compiler happy
+
+ DataTable dt = new DataTable();
+ dt.Columns.Add(new DataColumn("id", typeof(int)));
+ dt.Columns.Add(new DataColumn("dt", typeof(DateTime)));
+
+ da.Fill(dt);
+
+ DateTime now = DateTime.Now;
+ DataRow row = dt.NewRow();
+ row["id"] = 1;
+ row["dt"] = now;
+ dt.Rows.Add(row);
+ da.Update(dt);
+
+ dt.Clear();
+ da.Fill(dt);
+
+ Assert.AreEqual(1, dt.Rows.Count);
+ Assert.AreEqual(now.Date, ((DateTime)dt.Rows[0]["dt"]).Date );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ c.Close();
+ }
+ }
+
+
+ [Test]
+ public void SortingMySqlDateTimes()
+ {
+ execSQL("INSERT INTO Test (id, dt) VALUES (1, '2004-10-01')");
+ execSQL("INSERT INTO Test (id, dt) VALUES (2, '2004-10-02')");
+ execSQL("INSERT INTO Test (id, dt) VALUES (3, '2004-11-01')");
+ execSQL("INSERT INTO Test (id, dt) VALUES (4, '2004-11-02')");
+
+ CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
+ CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
+ CultureInfo cul = new CultureInfo("en-GB");
+ Thread.CurrentThread.CurrentCulture = cul;
+ Thread.CurrentThread.CurrentUICulture = cul;
+
+ using( MySqlConnection c = new MySqlConnection( conn.ConnectionString + ";allow zero datetime=yes" ))
+ {
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT dt FROM Test", c);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ DataView dv = dt.DefaultView;
+ dv.Sort = "dt ASC";
+
+ Assert.AreEqual( new DateTime(2004, 10, 1).Date, Convert.ToDateTime(dv[0]["dt"]).Date );
+ Assert.AreEqual( new DateTime(2004, 10, 2).Date, Convert.ToDateTime(dv[1]["dt"]).Date );
+ Assert.AreEqual( new DateTime(2004, 11, 1).Date, Convert.ToDateTime(dv[2]["dt"]).Date );
+ Assert.AreEqual( new DateTime(2004, 11, 2).Date, Convert.ToDateTime(dv[3]["dt"]).Date );
+
+ Thread.CurrentThread.CurrentCulture = curCulture;
+ Thread.CurrentThread.CurrentUICulture = curUICulture;
+ }
+ }
+
+ [Test]
+ public void TestZeroDateTimeException()
+ {
+ execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
+
+ MySqlDataReader reader = null;
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
+ reader = cmd.ExecuteReader();
+ reader.Read();
+ reader.GetDateTime(2);
+ Assert.Fail("Should throw an exception");
+ }
+ catch (MySqlConversionException)
+ {
+ }
+ catch (MySqlException ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ /// <summary>
+ /// Bug #8929 Timestamp values with a date > 10/29/9997 cause problems
+ /// </summary>
+ [Test]
+ public void LargeDateTime()
+ {
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, dt) VALUES(?id,?dt)", conn);
+ cmd.Parameters.Add(new MySqlParameter("?id", 1));
+ cmd.Parameters.Add(new MySqlParameter("?dt", DateTime.Parse("9997-10-29")));
+ cmd.ExecuteNonQuery();
+ cmd.Parameters[0].Value = 2;
+ cmd.Parameters[1].Value = DateTime.Parse("9997-10-30");
+ cmd.ExecuteNonQuery();
+ cmd.Parameters[0].Value = 3;
+ cmd.Parameters[1].Value = DateTime.Parse("9999-12-31");
+ cmd.ExecuteNonQuery();
+
+ cmd.CommandText = "SELECT id,dt FROM Test";
+ using (MySqlDataReader reader = cmd.ExecuteReader())
+ {
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(DateTime.Parse("9997-10-29").Date, reader.GetDateTime(1).Date);
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(DateTime.Parse("9997-10-30").Date, reader.GetDateTime(1).Date);
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(DateTime.Parse("9999-12-31").Date, reader.GetDateTime(1).Date);
+ }
+ }
+
+ [Test]
+ public void UsingDatesAsStrings()
+ {
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test (id, dt) VALUES (1, ?dt)", conn);
+ cmd.Parameters.Add("?dt", MySqlDbType.Date);
+ cmd.Parameters[0].Value = "2005-03-04";
+ cmd.ExecuteNonQuery();
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+ Assert.AreEqual(1, dt.Rows.Count);
+ DateTime date = (DateTime)dt.Rows[0]["dt"];
+ Assert.AreEqual(2005, date.Year);
+ Assert.AreEqual(3, date.Month);
+ Assert.AreEqual(4, date.Day);
+ }
+
+ /// <summary>
+ /// Bug #19481 Where clause with datetime throws exception [any warning causes the exception]
+ /// </summary>
+ [Test]
+ public void Bug19481()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test(ID INT NOT NULL AUTO_INCREMENT, " +
+ "SATELLITEID VARCHAR(3) NOT NULL, ANTENNAID INT, AOS_TIMESTAMP DATETIME NOT NULL, " +
+ "TEL_TIMESTAMP DATETIME, LOS_TIMESTAMP DATETIME, PRIMARY KEY (ID))");
+ execSQL("INSERT INTO test VALUES (NULL,'224','0','2005-07-24 00:00:00'," +
+ "'2005-07-24 00:02:00','2005-07-24 00:22:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'155','24','2005-07-24 03:00:00'," +
+ "'2005-07-24 03:02:30','2005-07-24 03:20:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'094','34','2005-07-24 09:00:00'," +
+ "'2005-07-24 09:00:30','2005-07-24 09:15:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'224','54','2005-07-24 12:00:00'," +
+ "'2005-07-24 12:01:00','2005-07-24 12:33:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'155','25','2005-07-24 15:00:00'," +
+ "'2005-07-24 15:02:00','2005-07-24 15:22:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'094','0','2005-07-24 17:00:00'," +
+ "'2005-07-24 17:02:12','2005-07-24 17:20:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'224','24','2005-07-24 19:00:00'," +
+ "'2005-07-24 19:02:00','2005-07-24 19:27:00')");
+ execSQL("INSERT INTO test VALUES (NULL,'155','34','2005-07-24 21:00:00'," +
+ "'2005-07-24 21:02:33','2005-07-24 21:22:55')");
+ execSQL("INSERT INTO test VALUES (NULL,'094','55','2005-07-24 23:00:00'," +
+ "'2005-07-24 23:00:45','2005-07-24 23:22:23')");
+
+ DateTime date = DateTime.Parse("7/24/2005");
+ StringBuilder sql = new StringBuilder();
+ sql.AppendFormat("SELECT ID, ANTENNAID, TEL_TIMESTAMP, LOS_TIMESTAMP FROM test " +
+ "WHERE TEL_TIMESTAMP >= '{0}'", date.ToString("u"));
+ MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), conn);
+ DataSet dataSet = new DataSet();
+ da.Fill(dataSet);
+ }
+
+ /// <summary>
+ /// Bug #17736 Selecting a row with with empty date '0000-00-00' results in Read() hanging.
+ /// </summary>
+ [Category("4.1")]
+ [Test]
+ public void PreparedZeroDateTime()
+ {
+ execSQL("INSERT INTO test VALUES(1, Now(), '0000-00-00', NULL, NULL)");
+ MySqlCommand cmd = new MySqlCommand("SELECT d FROM test WHERE id=?id", conn);
+ cmd.Parameters.Add("?id", 1);
+ cmd.Prepare();
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ reader.Read();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null)
+ reader.Close();
+ }
+ }
+ }
+
+}
Modified: trunk/TestSuite/GetSchemaTests.cs
===================================================================
--- trunk/TestSuite/GetSchemaTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/GetSchemaTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -52,15 +52,26 @@
{
DataTable dt = conn.GetSchema("Databases");
Assert.IsTrue(dt.Rows.Count >= 3);
+ Assert.AreEqual("Databases", dt.TableName);
dt = conn.GetSchema("Databases", new string[1] { "mysql" });
Assert.AreEqual(1, dt.Rows.Count);
- Assert.AreEqual("mysql", dt.Rows[0][0].ToString().ToLower());
+ Assert.AreEqual("mysql", dt.Rows[0][1].ToString().ToLower());
}
[Test]
public void Tables()
{
+ execSQL("DROP TABLE IF EXISTS test1");
+ execSQL("CREATE TABLE test1 (id int)");
+
+ string[] restrictions = new string[4];
+ restrictions[1] = "test";
+ restrictions[2] = "test1";
+ DataTable dt = conn.GetSchema("Tables", restrictions);
+ Assert.IsTrue(dt.Rows.Count == 1);
+ Assert.AreEqual("Tables", dt.TableName);
+ Assert.AreEqual("test1", dt.Rows[0][2]);
}
[Test]
@@ -71,6 +82,16 @@
[Test]
public void Procedures()
{
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ execSQL("CREATE PROCEDURE spTest (id int) BEGIN SELECT 1; END");
+
+ string[] restrictions = new string[4];
+ restrictions[1] = "test";
+ restrictions[2] = "spTest";
+ DataTable dt = conn.GetSchema("Procedures", restrictions);
+ Assert.IsTrue(dt.Rows.Count == 1);
+ Assert.AreEqual("Procedures", dt.TableName);
+ Assert.AreEqual("spTest", dt.Rows[0][3]);
}
[Test]
Modified: trunk/TestSuite/MySql.Data.Tests.2005.csproj
===================================================================
--- trunk/TestSuite/MySql.Data.Tests.2005.csproj 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/MySql.Data.Tests.2005.csproj 2006-07-30 03:45:48 UTC (rev 281)
@@ -71,9 +71,9 @@
<WarningLevel>4</WarningLevel>
</PropertyGroup>
<ItemGroup>
- <Reference Include="MySQL.Data, Version=5.0.0.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d, processorArchitecture=MSIL">
+ <Reference Include="MySql.Data, Version=5.0.0.0, Culture=neutral, processorArchitecture=MSIL">
<SpecificVersion>False</SpecificVersion>
- <HintPath>..\bin\net-2.0\Debug\MySQL.Data.dll</HintPath>
+ <HintPath>..\mysqlclient\bin\net-2.0\Debug\MySql.Data.dll</HintPath>
</Reference>
<Reference Include="nunit.framework">
<Name>nunit.framework</Name>
@@ -86,10 +86,8 @@
<Reference Include="System.Data">
<Name>System.Data</Name>
</Reference>
- <Reference Include="System.Web.Services" />
- <Reference Include="System.Xml">
- <Name>System.XML</Name>
- </Reference>
+ <Reference Include="System.Windows.Forms" />
+ <Reference Include="System.XML" />
</ItemGroup>
<ItemGroup>
<Compile Include="AssemblyInfo.cs">
@@ -114,6 +112,7 @@
<SubType>Code</SubType>
</Compile>
<Compile Include="AsyncTests.cs" />
+ <Compile Include="Copy of BaseTest.cs" />
<Compile Include="InterfaceTests.cs" />
<Compile Include="GetSchemaTests.cs" />
<Compile Include="DataReaderTests.cs" />
Modified: trunk/TestSuite/ParameterTests.cs
===================================================================
--- trunk/TestSuite/ParameterTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/ParameterTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -35,14 +35,20 @@
public void SetUp()
{
Open();
- execSQL("DROP TABLE IF EXISTS Test; CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, ts TIMESTAMP, PRIMARY KEY(id))");
}
[TestFixtureTearDown]
public void TearDown()
{
Close();
- }
+ }
+
+ protected override void Setup()
+ {
+ base.Setup();
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, ts TIMESTAMP, PRIMARY KEY(id))");
+ }
[Test]
public void TestUserVariables()
@@ -174,9 +180,10 @@
[Test]
public void NestedQuoting()
{
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, name) VALUES(1, 'this is ?\"my value\"')", conn);
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, name) " +
+ "VALUES(1, 'this is ?\"my value\"')", conn);
int count = cmd.ExecuteNonQuery();
- Assert.AreEqual( 1, count );
+ Assert.AreEqual(1, count);
}
[Test]
Modified: trunk/TestSuite/PreparedStatements.cs
===================================================================
--- trunk/TestSuite/PreparedStatements.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/PreparedStatements.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -1,668 +1,669 @@
-// Copyright (C) 2004-2006 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 System.IO;
-using NUnit.Framework;
-
-namespace MySql.Data.MySqlClient.Tests
-{
- [TestFixture]
- public class PreparedStatements : BaseTest
- {
- [TestFixtureSetUp]
- public void FixtureSetup()
- {
- Open();
-
- execSQL("DROP TABLE IF EXISTS Test");
- }
-
- [Test]
- public void Simple()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id INT, dec1 DECIMAL(5,2), name VARCHAR(100))");
- execSQL("INSERT INTO Test VALUES (1, 345.12, 'abcd')");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1,345.12,'abcd')", conn);
- cmd.Prepare();
- cmd.ExecuteNonQuery();
-
- cmd.CommandText = "SELECT * FROM Test";
- cmd.Prepare();
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue(reader.Read());
- Assert.AreEqual(1, reader.GetInt32(0));
- Assert.AreEqual(345.12, reader.GetDecimal(1));
- Assert.AreEqual("abcd", reader.GetString(2));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
-
- [Test]
- public void SimplePrepareBeforeParms()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (one INTEGER, two INTEGER)");
- execSQL("INSERT INTO Test VALUES (1, 2)");
-
- // create the command and prepare the statement
- IDbCommand cmd = conn.CreateCommand();
- cmd.CommandText = "SELECT * FROM Test WHERE one = ?p1";
- cmd.Prepare();
-
- // create the parameter
- IDbDataParameter p1 = cmd.CreateParameter();
- p1.ParameterName = "?p1";
- p1.DbType = DbType.Int32;
- p1.Precision = (byte)10;
- p1.Scale = (byte)0;
- p1.Size = 4;
- cmd.Parameters.Add(p1);
- p1.Value = 1;
-
- // Execute the reader
- IDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
-
- // Fetch the first record
- reader.Read();
-
- Assert.AreEqual(1, reader.GetInt32(0));
- Assert.AreEqual(2, reader.GetInt32(1));
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void DateAndTimes()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id INT NOT NULL, d DATE, dt DATETIME, tm TIME, ts TIMESTAMP, PRIMARY KEY(id))");
-
- string sql = "INSERT INTO Test VALUES(?id, ?d, ?dt, ?tm, NULL)";
- MySqlCommand cmd = new MySqlCommand(sql, conn);
- cmd.Prepare();
-
- DateTime dt = DateTime.Now;
- dt = dt.AddMilliseconds( dt.Millisecond * -1 );
- TimeSpan ts = new TimeSpan( 8, 11, 44, 56, 501 );
-
- cmd.Parameters.Add( "?id", 1 );
- cmd.Parameters.Add( "?d", dt );
- cmd.Parameters.Add( "?dt", dt );
- cmd.Parameters.Add( "?tm", ts );
- int count = cmd.ExecuteNonQuery();
- Assert.AreEqual( 1, count, "Records affected by insert" );
-
- cmd.CommandText = "SELECT * FROM Test";
- cmd.Prepare();
-
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- reader.Read();
- Assert.AreEqual( 1, reader.GetInt32(0), "Id column" );
- Assert.AreEqual( dt.Date, reader.GetDateTime(1).Date, "Date column" );
-
- DateTime dt2 = reader.GetDateTime(2);
- Assert.AreEqual( dt.Date, dt2.Date );
- Assert.AreEqual( dt.Hour, dt2.Hour );
- Assert.AreEqual( dt.Minute, dt2.Minute );
- Assert.AreEqual( dt.Second, dt2.Second );
-
- TimeSpan ts2 = reader.GetTimeSpan(3);
- Assert.AreEqual( ts.Days, ts2.Days );
- Assert.AreEqual( ts.Hours, ts2.Hours );
- Assert.AreEqual( ts.Minutes, ts2.Minutes );
- Assert.AreEqual( ts.Seconds, ts2.Seconds );
-
- Assert.AreEqual( dt.Date, reader.GetDateTime(4).Date, "Timestamp column" );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void ResetCommandText()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id int, name varchar(100))");
- execSQL("INSERT INTO Test VALUES (1, 'Test')");
-
- MySqlCommand cmd = new MySqlCommand("SELECT id FROM Test", conn);
- cmd.Prepare();
- object o = cmd.ExecuteScalar();
- Assert.AreEqual( 1, o );
-
- cmd.CommandText = "SELECT name FROM Test";
- cmd.Prepare();
- o = cmd.ExecuteScalar();
- Assert.AreEqual( "Test", o );
-
- }
-
- [Test]
- public void DifferentParameterOrder()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id int NOT NULL AUTO_INCREMENT, " +
- "id2 int NOT NULL, name varchar(50) DEFAULT NULL, " +
- "id3 int DEFAULT NULL, PRIMARY KEY (id))");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, id2, name, id3) " +
- "VALUES(?id, ?id2, ?name,?id3)", conn);
-
- MySqlParameter id = new MySqlParameter();
- id.ParameterName = "?id";
- id.DbType = DbType.Int32;
- id.Value = DBNull.Value;
-
- MySqlParameter id2 = new MySqlParameter();
- id2.ParameterName = "?id2";
- id2.DbType = DbType.Int32;
- id2.Value = 2;
-
- MySqlParameter name = new MySqlParameter();
- name.ParameterName = "?name";
- name.DbType = DbType.String;
- name.Value = "Test";
-
- MySqlParameter id3 = new MySqlParameter();
- id3.ParameterName = "?id3";
- id3.DbType = DbType.Int32;
- id3.Value = 3;
-
- try
- {
- cmd.Parameters.Add(id);
- cmd.Parameters.Add(id2);
- cmd.Parameters.Add(name);
- cmd.Parameters.Add(id3);
- cmd.Prepare();
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
-
- cmd.Parameters.Clear();
-
- id3.Value = DBNull.Value;
- name.Value = DBNull.Value;
- cmd.Parameters.Add(id);
- cmd.Parameters.Add(id2);
- cmd.Parameters.Add(id3);
- cmd.Parameters.Add(name);
-
- cmd.Prepare();
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
-
- cmd.CommandText = "SELECT id3 FROM Test WHERE id=1";
- Assert.AreEqual(3, cmd.ExecuteScalar());
-
- cmd.CommandText = "SELECT name FROM Test WHERE id=2";
- Assert.AreEqual(DBNull.Value, cmd.ExecuteScalar());
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- }
-
- [Test]
- public void Blobs()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id INT, blob1 LONGBLOB, text1 LONGTEXT)");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?blob1, ?text1)", conn);
- cmd.Prepare();
-
- byte[] bytes = Utils.CreateBlob( 400000 );
- string inStr = "This is my text";
-
- cmd.Parameters.Add( "?id", 1 );
- cmd.Parameters.Add( "?blob1", bytes );
- cmd.Parameters.Add( "?text1", inStr );
- int count = cmd.ExecuteNonQuery();
- Assert.AreEqual( 1, count );
-
- cmd.CommandText = "SELECT * FROM Test";
- cmd.Prepare();
- MySqlDataReader reader = null;
-
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( 1, reader.GetInt32(0) );
- Assert.AreEqual( bytes.Length, reader.GetBytes( 1, 0, null, 0, 0 ));
- byte[] outBytes = new byte[ bytes.Length ];
- reader.GetBytes( 1, 0, outBytes, 0, bytes.Length );
- for (int x=0; x < bytes.Length; x++)
- Assert.AreEqual( bytes[x], outBytes[x] );
- Assert.AreEqual( inStr, reader.GetString( 2 ) );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void SimpleTest2()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (one integer, two integer, three integer, four integer, five integer, six integer, seven integer)");
- execSQL("INSERT INTO Test VALUES (1, 2, 3, 4, 5, 6, 7)");
-
- // create the command and prepare the statement
- IDbCommand cmd = conn.CreateCommand();
- cmd.CommandText = "SELECT one, two, three, four, five, six, seven FROM Test";
- cmd.Prepare();
- // Execute the reader
- IDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- // Fetch the first record
- reader.Read();
-
- Assert.AreEqual( 1, reader.GetInt32(0) );
- Assert.AreEqual( 2, reader.GetInt32(1) );
- Assert.AreEqual( 3, reader.GetInt32(2) );
- Assert.AreEqual( 4, reader.GetInt32(3) );
- Assert.AreEqual( 5, reader.GetInt32(4) );
- Assert.AreEqual( 6, reader.GetInt32(5) );
- Assert.AreEqual( 7, reader.GetInt32(6) );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- [Category("4.1")]
- public void Bug6271()
- {
- execSQL("DROP TABLE IF EXISTS Test2");
-
- // Create the table again
- execSQL("CREATE TABLE `Test2` (id INT unsigned NOT NULL auto_increment, " +
- "`xpDOSG_Name` text,`xpDOSG_Desc` text, `Avatar` MEDIUMBLOB, `dtAdded` DATETIME, `dtTime` TIMESTAMP, " +
- "PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=latin1" );
-
- string sql = "INSERT INTO `Test2` (`xpDOSG_Name`,`dtAdded`, `xpDOSG_Desc`,`Avatar`, `dtTime`) " +
- "VALUES(?name, ?dt, ?desc, ?Avatar, NULL)";
-
- MySqlCommand cmd = new MySqlCommand(sql, conn);
- cmd.Prepare();
-
- DateTime dt = DateTime.Now;
- dt = dt.AddMilliseconds( dt.Millisecond * -1 );
-
- byte[] xpDOSG_Avatar = Utils.CreateBlob( 13000 );
- cmd.Parameters.Add( "?name", "Ceci est un nom");
-
- cmd.Parameters.Add( "?desc", "Ceci est une description facile - cmd.Parameters.Add( "?avatar",xpDOSG_Avatar);
- cmd.Parameters.Add( "?dt", dt);
- int count = cmd.ExecuteNonQuery();
- Assert.AreEqual( 1, count );
-
- MySqlDataReader reader = null;
- try
- {
- cmd.CommandText = "SELECT * FROM Test2";
- reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( "Ceci est un nom", reader.GetString(1) );
- Assert.AreEqual( dt.ToString("G"), reader.GetDateTime(4).ToString("G") );
- Assert.AreEqual( "Ceci est une description facile -
- long len = reader.GetBytes( 3, 0, null, 0, 0 );
- Assert.AreEqual( xpDOSG_Avatar.Length, len );
- byte[] outBytes = new byte[len];
- reader.GetBytes( 3, 0, outBytes, 0, (int)len );
-
- for (int x=0; x < xpDOSG_Avatar.Length; x++)
- Assert.AreEqual( xpDOSG_Avatar[x], outBytes[x] );
-
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- [Test]
- public void SimpleTest()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (one integer, two integer )");
- execSQL("INSERT INTO Test VALUES( 1, 2)");
- // create the command and prepare the statement
- IDbCommand cmd = conn.CreateCommand();
- cmd.CommandText = "SELECT * FROM test where one = ?p1";
- // create the parameter
- IDbDataParameter p1 = cmd.CreateParameter();
- p1.ParameterName = "p1";
- p1.DbType = DbType.Int32;
- p1.Precision = (byte)10;
- p1.Scale = (byte)0;
- p1.Size = 4;
- cmd.Parameters.Add(p1);
- // prepare the command
- cmd.Prepare();
- // set the parameter value
- p1.Value = 1;
- // Execute the reader
- IDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- // Fetch the first record
- reader.Read();
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- /// <summary>
- /// Bug #13662 Prepare() truncates accented character input
- /// </summary>
- [Test]
- [Category("4.1")]
- public void InsertAccentedCharacters()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (id INT UNSIGNED NOT NULL PRIMARY KEY " +
- "AUTO_INCREMENT, input TEXT NOT NULL) CHARACTER SET UTF8");
- // COLLATE " +
- //"utf8_bin");
- MySqlConnection conn2 = new MySqlConnection(GetConnectionString(true) +
- ";charset=utf8");
- try
- {
- conn2.Open();
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test(input) " +
- "VALUES (?input) ON DUPLICATE KEY UPDATE " +
- "id=LAST_INSERT_ID(id)", conn2);
- cmd.Parameters.Add(new MySqlParameter("?input", ""));
- cmd.Prepare();
- cmd.Parameters[0].Value = "irache mart- cmd.ExecuteNonQuery();
-
- MySqlCommand cmd2 = new MySqlCommand("SELECT input FROM test", conn2);
- Assert.AreEqual("irache mart- cmd2.ExecuteScalar());
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- conn2.Close();
- }
- }
-
- /// <summary>
- /// Bug #13541 Prepare breaks if a parameter is used more than once
- /// </summary>
- [Test]
- [Category("4.1")]
- public void UsingParametersTwice()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE IF NOT EXISTS test (input TEXT NOT NULL, " +
- "UNIQUE (input(100)), state INT NOT NULL, score INT NOT NULL)");
-
- MySqlCommand cmd = new MySqlCommand("Insert into test (input, " +
- "state, score) VALUES (?input, ?st, ?sc) ON DUPLICATE KEY " +
- "UPDATE state=state|?st;", conn);
- cmd.Parameters.Add (new MySqlParameter("?input", ""));
- cmd.Parameters.Add (new MySqlParameter("?st", Convert.ToInt32(0)));
- cmd.Parameters.Add (new MySqlParameter("?sc", Convert.ToInt32 (0)));
- cmd.Prepare();
-
- cmd.Parameters["input"].Value = "test";
- cmd.Parameters["st"].Value = 1;
- cmd.Parameters["sc"].Value = 42;
- int result = cmd.ExecuteNonQuery();
- Assert.AreEqual(1, result);
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
- Assert.AreEqual(1, dt.Rows.Count);
- Assert.AreEqual("test", dt.Rows[0]["input"]);
- Assert.AreEqual(1, dt.Rows[0]["state"]);
- Assert.AreEqual(42, dt.Rows[0]["score"]);
- }
-
- /// <summary>
- /// Bug #19261 Supplying Input Parameters
- /// </summary>
- [Test]
- [Category("4.1")]
- public void MoreParametersOutOfOrder()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE `test` (`BlackListID` int(11) NOT NULL auto_increment, " +
- "`SubscriberID` int(11) NOT NULL, `Phone` varchar(50) default NULL, " +
- "`ContactID` int(11) default NULL, " +
- "`AdminJunk` tinyint(1) NOT NULL default '0', " +
- "PRIMARY KEY (`BlackListID`), KEY `SubscriberID` (`SubscriberID`))");
-
- IDbCommand cmd = conn.CreateCommand();
- cmd.CommandText = "INSERT INTO `test`(`SubscriberID`,`Phone`,`ContactID`, " +
- "`AdminJunk`) VALUES (?SubscriberID,?Phone,?ContactID, ?AdminJunk);";
-
- MySqlParameter oParameterSubscriberID = new MySqlParameter();
- oParameterSubscriberID.ParameterName = "?SubscriberID";
- oParameterSubscriberID.DbType = DbType.Int32;
- oParameterSubscriberID.Value = 1;
-
- MySqlParameter oParameterPhone = new MySqlParameter();
- oParameterPhone.ParameterName = "?Phone";
- oParameterPhone.DbType = DbType.String;
- oParameterPhone.Value = DBNull.Value;
-
- MySqlParameter oParameterContactID = new MySqlParameter();
- oParameterContactID.ParameterName = "?ContactID";
- oParameterContactID.DbType = DbType.Int32;
- oParameterContactID.Value = DBNull.Value;
-
- MySqlParameter oParameterAdminJunk = new MySqlParameter();
- oParameterAdminJunk.ParameterName = "?AdminJunk";
- oParameterAdminJunk.DbType = DbType.Boolean;
- oParameterAdminJunk.Value = true;
-
- cmd.Parameters.Add(oParameterSubscriberID);
- cmd.Parameters.Add(oParameterPhone);
- cmd.Parameters.Add(oParameterAdminJunk);
- cmd.Parameters.Add(oParameterContactID);
-
- cmd.Prepare();
- int cnt = cmd.ExecuteNonQuery();
- Assert.AreEqual(1, cnt);
- }
-
- /// <summary>
- /// Bug #16627 Index and length must refer to a location within the string." when executing c
- /// </summary>
- [Test]
- [Category("4.1")]
- public void ParameterLengths()
- {
- execSQL("CREATE TABLE test (id int, name VARCHAR(255))");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (?id, ?name)", conn);
- cmd.Parameters.Add("?id", MySqlDbType.Int32);
- cmd.Parameters.Add("?name", MySqlDbType.VarChar);
- cmd.Parameters[1].Size = 255;
- cmd.Prepare();
-
- cmd.Parameters[0].Value = 1;
- cmd.Parameters[1].Value = "short string";
- cmd.ExecuteNonQuery();
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
- Assert.AreEqual(1, dt.Rows.Count);
- Assert.AreEqual(1, dt.Rows[0]["id"]);
- Assert.AreEqual("short string", dt.Rows[0]["name"]);
- }
-
- /// <summary>
- /// Bug #18570 Unsigned tinyint (NET byte) incorrectly determined param type from param val
- /// </summary>
- [Test]
- [Category("4.1")]
- public void UnsignedTinyInt()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test(ID TINYINT UNSIGNED NOT NULL, " +
- "Name VARCHAR(50) NOT NULL, PRIMARY KEY (ID), UNIQUE (ID), " +
- "UNIQUE (Name))");
- execSQL("INSERT INTO test VALUES ('127', 'name1')");
- execSQL("INSERT INTO test VALUES ('128', 'name2')");
- execSQL("INSERT INTO test VALUES ('255', 'name3')");
-
- string sql = " SELECT count(*) FROM TEST WHERE ID = ?id";
-
- MySqlCommand command = new MySqlCommand();
- command.CommandText = sql;
- command.CommandType = CommandType.Text;
- command.Connection = (MySqlConnection)conn;
- command.Prepare();
-
- command.Parameters.Add("?id", (byte)127);
- object count = command.ExecuteScalar();
- Assert.AreEqual(1, count);
-
- command.Parameters.Add("?id", (byte)128);
- count = command.ExecuteScalar();
- Assert.AreEqual(1, count);
-
- command.Parameters.Add("?id", (byte)255);
- count = command.ExecuteScalar();
- Assert.AreEqual(1, count);
-
- command.Parameters.Add("?id", "255");
- count = command.ExecuteScalar();
- Assert.AreEqual(1, count);
- }
-
- /// <summary>
- /// Bug #16934 Unsigned values > 2^63 (UInt64) cannot be used in prepared statements
- /// </summary>
- [Test]
- [Category("4.1")]
- public void UnsignedValues()
- {
- execSQL("DROP TABLE IF EXISTS test");
- execSQL("CREATE TABLE test (ulVal BIGINT UNSIGNED, lVal INT UNSIGNED, " +
- "mVal MEDIUMINT UNSIGNED, sVal SMALLINT UNSIGNED)");
-
- MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (?ulVal, " +
- "?lVal, ?mVal, ?sVal)", conn);
- cmd.Parameters.Add("?ulVal", MySqlDbType.UInt64);
- cmd.Parameters.Add("?lVal", MySqlDbType.UInt32);
- cmd.Parameters.Add("?mVal", MySqlDbType.UInt24);
- cmd.Parameters.Add("?sVal", MySqlDbType.UInt16);
- cmd.Prepare();
- cmd.Parameters[0].Value = UInt64.MaxValue;
- cmd.Parameters[1].Value = UInt32.MaxValue;
- cmd.Parameters[2].Value = 16777215;
- cmd.Parameters[3].Value = UInt16.MaxValue;
- Assert.AreEqual(1, cmd.ExecuteNonQuery());
- cmd.CommandText = "SELECT * FROM test";
- cmd.CommandType = CommandType.Text;
- MySqlDataReader reader = null;
- try
- {
- reader = cmd.ExecuteReader();
- reader.Read();
- Assert.AreEqual(UInt64.MaxValue, reader.GetUInt64(0));
- Assert.AreEqual(UInt32.MaxValue, reader.GetUInt32(1));
- Assert.AreEqual(16777215, reader.GetUInt32(2));
- Assert.AreEqual(UInt16.MaxValue, reader.GetUInt16(3));
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- if (reader != null)
- reader.Close();
- }
- }
- }
-}
+// Copyright (C) 2004-2006 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 System.IO;
+using NUnit.Framework;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ [TestFixture]
+ public class PreparedStatements : BaseTest
+ {
+ [TestFixtureSetUp]
+ public void FixtureSetup()
+ {
+ Open();
+
+ execSQL("DROP TABLE IF EXISTS Test");
+ }
+
+ [Test]
+ public void Simple()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT, dec1 DECIMAL(5,2), name VARCHAR(100))");
+ execSQL("INSERT INTO Test VALUES (1, 345.12, 'abcd')");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1,345.12,'abcd')", conn);
+ cmd.Prepare();
+ cmd.ExecuteNonQuery();
+
+ cmd.CommandText = "SELECT * FROM Test";
+ cmd.Prepare();
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(1, reader.GetInt32(0));
+ Assert.AreEqual(345.12, reader.GetDecimal(1));
+ Assert.AreEqual("abcd", reader.GetString(2));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+
+ [Test]
+ public void SimplePrepareBeforeParms()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (one INTEGER, two INTEGER)");
+ execSQL("INSERT INTO Test VALUES (1, 2)");
+
+ // create the command and prepare the statement
+ IDbCommand cmd = conn.CreateCommand();
+ cmd.CommandText = "SELECT * FROM Test WHERE one = ?p1";
+ cmd.Prepare();
+
+ // create the parameter
+ IDbDataParameter p1 = cmd.CreateParameter();
+ p1.ParameterName = "?p1";
+ p1.DbType = DbType.Int32;
+ p1.Precision = (byte)10;
+ p1.Scale = (byte)0;
+ p1.Size = 4;
+ cmd.Parameters.Add(p1);
+ p1.Value = 1;
+
+ // Execute the reader
+ IDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+
+ // Fetch the first record
+ reader.Read();
+
+ Assert.AreEqual(1, reader.GetInt32(0));
+ Assert.AreEqual(2, reader.GetInt32(1));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void DateAndTimes()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT NOT NULL, d DATE, dt DATETIME, tm TIME, ts TIMESTAMP, PRIMARY KEY(id))");
+
+ string sql = "INSERT INTO Test VALUES(?id, ?d, ?dt, ?tm, NULL)";
+ MySqlCommand cmd = new MySqlCommand(sql, conn);
+ cmd.Prepare();
+
+ DateTime dt = DateTime.Now;
+ dt = dt.AddMilliseconds( dt.Millisecond * -1 );
+ TimeSpan ts = new TimeSpan( 8, 11, 44, 56, 501 );
+
+ cmd.Parameters.Add( "?id", 1 );
+ cmd.Parameters.Add( "?d", dt );
+ cmd.Parameters.Add( "?dt", dt );
+ cmd.Parameters.Add( "?tm", ts );
+ int count = cmd.ExecuteNonQuery();
+ Assert.AreEqual( 1, count, "Records affected by insert" );
+
+ cmd.CommandText = "SELECT * FROM Test";
+ cmd.Prepare();
+
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ reader.Read();
+ Assert.AreEqual( 1, reader.GetInt32(0), "Id column" );
+ Assert.AreEqual( dt.Date, reader.GetDateTime(1).Date, "Date column" );
+
+ DateTime dt2 = reader.GetDateTime(2);
+ Assert.AreEqual( dt.Date, dt2.Date );
+ Assert.AreEqual( dt.Hour, dt2.Hour );
+ Assert.AreEqual( dt.Minute, dt2.Minute );
+ Assert.AreEqual( dt.Second, dt2.Second );
+
+ TimeSpan ts2 = reader.GetTimeSpan(3);
+ Assert.AreEqual( ts.Days, ts2.Days );
+ Assert.AreEqual( ts.Hours, ts2.Hours );
+ Assert.AreEqual( ts.Minutes, ts2.Minutes );
+ Assert.AreEqual( ts.Seconds, ts2.Seconds );
+
+ Assert.AreEqual( dt.Date, reader.GetDateTime(4).Date, "Timestamp column" );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void ResetCommandText()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id int, name varchar(100))");
+ execSQL("INSERT INTO Test VALUES (1, 'Test')");
+
+ MySqlCommand cmd = new MySqlCommand("SELECT id FROM Test", conn);
+ cmd.Prepare();
+ object o = cmd.ExecuteScalar();
+ Assert.AreEqual( 1, o );
+
+ cmd.CommandText = "SELECT name FROM Test";
+ cmd.Prepare();
+ o = cmd.ExecuteScalar();
+ Assert.AreEqual( "Test", o );
+
+ }
+
+ [Test]
+ public void DifferentParameterOrder()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id int NOT NULL AUTO_INCREMENT, " +
+ "id2 int NOT NULL, name varchar(50) DEFAULT NULL, " +
+ "id3 int DEFAULT NULL, PRIMARY KEY (id))");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, id2, name, id3) " +
+ "VALUES(?id, ?id2, ?name,?id3)", conn);
+
+ MySqlParameter id = new MySqlParameter();
+ id.ParameterName = "?id";
+ id.DbType = DbType.Int32;
+ id.Value = DBNull.Value;
+
+ MySqlParameter id2 = new MySqlParameter();
+ id2.ParameterName = "?id2";
+ id2.DbType = DbType.Int32;
+ id2.Value = 2;
+
+ MySqlParameter name = new MySqlParameter();
+ name.ParameterName = "?name";
+ name.DbType = DbType.String;
+ name.Value = "Test";
+
+ MySqlParameter id3 = new MySqlParameter();
+ id3.ParameterName = "?id3";
+ id3.DbType = DbType.Int32;
+ id3.Value = 3;
+
+ try
+ {
+ cmd.Parameters.Add(id);
+ cmd.Parameters.Add(id2);
+ cmd.Parameters.Add(name);
+ cmd.Parameters.Add(id3);
+ cmd.Prepare();
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+
+ cmd.Parameters.Clear();
+
+ id3.Value = DBNull.Value;
+ name.Value = DBNull.Value;
+ cmd.Parameters.Add(id);
+ cmd.Parameters.Add(id2);
+ cmd.Parameters.Add(id3);
+ cmd.Parameters.Add(name);
+
+ cmd.Prepare();
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+
+ cmd.CommandText = "SELECT id3 FROM Test WHERE id=1";
+ Assert.AreEqual(3, cmd.ExecuteScalar());
+
+ cmd.CommandText = "SELECT name FROM Test WHERE id=2";
+ Assert.AreEqual(DBNull.Value, cmd.ExecuteScalar());
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ }
+
+ [Test]
+ public void Blobs()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT, blob1 LONGBLOB, text1 LONGTEXT)");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?blob1, ?text1)", conn);
+ cmd.Prepare();
+
+ byte[] bytes = Utils.CreateBlob( 400000 );
+ string inStr = "This is my text";
+
+ cmd.Parameters.Add( "?id", 1 );
+ cmd.Parameters.Add( "?blob1", bytes );
+ cmd.Parameters.Add( "?text1", inStr );
+ int count = cmd.ExecuteNonQuery();
+ Assert.AreEqual( 1, count );
+
+ cmd.CommandText = "SELECT * FROM Test";
+ cmd.Prepare();
+ MySqlDataReader reader = null;
+
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue( reader.Read() );
+ Assert.AreEqual( 1, reader.GetInt32(0) );
+ Assert.AreEqual( bytes.Length, reader.GetBytes( 1, 0, null, 0, 0 ));
+ byte[] outBytes = new byte[ bytes.Length ];
+ reader.GetBytes( 1, 0, outBytes, 0, bytes.Length );
+ for (int x=0; x < bytes.Length; x++)
+ Assert.AreEqual( bytes[x], outBytes[x] );
+ Assert.AreEqual( inStr, reader.GetString( 2 ) );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void SimpleTest2()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (one integer, two integer, three integer, four integer, five integer, six integer, seven integer)");
+ execSQL("INSERT INTO Test VALUES (1, 2, 3, 4, 5, 6, 7)");
+
+ // create the command and prepare the statement
+ IDbCommand cmd = conn.CreateCommand();
+ cmd.CommandText = "SELECT one, two, three, four, five, six, seven FROM Test";
+ cmd.Prepare();
+ // Execute the reader
+ IDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ // Fetch the first record
+ reader.Read();
+
+ Assert.AreEqual( 1, reader.GetInt32(0) );
+ Assert.AreEqual( 2, reader.GetInt32(1) );
+ Assert.AreEqual( 3, reader.GetInt32(2) );
+ Assert.AreEqual( 4, reader.GetInt32(3) );
+ Assert.AreEqual( 5, reader.GetInt32(4) );
+ Assert.AreEqual( 6, reader.GetInt32(5) );
+ Assert.AreEqual( 7, reader.GetInt32(6) );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ [Category("4.1")]
+ public void Bug6271()
+ {
+ execSQL("DROP TABLE IF EXISTS Test2");
+
+ // Create the table again
+ execSQL("CREATE TABLE `Test2` (id INT unsigned NOT NULL auto_increment, " +
+ "`xpDOSG_Name` text,`xpDOSG_Desc` text, `Avatar` MEDIUMBLOB, `dtAdded` DATETIME, `dtTime` TIMESTAMP, " +
+ "PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=latin1" );
+
+ string sql = "INSERT INTO `Test2` (`xpDOSG_Name`,`dtAdded`, `xpDOSG_Desc`,`Avatar`, `dtTime`) " +
+ "VALUES(?name, ?dt, ?desc, ?Avatar, NULL)";
+
+ MySqlCommand cmd = new MySqlCommand(sql, conn);
+ cmd.Prepare();
+
+ DateTime dt = DateTime.Now;
+ dt = dt.AddMilliseconds( dt.Millisecond * -1 );
+
+ byte[] xpDOSG_Avatar = Utils.CreateBlob( 13000 );
+ cmd.Parameters.Add( "?name", "Ceci est un nom");
+
+ cmd.Parameters.Add( "?desc", "Ceci est une description facile + cmd.Parameters.Add( "?avatar",xpDOSG_Avatar);
+ cmd.Parameters.Add( "?dt", dt);
+ int count = cmd.ExecuteNonQuery();
+ Assert.AreEqual( 1, count );
+
+ MySqlDataReader reader = null;
+ try
+ {
+ cmd.CommandText = "SELECT * FROM Test2";
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue( reader.Read() );
+ Assert.AreEqual( "Ceci est un nom", reader.GetString(1) );
+ Assert.AreEqual( dt.ToString("G"), reader.GetDateTime(4).ToString("G") );
+ Assert.AreEqual( "Ceci est une description facile +
+ long len = reader.GetBytes( 3, 0, null, 0, 0 );
+ Assert.AreEqual( xpDOSG_Avatar.Length, len );
+ byte[] outBytes = new byte[len];
+ reader.GetBytes( 3, 0, outBytes, 0, (int)len );
+
+ for (int x=0; x < xpDOSG_Avatar.Length; x++)
+ Assert.AreEqual( xpDOSG_Avatar[x], outBytes[x] );
+
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ [Test]
+ public void SimpleTest()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (one integer, two integer )");
+ execSQL("INSERT INTO Test VALUES( 1, 2)");
+ // create the command and prepare the statement
+ IDbCommand cmd = conn.CreateCommand();
+ cmd.CommandText = "SELECT * FROM test where one = ?p1";
+ // create the parameter
+ IDbDataParameter p1 = cmd.CreateParameter();
+ p1.ParameterName = "p1";
+ p1.DbType = DbType.Int32;
+ p1.Precision = (byte)10;
+ p1.Scale = (byte)0;
+ p1.Size = 4;
+ cmd.Parameters.Add(p1);
+ // prepare the command
+ cmd.Prepare();
+ // set the parameter value
+ p1.Value = 1;
+ // Execute the reader
+ IDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ // Fetch the first record
+ reader.Read();
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ /// <summary>
+ /// Bug #13662 Prepare() truncates accented character input
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void InsertAccentedCharacters()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (id INT UNSIGNED NOT NULL PRIMARY KEY " +
+ "AUTO_INCREMENT, input TEXT NOT NULL) CHARACTER SET UTF8");
+ // COLLATE " +
+ //"utf8_bin");
+ MySqlConnection conn2 = new MySqlConnection(GetConnectionString(true) +
+ ";charset=utf8");
+ try
+ {
+ conn2.Open();
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test(input) " +
+ "VALUES (?input) ON DUPLICATE KEY UPDATE " +
+ "id=LAST_INSERT_ID(id)", conn2);
+ cmd.Parameters.Add(new MySqlParameter("?input", ""));
+ cmd.Prepare();
+ cmd.Parameters[0].Value = "irache mart+ cmd.ExecuteNonQuery();
+
+ MySqlCommand cmd2 = new MySqlCommand("SELECT input FROM test", conn2);
+ Assert.AreEqual("irache mart+ cmd2.ExecuteScalar());
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ conn2.Close();
+ }
+ }
+
+ /// <summary>
+ /// Bug #13541 Prepare breaks if a parameter is used more than once
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void UsingParametersTwice()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE IF NOT EXISTS test (input TEXT NOT NULL, " +
+ "UNIQUE (input(100)), state INT NOT NULL, score INT NOT NULL)");
+
+ MySqlCommand cmd = new MySqlCommand("Insert into test (input, " +
+ "state, score) VALUES (?input, ?st, ?sc) ON DUPLICATE KEY " +
+ "UPDATE state=state|?st;", conn);
+ cmd.Parameters.Add (new MySqlParameter("?input", ""));
+ cmd.Parameters.Add (new MySqlParameter("?st", Convert.ToInt32(0)));
+ cmd.Parameters.Add (new MySqlParameter("?sc", Convert.ToInt32 (0)));
+ cmd.Prepare();
+
+ cmd.Parameters["input"].Value = "test";
+ cmd.Parameters["st"].Value = 1;
+ cmd.Parameters["sc"].Value = 42;
+ int result = cmd.ExecuteNonQuery();
+ Assert.AreEqual(1, result);
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+ Assert.AreEqual(1, dt.Rows.Count);
+ Assert.AreEqual("test", dt.Rows[0]["input"]);
+ Assert.AreEqual(1, dt.Rows[0]["state"]);
+ Assert.AreEqual(42, dt.Rows[0]["score"]);
+ }
+
+ /// <summary>
+ /// Bug #19261 Supplying Input Parameters
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void MoreParametersOutOfOrder()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE `test` (`BlackListID` int(11) NOT NULL auto_increment, " +
+ "`SubscriberID` int(11) NOT NULL, `Phone` varchar(50) default NULL, " +
+ "`ContactID` int(11) default NULL, " +
+ "`AdminJunk` tinyint(1) NOT NULL default '0', " +
+ "PRIMARY KEY (`BlackListID`), KEY `SubscriberID` (`SubscriberID`))");
+
+ IDbCommand cmd = conn.CreateCommand();
+ cmd.CommandText = "INSERT INTO `test`(`SubscriberID`,`Phone`,`ContactID`, " +
+ "`AdminJunk`) VALUES (?SubscriberID,?Phone,?ContactID, ?AdminJunk);";
+
+ MySqlParameter oParameterSubscriberID = new MySqlParameter();
+ oParameterSubscriberID.ParameterName = "?SubscriberID";
+ oParameterSubscriberID.DbType = DbType.Int32;
+ oParameterSubscriberID.Value = 1;
+
+ MySqlParameter oParameterPhone = new MySqlParameter();
+ oParameterPhone.ParameterName = "?Phone";
+ oParameterPhone.DbType = DbType.String;
+ oParameterPhone.Value = DBNull.Value;
+
+ MySqlParameter oParameterContactID = new MySqlParameter();
+ oParameterContactID.ParameterName = "?ContactID";
+ oParameterContactID.DbType = DbType.Int32;
+ oParameterContactID.Value = DBNull.Value;
+
+ MySqlParameter oParameterAdminJunk = new MySqlParameter();
+ oParameterAdminJunk.ParameterName = "?AdminJunk";
+ oParameterAdminJunk.DbType = DbType.Boolean;
+ oParameterAdminJunk.Value = true;
+
+ cmd.Parameters.Add(oParameterSubscriberID);
+ cmd.Parameters.Add(oParameterPhone);
+ cmd.Parameters.Add(oParameterAdminJunk);
+ cmd.Parameters.Add(oParameterContactID);
+
+ cmd.Prepare();
+ int cnt = cmd.ExecuteNonQuery();
+ Assert.AreEqual(1, cnt);
+ }
+
+ /// <summary>
+ /// Bug #16627 Index and length must refer to a location within the string." when executing c
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void ParameterLengths()
+ {
+ execSQL("DROP TABLE test");
+ execSQL("CREATE TABLE test (id int, name VARCHAR(255))");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (?id, ?name)", conn);
+ cmd.Parameters.Add("?id", MySqlDbType.Int32);
+ cmd.Parameters.Add("?name", MySqlDbType.VarChar);
+ cmd.Parameters[1].Size = 255;
+ cmd.Prepare();
+
+ cmd.Parameters[0].Value = 1;
+ cmd.Parameters[1].Value = "short string";
+ cmd.ExecuteNonQuery();
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+ Assert.AreEqual(1, dt.Rows.Count);
+ Assert.AreEqual(1, dt.Rows[0]["id"]);
+ Assert.AreEqual("short string", dt.Rows[0]["name"]);
+ }
+
+ /// <summary>
+ /// Bug #18570 Unsigned tinyint (NET byte) incorrectly determined param type from param val
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void UnsignedTinyInt()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test(ID TINYINT UNSIGNED NOT NULL, " +
+ "Name VARCHAR(50) NOT NULL, PRIMARY KEY (ID), UNIQUE (ID), " +
+ "UNIQUE (Name))");
+ execSQL("INSERT INTO test VALUES ('127', 'name1')");
+ execSQL("INSERT INTO test VALUES ('128', 'name2')");
+ execSQL("INSERT INTO test VALUES ('255', 'name3')");
+
+ string sql = " SELECT count(*) FROM TEST WHERE ID = ?id";
+
+ MySqlCommand command = new MySqlCommand();
+ command.CommandText = sql;
+ command.CommandType = CommandType.Text;
+ command.Connection = (MySqlConnection)conn;
+ command.Prepare();
+
+ command.Parameters.Add("?id", (byte)127);
+ object count = command.ExecuteScalar();
+ Assert.AreEqual(1, count);
+
+ command.Parameters.Add("?id", (byte)128);
+ count = command.ExecuteScalar();
+ Assert.AreEqual(1, count);
+
+ command.Parameters.Add("?id", (byte)255);
+ count = command.ExecuteScalar();
+ Assert.AreEqual(1, count);
+
+ command.Parameters.Add("?id", "255");
+ count = command.ExecuteScalar();
+ Assert.AreEqual(1, count);
+ }
+
+ /// <summary>
+ /// Bug #16934 Unsigned values > 2^63 (UInt64) cannot be used in prepared statements
+ /// </summary>
+ [Test]
+ [Category("4.1")]
+ public void UnsignedValues()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (ulVal BIGINT UNSIGNED, lVal INT UNSIGNED, " +
+ "mVal MEDIUMINT UNSIGNED, sVal SMALLINT UNSIGNED)");
+
+ MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (?ulVal, " +
+ "?lVal, ?mVal, ?sVal)", conn);
+ cmd.Parameters.Add("?ulVal", MySqlDbType.UInt64);
+ cmd.Parameters.Add("?lVal", MySqlDbType.UInt32);
+ cmd.Parameters.Add("?mVal", MySqlDbType.UInt24);
+ cmd.Parameters.Add("?sVal", MySqlDbType.UInt16);
+ cmd.Prepare();
+ cmd.Parameters[0].Value = UInt64.MaxValue;
+ cmd.Parameters[1].Value = UInt32.MaxValue;
+ cmd.Parameters[2].Value = 16777215;
+ cmd.Parameters[3].Value = UInt16.MaxValue;
+ Assert.AreEqual(1, cmd.ExecuteNonQuery());
+ cmd.CommandText = "SELECT * FROM test";
+ cmd.CommandType = CommandType.Text;
+ MySqlDataReader reader = null;
+ try
+ {
+ reader = cmd.ExecuteReader();
+ reader.Read();
+ Assert.AreEqual(UInt64.MaxValue, reader.GetUInt64(0));
+ Assert.AreEqual(UInt32.MaxValue, reader.GetUInt32(1));
+ Assert.AreEqual(16777215, reader.GetUInt32(2));
+ Assert.AreEqual(UInt16.MaxValue, reader.GetUInt16(3));
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ if (reader != null)
+ reader.Close();
+ }
+ }
+ }
+}
Modified: trunk/TestSuite/StoredProcedure.cs
===================================================================
--- trunk/TestSuite/StoredProcedure.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/StoredProcedure.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -1,829 +1,837 @@
-// Copyright (C) 2004-2006 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;
-
-namespace MySql.Data.MySqlClient.Tests
-{
- /// <summary>
- /// Summary description for StoredProcedure.
- /// </summary>
- [TestFixture]
- public class StoredProcedure : BaseTest
- {
- private static string fillError = null;
-
- [TestFixtureSetUp]
- public void FixtureSetup()
- {
- csAdditions = ";pooling=false;procedure cache size=0";
- Open();
- execSQL("DROP TABLE IF EXISTS Test; CREATE TABLE Test (id INT, name VARCHAR(100))");
- }
-
- [TestFixtureTearDown]
- public void TestFixtureTearDown()
- {
- Close();
- }
-
- /// <summary>
- /// Bug #7623 Adding MySqlParameter causes error if MySqlDbType is Decimal
- /// </summary>
- [Test]
- [Category("5.0")]
- public void ReturningResultset()
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest(val decimal(10,3)) begin select val; end");
-
- using (MySqlCommand cmd = new MySqlCommand("spTest", conn))
- {
- cmd.CommandType = CommandType.StoredProcedure;
-
- MySqlParameter p = cmd.Parameters.Add("val", MySqlDbType.Decimal);
- p.Precision = 10;
- p.Scale = 3;
- p.Value = 21;
-
- decimal id = (decimal)cmd.ExecuteScalar();
- Assert.AreEqual(21, id);
- }
- }
-
- [Test]
- [Category("5.0")]
- public void NonQuery()
- {
- execSQL("CREATE PROCEDURE spTest(IN value INT) BEGIN INSERT INTO Test VALUES(value, 'Test'); END" );
-
- //setup testing data
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add( "?value", 2 );
- int rowsAffected = cmd.ExecuteNonQuery();
- Assert.AreEqual(1, rowsAffected);
-
- cmd.CommandText = "SELECT * FROM Test";
- cmd.CommandType = CommandType.Text;
- MySqlDataReader reader = null;
-
- try
- {
- reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( 2, reader.GetInt32(0) );
- Assert.AreEqual( "Test", reader.GetString(1) );
- Assert.IsFalse( reader.Read() );
- Assert.IsFalse( reader.NextResult() );
- }
- catch (Exception ex)
- {
- Assert.Fail( ex.Message );
- }
- finally
- {
- if (reader != null) reader.Close();
- }
- }
-
- /// <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, 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));
- 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("test", cmd.Parameters[4].Value);
- Assert.AreEqual(66, cmd.Parameters[5].Value);
-
- execSQL("DROP PROCEDURE spCount");
- }
-
- [Test()]
- [Category("5.0")]
- public void NoBatch()
- {
- try
- {
- MySqlCommand cmd = new MySqlCommand("spTest;select * from test", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.ExecuteNonQuery();
- Assert.Fail("Should have thrown an exception");
- }
- catch (MySqlException)
- {
- }
- }
-
- [Test()]
- [Category("5.0")]
- public void WrongParameters()
- {
- try
- {
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add( "?p2", 1 );
- cmd.ExecuteNonQuery();
- Assert.Fail("Should have thrown an exception");
- }
- catch (MySqlException)
- {
- }
- }
-
- [Test]
- [Category("5.0")]
- public void NoInOutMarker()
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest( valin varchar(50) ) BEGIN SELECT valin; END" );
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add( "?valin", "myvalue" );
- object val = cmd.ExecuteScalar();
- Assert.AreEqual( "myvalue", val );
- }
-
- [Test()]
- [Category("5.0")]
- public void InputOutputParameters()
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest( INOUT strVal VARCHAR(50), INOUT numVal INT ) " +
- "BEGIN SET strVal = CONCAT(strVal,'ending'); SET numVal=numVal * 2; END" );
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add( "?strVal", "beginning" );
- cmd.Parameters.Add( "?numVal", 33 );
- cmd.Parameters[0].Direction = ParameterDirection.InputOutput;
- cmd.Parameters[1].Direction = ParameterDirection.InputOutput;
- int rowsAffected = cmd.ExecuteNonQuery();
- Assert.AreEqual( 0, rowsAffected );
- Assert.AreEqual( "beginningending", cmd.Parameters[0].Value );
- Assert.AreEqual( 66, cmd.Parameters[1].Value );
-
- }
-
- [Test()]
- [Category("5.0")]
- public void NoSPOnPre50()
- {
- try
- {
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.ExecuteNonQuery();
- Assert.Fail( "This should not have worked" );
- }
- catch (Exception)
- {
- }
- }
-
- [Test]
- [Category("5.0")]
- public void ExecuteScalar()
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest( IN valin VARCHAR(50), OUT valout VARCHAR(50) ) " +
- "BEGIN SET valout=valin; SELECT 'Test'; END" );
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add( "?valin", "valuein" );
- cmd.Parameters.Add( new MySqlParameter("?valout", MySqlDbType.VarChar));
- cmd.Parameters[1].Direction = ParameterDirection.Output;
- object result = cmd.ExecuteScalar();
- Assert.AreEqual( "Test", result );
- Assert.AreEqual( "valuein", cmd.Parameters[1].Value );
- }
-
- /// <summary>
- /// Bug #13590 ExecuteScalar returns only Int64 regardless of actual SQL type
- /// </summary>
- [Test]
- [Category("5.0")]
- public void ExecuteScalar2()
- {
- // create our procedure
- execSQL("CREATE PROCEDURE spTest() " +
- "BEGIN DECLARE myVar1 INT; SET myVar1 := 1; SELECT myVar1; END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- object result = cmd.ExecuteScalar();
- Assert.AreEqual(1, result);
- Assert.IsTrue(result is Int32);
- }
-
- [Test()]
- [Category("5.0")]
- public void ExecuteReader()
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest() " +
- "BEGIN SELECT * FROM mysql.db; END" );
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.Parameters.Add("?a", 3);
- cmd.CommandType = CommandType.StoredProcedure;
- MySqlDataReader reader = cmd.ExecuteReader();
- Assert.AreEqual( true, reader.Read() );
- Assert.AreEqual( false, reader.NextResult() );
- Assert.AreEqual( false, reader.Read() );
- reader.Close();
- }
-
- [Test()]
- [Category("5.0")]
- public void MultipleResultsets()
- {
- MultipleResultsetsImpl(false);
-// MultipleResultsetsImpl(true);
- }
-
- private void MultipleResultsetsImpl(bool prepare)
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest() " +
- "BEGIN SELECT 1; SELECT 2; END" );
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- if (prepare) cmd.Prepare();
- cmd.CommandType = CommandType.StoredProcedure;
- MySqlDataReader reader = cmd.ExecuteReader();
- Assert.AreEqual( true, reader.Read() );
- Assert.AreEqual( true, reader.NextResult() );
- Assert.AreEqual( true, reader.Read() );
- Assert.AreEqual( false, reader.NextResult() );
- Assert.AreEqual( false, reader.Read() );
- reader.Close();
-
- DataSet ds = new DataSet();
- MySqlCommand cmd2 = new MySqlCommand("spTest", conn);
- cmd2.CommandType = CommandType.StoredProcedure;
- MySqlDataAdapter da = new MySqlDataAdapter( cmd2 );
- da.FillError += new FillErrorEventHandler(da_FillError);
- fillError = null;
- da.Fill(ds);
- Assert.AreEqual( 2, ds.Tables.Count );
- Assert.AreEqual( 1, ds.Tables[0].Rows.Count );
- Assert.AreEqual( 1, ds.Tables[1].Rows.Count );
- Assert.AreEqual( 1, ds.Tables[0].Rows[0][0] );
- Assert.AreEqual( 2, ds.Tables[1].Rows[0][0] );
- Assert.IsNull( fillError );
- }
-
- private void da_FillError(object sender, FillErrorEventArgs e)
- {
- fillError = e.Errors.Message;
- e.Continue = true;
- }
-
- [Test()]
- [Category("5.0")]
- public void FunctionNoParams()
- {
- execSQL( "CREATE FUNCTION fnTest() RETURNS CHAR(50)" +
- "BEGIN RETURN \"Test\"; END" );
-
- MySqlCommand cmd = new MySqlCommand("SELECT fnTest()", conn);
- cmd.CommandType = CommandType.Text;
- object result = cmd.ExecuteScalar();
- Assert.AreEqual( "Test", result );
- }
-
- [Test()]
- [Category("5.0")]
- public void FunctionParams()
- {
- execSQL( "CREATE FUNCTION fnTest( val1 INT, val2 CHAR(40) ) RETURNS INT " +
- "BEGIN RETURN val1 + LENGTH(val2); END" );
-
- MySqlCommand cmd = new MySqlCommand("SELECT fnTest(22, 'Test')", conn);
- cmd.CommandType = CommandType.Text;
- object result = cmd.ExecuteScalar();
- Assert.AreEqual( 26, result);
- }
-
- [Test()]
- [Category("5.0")]
- public void UseOldSyntax()
- {
- // create our procedure
- execSQL("CREATE PROCEDURE spTest( IN valin VARCHAR(50), OUT valout VARCHAR(50) ) " +
- "BEGIN SET valout=valin; SELECT 'Test'; END");
-
- MySqlConnection c2 = new MySqlConnection(conn.ConnectionString + ";old syntax=yes");
- c2.Open();
-
- MySqlCommand cmd = new MySqlCommand("spTest", c2);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("@valin", "value");
- cmd.Parameters.Add( new MySqlParameter("@valout", MySqlDbType.VarChar));
- cmd.Parameters[1].Direction = ParameterDirection.Output;
- object result = cmd.ExecuteScalar();
- Assert.AreEqual("Test", result);
- Assert.AreEqual("value", cmd.Parameters[1].Value);
- c2.Close();
- }
-
- [Test]
- [Category("5.0")]
- public void ExecuteWithCreate()
- {
- // create our procedure
- string sql = "CREATE PROCEDURE spTest(IN var INT) BEGIN SELECT var; END; call spTest(?v)";
-
- MySqlCommand cmd = new MySqlCommand(sql, conn);
- cmd.Parameters.Add(new MySqlParameter("?v", 33));
- object val = cmd.ExecuteScalar();
- Assert.AreEqual( 33, val );
- }
-
- /// <summary>
- /// Bug #9722 Connector does not recognize parameters separated by a linefeed
- /// </summary>
- [Test]
- [Category("5.0")]
- public void OtherProcSigs()
- {
- // create our procedure
- execSQL( "CREATE PROCEDURE spTest(IN \r\nvalin DECIMAL(10,2),\nIN val2 INT) " +
- "SQL SECURITY INVOKER BEGIN SELECT valin; END" );
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("?valin", 20.4);
- cmd.Parameters.Add("?val2", 4);
- decimal val = (decimal)cmd.ExecuteScalar();
- Decimal d = new Decimal(20.4);
- Assert.AreEqual(d, val);
-
- // create our second procedure
- execSQL("DROP PROCEDURE IF EXISTS spTest");
- execSQL("CREATE PROCEDURE spTest( \r\n) BEGIN SELECT 4; END" );
- cmd.Parameters.Clear();
- object val1 = cmd.ExecuteScalar();
- Assert.AreEqual(4, val1);
- execSQL("DROP PROCEDURE IF EXISTS spTest");
- }
-
-
- /// <summary>
- /// Bug #10644 Cannot call a stored function directly from Connector/Net
- /// </summary>
- [Test]
- [Category("5.0")]
- public void CallingStoredFunctionasProcedure()
- {
- execSQL("CREATE FUNCTION fnTest(valin int) RETURNS INT BEGIN return valin * 2; END");
- MySqlCommand cmd = new MySqlCommand("fnTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("?valin", 22);
- cmd.Parameters.Add("retval", MySqlDbType.Int32);
- cmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
- cmd.ExecuteNonQuery();
- Assert.AreEqual(44, cmd.Parameters[1].Value);
- }
-
- /// <summary>
- /// Bug #11450 Connector/Net, current database and stored procedures
- /// </summary>
- [Test]
- [Category("5.0")]
- public void NoDefaultDatabase()
- {
- // create our procedure
- execSQL("CREATE PROCEDURE spTest() BEGIN SELECT 4; END" );
-
- string newConnStr = GetConnectionString(false);
- MySqlConnection c = new MySqlConnection(newConnStr);
- try
- {
- c.Open();
- MySqlCommand cmd2 = new MySqlCommand("use test", c);
- cmd2.ExecuteNonQuery();
-
- MySqlCommand cmd = new MySqlCommand("spTest", c);
- cmd.CommandType = CommandType.StoredProcedure;
- object val = cmd.ExecuteScalar();
- Assert.AreEqual(4, val);
-
- cmd2.CommandText = "use mysql";
- cmd2.ExecuteNonQuery();
-
- cmd.CommandText = "test.spTest";
- val = cmd.ExecuteScalar();
- Assert.AreEqual(4, val);
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- c.Close();
- }
- }
-
- /// <summary>
- /// Bug #13590 ExecuteScalar returns only Int64 regardless of actual SQL type
- /// </summary>
- [Category("NotWorking")]
- [Test]
- public void TestSelectingInts()
- {
- execSQL("CREATE PROCEDURE spTest() BEGIN DECLARE myVar INT; " +
- "SET MyVar := 1; SELECT CAST(myVar as INT); END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- object val = cmd.ExecuteScalar();
- Assert.AreEqual(1, val, "Checking value");
- Assert.IsTrue(val is System.Int32, "Checking type");
- }
-
- /// <summary>
- /// Bug #13632 the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5
- /// Bug #15077 Error MySqlCommandBuilder.DeriveParameters for sp without parameters.
- /// Bug #19515 DiscoverParameters fails on numeric datatype
- /// </summary>
- [Category("5.0")]
- [Test]
- public void DeriveParameters()
- {
- execSQL("DROP TABLE IF EXISTS test2");
- execSQL("CREATE TABLE test2 (c CHAR(20))");
- execSQL("INSERT INTO test2 values ( 'xxxx')");
- MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM test2", conn);
- MySqlDataReader reader =cmd2.ExecuteReader();
- reader.Close();
-
- 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'), val8 CHAR, val9 NUMERIC(10,2)) " +
- "BEGIN SELECT 1; END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- MySqlDataAdapter da = new MySqlDataAdapter(cmd);
- MySqlCommandBuilder.DeriveParameters(cmd);
-
- Assert.AreEqual(9, 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);
-
- Assert.AreEqual("val8", cmd.Parameters[7].ParameterName);
- Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[7].Direction);
- Assert.AreEqual(MySqlDbType.String, cmd.Parameters[7].MySqlDbType);
-
- Assert.AreEqual("val9", cmd.Parameters[8].ParameterName);
- 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();
- da = new MySqlDataAdapter(cmd);
- MySqlCommandBuilder.DeriveParameters(cmd);
- Assert.AreEqual(0, cmd.Parameters.Count);
- }
-
- /// <summary>
- /// Bug #13632 the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5
- /// </summary>
- [Category("5.0")]
- [Test]
- public void DeriveParametersForFunction()
- {
- try
- {
- execSQL("CREATE FUNCTION fnTest(v1 DATETIME) RETURNS INT " +
- " BEGIN RETURN 1; END");
-
- MySqlCommand cmd = new MySqlCommand("fnTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- MySqlDataAdapter da = new MySqlDataAdapter(cmd);
- MySqlCommandBuilder.DeriveParameters(cmd);
-
- 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);
- }
- }
-
- /// <summary>
- /// Bug #11386 Numeric parameters with Precision and Scale not taken into account by Connector
- /// </summary>
- [Test]
- [Category("5.0")]
- public void DecimalAsParameter()
- {
- execSQL("CREATE PROCEDURE spTest(IN d DECIMAL(19,4)) BEGIN SELECT d; END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("?d", 21);
- decimal d = (decimal)cmd.ExecuteScalar();
- Assert.AreEqual(21, d);
- }
-
- /// <summary>
- /// Bug #6902 Errors in parsing stored procedure parameters
- /// </summary>
- [Test]
- [Category("5.0")]
- public void ParmWithCharacterSet()
- {
- execSQL("CREATE PROCEDURE spTest(P longtext character set utf8) " +
- "BEGIN SELECT P; END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("?P", "This is my value");
- string p = (string)cmd.ExecuteScalar();
- Assert.AreEqual("This is my value", p);
- }
-
- /// <summary>
- /// Bug #13753 Exception calling stored procedure with special characters in parameters
- /// </summary>
- [Test]
- [Category("5.0")]
- public void SpecialCharacters()
- {
- execSQL("SET sql_mode=ANSI_QUOTES");
- try
- {
- execSQL("CREATE PROCEDURE spTest(\"@Param1\" text) BEGIN SELECT \"@Param1\"; END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.Parameters.Add("@Param1", "This is my value");
- cmd.CommandType = CommandType.StoredProcedure;
-
- string val = (string)cmd.ExecuteScalar();
- Assert.AreEqual("This is my value", val);
- }
- catch (Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- finally
- {
- execSQL("SET sql_mode=\"\"");
- }
- }
-
- [Test]
- [Category("NotWorking")]
- public void CallingSPWithPrepare()
- {
- execSQL("DROP PROCEDURE IF EXISTS spTest");
- execSQL("CREATE PROCEDURE spTest(P int) BEGIN SELECT P; END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add("?P", 33);
- cmd.Prepare();
-
- int p = (int)cmd.ExecuteScalar();
- Assert.AreEqual(33, p);
- }
-
- /// <summary>
- /// Bug #13927 Multiple Records to same Table in Transaction Problem
- /// </summary>
- [Test]
- [Category("5.0")]
- public void MultileRecords()
- {
- execSQL("DROP PROCEDURE IF EXISTS spTest");
- execSQL("CREATE PROCEDURE spTest(id int, str VARCHAR(45)) BEGIN INSERT INTO test VALUES(id, str); END");
-
- MySqlCommand cmd = new MySqlCommand("spTest", conn);
- cmd.CommandType = CommandType.StoredProcedure;
-
- cmd.Parameters.Add("?id", 1);
- cmd.Parameters.Add("?str", "First record");
- cmd.ExecuteNonQuery();
-
- cmd.Parameters.Add("?id", 2);
- cmd.Parameters.Add("?str", "Second record");
- cmd.ExecuteNonQuery();
-
- MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
- DataTable dt = new DataTable();
- da.Fill(dt);
-
- Assert.AreEqual(1, dt.Rows[0]["id"]);
- Assert.AreEqual(2, dt.Rows[1]["id"]);
- Assert.AreEqual("First record", dt.Rows[0]["name"]);
- Assert.AreEqual("Second record", dt.Rows[1]["name"]);
- }
-
- /// <summary>
- /// Bug #16788 Only byte arrays and strings can be serialized by MySqlBinary
- /// </summary>
- [Test]
- [Category("5.0")]
- public void Bug16788()
- {
- execSQL("DROP TABLE IF EXISTS Test");
- execSQL("CREATE TABLE Test (id integer(9), state varchar(2))");
- execSQL("CREATE PROCEDURE spTest(IN p1 integer(9), IN p2 varchar(2)) " +
- "BEGIN " +
- "INSERT INTO test (id, state) VALUES (p1, p2); " +
- "END");
-
- MySqlCommand cmd = conn.CreateCommand();
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "spTest";
- cmd.Parameters.Add("p1", MySqlDbType.UInt16, 9);
- cmd.Parameters["p1"].Value = 44;
- cmd.Parameters.Add("p2", MySqlDbType.VarChar, 2);
- cmd.Parameters["p2"].Value = "ss";
- try
- {
- cmd.ExecuteNonQuery();
- }
- catch(Exception ex)
- {
- Assert.Fail(ex.Message);
- }
- }
-
- [Test]
- [Category("5.0")]
- public void ReturningEmptyResultSet()
- {
- execSQL("DROP PROCEDURE IF EXISTS spTest");
- execSQL("DROP TABLE IF EXISTS test1");
- execSQL("DROP TABLE IF EXISTS test2");
- execSQL("CREATE TABLE test1 (id int AUTO_INCREMENT NOT NULL, " +
- "Name VARCHAR(100) NOT NULL, PRIMARY KEY(id))");
- execSQL("CREATE TABLE test2 (id int AUTO_INCREMENT NOT NULL, " +
- "id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id))");
-
- execSQL("INSERT INTO test1 (Id, Name) VALUES (1, 'Item1')");
- execSQL("INSERT INTO test1 (Id, Name) VALUES (2, 'Item2')");
- execSQL("INSERT INTO test2 (Id, Id1, Id2) VALUES (1, 1, 1)");
- execSQL("INSERT INTO test2 (Id, Id1, Id2) VALUES (2, 2, 1)");
-
- execSQL("CREATE PROCEDURE spTest(Name VARCHAR(100), OUT Table1Id INT) " +
- "BEGIN SELECT t1.Id INTO Table1Id FROM test1 t1 WHERE t1.Name LIKE Name; " +
- "SELECT t3.Id2 FROM test2 t3 WHERE t3.Id1 = Table1Id; END");
-
- MySqlCommand cmd = conn.CreateCommand();
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "spTest";
- cmd.Parameters.Add("Name", "Item3");
- cmd.Parameters.Add("Table1Id", MySqlDbType.Int32);
- cmd.Parameters["Table1Id"].Direction = ParameterDirection.Output;
-
- DataSet ds = new DataSet();
- MySqlDataAdapter da = new MySqlDataAdapter(cmd);
- da.Fill(ds);
- }
-
- [Category("5.0")]
- [Test]
- public void ProcedureCache()
- {
- // 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();
-
- // 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();
- }
- }
-
- // 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);
- }
- }
- }
-}
+// Copyright (C) 2004-2006 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;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+ /// <summary>
+ /// Summary description for StoredProcedure.
+ /// </summary>
+ [TestFixture]
+ public class StoredProcedure : BaseTest
+ {
+ private static string fillError = null;
+
+ [TestFixtureSetUp]
+ public void FixtureSetup()
+ {
+ csAdditions = ";pooling=false;procedure cache size=0";
+ Open();
+ }
+
+ [TestFixtureTearDown]
+ public void TestFixtureTearDown()
+ {
+ Close();
+ }
+
+ protected override void Setup()
+ {
+ base.Setup();
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id INT, name VARCHAR(100))");
+ }
+
+ /// <summary>
+ /// Bug #7623 Adding MySqlParameter causes error if MySqlDbType is Decimal
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void ReturningResultset()
+ {
+ // create our procedure
+ execSQL("CREATE PROCEDURE spTest(val decimal(10,3)) begin select val; end");
+
+ using (MySqlCommand cmd = new MySqlCommand("spTest", conn))
+ {
+ cmd.CommandType = CommandType.StoredProcedure;
+
+ MySqlParameter p = cmd.Parameters.Add("val", MySqlDbType.Decimal);
+ p.Precision = 10;
+ p.Scale = 3;
+ p.Value = 21;
+
+ decimal id = (decimal)cmd.ExecuteScalar();
+ Assert.AreEqual(21, id);
+ }
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void NonQuery()
+ {
+ execSQL("CREATE PROCEDURE spTest(IN value INT) BEGIN INSERT INTO Test VALUES(value, 'Test'); END" );
+
+ //setup testing data
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add( "?value", 2 );
+ int rowsAffected = cmd.ExecuteNonQuery();
+ Assert.AreEqual(1, rowsAffected);
+
+ cmd.CommandText = "SELECT * FROM Test";
+ cmd.CommandType = CommandType.Text;
+ MySqlDataReader reader = null;
+
+ try
+ {
+ reader = cmd.ExecuteReader();
+ Assert.IsTrue( reader.Read() );
+ Assert.AreEqual( 2, reader.GetInt32(0) );
+ Assert.AreEqual( "Test", reader.GetString(1) );
+ Assert.IsFalse( reader.Read() );
+ Assert.IsFalse( reader.NextResult() );
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail( ex.Message );
+ }
+ finally
+ {
+ if (reader != null) reader.Close();
+ }
+ }
+
+ /// <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, 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));
+ 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("test", cmd.Parameters[4].Value);
+ Assert.AreEqual(66, cmd.Parameters[5].Value);
+
+ execSQL("DROP PROCEDURE spCount");
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void NoBatch()
+ {
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("spTest;select * from test", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.ExecuteNonQuery();
+ Assert.Fail("Should have thrown an exception");
+ }
+ catch (Exception)
+ {
+ }
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void WrongParameters()
+ {
+ execSQL("CREATE PROCEDURE spTest(p1 INT) BEGIN SELECT 1; END");
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?p2", 1);
+ cmd.ExecuteNonQuery();
+ Assert.Fail("Should have thrown an exception");
+ }
+ catch (Exception)
+ {
+ }
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void NoInOutMarker()
+ {
+ // create our procedure
+ execSQL( "CREATE PROCEDURE spTest( valin varchar(50) ) BEGIN SELECT valin; END" );
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add( "?valin", "myvalue" );
+ object val = cmd.ExecuteScalar();
+ Assert.AreEqual( "myvalue", val );
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void InputOutputParameters()
+ {
+ // create our procedure
+ execSQL( "CREATE PROCEDURE spTest( INOUT strVal VARCHAR(50), INOUT numVal INT ) " +
+ "BEGIN SET strVal = CONCAT(strVal,'ending'); SET numVal=numVal * 2; END" );
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add( "?strVal", "beginning" );
+ cmd.Parameters.Add( "?numVal", 33 );
+ cmd.Parameters[0].Direction = ParameterDirection.InputOutput;
+ cmd.Parameters[1].Direction = ParameterDirection.InputOutput;
+ int rowsAffected = cmd.ExecuteNonQuery();
+ Assert.AreEqual( 0, rowsAffected );
+ Assert.AreEqual( "beginningending", cmd.Parameters[0].Value );
+ Assert.AreEqual( 66, cmd.Parameters[1].Value );
+
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void NoSPOnPre50()
+ {
+ try
+ {
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.ExecuteNonQuery();
+ Assert.Fail( "This should not have worked" );
+ }
+ catch (Exception)
+ {
+ }
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void ExecuteScalar()
+ {
+ // create our procedure
+ execSQL( "CREATE PROCEDURE spTest( IN valin VARCHAR(50), OUT valout VARCHAR(50) ) " +
+ "BEGIN SET valout=valin; SELECT 'Test'; END" );
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add( "?valin", "valuein" );
+ cmd.Parameters.Add( new MySqlParameter("?valout", MySqlDbType.VarChar));
+ cmd.Parameters[1].Direction = ParameterDirection.Output;
+ object result = cmd.ExecuteScalar();
+ Assert.AreEqual( "Test", result );
+ Assert.AreEqual( "valuein", cmd.Parameters[1].Value );
+ }
+
+ /// <summary>
+ /// Bug #13590 ExecuteScalar returns only Int64 regardless of actual SQL type
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void ExecuteScalar2()
+ {
+ // create our procedure
+ execSQL("CREATE PROCEDURE spTest() " +
+ "BEGIN DECLARE myVar1 INT; SET myVar1 := 1; SELECT myVar1; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ object result = cmd.ExecuteScalar();
+ Assert.AreEqual(1, result);
+ Assert.IsTrue(result is Int32);
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void ExecuteReader()
+ {
+ // create our procedure
+ execSQL( "CREATE PROCEDURE spTest() " +
+ "BEGIN SELECT * FROM mysql.db; END" );
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.Parameters.Add("?a", 3);
+ cmd.CommandType = CommandType.StoredProcedure;
+ MySqlDataReader reader = cmd.ExecuteReader();
+ Assert.AreEqual(true, reader.Read());
+ Assert.AreEqual(false, reader.NextResult());
+ Assert.AreEqual(false, reader.Read());
+ reader.Close();
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void MultipleResultsets()
+ {
+ MultipleResultsetsImpl(false);
+// MultipleResultsetsImpl(true);
+ }
+
+ private void MultipleResultsetsImpl(bool prepare)
+ {
+ // create our procedure
+ execSQL( "CREATE PROCEDURE spTest() " +
+ "BEGIN SELECT 1; SELECT 2; END" );
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ if (prepare) cmd.Prepare();
+ cmd.CommandType = CommandType.StoredProcedure;
+ MySqlDataReader reader = cmd.ExecuteReader();
+ Assert.AreEqual( true, reader.Read() );
+ Assert.AreEqual( true, reader.NextResult() );
+ Assert.AreEqual( true, reader.Read() );
+ Assert.AreEqual( false, reader.NextResult() );
+ Assert.AreEqual( false, reader.Read() );
+ reader.Close();
+
+ DataSet ds = new DataSet();
+ MySqlCommand cmd2 = new MySqlCommand("spTest", conn);
+ cmd2.CommandType = CommandType.StoredProcedure;
+ MySqlDataAdapter da = new MySqlDataAdapter( cmd2 );
+ da.FillError += new FillErrorEventHandler(da_FillError);
+ fillError = null;
+ da.Fill(ds);
+ Assert.AreEqual( 2, ds.Tables.Count );
+ Assert.AreEqual( 1, ds.Tables[0].Rows.Count );
+ Assert.AreEqual( 1, ds.Tables[1].Rows.Count );
+ Assert.AreEqual( 1, ds.Tables[0].Rows[0][0] );
+ Assert.AreEqual( 2, ds.Tables[1].Rows[0][0] );
+ Assert.IsNull( fillError );
+ }
+
+ private void da_FillError(object sender, FillErrorEventArgs e)
+ {
+ fillError = e.Errors.Message;
+ e.Continue = true;
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void FunctionNoParams()
+ {
+ execSQL( "CREATE FUNCTION fnTest() RETURNS CHAR(50)" +
+ "BEGIN RETURN \"Test\"; END" );
+
+ MySqlCommand cmd = new MySqlCommand("SELECT fnTest()", conn);
+ cmd.CommandType = CommandType.Text;
+ object result = cmd.ExecuteScalar();
+ Assert.AreEqual( "Test", result );
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void FunctionParams()
+ {
+ execSQL( "CREATE FUNCTION fnTest( val1 INT, val2 CHAR(40) ) RETURNS INT " +
+ "BEGIN RETURN val1 + LENGTH(val2); END" );
+
+ MySqlCommand cmd = new MySqlCommand("SELECT fnTest(22, 'Test')", conn);
+ cmd.CommandType = CommandType.Text;
+ object result = cmd.ExecuteScalar();
+ Assert.AreEqual( 26, result);
+ }
+
+ [Test()]
+ [Category("5.0")]
+ public void UseOldSyntax()
+ {
+ // create our procedure
+ execSQL("CREATE PROCEDURE spTest( IN valin VARCHAR(50), OUT valout VARCHAR(50) ) " +
+ "BEGIN SET valout=valin; SELECT 'Test'; END");
+
+ MySqlConnection c2 = new MySqlConnection(conn.ConnectionString + ";old syntax=yes");
+ c2.Open();
+
+ MySqlCommand cmd = new MySqlCommand("spTest", c2);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("@valin", "value");
+ cmd.Parameters.Add( new MySqlParameter("@valout", MySqlDbType.VarChar));
+ cmd.Parameters[1].Direction = ParameterDirection.Output;
+ object result = cmd.ExecuteScalar();
+ Assert.AreEqual("Test", result);
+ Assert.AreEqual("value", cmd.Parameters[1].Value);
+ c2.Close();
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void ExecuteWithCreate()
+ {
+ // create our procedure
+ string sql = "CREATE PROCEDURE spTest(IN var INT) BEGIN SELECT var; END; call spTest(?v)";
+
+ MySqlCommand cmd = new MySqlCommand(sql, conn);
+ cmd.Parameters.Add(new MySqlParameter("?v", 33));
+ object val = cmd.ExecuteScalar();
+ Assert.AreEqual( 33, val );
+ }
+
+ /// <summary>
+ /// Bug #9722 Connector does not recognize parameters separated by a linefeed
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void OtherProcSigs()
+ {
+ // create our procedure
+ execSQL("CREATE PROCEDURE spTest(IN \r\nvalin DECIMAL(10,2),\nIN val2 INT) " +
+ "SQL SECURITY INVOKER BEGIN SELECT valin; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?valin", 20.4);
+ cmd.Parameters.Add("?val2", 4);
+ decimal val = (decimal)cmd.ExecuteScalar();
+ Decimal d = new Decimal(20.4);
+ Assert.AreEqual(d, val);
+
+ // create our second procedure
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ execSQL("CREATE PROCEDURE spTest( \r\n) BEGIN SELECT 4; END" );
+ cmd.Parameters.Clear();
+ object val1 = cmd.ExecuteScalar();
+ Assert.AreEqual(4, val1);
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ }
+
+
+ /// <summary>
+ /// Bug #10644 Cannot call a stored function directly from Connector/Net
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void CallingStoredFunctionasProcedure()
+ {
+ execSQL("CREATE FUNCTION fnTest(valin int) RETURNS INT BEGIN return valin * 2; END");
+ MySqlCommand cmd = new MySqlCommand("fnTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?valin", 22);
+ cmd.Parameters.Add("retval", MySqlDbType.Int32);
+ cmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
+ cmd.ExecuteNonQuery();
+ Assert.AreEqual(44, cmd.Parameters[1].Value);
+ }
+
+ /// <summary>
+ /// Bug #11450 Connector/Net, current database and stored procedures
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void NoDefaultDatabase()
+ {
+ // create our procedure
+ execSQL("CREATE PROCEDURE spTest() BEGIN SELECT 4; END" );
+
+ string newConnStr = GetConnectionString(false);
+ MySqlConnection c = new MySqlConnection(newConnStr);
+ try
+ {
+ c.Open();
+ MySqlCommand cmd2 = new MySqlCommand("use test", c);
+ cmd2.ExecuteNonQuery();
+
+ MySqlCommand cmd = new MySqlCommand("spTest", c);
+ cmd.CommandType = CommandType.StoredProcedure;
+ object val = cmd.ExecuteScalar();
+ Assert.AreEqual(4, val);
+
+ cmd2.CommandText = "use mysql";
+ cmd2.ExecuteNonQuery();
+
+ cmd.CommandText = "test.spTest";
+ val = cmd.ExecuteScalar();
+ Assert.AreEqual(4, val);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ c.Close();
+ }
+ }
+
+ /// <summary>
+ /// Bug #13590 ExecuteScalar returns only Int64 regardless of actual SQL type
+ /// </summary>
+ [Category("NotWorking")]
+ [Test]
+ public void TestSelectingInts()
+ {
+ execSQL("CREATE PROCEDURE spTest() BEGIN DECLARE myVar INT; " +
+ "SET MyVar := 1; SELECT CAST(myVar as INT); END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ object val = cmd.ExecuteScalar();
+ Assert.AreEqual(1, val, "Checking value");
+ Assert.IsTrue(val is System.Int32, "Checking type");
+ }
+
+ /// <summary>
+ /// Bug #13632 the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5
+ /// Bug #15077 Error MySqlCommandBuilder.DeriveParameters for sp without parameters.
+ /// Bug #19515 DiscoverParameters fails on numeric datatype
+ /// </summary>
+ [Category("5.0")]
+ [Test]
+ public void DeriveParameters()
+ {
+ execSQL("DROP TABLE IF EXISTS test2");
+ execSQL("CREATE TABLE test2 (c CHAR(20))");
+ execSQL("INSERT INTO test2 values ( 'xxxx')");
+ MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM test2", conn);
+ MySqlDataReader reader =cmd2.ExecuteReader();
+ reader.Close();
+
+ 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'), val8 CHAR, val9 NUMERIC(10,2)) " +
+ "BEGIN SELECT 1; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ MySqlDataAdapter da = new MySqlDataAdapter(cmd);
+ MySqlCommandBuilder.DeriveParameters(cmd);
+
+ Assert.AreEqual(9, 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);
+
+ Assert.AreEqual("val8", cmd.Parameters[7].ParameterName);
+ Assert.AreEqual(ParameterDirection.Input, cmd.Parameters[7].Direction);
+ Assert.AreEqual(MySqlDbType.String, cmd.Parameters[7].MySqlDbType);
+
+ Assert.AreEqual("val9", cmd.Parameters[8].ParameterName);
+ 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();
+ da = new MySqlDataAdapter(cmd);
+ MySqlCommandBuilder.DeriveParameters(cmd);
+ Assert.AreEqual(0, cmd.Parameters.Count);
+ }
+
+ /// <summary>
+ /// Bug #13632 the MySQLCommandBuilder.deriveparameters has not been updated for MySQL 5
+ /// </summary>
+ [Category("5.0")]
+ [Test]
+ public void DeriveParametersForFunction()
+ {
+ try
+ {
+ execSQL("CREATE FUNCTION fnTest(v1 DATETIME) RETURNS INT " +
+ " BEGIN RETURN 1; END");
+
+ MySqlCommand cmd = new MySqlCommand("fnTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ MySqlDataAdapter da = new MySqlDataAdapter(cmd);
+ MySqlCommandBuilder.DeriveParameters(cmd);
+
+ 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);
+ }
+ }
+
+ /// <summary>
+ /// Bug #11386 Numeric parameters with Precision and Scale not taken into account by Connector
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void DecimalAsParameter()
+ {
+ execSQL("CREATE PROCEDURE spTest(IN d DECIMAL(19,4)) BEGIN SELECT d; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?d", 21);
+ decimal d = (decimal)cmd.ExecuteScalar();
+ Assert.AreEqual(21, d);
+ }
+
+ /// <summary>
+ /// Bug #6902 Errors in parsing stored procedure parameters
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void ParmWithCharacterSet()
+ {
+ execSQL("CREATE PROCEDURE spTest(P longtext character set utf8) " +
+ "BEGIN SELECT P; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?P", "This is my value");
+ string p = (string)cmd.ExecuteScalar();
+ Assert.AreEqual("This is my value", p);
+ }
+
+ /// <summary>
+ /// Bug #13753 Exception calling stored procedure with special characters in parameters
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void SpecialCharacters()
+ {
+ execSQL("SET sql_mode=ANSI_QUOTES");
+ try
+ {
+ execSQL("CREATE PROCEDURE spTest(\"@Param1\" text) BEGIN SELECT \"@Param1\"; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.Parameters.Add("@Param1", "This is my value");
+ cmd.CommandType = CommandType.StoredProcedure;
+
+ string val = (string)cmd.ExecuteScalar();
+ Assert.AreEqual("This is my value", val);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ finally
+ {
+ execSQL("SET sql_mode=\"\"");
+ }
+ }
+
+ [Test]
+ [Category("NotWorking")]
+ public void CallingSPWithPrepare()
+ {
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ execSQL("CREATE PROCEDURE spTest(P int) BEGIN SELECT P; END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.Add("?P", 33);
+ cmd.Prepare();
+
+ int p = (int)cmd.ExecuteScalar();
+ Assert.AreEqual(33, p);
+ }
+
+ /// <summary>
+ /// Bug #13927 Multiple Records to same Table in Transaction Problem
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void MultipleRecords()
+ {
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ execSQL("CREATE PROCEDURE spTest(id int, str VARCHAR(45)) " +
+ "BEGIN INSERT INTO test VALUES(id, str); END");
+
+ MySqlCommand cmd = new MySqlCommand("spTest", conn);
+ cmd.CommandType = CommandType.StoredProcedure;
+
+ cmd.Parameters.Add("?id", 1);
+ cmd.Parameters.Add("?str", "First record");
+ cmd.ExecuteNonQuery();
+
+ cmd.Parameters.Add("?id", 2);
+ cmd.Parameters.Add("?str", "Second record");
+ cmd.ExecuteNonQuery();
+
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+
+ Assert.AreEqual(1, dt.Rows[0]["id"]);
+ Assert.AreEqual(2, dt.Rows[1]["id"]);
+ Assert.AreEqual("First record", dt.Rows[0]["name"]);
+ Assert.AreEqual("Second record", dt.Rows[1]["name"]);
+ }
+
+ /// <summary>
+ /// Bug #16788 Only byte arrays and strings can be serialized by MySqlBinary
+ /// </summary>
+ [Test]
+ [Category("5.0")]
+ public void Bug16788()
+ {
+ execSQL("DROP TABLE IF EXISTS Test");
+ execSQL("CREATE TABLE Test (id integer(9), state varchar(2))");
+ execSQL("CREATE PROCEDURE spTest(IN p1 integer(9), IN p2 varchar(2)) " +
+ "BEGIN " +
+ "INSERT INTO test (id, state) VALUES (p1, p2); " +
+ "END");
+
+ MySqlCommand cmd = conn.CreateCommand();
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.CommandText = "spTest";
+ cmd.Parameters.Add("p1", MySqlDbType.UInt16, 9);
+ cmd.Parameters["p1"].Value = 44;
+ cmd.Parameters.Add("p2", MySqlDbType.VarChar, 2);
+ cmd.Parameters["p2"].Value = "ss";
+ try
+ {
+ cmd.ExecuteNonQuery();
+ }
+ catch(Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+ }
+
+ [Test]
+ [Category("5.0")]
+ public void ReturningEmptyResultSet()
+ {
+ execSQL("DROP PROCEDURE IF EXISTS spTest");
+ execSQL("DROP TABLE IF EXISTS test1");
+ execSQL("DROP TABLE IF EXISTS test2");
+ execSQL("CREATE TABLE test1 (id int AUTO_INCREMENT NOT NULL, " +
+ "Name VARCHAR(100) NOT NULL, PRIMARY KEY(id))");
+ execSQL("CREATE TABLE test2 (id int AUTO_INCREMENT NOT NULL, " +
+ "id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id))");
+
+ execSQL("INSERT INTO test1 (Id, Name) VALUES (1, 'Item1')");
+ execSQL("INSERT INTO test1 (Id, Name) VALUES (2, 'Item2')");
+ execSQL("INSERT INTO test2 (Id, Id1, Id2) VALUES (1, 1, 1)");
+ execSQL("INSERT INTO test2 (Id, Id1, Id2) VALUES (2, 2, 1)");
+
+ execSQL("CREATE PROCEDURE spTest(Name VARCHAR(100), OUT Table1Id INT) " +
+ "BEGIN SELECT t1.Id INTO Table1Id FROM test1 t1 WHERE t1.Name LIKE Name; " +
+ "SELECT t3.Id2 FROM test2 t3 WHERE t3.Id1 = Table1Id; END");
+
+ MySqlCommand cmd = conn.CreateCommand();
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.CommandText = "spTest";
+ cmd.Parameters.Add("Name", "Item3");
+ cmd.Parameters.Add("Table1Id", MySqlDbType.Int32);
+ cmd.Parameters["Table1Id"].Direction = ParameterDirection.Output;
+
+ DataSet ds = new DataSet();
+ MySqlDataAdapter da = new MySqlDataAdapter(cmd);
+ da.Fill(ds);
+ }
+
+ [Category("5.0")]
+ [Test]
+ public void ProcedureCache()
+ {
+ // 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();
+
+ // 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();
+ }
+ }
+
+ // 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);
+ }
+ }
+ }
+}
Modified: trunk/TestSuite/StressTests.cs
===================================================================
--- trunk/TestSuite/StressTests.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/StressTests.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -22,6 +22,7 @@
using MySql.Data.MySqlClient;
using System.Data;
using NUnit.Framework;
+using System.Diagnostics;
namespace MySql.Data.MySqlClient.Tests
{
@@ -56,25 +57,24 @@
execSQL("set @@global.max_allowed_packet=35000000");
- MySqlConnection c = new MySqlConnection( conn.ConnectionString + ";pooling=false" );
+ MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";pooling=false");
c.Open();
- byte[] dataIn = Utils.CreateBlob( len );
- byte[] dataIn2 = Utils.CreateBlob( len );
+ byte[] dataIn = Utils.CreateBlob(len);
+ byte[] dataIn2 = Utils.CreateBlob(len);
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, NULL, ?blob, NULL )", c);
- cmd.Parameters.Add( new MySqlParameter("?id", 1));
- cmd.Parameters.Add( new MySqlParameter("?blob", dataIn));
+ cmd.Parameters.Add(new MySqlParameter("?id", 1));
+ cmd.Parameters.Add(new MySqlParameter("?blob", dataIn));
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
-
cmd.Parameters[0].Value = 2;
cmd.Parameters[1].Value = dataIn2;
cmd.ExecuteNonQuery();
@@ -88,22 +88,22 @@
reader = cmd.ExecuteReader();
reader.Read();
byte[] dataOut = new byte[ len ];
- long count = reader.GetBytes( 2, 0, dataOut, 0, len );
- Assert.AreEqual( len, count );
+ long count = reader.GetBytes(2, 0, dataOut, 0, len);
+ Assert.AreEqual(len, count);
for (int i=0; i < len; i++)
- Assert.AreEqual( dataIn[i], dataOut[i] );
+ Assert.AreEqual(dataIn[i], dataOut[i]);
reader.Read();
- count = reader.GetBytes( 2, 0, dataOut, 0, len );
- Assert.AreEqual( len, count );
+ count = reader.GetBytes(2, 0, dataOut, 0, len);
+ Assert.AreEqual(len, count);
for (int i=0; i < len; i++)
- Assert.AreEqual( dataIn2[i], dataOut[i] );
+ Assert.AreEqual(dataIn2[i], dataOut[i]);
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
finally
{
@@ -121,6 +121,7 @@
for (int i=1; i <= 8000; i++)
{
+ Trace.WriteLine("iteration " + i);
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
@@ -133,18 +134,18 @@
reader = cmd.ExecuteReader();
while (reader.Read())
{
- Assert.AreEqual( i2+1, reader.GetInt32(0), "Sequence out of order" );
+ Assert.AreEqual(i2+1, reader.GetInt32(0), "Sequence out of order");
i2++;
}
reader.Close();
- Assert.AreEqual( 8000, i2 );
+ Assert.AreEqual(8000, i2);
cmd = new MySqlCommand("delete from Test where id >= 100", conn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
finally
{
Modified: trunk/TestSuite/Syntax.cs
===================================================================
--- trunk/TestSuite/Syntax.cs 2006-07-30 03:34:15 UTC (rev 280)
+++ trunk/TestSuite/Syntax.cs 2006-07-30 03:45:48 UTC (rev 281)
@@ -56,8 +56,8 @@
DataTable dt = new DataTable();
da.Fill(dt);
- Assert.AreEqual( 1, dt.Rows.Count );
- Assert.AreEqual( 2, dt.Columns.Count );
+ Assert.AreEqual(1, dt.Rows.Count);
+ Assert.AreEqual(2, dt.Columns.Count);
}
[Test]
@@ -68,9 +68,9 @@
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), mt MEDIUMTEXT, PRIMARY KEY(id)) CHAR SET utf8");
MySqlCommand cmd = new MySqlCommand( "INSERT INTO Test VALUES (?id, ?text, ?mt)", conn);
- cmd.Parameters.Add( "?id", 1 );
- cmd.Parameters.Add( "?text", "This is my;test ? string- cmd.Parameters.Add( "?mt", "My MT string: + cmd.Parameters.Add("?id", 1);
+ cmd.Parameters.Add("?text", "This is my;test ? string+ cmd.Parameters.Add("?mt", "My MT string: cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
@@ -78,17 +78,17 @@
try
{
reader = cmd.ExecuteReader();
- Assert.IsTrue( reader.Read() );
- Assert.AreEqual( 1, reader.GetInt32(0));
+ Assert.IsTrue(reader.Read());
+ Assert.AreEqual(1, reader.GetInt32(0));
if (Is40)
- Assert.AreEqual( "This is my;test ? string-'''\"\".", reader.GetString(1));
+ Assert.AreEqual("This is my;test ? string-'''\"\".", reader.GetString(1));
else
- Assert.AreEqual( "This is my;test ? string- Assert.AreEqual( "My MT string: + Assert.AreEqual("This is my;test ? string+ Assert.AreEqual("My MT string: }
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
finally
{
@@ -102,11 +102,11 @@
execSQL("set @@global.max_allowed_packet=250000000");
string connString = conn.ConnectionString + ";pooling=false";
- MySqlConnection c = new MySqlConnection( connString );
+ MySqlConnection c = new MySqlConnection(connString);
c.Open();
string path = Path.GetTempFileName();
- StreamWriter sw = new StreamWriter( path );
+ StreamWriter sw = new StreamWriter(path);
for (int i=0; i < 2000000; i++)
sw.WriteLine(i + ",'Test'");
sw.Flush();
@@ -122,13 +122,13 @@
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
- Assert.AreEqual( 2000000, cnt );
+ Assert.AreEqual(2000000, cnt);
cmd.CommandText = "SELECT COUNT(*) FROM Test";
cnt = cmd.ExecuteScalar();
- Assert.AreEqual( 2000000, cnt );
+ Assert.AreEqual(2000000, cnt);
c.Close();
execSQL("set @@global.max_allowed_packet=1047256");
@@ -146,11 +146,11 @@
}
catch (MySqlException)
{
- Assert.AreEqual( ConnectionState.Open, conn.State );
+ Assert.AreEqual(ConnectionState.Open, conn.State);
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
finally
{
@@ -172,7 +172,7 @@
"`LastVisit` timestamp(14) NOT NULL, `Categorie` int(11) NOT NULL default '0', " +
"PRIMARY KEY (`KlantNummer`), UNIQUE KEY `UniqueUsername` (`Username`), " +
"UNIQUE KEY `UniqueDefaultMail` (`DefaultMail`) )";
- createTable( sql, "MyISAM" );
+ createTable(sql, "MyISAM");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM KLANT", conn);
MySqlDataReader reader = null;
@@ -183,7 +183,7 @@
}
catch (Exception ex)
{
- Assert.Fail( ex.Message );
+ Assert.Fail(ex.Message);
}
finally
{
| Thread |
|---|
| • Connector/NET commit: r281 - trunk/TestSuite | rburnett | 30 Jul |
