MySQL Bugs: #60149: "Sql Server Mode" required to manage table names in square brackets

Bug #60149	"Sql Server Mode" required to manage table names in square brackets
Submitted:	16 Feb 2011 18:17	Modified:	16 Feb 2011 18:24
Reporter:	Robert Bergs	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Microsoft Excel 2010's PowerPivot tool (and possibly other MS applications) does not work with MySQL as the SQL queries it sends are surrounded by square brackets [].

SELECT [auth_group].* FROM [auth_group]

gives the error:

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 '[auth_group].* FROM [auth_group]' at line 1

How to repeat:
Create a new File DSN with the parameters to connect to a MySQL database, using the MyODBC driver.

Open PowerPivot window.
Select "From Other Sources" from the toolbar.
Click connect to a data source.
Select 'Others (OLEDB / ODBC)'
Select Build
Select the data source name & click OK.
Verify the connection string, it will be like this:

Provider=MSDASQL.1;Persist Security Info=False;DSN=mydsn

Click Next.
Choose "Select from a list of tables"
Click Next.
Select any one of the tables from your database.
Click "Preview & Filter"
The error is shown:

Failed to retrieve data from auth_group. Reason: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-log]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 '[auth_group].*
		FROM [auth_group]' at line 1

Suggested fix:
The .NET connector has a connection option called "Sql Server Mode". When True this handles the case where field names are surrounded by square brackets in the SQL sent by various Microsoft applications.

Please could a similar option be added to MyODBC when configuring a DSN?

Looks like a reasonable feature request.

I have the same problem using ODBC to connect e-Campaign on a Windows PC to MySQL database.  I have to construct view names that do not contain special characters (like spaces) that cause brackets to get inserted.  This does not make for readable view names, especially after a view name is forced to all lower case.

i have te same problem, have you got a solution?

I'd try 'ANSI_QUOTES' and, if there are spaces in names, 'IGNORE_SPACE' too.

It would be really useful to have the sqlservermode=True; option in the ODBC driver to handle the table names in square brackets, or just to handle them natively.