Bug #5407 MySQL Connector -- Not ADO compatible
Submitted: 4 Sep 2004 8:18 Modified: 10 Dec 2004 20:20
Reporter: Timothy Graupmann Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:Beta 1.0.0 OS:Windows (Win2k)
Assigned to: Reggie Burnett CPU Architecture:Any

[4 Sep 2004 8:18] Timothy Graupmann
Description:
Upgrading the connector from ByteFX has left some serious gaps in functionality.

To give an example, let's look at this block of code:
			DateTime getTime = new DateTime();
			IDbConnection myConnection = DBFactory.CreateConnection();
			myConnection.Open();
			try
			{
				IDbCommand myCommand = myConnection.CreateCommand();
				myCommand.CommandText = "SELECT sysdate()";
				IDataReader reader = myCommand.ExecuteReader();				
				if(reader.Read())
					getTime = (DateTime)reader["sysdate()"];
				reader.Close();
			}
			catch(Exception e)
			{
				myConnection.Close();
				throw e;
			}
			myConnection.Close();
			return getTime;

ByteFX used to be ADO compatible:
getTime = (DateTime)reader["sysdate()"];

But now, this call fails and has to be accessed this way:
getTime = (DateTime)reader.GetDateTime(0);

How to repeat:
See description

Suggested fix:
n/a
[4 Sep 2004 10:14] Timothy Graupmann
Another odd workaround. This doesn't work:

				IDbCommand myCommand = myConnection.CreateCommand();			
				myCommand.CommandText = "SELECT ImageData FROM tagml_material WHERE MaterialId=@id";
				IDbDataParameter id = myCommand.CreateParameter();
				id.ParameterName = "@id";
				id.Value = 1;
				myCommand.Parameters.Add(id);

But this does:

				IDbCommand myCommand = myConnection.CreateCommand();			
				myCommand.CommandText = "SELECT ImageData FROM tagml_material WHERE MaterialId=?id";
				IDbDataParameter id = myCommand.CreateParameter();
				id.ParameterName = "@id";
[8 Sep 2004 18:50] Reggie Burnett
Timothy

This is a difficult issue because MySql supports a datetime value of '00/00/00'. This value cannot be held in a DateTime struct.  However, i want reader["col1"].ToString() to print '00/00/00' if col1 contains a zero datetime.  To do that, reader["col"] needs to return my type MySqlDateTime.  However, because the method sig returns it as object, there is no way to use operators to allow this  DateTime dt = (DateTime)reader["col1"].

There are three options (that I know of right now).
1.  Tell everyone who uses Connector/net that zero datetime values are not supported and a have MySqlDataReader.this[] always return DateTime for date time columns.

2.  Have MySqlDataReader.this[] return a DateTime for non zero datetime columns and throw an exception/return DateTime.MinValue for zero columns. 

3.  Have MySqlDataReader.this[] return MySqlDateTime for date time columns and you have to cast it like this to get a DateTime.  
DateTime dt = (DateTime)(MySqlDateTime)reader["col1"];

Any thoughts on which would be preferrable?

-reggie
[13 Sep 2004 13:26] Puiu Hrenciuc
Hi,

Regarding the three available options the 2nd would be better :

2.  Have MySqlDataReader.this[] return a DateTime for non zero datetime columns
and throw an exception/return DateTime.MinValue for zero columns. 

But I don't know if throwing an exception is necessary since having a default value
is not something wrong. Should Connector throw an exception when an empty
string ( the default ) is returned when reading a varchar column ? Guess not.
So if the date returned is the MinValue it should be ok .

Rgds,
Puiu.
[29 Sep 2004 11:34] Liam Westley
I hit this problem too, but it's easy to solve, and works for the .NET connectors for OleDb (Access MDB), SQL 2000, MySQL.

   DateTime addedOn = System.Convert.ToDateTime(dr["AddedOn"]);

Similarly, use this for boolean conversion for all the above databases, this handles mySQL not having a native BIT field and still works fine,

   bool isEnabled = System.Convert.ToBoolean(dr["IsEnabled"]);

I've found direct casting can be very tricky, whereas the System.Convert namespace handles a lot more cases.

Regards

Liam Westley
[30 Sep 2004 4:31] Timothy Graupmann
I think #2 is the best option. It is the closest to ADO behavior.

AND) If the DB DateTime field is null, throw a DBNULL exception.
[25 Nov 2004 9:44] Sergey Koshcheyev
Hi,

Convert.ToDateTime doesn't work for me. I'm using NHibernate and it internally uses Convert.ToDateTime for a MySqlDateTime value. This fails however:

public override object Get(IDataReader rs, int index) 
{
	return Convert.ToDateTime(rs[index]);
}

This function throws an InvalidCastException when rs[index] is a MySqlDateTime. I don't know whether this problem is related to Connector.Net or to NHibernate, but I think the connector is to blame, since everything worked flawlessly in the previous version.

Sergey.
[25 Nov 2004 17:50] Sergey Koshcheyev
Hmm, I've tried a small test on SqlDateTime and it also isn't convertible to DateTime using System.Convert. Am I missing something? Also MSDN says in the remarks for IDataReader that "Providers implementing a DataReader are required to expose data in common language runtime (CLR) types." So I believe just returning a MySqlDateTime from IDataReader.Get(int) is wrong...
[10 Dec 2004 20:20] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I believe the new functionality will take care of this problem.  Basically, by default it will return DateTime objects from the reader and throw a conversion exception when it sees a zero datetime.

However, if you are using a database that has zero datetimes and require them, then you can add "allow zero datetime=yes" to your connection string and it will then return MySqlDateTime objects.
[18 Dec 2004 20:03] Timothy Graupmann
I just got the Beta 1.0.3 update.

I'm getting a datetime conversion error with Win2k.

Here is sample code to get the sysdate() from the mysql database. And it's having the conversion error. Which worked in the previous 1.0.0 beta version.

		static public DateTime GetDatabaseTime() 
		{
			DateTime getTime = new DateTime();
			IDbConnection myConnection = DBFactory.CreateConnection();
			myConnection.Open();
			try
			{
				IDbCommand myCommand = myConnection.CreateCommand();
				myCommand.CommandText = "SELECT sysdate()";
				IDataReader reader = myCommand.ExecuteReader();				
				if(reader.Read())
				{
					//getTime = reader.GetDateTime( reader.GetOrdinal("sysdate()") );
					getTime = (DateTime)reader["sysdate()"];
				}
				reader.Close();
			}
			catch(Exception e)
			{
				myConnection.Close();
				throw e;
			}
			myConnection.Close();
			return getTime;
		}
[18 Dec 2004 20:05] Timothy Graupmann
BTW, the conversion error is DateTime can't be cast to Double.

Here is the factory code:

	public class DBFactory 
	{
		public static IDbConnection CreateConnection()
		{
			String myConnectionString =
				ConfigurationSettings.AppSettings["sqlConnectionString"];
			try
			{
				return new MySqlConnection( myConnectionString );
			}
			catch
			{
				throw new Exception(ErrorCode.FAILED_DB_CONNECTION);
			}
		} 

		public static DbDataAdapter CreateDataAdapter()
		{
			return new MySqlDataAdapter();
		}

	}
[18 Dec 2004 20:13] Timothy Graupmann
My bad. The above code is working.

The conversion error occurs if you attempt to set a dateTime object as a parameter. "?dt" is the source of the problem. As follows:

			IDbConnection myConnection = DBFactory.CreateConnection();
			myConnection.Open();
			try
			{
				IDbCommand myCommand = myConnection.CreateCommand();
				myCommand.CommandText = "SELECT UserId,ScreenName,LastModified FROM tagml_user WHERE LastModified > ?dt ORDER BY ScreenName";
				IDbDataParameter p = myCommand.CreateParameter();
				p.ParameterName = "?dt";
				p.Value = sinceDateTime;
				myCommand.Parameters.Add(p);
				IDataReader reader = myCommand.ExecuteReader();				
				while(reader.Read())
				{
					User getUser = new User();
					getUser.UserId = (UInt32)reader["UserId"];
					getUser.ScreenName = (String)reader["ScreenName"];
					getUser.LastModified = (DateTime)reader["LastModified"];
					userList.Add(getUser);
				}
				reader.Close();
			}
			catch(Exception e)
			{
				myConnection.Close();
				throw e;
			}

An exception is thrown at "ExecuteReader".

Which reads:

{"Invalid cast from DateTime to Decimal." }
[18 Dec 2004 20:15] Timothy Graupmann
Ahh. The solution is to set the DbType explicitly. The exception is no longer thrown.

			ArrayList userList = new ArrayList();

			IDbConnection myConnection = DBFactory.CreateConnection();
			myConnection.Open();
			try
			{
				IDbCommand myCommand = myConnection.CreateCommand();
				myCommand.CommandText = "SELECT UserId,ScreenName,LastModified FROM tagml_user WHERE LastModified > ?dt ORDER BY ScreenName";
				IDbDataParameter p = myCommand.CreateParameter();
				p.ParameterName = "?dt";
				p.Value = sinceDateTime;
				p.DbType = DbType.DateTime;
				myCommand.Parameters.Add(p);
				IDataReader reader = myCommand.ExecuteReader();				
				while(reader.Read())
				{
					User getUser = new User();
					getUser.UserId = (UInt32)reader["UserId"];
					getUser.ScreenName = (String)reader["ScreenName"];
					getUser.LastModified = (DateTime)reader["LastModified"];
					userList.Add(getUser);
				}
				reader.Close();
			}
			catch(Exception e)
			{
				myConnection.Close();
				throw e;
			}
			myConnection.Close();
[18 Dec 2004 20:31] Timothy Graupmann
There is a bug here.

You now have to explicitly set DbType for all parameters.
[10 Jan 2005 3:44] Timothy Graupmann
I ended up using this hack, since the DbType is not set implicitly.

		#region MySQL DbType Connection Hack for Reggie ;P
		
		private static void AddDbTypeToParameter(IDbCommand myCommand, IDbDataParameter p)
		{
			if(p.Value is String)
				p.DbType = DbType.String;
			else if(p.Value is DateTime)
				p.DbType = DbType.DateTime;
			else if(p.Value is UInt32)
				p.DbType = DbType.UInt32;
			else if(p.Value is Int16)
				p.DbType = DbType.Int16;
			else if(p.Value is Double)
				p.DbType = DbType.Double;
			else if(p.Value is Byte[])
				p.DbType = DbType.Byte;
			myCommand.Parameters.Add(p);
		}

		#endregion

Now instead of calling:
myCommand.Parameters.Add(p);

I call:
AddDbTypeToParameter(myCommand, p);

Is there a faster way?