| Bug #6336 | VVB6 / ODBC 3.51 converts TIME columns wrong | ||
|---|---|---|---|
| Submitted: | 30 Oct 2004 14:46 | Modified: | 30 Oct 2004 15:53 |
| Reporter: | A. Weh | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.10 | OS: | Windows (WinXP) |
| Assigned to: | CPU Architecture: | Any | |
[30 Oct 2004 15:53]
MySQL Verification Team
Thank you for the bug report but a related bug report was already reported: http://bugs.mysql.com/bug.php?id=6026
[31 Oct 2004 10:09]
A. Weh
Thanks for the fast response! Bug 6026 reports an error for inserting TIME values, mine reports one when retrieving a TIME value. Interestingly I can insert TIME values without problems with a Visual Basic string-value into a MySQL TIME-column (like "15:46:00"). I just want to make shure that the one who is able to fix this, does not overlook something. The two bugs may have the same technical reason, but they need not.
[17 Dec 2004 18:26]
Raul Carlomagno
use TIME_FORMAT to read it right, this way odbc thinks TIME_FORMAT is an string so it is ok TIME_FORMAT(time_field, '%H:%i:%s')
[19 Dec 2004 15:17]
A. Weh
Thanks, this works. In the Visual Basic program (above) I changed the line to the following: strSQL = "SELECT keycol, TIME_FORMAT(timevalue, '%H:%i:%s') timevalue FROM T1" Now the procedure gives correctly (the date portion of the result is meaningless, of course): timevalue=1899-12-30 08:10:01 Key= 1 TimeValue= 1899-12-30 08:10:01 timevalue=1899-12-30 08:10:02 Key= 2 TimeValue= 1899-12-30 08:10:02 Greetings A. Weh
[27 Sep 2005 20:33]
Anthony Johnston
I too have a problem related to this where by a select containing a TIME data typed field will not return any rows at all, the same statement omitting these fields works fine. Also, the solution of formatting the field does work but it will require me to re-deploy all my applications to all my clients, which I don't really want to do if it can be avoided Is there any plan to fix this one soon? or any alternative that you could suggest? Many thanks, Ant
[28 Sep 2005 8:13]
Anthony Johnston
Found the fix here; http://bugs.mysql.com/bug.php?id=5681&thanks=3¬ify=71 Cheers, Ant.
Description: A column of type TIME is not correctly interpreted by Visual Basis 6 via ODBC and ADODB. If a table with a column of type TIME contains a valid time-value, VB6 reads the current system date, but no time information. e.g. if the the time-column holds a value of '08:10:01', VB6 reads "2004-10-30 00:00:00" I discovered the error with MySQL/ODBC 3.51.07, upgraded to 3.51.10 and it still exists. Database is: MySQL 4.1.1a-alpha-nt (not the newest, ...) How to repeat: Create a table and fill it: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (keycol integer, timevalue time, PRIMARY KEY (keycol)); INSERT INTO t1 VALUES (1,"08:10:01"); INSERT INTO t1 VALUES (2,"08:10:02"); Gives correctly: mysql> SELECT * FROM t1; +--------+-----------+ | keycol | timevalue | +--------+-----------+ | 1 | 08:10:01 | | 2 | 08:10:02 | +--------+-----------+ 2 rows in set (0.00 sec) Read the same table with VB6 / ODBC / ADOB (just cut and paste this procedure): Public Sub TestTime() Dim recSet1 As New ADODB.Recordset Dim strSql As String Dim Key As Integer Dim TimeValue As Date strSql = "SELECT * FROM T1" recSet1.Open strSql, gcnGoldesel recSet1.MoveFirst While Not recSet1.EOF Debug.Print "timevalue="; Format(recSet1.Fields("timevalue"), "YYYY-MM-DD hh:mm:ss") Key = recSet1.Fields("keycol") TimeValue = recSet1.Fields("timevalue") Debug.Print "Key="; Key, "TimeValue= "; Format(TimeValue, "YYYY-MM-DD hh:mm:ss") recSet1.MoveNext Wend End Sub ... and you get wrongly: timevalue=2004-10-30 00:00:00 Key= 1 TimeValue= 2004-10-30 00:00:00 timevalue=2004-10-30 00:00:00 Key= 2 TimeValue= 2004-10-30 00:00:00 Suggested fix: Workaround: use DATETIME instead of TIME, seems to work.