Unable to pull script for views in MySQL

Symptom: You run into an error when displaying the script for any view in WinSQL. This only occurs when using ODBC driver version 8 from Oracle.

Details

WinSQL tries to submit the following query when pulling information for any view:

SHOW CREATE VIEW ?

This query is dynamically created using plugins. The last parameter (the question mark) gets replaced by an actual value. An example of a real query that pulls the script for a view called MyView is:

SHOW CREATE VIEW MyView

However, WinSQL generates the following query due to a bug in MySQL's ODBC driver:

SHOW CREATE VIEW 'MyView'

Notice the single-quotes around MyView, which causes a syntax error to occur.

Bug in MySQL Driver

This bug is related to the SQLGetTypeInfo function. According to the ODBC specs, this function is supposed to return a NULL value for types where prefix and suffix are not applicable.

Since the driver does not return a NULL for integers, WinSQL puts single quotes around the parameter, creating this problem.

Reproducing this problem

This bug is easily reproducible using an ODBC driver testing utility from Microsoft. Use the following steps to reproduce it.

• Download ODBC Test and launch it.
• Press Ctrl+F to connect
• Click Catalog/GetSQLTypeInfo and click the Ok button
• Press Ctrl+R to fetch the results
• Notice the values for LITERAL_PREFIX and LITERAL_SUFFIX is always a single quote, regardless of the data type. See image below
  
  

Work-around

Use the older version of MySQL's ODBC driver. This problem does not exist with version 5.x of the driver.