Bug #2966 ADO Recordset Update
Submitted: 26 Feb 2004 0:39 Modified: 17 Dec 2007 20:36
Reporter: Nabil Barakat Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 and 5.1 OS:Windows (Windows XP)
Assigned to: Jim Winstead CPU Architecture:Any

[26 Feb 2004 0:39] Nabil Barakat
When updating an ADO recordset using  the MySQL ODBC connector run-time error # 80004005 appears on RS.Update. This error appears only when the database field name is a reserved word.

How to repeat:
To recreate the error: create a database with fieldnames Unique, Limit, Desc

    Rs.Open TableName, Cnn, adOpenForwardOnly, adLockOptimistic
    RS("Desc")="Error Test"

Suggested fix:
It seems that the ODBC connector does not enclose tablenames and fields in ``. A workaround that works is to use the execute method on the connection:

 Cnn.Execute "Insert TableName Set `Unique`=1, `Limit`=2, `Desc`='Error Test'"

I suggest to default the ODBC connector to enclose tablename.fieldname with `tablename`.`fieldname` which minimizes migration time of software developed to MySQL.
[6 Mar 2004 22:32] MySQL Verification Team
This is the expected behavior and explained in the Manual:

If an identifier is a reserved word or contains special characters,
you must quote it whenever you refer to it.
[7 Mar 2004 0:51] Nabil Barakat
Hi Migual;

Thank you for your feedback.

I understand the ability to quote the identifier in quotes when executing an SQL command. But the ability to do so when accessing the identifier in a recordset doesn't work.

RS("`Unique`")=1 produces an error.

Shouldn't this be taken care of in the ODBC driver?
[7 Mar 2004 8:00] MySQL Verification Team
Could you please try to use escaping:


[7 Mar 2004 14:26] Nabil Barakat
Hi Again;

I tried RS("\`Unique\`")=1 this time visual basic generates an error 3265 : Item cannot be found in the collection corresponding to the requested name or ordinal.

This error is generated on the actual RS("\`Unique\`")=1 statement and not on the RS.Update. 

Thank you for your efforts.
[11 Mar 2004 13:41] MySQL Verification Team
Well my main problem here is that I don't have VB for to test your
code, but sometime ago I tested MyODBC/ADO/VC++ and I used a class
which uses the ordinal position of the columns, also I think that
the below syntax is valid:

 RS!Unique = 1
 RS!Limit  = 2
 RS!Desc   = "Error Test"
[12 Mar 2004 5:40] Nabil Barakat
We still get the following error:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.1a-alpha-nt]You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Desc,Unique,Test) VALUES('hjh','hjhj','JDSHJ')' at line 1

I would think this error will also be generated if using C++ (Iam not sure though). 

We have no problems when inserting or updating a table that has no reserved words in the fieldnames.

We are currently trying to decide if we should migrate our application from access Jet to either MySQL or MSSQL DBMS. We don't seem to have this problem when tested on an msSQL server. 

Could it be that older versions of the ODBC drivers will not have the same problem???
[31 Aug 2004 17:26] MySQL Verification Team
Test with Access

Attachment: bug2966.JPG (image/jpeg, text), 31.79 KiB.

[31 Aug 2004 17:30] MySQL Verification Team
Verified with Access. See picture attached.
[31 Aug 2004 17:31] MySQL Verification Team
Forgot to update the status.
[10 Dec 2005 20:51] rudy hinojosa
try this...

rs.open sqlstring, connectionobject , 1,3,1
[20 Apr 2006 21:15] Aaron Humphrey
I just downloaded mysql-connector-odbc-3.51.12, and I am still seeing the problem.

I created a table in the "test" database as follows:

create table reserved (id serial, `default` varchar(15));

Then I run the following code under Visual Basic 6:

    Dim cnTest As ADODB.Connection
    Dim rsTest As ADODB.Recordset

    Set cnTest = New ADODB.Connection

    cnTest.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=oracle;DATABASE=test;UID=root;PWD=root;OPTION=3"

    Set rsTest = New ADODB.Recordset
    rsTest.Open "SELECT id, `default` FROM reserved", cnTest, adOpenKeyset, adLockOptimistic, adCmdText

    rsTest!Default = "Test"

It crashes on the Update with the error:

[MySQL][ODBC 3.51 Driver][mysqld-5.0.20-standard]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default) VALUES('Test')' at line 1

Patently it is internally constructing an INSERT statement without escaping the field names.  Earlier suggestions to try escaping the field name in VB are wrongheaded; ADO and ODBC are supposed to handle all that.
[26 Jun 2007 16:09] Dylan Evans
The priority of this fix should be seriously bumped up. This is an extremely critical issue that needs fixing asap.
[26 Jun 2007 18:27] Jim Winstead
Re-opening this old bug -- we need to figure out how to resolve this (and there are later duplicate filings).

To be clear, the quoting problem is on the ADO/VB side -- queries that are generated within the driver always quote the identifiers correctly. This impacts both identifiers that are reserved words, and identifiers that contain non-alphanumeric characters.
[26 Jun 2007 18:32] Jim Winstead
Bug #19875 has been marked as a duplicate of this bug.
[18 Jul 2007 19:58] Jim Winstead
Bug #13121 was marked as a duplicate of this bug.
[8 Oct 2007 17:31] Jim Winstead
We are waiting on feedback from Microsoft as to whether this is an ADO problem or something we are not doing correctly in our driver.
[9 Oct 2007 21:24] Jim Winstead
This bug does not occur with the new 5.1 alpha driver, although it is not clear why.
[13 Oct 2007 15:25] Jose Manuel Sanchez Baez
I tried with connector/ODBC 5 and it happend the same error.

Public Function alta()
    Dim rs As adodb.Recordset
    Dim str As String
    Set rs = New adodb.Recordset
    rs.Open "t_persona", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable
    rs!nombre = "hola"
    rs!apellido1 = "hola1"
    rs!apellido2 = "hola2"
    Debug.Print rs!id
    Set rs = Nothing
End Function

When it try to recover the id -> ERROR.

Error: The record is deleted.
[15 Oct 2007 10:46] Susanne Ebrecht
Hello Jose Manuel,

Jim recommended to use the version 5.1 not the version 5.0.
Please try it again with MyODBC version 5.1

[18 Oct 2007 10:19] Jose Manuel Sanchez Baez
Thanks Susanne but using Connector ODBC 5.1 I got the same error.
[17 Dec 2007 20:36] Jim Winstead
This works fine with Connector/ODBC 5.1.1. It looks like this may be a bug (in ADO) that only occurs when a non-Unicode driver is used with ADO.
[2 Sep 2011 23:44] Javier Santiago
No puedo solucionar este problema. EL CONECTOR ODBC NO ADMITE LA OPERACION SOLICITADA... EL error sucede en: rs.Open Str, cnn, adOpenDynamic, adLockOptimistic, help me please.!