MySQL Bugs: #26654: MyODBC / ADO Enum data type is failing to update

Bug #26654	MyODBC / ADO Enum data type is failing to update
Submitted:	26 Feb 2007 23:43	Modified:	2 Jan 2008 22:58
Reporter:	Erica Moss	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1	OS:	Windows (win xp)
Assigned to:		CPU Architecture:	Any

Description:
This was discovered while running the fldupdate.vbs ADO compliance test.  I've
attached a smaller vbs script setbug.vbs to this report.

When run against MyODBC 5.0 this script will faile on the first record update of the Enum data type.  This is similar and most likely related to Bug 26652 written against the "SET" type, however I've entered against Enum here for tracking purposes.  

If you manual select from mysql after the running the test, it is evident that the script is reporting correctly. When run against myODBC 3.51 it will sometimes update a few records before failing.  This was filed in a separate report again for tracking purposes.

How to repeat:
Create test DB ado:
mysql>CREATE DATABASE ado;

To run the attached files in the ADO/script-unit test framework, drop the
attached script file in the 'level 0' dir, and the data file in the common dir. 
Open script-unit, drag the script file to the work area, highlight and run it.

What the test does:
1. Creates a test table as:  
CREATE TABLE set_test (C1 Enum ('MySQL', 'is', 'number', '1'), C2 Timestamp
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

2. Adds 100 records to the new table
3. Opens a new connection and record set as: SELECT * FROM ado_test
4. Calls Update on each field giving it a new value
5. Compares the field.OriginalValue property with the newly assigned value to
make sure that the new value was updated in the DB, and fails if they don't
match.

data file

Attachment: enumTest.csv (application/vnd.ms-excel, text), 443 bytes.

script unit test fle

Attachment: enumbug.vbs (application/octet-stream, text), 3.60 KiB.

have to look into this one a bit more

NEW script unit test fle

Attachment: enumbug.vbs (application/octet-stream, text), 3.66 KiB.

NEW data file

Attachment: enumTest.csv (application/vnd.ms-excel, text), 967 bytes.

There are a few mistakes in the first set of test files uploaded so please refer to the ones marked New.

Additional information: With the 3.51 drivers it was found that if you add a primary key column to the data set, and make the necessary adjustments to the script, then the test will pass.  However making these changes does not alter the results when run against the 5.0 drivers.  This error is received on the client on the first update attempt:
Error: Source: ADODB.Recordset  Line 121  Column 6
Operation is not allowed in this context.

This is from the mysql general log when the first record update attempt is made.  Note the extra spaces in the value field...
192 Query       SET NAMES utf8
191 Quit       
192 Query       SELECT database()
192 Query       SELECT database()
192 Query       SELECT * FROM enum_test
193 Connect     root@localhost on ado
193 Query       SET NAMES utf8
193 Quit       
194 Connect     root@localhost on ado
194 Query       SET NAMES utf8
194 Quit       
195 Connect     root@localhost on ado
195 Query       SET NAMES utf8
196 Connect     root@localhost on ado
196 Query       SET NAMES utf8
196 Quit       
195 Query       UPDATE def.ado.enum_test SET C1='number            ' WHERE C1='MySQL' AND C2='2007-02-27 00:57:33'
195 Quit

This is verified working against 3.51.20, however can't be tested against 5.1.0 until Bug #26985 is resolved

Testcase added to script unit test framework:
ado-compliance\trunk\bugs\26654.vbs

ADO test case passes against ODBC 5.1 svn r975 (5.1.2-dev)