Bug #19875 ADO Recordset (Update) - Badly formed SQL where field names contain spaces
Submitted: 17 May 2006 10:00 Modified: 26 Jun 2007 18:27
Reporter: Chris Lockwood Email Updates:
Status: Duplicate Impact on me:
Category:Connector / ODBC Severity:S3 (Non-critical)
Version: OS:Microsoft Windows (Win XP Pro (SP2))
Assigned to: Assigned Account CPU Architecture:Any

[17 May 2006 10:00] Chris Lockwood
When calling the update method of a ADO recordset (v2.8 & v2.7) an ODBC driver error is thrown if the name of a source field contains one or more spaces (see attached JPEG).

Seemingly the ODBC driver fails to encapsulate the offending field name(s) in back ticks (`) causing the above syntax error to be thrown.

How to repeat:
Import the attached sql dump file into your mysql server, modify the vbs script constants (host, usr, pwd) using a text editor.

Execute the script on a Windows XP (SP2) box running 2.7 or later.

If followed correctly the error displayed should resemble that of the attached JPEG.

Suggested fix:
Ensure the ODBC driver encapsulates field names containing spaces with ticks (`).
[17 May 2006 10:01] Chris Lockwood
Error Message

Attachment: error.JPG (image/pjpeg, text), 26.39 KiB.

[17 May 2006 10:01] Chris Lockwood
MySQL Dump File

Attachment: recreate.sql (text/plain), 1.88 KiB.

[17 May 2006 10:02] Chris Lockwood
VBS script to recreate problem

Attachment: recreate.vbs (application/octet-stream, text), 898 bytes.

[19 May 2006 10:57] Tonci Grgin
Hi Chris.
Thanks for complete test case. I was able to reproduce your problem in VS2005.
But if you try .AddNew("`Logged By`", "Chris") you will see that no statement reaches MyODBC since ADO stops execution! Syntax used (`Logged By`) works in cl client and other clients *not* using ADO. MyODBC is a connector receiving requests from upper level code and, in this case, ADO refuses to pass valid statement to MyODBC due to it's designers choice to treat backtick as valid column name character which is stupid. There are a lot of stupid things related to ADO, you can check some of them in bug #19065.
Anyway problem exists but it should be treated as "...minor loss of service..." (S3) since you're asking for parser enhancements and several workarounds are available.
For now, you can try forming UPDATE statement by yourself : "INSERT INTO notes (`Logged By`) VALUES ("Chris")" or abandon column names with spaces as noted in manual: http://dev.mysql.com/doc/refman/5.0/en/legal-names.html.
[22 May 2006 10:30] Chris Lockwood
I was kind of expecting that response.

I have however descovered another simple solution to this problems which 
goes something like this:

                        modified code snippet from recreate.vbs
   With rs

      .Open "SELECT `logged By` AS '`logged by`' FROM `notes`", Conn, 2, 3
         If .State = 1 Then
            .Fields("`Logged By`") = "Chris"
         End If
      End With


It seems that creating a field alias in the SQL string tricks ADO into actually
doing what its told.

[22 May 2006 11:11] Tonci Grgin
Chris great, thanks.
[17 May 2007 20:25] Jim Winstead
Bug #20025 was marked as a duplicate of this bug. Fields with names that are the same as reserved words also fail because they aren't getting quoted.
[26 Jun 2007 18:27] Jim Winstead
This is a duplicate of Bug #2966.