| 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: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.1.5 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[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.
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'