MySQL Bugs: #9410: Problem with datetime and timestamp

Bug #9410	Problem with datetime and timestamp
Submitted:	26 Mar 2005 11:39	Modified:	22 Aug 2007 10:43
Reporter:	Manuel Ruiz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
Delphi's 7 SQL Explore fail in tables with columns datetime & timestamp with MYSQL 4.0.18 & 4.1
Visual Studio NET fail in tables with columns datetime & timestamp with MYSQL 4.0.18 & 4.1
Programs not read tables with these columns

How to repeat:
Run Delphi's 7 SQL Explore to access a MYSQL via ODBC drivers

I created test program and tried to repeat the bug. There was not any error.
The trace log of program:

Connect....
Drop....
Create....
Test....
ColName = D2,bColName = 2,SqlType = 3,ColDef = 32,Scale = 2,Nullable = 1
ColName = D4,bColName = 2,SqlType = 3,ColDef = 32,Scale = 4,Nullable = 1
Disconnect....

Sorry... I should post it  into other bug report...Please ignore it !

Seems the problem is in timestamp type, datetime seems ok in my case.
Here is a short analysis of log files (tested on MS Visual Studio .NET 2003):
Case 1: Table with a varchar column.
Case 2: Table with a timestamp column.

************** myodbc.log.1 ****************
......
>SQLSetStmtAttr
| enter: Atrr: 9, ValuePtr: 0x1, StrLenPtr: -6
| exit: SQL_SUCCESS
<SQLSetStmtAttr
>SQLExtendedFetch
| enter: fetchtype: 1  row: 0  current top-row: 0  rows_found: 0
| info: mysql_data_seek(0)
| >mysql_fetch_row
| <mysql_fetch_row
| exit: SQL_SUCCESS
<SQLExtendedFetch
>SQLExtendedFetch
| enter: fetchtype: 1  row: 0  current top-row: 0  rows_found: 1
| >mysql_fetch_row
| <mysql_fetch_row
| exit: SQL_SUCCESS
<SQLExtendedFetch
>SQLExtendedFetch
| enter: fetchtype: 1  row: 0  current top-row: 1  rows_found: 1
| exit: SQL_NO_DATA_FOUND
<SQLExtendedFetch
>SQLFreeStmt
| enter: stmt: 0x3b3dd48  option: SQL_UNBIND
| >my_free
| | my: ptr: 0xb544b08
| <my_free
| exit: SQL_SUCCESS
<SQLFreeStmt
>SQLMoreResults
| exit: SQL_NO_DATA_FOUND
<SQLMoreResults
......

************** myodbc.log.2 ****************
......
>SQLSetStmtAttr
| enter: Atrr: 9, ValuePtr: 0x1, StrLenPtr: -6
| exit: SQL_SUCCESS
<SQLSetStmtAttr
>SQLExtendedFetch
| enter: fetchtype: 1  row: 0  current top-row: 0  rows_found: 0
| info: mysql_data_seek(0)
| >mysql_fetch_row
| <mysql_fetch_row
| exit: SQL_SUCCESS
<SQLExtendedFetch
>SQLFreeStmt
| enter: stmt: 0x3b43838  option: SQL_UNBIND
| >my_free
| | my: ptr: 0xb534b08
| <my_free
| exit: SQL_SUCCESS
<SQLFreeStmt
>SQLMoreResults
| exit: SQL_NO_DATA_FOUND
<SQLMoreResults
......

We can see that in timestamp case (myodbc.log.2) VS Server explorer doesn't wait for SQL_NO_DATA_FOUND status from SQLExtendedFetch function (as it is in myodbc.log.1). It calls SQLFreeStmt at once.
VS has this problem with only '0000-00-00 00:00:00' values for timestamp field. In such cases MyODBC returns SQL_NULL_DATA value which causes the error in VS Server Explorer, but MS Access (for example) works well. On other hand VS Server Explorer worked ok if MyODBC had returned '0000-00-00 00:00:00' instead of SQL_NULL_DATA (after a small patch, whitch is illegal because such values for timestamp aren't supported).

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Our part of this problem is addressed in patch for Bug#13766 and follow-up in Bug#30539. I don't see what else we can do. Please try latest MyODBC driver.