Bug #1680 Date/Time fields containing NULLs return E_FAIL results
Submitted: 27 Oct 2003 12:09 Modified: 30 Oct 2003 13:17
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.06.00 OS:Microsoft Windows (Windows 32-bit)
Assigned to: CPU Architecture:Any

[27 Oct 2003 12:09] [ name withheld ]
Description:
A table contains date/time fields, and some records have NULL values. When using MyODBC with ADO (and VB6), the following code will return an E_FAIL error when either the .RecordCount or .MoveFirst commands are executed.

The only occurs when the CursorLocation is set to adUseClient; adUseServer works fine.

If the date/time field (containing the NULL) is not included in the query, the .RecordCount or .MoveFirst commands work fine.

How to repeat:
Using a table with NULL vaules in date/time fields, run the following VB6 code:

Set conn = New ADODB.Connection   
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
     
Dim fld As ADODB.Field
 
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
	& "SERVER=sequel;" _
	& "DATABASE=cmsip;" _
	& "UID=root;" _
	& "PWD=;" _
	& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.Open

sSQL = "SELECT * FROM users"
rs.Open sSQL, conn, adOpenStatic, adLockReadOnly

MsgBox "Recs Found: " & rs.RecordCount

rs.MoveFirst
Do Until rs.EOF
   rs.MoveNext
Loop

Suggested fix:
Have MyODBC return NULLs in the date/time fields of the cursor.
[30 Oct 2003 13:17] Dean Ellis
Using MDAC 2.7, MySQL 4.0.16 and MyODBC 3.51.06 I am unable to duplicate this behaviour.  ISNULL(rs("nulldate")) also returns the expected value.
[2 Nov 2003 15:16] [ name withheld ]
Dean,

You are correct. I mistakenly thought a date of '0000-00-00 00:00:00' was the same as a NULL.

Thanks,

Bruce.
[14 Jul 2004 12:19] Tavanti Pier Luigi
I've reached the same problem only in the case I've got a text or blob field without any char.
The problem is that Mysql has not a 'default' value for those kind of fields so you must edit the program and write just a char (not a blank space!) in the field if the user don't fill anything !

I hope is the same problem...
[26 Jul 2004 21:08] Jerry Killion
I too have had issues with datetime, If I set the cursortype to adOpenStatic (myODBC 3.51) and attempt to retrieve a recordset, I get an EOF condition if any datetime fields for any rows are set to the default ( 0 ). If I ensure there are no  0 datetime fields, the cursor returns the valid EOF condition. This to me is a real problem, since I have a datetime field that should not be filled in until an action is taken. I should not need to create an artificial datetime to handle a recordset issue. I am not using the datetime field to select the rows.
[17 Sep 2004 8:44] Jochen Ulbricht
Hi, I had the same problem, too. My workaround was to set the default value of all datetime cols to '1970-01-01 00:00:00'. In this project it did't matter, but what if I wanna get a datetime col which is insered maybe from php with not supported format so that I get a '0000-00-00 00:00:00'? Then I'll get a bit in trouble in vb. It would be nice, if I could at least catch this error, but adodb does only amusing things from this time on.
[6 Jun 2007 23:10] raul anguiano
the easiest way i found to workaround this problem is using the function cast in the query for the fields with null values in this case is a date field so i do something like this.

select cast(field as date) from table 

or 

select cast(field as char(10)) from table

both worked for me with vb6 and ado 2.5
[19 Jul 2007 8:49] Tonci Grgin
This has been retested in Bug#28016 and I found no problems.

There are two things to consider here:
 - SQL_MODE MySQL server runs in
and (if updating is required)
 - the way ADO is doing UPDATEs.
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1

If all conditions are met, everything works.