MySQL Bugs: #7882: Function CONVERT ODBC database independent types not translated

Bug #7882	Function CONVERT ODBC database independent types not translated
Submitted:	13 Jan 2005 18:02	Modified:	29 Sep 2008 21:17
Reporter:	[ name withheld ]	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	all	OS:	Any (all)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	affects_connectors

Description:
For the function CONVERT ODBC defines
databse independent data types like SQL_CHAR or SQL_VARCHAR
which should be used for database independent
queries.
The data types are normally translated by the ODBC driver
(or the database server) to its local data types.

example:

SELECT {fn CONVERT(1,SQL_CHAR)}

is not working

SELECT {fn CONVERT(1,char)}

is working, but not ODBC conform.

For further information, lookup the MS ODBC specification.

How to repeat:
execute 

SELECT {fn CONVERT(1,SQL_CHAR)}

Suggested fix:
implementation in the odbc driver or the server

confirm.

bash-2.05b$ isql mytest
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select {fn CONVERT(1,SQL_CHAR)};
[ISQL]ERROR: Could not SQLExecute
SQL> select {fn CONVERT(1,char)};
+---------------------+
| {fn CONVERT(1,char)}|
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

The error I am getting is coming from below the ODBC driver (read; server). Try myodbc3m with -v. The server *should* support the native types *and* the standard SQL types. This should be reassigned to the server group (mark).

Useful info; 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_minimum_...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_92_cast_...

Note; Min SQL supported by ODBC is supposed to be a subset of SQL-92 but in this case it also means supporting SQL_* data types. These types are tranlated either in the driver or at the server. This driver does not have a SQL parser so I suggest we get the server to support the SQL_* types - at least under certian circumstances - such as this.

We'd like to move support for all of these escape sequences into the server. Currently, fixing this in ODBC requires developing an almost full-fledged parser in the client, which is going backwards, since JDBC also needs the same functionality (and includes a partial parser to deal with common cases of escape syntax).

mysql> SELECT {fn CONVERT(1,CHAR)};
+----------------------+
| {fn CONVERT(1,CHAR)} |
+----------------------+
| 1                    | 
+----------------------+
1 row in set (0.00 sec)

Escape sequences are supported by the parser now (starting from 5.0 I believe).
However, there is no such type SQL_CHAR, and the parser rightfully chokes on it.
Moving this bug back to ODBC group and re-opening it.