Bug #7046 MyODBC handles TIME types improperly from IIS/ASP/ADO
Submitted: 6 Dec 2004 18:55 Modified: 21 Oct 2005 14:38
Reporter: Emmanuel KARTMANN Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.09/3.51.10 OS:Windows (Windows XP)
Assigned to: Peter Harvey CPU Architecture:Any

[6 Dec 2004 18:55] Emmanuel KARTMANN
Description:
We just upgraded our MyODBC to 3.51.09 and it turns out that it returns wrong values for TIME types...

Writing a TIME value in a table via ASP/ADO/ODBC and reading it back returns "00:00:00" with MyODBC 3.51.09 (whereas 3.51.06 worked fine). I have written a test ASP page (code below) that creates a TABLE with a TIME field, insert a value ("10:03:57") and tries to read it back with SELECT (the output was "06/12/2004 10:03:57" in MyODBC 3.51.06, and is now "06/12/2004" in MyODBC 3.51.09).

With MyODBC 3.51.06 you have this output:
=================================
  MySQL: Check TIME ADO/ODBC Bug
  Connecting to Database...
  MyODBC Driver version is 03.51.06
  Creating table with TIME field...
  Inserting row with TIME field value "10:03:57"...
  Reading row with TIME field value...
  objRecordset("mytime").Value = "06/12/2004 10:03:57"
  Your ASP/ADO/ODBC Connection has not the TIME bug: objRecordset("mytime").Value == "10:03:57" 

With MyODBC 3.51.09 you have this output:
=================================

  MySQL: Check TIME ADO/ODBC Bug
  Connecting to Database...
  MyODBC Driver version is 03.51.09
  Creating table with TIME field...
  Inserting row with TIME field value "10:03:57"...
  Reading row with TIME field value...
  objRecordset("mytime").Value = "06/12/2004"
  Your ASP/ADO/ODBC Connection HAS the TIME bug: 00:00:00 != "10:03:57" 

How to repeat:
Use this ASP code to repeat:
======================

<%@ language="javascript" %>
<html>
  <head>
    <title>MySQL: Check TIME ADO/ODBC Bug</title>
  </head>
  <body>
    <h3>MySQL: Check TIME ADO/ODBC Bug</h3>
<%
  var strMyConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=temporary;User ID=guest;Password=" + Application("LIB_DB_GUEST_PASSWORD");
  var strTimeValue = "10:03:57";
  var objConnection=Server.CreateObject("ADODB.Connection");
%>Connecting to Database...<br/><%
  objConnection.Open(strMyConnectionString);
%>MyODBC Driver version is <%=objConnection.Properties("Driver Version").Value%><br/><%
%>Creating table with TIME field...<br/><%
  objConnection.Execute("DROP TABLE IF EXISTS test_mysql_time_bug");
  objConnection.Execute("CREATE TEMPORARY TABLE test_mysql_time_bug(mytime TIME)");
%>Inserting row with TIME field value "<%=strTimeValue%>"...<br/><%
  objConnection.Execute("INSERT INTO test_mysql_time_bug(mytime) VALUES('" + strTimeValue + "')");
%>Reading row with TIME field value...<br/><%
  var objRecordset = objConnection.Execute("SELECT * FROM test_mysql_time_bug");
%>
  objRecordset("mytime").Value = "<%=objRecordset("mytime").Value%>"<br />
<%
  // Use a Date objet to read value
  var objDateTime = new Date(objRecordset("mytime").Value);
  // Convert into a HH:MM:SS format (locale time string)
  var strReadTimeValue = objDateTime.toLocaleTimeString();
  if (strReadTimeValue != strTimeValue)
  {
%>
    <font color="red">Your ASP/ADO/ODBC Connection HAS the TIME bug: 
      <%=strReadTimeValue%> != "<%=strTimeValue%>"</font><%
  }
  else
  {
%><font color="green">Your ASP/ADO/ODBC Connection has not the TIME bug: 
      objRecordset("mytime").Value == "<%=strTimeValue%>"</font><%
  }
%>
  </body>
</html>
[7 Dec 2004 22:15] MySQL Verification Team
Thank you for the bug report I was able to repeat with driver
3.51.10
[5 Mar 2005 21:07] [ name withheld ]
Hi,

Is there a solution to this bug?

Cheers
bjawnie
[17 Mar 2005 12:05] Sledge Hammer
Solution wanted :)
a $$$ worldwide reward pending :) (more happy users and inter-cashflow)
[4 Apr 2005 15:29] Pedro Beirao
I confirm this bug win MyODBC 3.51.11 / ADO 2.8 and VB.

The problem is the same as ASP code.

Only the MyODBC 3.51.06 retrieve the correct value of time. Nevertheless the value of the time field is date + time, but with a format function, I can show only the time.

Please, can someone correct this error. This is critical when you store any time value. All fields are converted to 00:00:00
[6 Apr 2005 17:55] Stephen Coles
writing a vb program come across same bug in the lastest version 3.51.11

used to test - getstring converts all fields to one long string:

adorec.open strSQL, adoCon
rr = adorec.getstring
msgbox(rr)

if one of columns in strSQL is TIME it isn't returned in recordset coverting column to VARCHAR and it appears.

when will this be fixed it really annoying
[21 Apr 2005 10:55] Jeroen Boonstra
I am no C programmer, but i lookup at the source code and changed the following :

execute.c 
 case SQL_TYPE_TIME:
    if (param->CType == SQL_C_TIMESTAMP ||
  param->CType == SQL_C_TYPE_TIMESTAMP)
    {
      TIMESTAMP_STRUCT *time=(TIMESTAMP_STRUCT*) param->buffer;
      sprintf(buff,"'%02d:%02d:%02d'",time->hour,time->minute,time->second);
      return add_to_buffer(net,to,buff,10);
    }
    else
    {
      ulong time=str_to_time(data,length);
      sprintf(buff,"'%02d:%02d:%02d'",time/10000,time/100%100,time%100);
      return add_to_buffer(net,to,buff,10);
    }

#########new version ############

 case SQL_TYPE_TIME:
    if (param->CType == SQL_C_TIMESTAMP ||
  param->CType == SQL_C_TYPE_TIMESTAMP)
    {
      TIMESTAMP_STRUCT *time=(TIMESTAMP_STRUCT*) param->buffer;
      sprintf(buff,"'%02d:%02d:%02d'",time->hour,time->minute,time->second);
      return add_to_buffer(net,to,buff,8);
    }
    else
    {
      ulong time=str_to_time(data,length);
      sprintf(buff,"'%02d:%02d:%02d'",time/10000,time/100%100,time%100);
      return add_to_buffer(net,to,buff,8);
    }

###

Then i compiled a new version, and it solved the problem for me.
I can't say it will work for you, because i have no c programming knowledge.

I will attach the two files. Copy them to  c:\windows\system32\
[26 Apr 2005 11:25] Taras Bredel
Could you please add the files to the bugreport ?
http://bugs.mysql.com/bug.php?id=7046

Thanx!
[26 Apr 2005 11:53] Jeroen Boonstra
Sorry, i am unable to attach files here. I have sent them to Peter Harvey, Because he can attach files, currently i haven't recieved any response.
[26 Apr 2005 11:57] Taras Bredel
Could you please mail them to me, I'm in the middle of a large migration, and this is killing the entire process.

ttb@bluefish.dk
[12 May 2005 8:44] [ name withheld ]
I have the same problem, when possible can somebody send me also the files or attach them to this report. Thank already, s.lambert@madeware.nl
[4 Jun 2005 4:17] Dietrich Speer
Without having seen this bug report I first encountered this problem on 3.51.11 after an upgrade. Then reverted back to .10, then to .9. Going all the way back ot 3.51.6 would then break the connection altogether. I am running Win2000 Server.
[17 Jul 2005 11:08] 50 PHIE
Hi,

Connector ODBC 3.51.12 fix problem.

http://forums.mysql.com/read.php?37,33923,33923#msg-33923

Chris