MySQL Bugs: #18883: Error fetching TIME columns

Bug #18883	Error fetching TIME columns > 23:59:59
Submitted:	7 Apr 2006 11:54	Modified:	3 May 2006 7:55
Reporter:	Jacques Beaufls	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:

mySQL reference manuel (MySQL.chm) says :

"  11.3.2. The TIME Type
MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The reason the hours part may be so large is that the TIME type may be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).  "

When trying to fetch values >= 24:00:00, an error occurs :

Erreur d'exécution '-2147217887 (80040e21)':

Une opération OLE-DB en plusieurs étapes a généré des erreurs.
Vérifiez chaque valeur d'état OLE-DB disponible. Aucun travail n'a été effectué.

How to repeat:

---> table format & data

CREATE DATABASE `timebugcase` ;

CREATE TABLE `tbltimebugcase` (
  `timTest` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tblTimeBugCase` VALUES ('01:02:03');
INSERT INTO `tblTimeBugCase` VALUES ('13:22:45');
INSERT INTO `tblTimeBugCase` VALUES ('23:59:59');
INSERT INTO `tblTimeBugCase` VALUES ('24:00:01');

---> excel 2003 macro

Don't forget to check "Microsoft ActiveX Data Object 2.0 Library" in <Tools/Références>

Don't forget to create an ODBC entry with DSN = "timebugcase"; UID = "root"; PWD = "", accessing database "timebugcase".

Sub TimeBugCase()

    Const DSN = "timebugcase"
    Const UID = "root"
    Const PWD = ""

    Dim cnxDatabase As ADODB.Connection
    Dim cmdCommand As ADODB.Command
    Dim myRecordset As ADODB.Recordset
    
    Dim strSQL As String
    Dim intLine As Integer
    Dim varCell As Variant
    
    
    ' connecting database

    Set cnxDatabase = CreateObject("ADODB.Connection")
    cnxDatabase.Open "DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD & ";"

    ' querying data
    
    strSQL = ""
    strSQL = strSQL & "SELECT * "
    strSQL = strSQL & "FROM tblTimeBugCase"
    
    Set cmdCommand = CreateObject("ADODB.Command")
    With cmdCommand
    Set .ActiveConnection = cnxDatabase
        .CommandText = strSQL
        .Execute
    End With
    
    Set myRecordset = CreateObject("ADODB.Recordset")
    With myRecordset
        Set .ActiveConnection = cnxDatabase
        .Open cmdCommand
    End With
    
    intLine = 1
    
    ' reading result cursor
    
    While Not myRecordset.EOF
     
        varCell = myRecordset.fields("timTest").Value
        
        Cells(intLine, 1).Value = Format$(varCell, "hh:mm:ss")
        
        myRecordset.movenext
        
        intLine = intLine + 1
    Wend
    
    Set myRecordset = Nothing
    Set cmdCommand = Nothing
    Set cnxDatabase = Nothing

End Sub

When fetching last line :

Erreur d'exécution '-2147217887 (80040e21)':

Une opération OLE-DB en plusieurs étapes a généré des erreurs.
Vérifiez chaque valeur d'état OLE-DB disponible. Aucun travail n'a été effectué.

Suggested fix:

hum.. no idea

Hi. Thanks for your problem report. So far it seems something's wrong with DAO components, not MyODBC since my testcase shows:
From DB:  TimeSpan ==> 01:02:03
Expected: String ==> 01:02:03
From DB:  TimeSpan ==> 13:22:45
Expected: String ==> 13:22:45
From DB:  TimeSpan ==> 23:59:59
Expected: String ==> 23:59:59
From DB:  TimeSpan ==> 1.00:00:01
Expected: String ==> 24:00:01

If you can provide me with full source showing this problem I'll test again.

Excel with macro fetching rows from database

Attachment: timebugcase.xls (application/octet-stream, text), 26.50 KiB.

mySQL directory with database files

Attachment: timebugcase.zip (application/x-zip-compressed, text), 482 bytes.

mySQL files I have uploaded are 5.0.18-nt version.

I try to get the last update for MDAC (Microsoft Data Access Component), by I don't know what version I am running now (WXP SP1, .Net framework 1.1).

I keep you informed...

Thanks for your input.

Please reference the ODBC specification (http://msdn.microsoft.com/library/en-us/odbc/htm/odbcc_data_types.asp and http://msdn.microsoft.com/library/en-us/odbc/htm/odbcconstraints_of_the_gregorian_calendar...), where TIME values are constrained to the following (and thus not all TIMEs that MySQL can deal with can be consumed by ODBC or layers that sit on top of it):

> Constraints of the Gregorian Calendar
> 
> Date and datetime data types, and the trailing fields of interval data types, must conform to the constraints of the Gregorian calendar. These constraints are as follows:
> 
>     * The value of the month field must be between 1 and 12, inclusive.
>     * The value of the day field must be in the range from 1 through the number of days in the month. The number of days in the month is determined from the values of the year and months fields and can be 28, 29, 30, or 31. (The number of days in the month can also depend on whether it is a leap year.)
>     * The value of the hour field must be between 0 and 23, inclusive.
>     * The value of the minute field must be between 0 and 59, inclusive.
>     * For the trailing seconds field of interval data types, the value of the seconds field must be between 0 and 59.9(n), inclusive, where n is the number of digits in the fractional seconds precision.
>     * For the trailing seconds field of datetime data types, the value of the seconds field must be between 0 and 61.9(n), inclusive, where n specifies the number of "9" digits and the value of n is the fractional seconds precision. (The range of seconds allows as many as two leap seconds to maintain synchronization of sidereal time.)

The only way I can think of getting to these values in "stock" ODBC from ADO is to treat them as strings.

Thank you, guys !