MySQL Bugs: #20025: Cannot update reserved-word-named fields through recordset

Bug #20025	Cannot update reserved-word-named fields through recordset
Submitted:	23 May 2006 19:33	Modified:	17 May 2007 20:24
Reporter:	Aaron Humphrey	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Windows NT)
Assigned to:		CPU Architecture:	Any

Description:
In Visual Basic 6, an error is generated when attempting to update a column whose name is a reserved word by using an ADO recordset.

How to repeat:
Create a table in the "test" database as follows:

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

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

Suggested fix:
There's already code in cursor.c's my_SQLSetPos() method to do the quoting, but it doesn't seem to be working, perhaps because of the VB interface to ODBC.

Hi Aaron, thanks for your problem report.
VS2005 can not compile your code at all (doesn't allow Default word after !:
"C:\TEMP\odbc18883\18883.vb(16) : error BC30516: Overload resolution failed because no accessible 'Fields' accepts this number of arguments."
thus I'm unable to verify your test case... I tried with AddNew("Default","Test") but got exception:
System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146825023
  HelpLink="C:\WINDOWS\HELP\ADO270.CHM#1240649"
  Message="Item cannot be found in the collection corresponding to the requested name or ordinal."
  Source="ADODB.Recordset"
  StackTrace:
       at ADODB.RecordsetClass.AddNew(Object FieldList, Object Values)
       at _18883.mainModule.Main() in C:\TEMP\odbc18883\18883.vb:line 15
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
which has nothing to do with MyODBC and error you reported...
If you can provide any more input on this, please do.

Also, see disscusion in http://bugs.mysql.com/bug.php?id=19875 and resolution given there.

You can replace

rsTest!Default = "Test"

with

rsTest.fields("Default").value = "Test"

As I said, this is a bug with VB6; I can't vouch for it with anything later than that.  We're not planning to move into .NET for a year or two yet.  Chris Lockwood's workaround may not work well for us in general because field names are not hard-coded in most tables, so "SELECT *" is often more useful than hard-coded field lists that may go out of date.

I know there are workarounds, but aliasing(as well as just quoting) every field names in SQL is a pain, and converting all recordset adds to INSERT statements ditto.  I'd make a snarky comment about how the other three databases we support don't have this problem, but I'll restrain myself because none of them are open-source.

Hi Aaron.
Actually error raised comes from "Microsoft OLE DB Provider for ODBC Drivers" according to my debugger.
As for "other databases" you can recheck http://bugs.mysql.com/bug.php?id=20064 and play with google, among others you'll find IBM, Btrieve... having similar problems... Just try typing "SELECT Config, nValue FROM MSysConf" in google and check.
Anyway, this is serious problem for you and I'll put some more work into this.

We have a conflict in behavior here. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html: "A reserved word can be used as an identifier if you quote it." but if you try, for example, .AddNew("`Default`", "Chris")  no statement reaches MyODBC since ADO stops execution...
Verified as described by reporter.
To be checked together with http://bugs.mysql.com/bug.php?id=19875.

Test case:
Module mainModule

    Sub Main()

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

        cnTest = New ADODB.Connection

        cnTest.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=munja;DATABASE=test;UID=root;PWD=;PORT=3307,OPTION=3")

        rsTest = New ADODB.Recordset
        rsTest.Open("SELECT id, `default` FROM reserved", cnTest, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
        rsTest.AddNew()
        rsTest.Fields("default").Value = "Test"
        'rsTest.AddNew("`default`", "Test")
        rsTest.Update()

        rsTest = Nothing
        cnTest = Nothing
    End Sub
End Module

ODBC trace file

Attachment: SQL.LOG (application/octet-stream, text), 56.48 KiB.

This is a duplicate of Bug #19875, even though this is about fields with keywords as names, and the other is about fields with spaces in their names. It's the same underlying problem of identifiers not being quoted.

I still got this problem when i try to update a field which contains chinese
My email address: kingl_xp@126.com