| Bug #15681 | Cannot enter correct datetime through ADO and MyODBC | ||
|---|---|---|---|
| Submitted: | 12 Dec 2005 13:39 | Modified: | 10 Aug 2007 12:02 |
| Reporter: | Dusan Pavlica | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.12 | OS: | Windows (WinXP) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[12 Dec 2005 13:49]
Dusan Pavlica
Little correction - I see that I have an error in one line of code. Wrong line: q1->SQL->Text = "insert into datetimetest (dt_col) values (:pDate)" Corrected: q1->SQL->Text = "insert into dt_test (dt_col) values (:pDate)";
[12 Dec 2005 14:50]
MySQL Verification Team
I tested with the ODBC tool odbcte32 and it worked as expected, then I just guess it is an issue with C++ Builder and Delphi is handling the datetime just with the date part. Please verify if there is some property on your environment if the datetime is configured in that way.
[13 Dec 2005 12:29]
Dusan Pavlica
I didn't find any property which I could change or set in Builder or in Delphi. But if I choose in my TADOConnection component only different ODBC data source name (now pointing to MS Access database) all datetime columns work as expacted and I don't have to change any line of code. That is why I think the problem is in the MyODBC driver and not in C++ Builder or in Delphi.
[21 Apr 2006 17:40]
Renato Oviedo
Hi There Can you send me a complete example using MyODBC in C++ Builder? I just instaled MySQL server and MyODBC 3.51 driver. I configure both and test the connection but I don't know what else to do. An example of an INSERT and SELECT will be very useful. Thanks. Regards. Renato
[23 Nov 2006 15:13]
Laurent Caille
Hi Any news about this problem ?
[23 Nov 2006 20:09]
Bogdan Degtyariov
This bug is in TODO for developers, but it is not solved yet.
[2 Jul 2007 19:01]
Alberto Lago
same for 3.51.15 Any solution?
[25 Jul 2007 17:17]
Jim Winstead
I believe this may have been fixed in 3.51.16 with the fix for Bug #15773. Please re-verify. Thanks.
[9 Aug 2007 18:07]
Bogdan Degtyariov
Delphi Test case
Attachment: test15681.zip (application/x-zip-compressed, text), 7.29 KiB.
[9 Aug 2007 19:29]
Bogdan Degtyariov
This looks strange, but in case of MS Access the above test case inserts only the date part as well as in case of MySQL ODBC Driver. I even tested it on Oracle 10XE database with the same result. This makes me think that the problem is in Delphi libs. Can anybody check the test case and comment? Thanks.
[10 Aug 2007 11:39]
Dusan Pavlica
It is really strange because when I did test case in C++ Builder 6 (I don't have it installed right now) it worked OK with Access tables and wrong with MySQL. Today I tested it again under BDS2006 (C++ Builder personality) and time part didn't get through. Same result for MS Access and MySQL. Version 3.51.17.
[10 Aug 2007 12:02]
Bogdan Degtyariov
Unfortunately, the only version of Delphi I have is Delphi 7. However, I don't think it changed the interaction with ADO components so dramatically. It is clearly visible in the logs that Delphi application bounds the parameter as DATE from the beginning: Project15681 654-f10 ENTER SQLBindParameter HSTMT 02AE1D30 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 9 <SQL_C_DATE> SWORD 9 <SQL_DATE> SQLULEN 19 SWORD 0 PTR 0x0218A038 SQLLEN 0 SQLLEN * 0x021877E0 I made unsuccessfull attempts to tweak MyODBC code and cast the buffer bound by SQLBindParameter as TIMESTAMP_STRUCT instead of DATE_STRUCT. Therefore I consider this bug record as Delphi problem that cannot be resolved within MyODBC layer. Thus, the status of the bug is being changed to "Not a bug". In case you find something that proves it as MyODBC problem, please give us the update. Thanks.
[27 Aug 2007 10:50]
Tonci Grgin
There is a further discussion in Bug#18084.
Description: When inserting or updating datetime column from C++ Builder 6 or Delphi 6 using ADO components only date portion of date time value is stored and time is always set to 00:00:00 (time is omitted). MyODBC 3.51.12 and also 3.51.11 WinXP Delphi 6, C++ Builder 6 ADO MySQL 4.1.14 How to repeat: CREATE TABLE `test`.`dt_test` ( `id` int(10) unsigned NOT NULL auto_increment, `dt_col` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB In C++ Builder or in Delphi drop TADOQuery on the form q1->SQL->Text = "insert into datetimetest (dt_col) values (:pDate)" q1->Parameters->ParamByName("pDate")->DataType = ftDateTime; q1->Parameters->ParamByName("pDate")->Value = Now(); q1->ExecSQL(); Output from MySQL Administrator->Server Logs->General Log: 051212 14:22:42 7 Query insert into dt_test (dt_col) values ('20051212') This example when connected to MS Access works fine Suggested fix: For applications working only with MySQL it is possible to use string parameter and FormatString function q1->Parameters->ParamByName("pDate")->DataType = ftString; q1->Parameters->ParamByName("pDate")->Value = Now().FormatString("yyyymmddhhnnss")