"Convert Zero Datetime=true;" in connection string?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
KendallB
Posts: 12
Joined: Fri 24 Jun 2011 01:14

"Convert Zero Datetime=true;" in connection string?

Post by KendallB » Sat 25 Jun 2011 01:52

Hi Guys,

I am testing out dotConnect for MySQL over the MySQL provided connector, but I noticed that I cannot use "Convert Zero Datetime=true;" value in the connection string. Since we have date's stored in our database as all 0's, we need this value with regular MySQL to convert those to DateTime.MinValue when we read them in.

Does dotConnect for MySQL already do the same thing, or is it going to throw an expection if we try to load something from the database where the DATETIME column is set to the special MYSQL value of all 0's?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 27 Jun 2011 17:10

If your DateTime column has 'null', reader.GetDateTime(i) returns DateTime.MinValue. You can call reader.IsDBNull(i) to check for null values before calling reader.GetDateTime(i).

KendallB
Posts: 12
Joined: Fri 24 Jun 2011 01:14

Post by KendallB » Mon 27 Jun 2011 18:53

That is not what I am talking about. I am talking about the "Convert Zero Datetime=true;" connection string parameter to the MySQL Connector/Net connector. You can read about it on this page:

http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html

MySQL allows a special value of 0 in DATETIME columns in the database, which is NOT a null column. It is a value that can be put into a DATETIME column when the column is a non-nullable column, to represent the smallest possible date. However that cannot be converted to a legal System.DateTime class in C#, since the value cannot be represented. So if you don't use this option, and you try to load a row of data from the database that has this special value, you will get an exception "Unable to convert MySQL date/time value to System.DateTime". This option tells Connector/Net to convert illegal DATETIME column values (basically anything prior to DateTime.MinValue) into DateTime.MinValue. This allows us to work around the issue with the legacy data in our systems.

What would happen in dotConnect for MySQL if I try to read a DATETIME column that is the special 0 value? Will I get an exception, or will it convert it to DateTime.MinValue?

Also, if my column is a NULLABLE DATETIME column, I would expect dotConnect to convert it to a null value when it is ready also.

Note that I have been testing dotConnect using Entity Framework, and due to other issues have not been able to actually get it to work with our database (next build in a few weeks should let me test it further).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 28 Jun 2011 12:12

KendallB wrote:What would happen in dotConnect for MySQL if I try to read a DATETIME column that is the special 0 value? Will I get an exception, or will it convert it to DateTime.MinValue?
If your DateTime column has the special 0 value, reader.GetDateTime(i) returns DateTime.MinValue.
KendallB wrote:Also, if my column is a NULLABLE DATETIME column, I would expect dotConnect to convert it to a null value when it is ready also.
Could you please explain what you mean?
If you have the NULL value in your 'DATETIME DEFAULT NULL' column, reader.GetDateTime(i) cannot return a null value because it returns not nullable System.DateTime.

KendallB
Posts: 12
Joined: Fri 24 Jun 2011 01:14

Post by KendallB » Tue 28 Jun 2011 15:42

Excellent to hear it wil convert zero values to DateTime.MinValue.

As for the null columns I am talking about from the Entiry Framework and I assume that will work as expected and return a null value if the column is a nullable DateTime column (and the Entity Type is a nullablr System.DateTime).

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 29 Jun 2011 12:45

Yes. the null values in database will be returned as null values for nullable DateTime properties.

Post Reply