Bug #41507 MyODBC Driver 5.1.5 With Delphi 2007 Don't clear date field

Submitted: 16 Dec 2008 14:04 Modified: 12 Feb 2009 16:02
Reporter: Carlos Nascimento Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[16 Dec 2008 14:04] Carlos Nascimento
Description:
My scenario:

- Windows XP Professional
- Delphi 2007 Update 3
- MySQL Server 5.0 or 5.1 (both versions appeard the error)
- MyODBC Driver 5.15

My table structure:

CREATE TABLE TESTE (ID INT NOT NULL AUTO_INCREMENT, NASCIMENTO DATE, PRIMARY KEY (ID));

Afert created, i had inserted any records, and all ok.

My Delphi code:
  ADOConnection1.LoginPrompt:= False;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString:= 'Driver={MySQL ODBC 5.15 Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;';
  ADOConnection1.Connected:= True;

  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT * FROM TESTE');
  ADOQuery1.Open;

  ADOQuery1.Edit;
  ADOQuery1.FieldByName('NASCIMENTO').Clear;
  ADOQuery1.Post;

Error occurrs on method "ADOQuery1.Post" only on MyODBC 5.15 Driver, but not ocurrs on MyODBC 3.51 driver.

MySQL Incorrect date value: '0000-00-65'

How to repeat:
My scenario:

- Windows XP Professional
- Delphi 2007 Update 3
- MySQL Server 5.0 or 5.1 (both versions appeard the error)
- MyODBC Driver 5.15

My table structure:

CREATE TABLE TESTE (ID INT NOT NULL AUTO_INCREMENT, NASCIMENTO DATE, PRIMARY KEY (ID));

Afert created, i had inserted any records, and all ok.

My Delphi code:
  ADOConnection1.LoginPrompt:= False;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString:= 'Driver={MySQL ODBC 5.15 Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;';
  ADOConnection1.Connected:= True;

  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT * FROM TESTE');
  ADOQuery1.Open;

  ADOQuery1.Edit;
  ADOQuery1.FieldByName('NASCIMENTO').Clear;
  ADOQuery1.Post;

Error occurrs on method "ADOQuery1.Post" only on MyODBC 5.15 Driver, but not ocurrs on MyODBC 3.51 driver.

MySQL Incorrect date value: '0000-00-65'
[30 Jan 2009 18:03] Jess Balint
Carlos,
When you said: Error occurrs on method "ADOQuery1.Post" 
What is the error received?

Also, can you create and attach an ODBC trace file as described here:
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-trace.html
[2 Feb 2009 12:15] Carlos Nascimento
ODBC trace file

Attachment: SqlTrace.Log (application/octet-stream, text), 77.85 KiB.

[2 Feb 2009 12:16] Carlos Nascimento
Log queries

Attachment: myodbc.sql (application/octet-stream, text), 254 bytes.

[2 Feb 2009 12:17] Carlos Nascimento
About your questions.
Files in attach.
[2 Feb 2009 18:12] Jess Balint
Thanks Carlos. I'll take a look.
[12 Feb 2009 15:54] Tonci Grgin
ODBC trace

Attachment: SQL41507.rar (application/octet-stream, text), 4.28 KiB.

[12 Feb 2009 16:02] Tonci Grgin
Verified as described on WinXP Pro 32bit, c/ODBC 5.1.6, remote MySQL server 5.1.30 with traces attached.

mysql> select * from teste;
+----+------------+
| ID | NASCIMENTO |
+----+------------+
|  1 | 1968-05-17 |
+----+------------+
1 row in set (0.00 sec)

mysql> select * from teste;
+----+------------+
| ID | NASCIMENTO |
+----+------------+
|  1 | 0004-00-00 |
+----+------------+
1 row in set (0.00 sec)

Actual Delphi code is much simpler and I played with settings for ADO there but with no luck.
I think it's just ADO's idea of NULL-Date as my tests constantly return same value upon clear, "0004-00-00", and Delphi ADO components are unable to show this as Date (so what one actually sees *is* cleared field in datagrid).

Server query log:
090212 16:49:44   52 Connect	root@QCW2K8.lan on test
		   52 Query	SET NAMES utf8
		   52 Query	SET character_set_results = NULL
		   52 Query	select database()
		   52 Query	select database()
		   52 Init DB	test
		   52 Query	SELECT @@tx_isolation
		   52 Query	SELECT * FROM teste
090212 16:49:45   52 Query	UPDATE `test`.`teste` SET `NASCIMENTO`=_latin1'0004-00-00' WHERE `ID`=1 AND `NASCIMENTO`=_latin1'1968-05-17'
		   52 Query	SELECT `ID`,`NASCIMENTO` FROM `test`.`teste` WHERE `ID`=1
090212 16:49:47   52 Quit	

Notice UPDATE statement! I did remove NASCIMENTO field from WHERE *and* UPDATE in pf flags so it should not be there at all (as it isn't there in WHERE)! I guess there are still problems with Delphi implementation of ADO...

Workaround would be not to use ADO.Clear but issue UPDATE statement manually.
[12 Feb 2009 16:07] Tonci Grgin
NASCIMENTO removed from provider flags, still used in forming UPDATE

Attachment: bug41507.JPG (image/jpeg, text), 18.68 KiB.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.