MySQL Bugs: #9767: Updating Recordsets via ADO

Bug #9767	Updating Recordsets via ADO
Submitted:	8 Apr 2005 16:21	Modified:	14 Jun 2013 10:56
Reporter:	Jay Alverson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	4.1/3.51	OS:	Windows (Windows)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
If you have a table that has a column with a space in the name, and you use ADO to update the table via a recordset, you'll get an error that your syntax is incorrect.

For some reason the driver doesn't build the SQL statement to do the insert/update correctly.

This is similar to bug #2966, but occurs even if you don't specifically reference the column's name (via a SELECT *...).

How to repeat:
Create a table in MySQL, create a text column with a space in the name like "the column". Put data into the table.

Use ADO via ODBC to connect to the database via a DSN. open the recordset using a select * statement (not using any column names).

After opening the recordset, update one of the columns (not the one with a space in the name) using the 

rs.fields(3).value = NewValue

Now, even though you did not reference the column with a space in the name, you'll get an error.

Suggested fix:
Update the driver to find columns with spaces and place `column name` around those columns (or all columns).

Could you please show a test case like was done in bug:

http://bugs.mysql.com/bug.php?id=9009

this makes easy to understand and test the bug report.

Thanks in advance.

#include "C:\Test\MySQL\MySQLFunctions.wbt"
#include "C:\Test\MySQL\MySQL ADO Config.wbt"

DSN = "MySQLTest"
DBASE = "test"

MySQLADOSetup()

rs = ObjectCreate("ADODB.Recordset")
sqlstr = "select * from  FileTable order by 1 asc"  ;<-- select all fields/records...
;
rs.Open(sqlstr, cConn, 1,4,1)  ;<-- doesn't matter what combo 
;											of cursor/rec locking you use...
;
clipput("")
while !rs.eof
	for x = 0 to rs.fields.count-1
		message(x, rs.fields(x).name)
		clipappend(strcat("Field ", x, "-- ", rs.fields(x).name, @crlf))
	next
	rs.fields(2).value = 577  ;<-- update a field WITH NO SPACE in the column name
	rs.updatebatch()
	rs.movenext
endwhile
rs.close
;
rs = 0
DB = 0
message("Debug", "Done")
;
exit

Output of the field names

Field 0-- FileName
Field 1-- SizeOfDLL
Field 2-- DateOfDLL
Field 3-- Owner Of DLL

Here's the limited debug output from my script...

[COM Exception]
Microsoft OLE DB Provider for ODBC Drivers=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 'Of DLL='New Owner' WHERE (FileName='6to4svc.dll' 
AND SizeOfDLL=35840 AND DateOfD' at line 1

[COM Sub-system]
Function=InvokeMember
ErrorCode=9 (0x80020009)
ErrorDesc=Exception occurred.

If you go to MySQLQueryBrowser or use the command line utility and change the 
field  `Owner Of DLL` to `OwnerOfDLL` (remove spaces) it works fine.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

this bug needs to be re-checked

VB test case\

Attachment: test9767.vbs (application/octet-stream, text), 771 bytes.

VB test case

Attachment: test9767.vbs (application/octet-stream, text), 771 bytes.

The test case works well with MyODBC 5.2.5. The problem probably was fixed ages ago. Closing the bug.