MySQL Bugs: #2966: ADO Recordset Update

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:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51 and 5.1	OS:	Windows (Windows XP)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
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.AddNew
    RS("Unique")=1
    RS("Limit")=2
    RS("Desc")="Error Test"
    RS.Update
 

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.

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.

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?

Could you please try to use escaping:

RS("\`Unique\`")=1

Thanks.

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.

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.AddNew
 RS!Unique = 1
 RS!Limit  = 2
 RS!Desc   = "Error Test"

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???

Test with Access

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

Verified with Access. See picture attached.

Forgot to update the status.

try this...

rs.open sqlstring, connectionobject , 1,3,1

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.AddNew
    rsTest!Default = "Test"
    rsTest.Update

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.

The priority of this fix should be seriously bumped up. This is an extremely critical issue that needs fixing asap.

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.

Bug #19875 has been marked as a duplicate of this bug.

Bug #13121 was marked as a duplicate of this bug.

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.

This bug does not occur with the new 5.1 alpha driver, although it is not clear why.

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.AddNew
    rs!nombre = "hola"
    rs!apellido1 = "hola1"
    rs!apellido2 = "hola2"
    rs.Update
    Debug.Print rs!id
    rs.Close
    Set rs = Nothing
End Function

When it try to recover the id -> ERROR.

Error: The record is deleted.

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

Susanne

Thanks Susanne but using Connector ODBC 5.1 I got the same error.

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.

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.!