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:Microsoft Windows (WinXP)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[12 Dec 2005 13:39] Dusan Pavlica
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
 Delphi 6, C++ Builder 6
 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`)

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();

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")
[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)"
q1->SQL->Text = "insert into dt_test (dt_col) values (:pDate)";
[12 Dec 2005 14:50] Miguel Solorzano
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.


[23 Nov 2006 15:13] Laurent Caille

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.

[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?
[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.
[27 Aug 2007 10:50] Tonci Grgin
There is a further discussion in Bug#18084.