MySQL Bugs: #63045: SQLColumns default literal value is not quoted

Bug #63045	SQLColumns default literal value is not quoted
Submitted:	31 Oct 2011 18:52	Modified:	1 Nov 2011 14:55
Reporter:	Farid Zidan (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.01.09	OS:	Windows (64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Both 3.51.29 and 5.1.9 MySQL ODBC driver do not quote literal returned in call to SQLColumns for COLUMN_DEF in the returned resultset.

This makes it very difficult for client to script a table since it scripting a literal default in create table MySQL statement will result in error and there is no easy way to determine whether the default should be quoted as literal or not (example, CURRENT_TIMESTAMP should not be quoted, but a default of 'D' should be.

Examplle,
create table test_default(col1 char(1) default 'N')

SQLColumns default value for col1 is returne as N rather than 'N'

How to repeat:
See above

Suggested fix:
Return defaults quoted/not quoted as exactly given in the table create syntax ddl. If the default quoted in create table dll then SQLColumns should returned as quoted, otherwise not quoted.

MS DOC for SQLColumns
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683(v=vs.85).aspx

COLUMN_DEF (ODBC 3.0)
13
Varchar
The default value of the column. The value in this column should be interpreted as a string if it is enclosed in quotation marks.

I do not see any demand on that http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683%28v=vs.85%29.aspx page to return default value quoted. It just says that if it is quoted, it should be interpreted as string. Data type for column actually defines the interpretation, I'd say.

So, do you see default value for the char(1) column quoted when working with any other RDBMS?

Actually, I had the wrong issue. I was getting the default info from information_schema.columns.column_default which exhibit the describe behavior of not quoted literals.

All other DBMSs that I have tested such as MS SQL Server, Oracle, DB2, Informix, etc store column default in system catalog view/table with quotes when necessary, unlike MySQL.

The default value returned by SQLColumns is appropriately quoted. It has other issues, but I can work around them.

Since SQLColumns is returning column default, unlike the other mentioned DBMSs, there is no need for me to access MySQL information schema to get the column defaults (I was only doing that because previous versions of MySQL ODBC driver did not return info for SQLColumn column default).

Thank you