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:
None 
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
Description:
    I'm migrating a databse to MySQL and all is good.  Exept for this one big problem.

    A few of my forms(tables) have got dates in them.  I can create records without a glitch, but when i try to update them, nothing happens...

    I thought it was just my programming but after some investigation, i discovered that the ODBC driver was formating the dates in a way that the MySQL server wasn't recognising. And so the rs.Edit method is failing.

    I think the most worrying thing about this is, although it is failing,  no error message is generated as the sql statement generated by ODBC is valid, it simpily doesn't do anything because the WHERE clause is not returning a record.

How to repeat:
Create a table - 

use TEST

CREATE TABLE Buggy
(bref SERIAL, 
somename VARCHAR(20), 
somedate DATE);

connect in VB and create a record

set rs=ws.openrecordset("Buggy")
rs.addnew
rs!somename = "John"
rs!somedate = format(date,"YYYY-MM-DD")
rs.update

now try and change the name (assuming its the only record bref will be 1)

set rs=ws.openrecordset("select * from buggy where bref = 1")
rs.edit
rs!somename = "Peter"
rs.update

Finaly, veiw the record however you choose to discover that the edit has not done any thing.  

eg.

select * from buggy
[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