MySQL Bugs: #19875: ADO Recordset (Update) - Badly formed SQL where field names contain spaces

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:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12.00	OS:	Windows (Win XP Pro (SP2))
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
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 (`).

Error Message

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

MySQL Dump File

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

VBS script to recreate problem

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

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.

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
					
            .AddNew
            .Fields("`Logged By`") = "Chris"
					
            .Update
            .Close
					
         End If
      End With

-------------------------------------------------------------------------

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

Enjoy.

Chris great, thanks.

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.

This is a duplicate of Bug #2966.