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: | 3.51.12.00 | OS: | Windows (Win XP Pro (SP2)) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[17 May 2006 10:00]
Chris Lockwood
[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 .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.
[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.