Bug #29416 | Problem Updateing tables with dates in | ||
---|---|---|---|
Submitted: | 28 Jun 2007 10:56 | Modified: | 27 Jul 2007 10:19 |
Reporter: | Steve Carloss | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
Version: | 3.51.16 | OS: | Windows (VB6 / VBA) |
Assigned to: | CPU Architecture: | Any |
[28 Jun 2007 10:56]
Steve Carloss
[11 Jul 2007 1:25]
Jess Balint
Steve, a few notes: Please look at the result you are getting from: format(date,"YYYY-MM-DD") I think you will find it is not correct. Why do you need to format the date? Using a date object will work fine, such as: rs!somefield = #2/3/2004# Also, what is the method "rs.Edit"? I am not familiar with this, nor could I find any documentation. I think you should only need to update the fields and then call rs.Update. Can you please explain how you came to the conclusion that "the ODBC driver was formating the dates in a way that the MySQL server wasn't recognising"?
[11 Jul 2007 9:28]
Steve Carloss
I use 'Format(date "yyyy-mm-dd")' to prevent vb from switching the day and the month round before puting it to the database I'm from the UK and our normal date format is dd/mm/yyyy where vb thinks its mm/dd/yyyy so swaps the day and the month. This method works anyway, and is not an issue. 'RS.Edit' is Box Standard DAO. Iv'e never tried in ODBC, but in JET, You can't use RS.Update without RS.AddNew or RS.Edit. I think its a DAO thing so I guess its true of ODBC too. It is used in the DAO/odbc example here http://dev.mysql.com/doc/refman/5.1/en/myodbc-examples-programming.html This part of the code works so is also not the issue. My example shows that using ODBC, It is almost impossible to update a record in a table that contains a data type 'DATE' column using DAO.....Imposible! That has got to be wrong. Through some investigation I worked out that ODBC converts my DAO methods into SQL statements and puts those statments to the database to process. an update will produce a statment such as - UPDATE field1='fish' field2='Chips' field3="Mushy peas" IN dinner WHERE field1='Sausage' and field2='Mash' and field3 = 'Gravy'; The WHERE statment is created by Matching EVERY field with its current value If the record has a Data type 'DATE', ODBC writes the querie with a 'DATETIME' expresion in the WHERE statmant. MySQL doesn't match these two different datatypes so the WHERE statment does not return a record to be updated... The statment does nothing at all and the update fails.
[11 Jul 2007 10:31]
Tonci Grgin
Hi Steve. > The WHERE statment is created by Matching EVERY field with its current value If the record has a Data type 'DATE', ODBC writes the querie with a 'DATETIME' expresion in the WHERE statmant. MySQL doesn't match these two different datatypes so the WHERE statment does not return a record to be updated... The statment does nothing at all and the update fails. Not entirely true. Please check http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1 and examine what happens if you use, for example, adCriteriaKey = 0. Bare in mind that MS pays little attention to anyone else but themselves! adCriteriaTimeStamp = 3 was introduced only because of Access Jet engine for example. Second thing to check would be our manual regarding TYPE of field returned from FORMAT (CONCAT, DATE etc. functions) which can be other than expected: mysql> SELECT (CONCAT("2007-07","-11")) AS F1; Field 1: `F1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 10 Max_length: 10 Decimals: 31 Flags: NOT_NULL +------------+ | F1 | +------------+ | 2007-07-11 | +------------+ 1 row in set (0.00 sec) mysql> SELECT DATE(CONCAT("2007-07","-11")) AS F1; Field 1: `F1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: DATE Collation: binary (63) Length: 10 Max_length: 10 Decimals: 31 Flags: BINARY +------------+ | F1 | +------------+ | 2007-07-11 | +------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_FORMAT('2007-07-11', '%X %V') AS F1; Field 1: `F1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 7 Max_length: 7 Decimals: 0 Flags: +---------+ | F1 | +---------+ | 2007 27 | +---------+ 1 row in set (0.00 sec) Notice also that casting value explicitly to DATE sets BINARY flag which is different but painful problem at this moment. Next, you can change date format on server level avoiding the need for conversion: | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | ... Please notify me if any of this helps.
[12 Jul 2007 16:46]
Steve Carloss
Hi, Really thanks for all the info. I Wasn't really asking for help though. The problem I was having was very easy to 'WORK AROUND' once I realised what was going wrong. Simply using DATETIME instead of DATE. I'm not an expert, by any means, but I have read that DATETIME should only be used where date and time is to be considered. Otherwise DATE should be used for dates and TIME for times. This, surely, is why there are the 3 different types. The problem I faced is by definition a bug. The ODBC should match the data format with the Datatype when creating the SQL statements. I wasted an entire day trying to work out why it wasn't working. And gave a pretty comprehancive explanation of the problem in the hope that other people didn't have to do the same. My workaround is a COMPRIMISE not the solution. The solution is that the ODBC team are made aware of the problem so it can be rectified in the next release. Steve
[26 Jul 2007 10:52]
Tonci Grgin
Steve, I am unable to repeat problem reported with test case attached. My local date format is "dd.MM.yyyy". I am using MyODBC 3.51.17 GA C:\mysql507\bin>mysql -uroot -p test Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.0.44-max-nt-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from bug29416; +---+---------+------------+ | x | VCField | DtField | +---+---------+------------+ | 1 | Johnny | 2007-07-26 | +---+---------+------------+ 1 row in set (0.00 sec) and accompanying log: 6 Connect root@localhost on test 6 Query SET SQL_AUTO_IS_NULL=0 6 Query select database() 6 Query SELECT @@tx_isolation 6 Query drop table if exists bug29416 6 Query create table bug29416(x SERIAL, VCField VARCHAR(30) DEFAULT NULL, DtField DATE) 6 Query SELECT * FROM bug29416 6 Query INSERT INTO `test`.`bug29416` (`x`,`VCField`,`DtField`) VALUES (0,'Test','2007-07-26') 6 Query SELECT @@IDENTITY 6 Query SELECT * FROM bug29416 6 Query UPDATE `test`.`bug29416` SET `VCField`='Johnny' WHERE `x`=1 6 Query drop table if exists bug29416 6 Quit
[26 Jul 2007 10:52]
Tonci Grgin
Test case
Attachment: 29416.vbs (application/octet-stream, text), 2.17 KiB.
[27 Jul 2007 10:19]
Steve Carloss
Thanks for your efforts Tony. My example was in VBA for Excel and has been repeated in VB6. If not being able to repeat it in VB Script satisfies your your bug reporting criteria then thats fine... As I previously said, the workaround is to use 'DATETIME' instead of 'DATE' and it works fine. I shall just continue in that way. Again thanks Steve
[27 Jul 2007 13:21]
Tonci Grgin
Hi Steve and thanks for your comment. There are indeed differences between VBS and VBA (though I don't know why MS left it that way...) so I'll retest with VS2005 to be sure. Thanks again for your interest in MySQL!
[27 Jul 2007 21:16]
Tonci Grgin
Steve, still "Can't repeat". Please try 3.51.17. Server version: 5.0.44-max-nt-log Source distribution mysql> select * from bug29416; +---+-------------------+------------+ | x | VCField | DtField | +---+-------------------+------------+ | 1 | New name z0000008 | 2007-07-27 | +---+-------------------+------------+ 1 row in set (0.00 sec) --VB test case-- Dim cnn As ADODB.Connection strConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;PORT=3306;DATABASE=test;UID=root;PWD=xxx;" & _ "OPTION=3;" cnn = New ADODB.Connection cnn.CommandTimeout = 1800 cnn.ConnectionTimeout = 1800 cnn.Open(strConnectString) cnn.Execute("drop table if exists bug29416") cnn.Execute("create table bug29416(x SERIAL, VCField VARCHAR(30) DEFAULT NULL, DtField DATE)") Dim rs As ADODB.Recordset rs = New ADODB.Recordset With rs .ActiveConnection = cnn .LockType = ADODB.LockTypeEnum.adLockOptimistic .CursorType = ADODB.CursorTypeEnum.adOpenDynamic .CursorLocation = ADODB.CursorLocationEnum.adUseClient .Open("SELECT * FROM bug29416") .AddNew() rs.Fields(0).Value = 0 rs.Fields(1).Value = "Test" rs.Fields(2).Value = Date.Today .Update() .Close() End With Dim rs1 As ADODB.Recordset rs1 = New ADODB.Recordset With rs1 .ActiveConnection = cnn .LockType = ADODB.LockTypeEnum.adLockOptimistic .CursorType = ADODB.CursorTypeEnum.adOpenDynamic .CursorLocation = ADODB.CursorLocationEnum.adUseClient .Open("SELECT * FROM bug29416") .Update("VCField", "New name z0000008") .Update() .Close() End With cnn.Close() cnn = Nothing