MySQL Bugs: #7918: DATE Datatype value "0000-00-00" _not

Bug #7918	DATE Datatype value "0000-00-00" _not_ converted to NULL by ODBC
Submitted:	14 Jan 2005 22:26	Modified:	19 Jan 2005 19:14
Reporter:	James Blevins	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.5.???	OS:	Windows (Windows 2000 Advanced Server)
Assigned to:		CPU Architecture:	Any

Description:
When a field is type DATE and the default is NOT NULL, it defaults to '0000-00-00'. According to the manual:

http://dev.mysql.com/doc/mysql/en/Using_DATE.html

"The special date '0000-00-00' can be stored and retrieved as '0000-00-00'. When using a '0000-00-00' date through MyODBC, it is automatically converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of date."

This is not true! VB6 programs written using an ODBC connection barf when reading a '0000-00-00' date, and so does VBScript.

How to repeat:
Here's how to verify it using VBScript.

Create the file 'test.asp'.

Create an ODBC connection--call it whatever you want--and point it to your MySQL server. For the sake of argument, we'll call it 'connection'.

Create a table called 'test'. Create a field, call it 'asdfg'. Make it a DATE datatype, and NOT NULL so it defaults to '0000-00-00'

In the file test.asp, insert the following to read from the new table and test the result for being NULL:

<%
set connect = Server.CreateObject("ADODB.Connection")
connect.Open "connection"

Set Result_rs = Server.CreateObject("ADODB.Recordset")
qry = "SELECT * FROM asdfg"
Result_rs.Open qry, connect, 3, 3

'response.write Account_rs("thing")
response.write isNull(Account_rs("thing"))
%>

Put this on your IIS website and try loading it in your browser. You'll see the following:

False

Now go back to 'test.asp', uncomment the next to last line by removing the single quote, and comment out the last line. This time you'll get:

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

test.asp, line 0

See? This is nonsense! The record _does_ exist, and the line number, 0, certainly doesn't! VB6 gives a similar message about not being able to find any records either--even if the table is completely full!

BTW, if you change the default on this datatype to NULL, it works as expected.

Suggested fix:
None.

There's a typo. In the query, it should be:

  qry = "SELECT * FROM test"

I just used the table 'asdfg' for my own testing purposes before the bug submit. I have no idea why.

Another typo.

Instead of

	'response.write Account_rs("thing")
	response.write isNull(Account_rs("thing"))

It should be

	'response.write Result_rs("asdfg")
	response.write isNull(Result_rs("asdfg"))

Anyway, If you use the correct variables, it will behave as described.

That's what I get for doing this at the end of my workday.

Strange and curious. I had updated to the latest driver, but still had the bug. However, I had to restart IIS today, and now the bug is non-existent on the exact same script. Maybe it was something fixed between versions and the new version didn't take until I restarted IIS. I have no clue. But now things work as expected, so this should probably be closed.

Was unable to repeat this bug using MS Access 2003 & ASP

I can recreate this bug using Visual Basic 6, ADO 2.8, and MyODBC 3.51.12-win32.

CREATE TABLE `foo` (
  `pk` int(10) unsigned NOT NULL auto_increment,
  `df` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`pk`)

INSERT INTO `foo` VALUES ( NULL, '0000-00-00' );

The, in VB, create a new "Standard EXE" project.  Add the "Microsoft ActiveX Data Objects 2.8 Library" reference.  Finally, run the following code:

    Dim objODBCConnection As New ADODB.Connection
    Dim rstTest As New ADODB.Recordset
    Dim strSQL As String
        
    objODBCConnection.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=theServer;"DATABASE=theDatabase;UID=username;                      PWD=password;OPTION=3"

    objODBCConnection.open

    rstTest.CursorLocation = adUseClient

    strSQL = "SELECT * FROM foo"

    rstTest.Open strSQL, objODBCConnection

Place a breakpoint on the "rstTest.Open" line and a watch on "rstTest".  The status property after the line is executed becomes "Either BOF or EOF is True, or the current reocrd has been delete. Requested operations requires a current record."  Also notice that PageCount and RecordCount failed.

This bug and bug #7918 are similar, if not duplicates.

Check Bug#3621.