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.