[Mono-list] Re: MySQLDataReader.Read Exception

[Jeremy N. Morgan]

According to the MySQL docs, the supported DateTime range is '1000-01-01
00:00:00' to '9999-12-31 23:59:59', although the supported TimeStamp
range is 1970 to 2037.

There's also the date and year types which can produce an illegal value
with empty strings, so maybe (please check my code):

	// new code BEGIN
	case MySqlEnumFieldTypes.FIELD_TYPE_DATETIME:
		if(myValue.Equals("0000-00-00 00:00:00"))
			return DBNull.Value;
		break;
	case MySqlEnumFieldTypes.FIELD_TYPE_DATE:
		if(myValue.Equals("0000-00-00"))
			return DBNull.Value;
		break;
	case MySqlEnumFieldTypes.FIELD_TYPE_YEAR:
		if(myValue.Equals("0000"))
			return DBNull.Value;
		break;
	// new code END

That should match the illegal thrown values that are possible with
dates.

BTW, the if() statement is not SQL 92 compliant, but the Case statement
is.  If() is just less clumsy...

Thanks!

Jeremy


-----Original Message-----
From: Daniel Morgan [mailto:danmorg@sc.rr.com] 
Sent: Thursday, January 09, 2003 3:28 PM
To: Vincent Daron; Jeremy N. Morgan
Cc: mono-list@ximian.com
Subject: RE: [Mono-list] Re: MySQLDataReader.Read Exception


In MySqlTypes.cs, in
object ConvertDbTypeToSystem (MySqlEnumFieldTypes mysqlFieldType,
						DbType typ, String
myValue)

I can have a special case, for when the date is '0000-00-00 00:00:00', i
can set the DateTime to DBNull.Value.

switch(mysqlFieldType) {
	case MySqlEnumFieldTypes.FIELD_TYPE_TIMESTAMP:
		if(myValue.Equals("00000000000000"))
			return DBNull.Value;
		break;
	// new code BEGIN
	case MySqlEnumFieldTypes.FIELD_TYPE_DATETIME:
		if(myValue.Equals("0000-00-00 00:00:00"))
			return DBNull.Value;
		break;
	// new code END
}

I think the minimum DateTime that can be used is '0001-01-01 00:00:00'.

-----Original Message-----
From: mono-list-admin@ximian.com [mailto:mono-list-admin@ximian.com]On
Behalf Of Vincent Daron
Sent: Thursday, January 09, 2003 4:02 PM
To: Jeremy N. Morgan
Cc: mono-list@ximian.com
Subject: RE: [Mono-list] Re: MySQLDataReader.Read Exception


Tanks,

The response is simple: do not use date = 0000-00-00 00:00:00 with
MySQL, use 1900-01-01 instead. ;-)

But the problem is the exception in the DataReader.

> Select if(date1='', '1900-01-01', date1) as date1 from table1;

Is this a standard SQL 92 Select ?

Thanks a lot

Vincent

On Thu, 2003-01-09 at 19:28, Jeremy N. Morgan wrote:
> As far as I can tell, the problem appears to be that MySQL doesn't use

> an actual date to display an empty string.  MySQL uses 0000-00-00, 
> unlike the other databases I am familiar with (MS-SQL and Oracle), 
> which use 1900-01-01, a valid date.  When the MySQLreader gets the 
> 0000-00-00, it tries to cast it into a datetime value and fails (with 
> 0000-00-00 not being a valid date).
>
> Try setting your select statement to do an IF(expr1,expr2,expr3) when 
> returning that field to return a valid date if it encounters an empty 
> string like:
>
> Select if(date1='', '1900-01-01', date1) as date1 from table1;
>
>
> Not knowing C# very well, I don't know how to fix this in the 
> MySQLReader code, but my hunch is that the MySQLReader just needs a 
> datetime '' handler.  If it was based on the SQLDataReader, it 
> probably doesn't have one because SQLDataReader doesn't need one since

> it would only ever get a valid date or null.
>
> Does that help?
>
> Jeremy N. Morgan
>
>
> -----Original Message-----
> From: Rodrigo Moya [mailto:rodrigo@ximian.com]
> Sent: Wednesday, January 08, 2003 4:24 PM
> To: Vincent Daron
> Cc: mono-list@ximian.com
> Subject: [Mono-list] Re: MySQLDataReader.Read Exception
>
>
> On Wed, 2003-01-08 at 22:03, Vincent Daron wrote:
> > hello
> >
> > I've got an invalid cast exception in MySQLDataReader.Read() while 
> > reading a row containing a DateTime field = 0000-00-00 00:00:00.
> >
> > It's less than DateTime.MinValue and I suppose that the problem is 
> > the
>
> > same with value bigger than MaxValue.
> >
> > Any idea ?
> >
> not sure what it is, so forwarding to the mono list.
>
> cheers
--
Vincent Daron <vdaron@ask.be>
ASK sa


_______________________________________________
Mono-list maillist  -  Mono-list@ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list