MySQL Bugs: #1914: Add support for {fn CONVERT

Bug #1914	Add support for {fn CONVERT
Submitted:	21 Nov 2003 15:50	Modified:	2 Dec 2003 11:40
Reporter:	Kevin Grittner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	3.0.9-stable	OS:
Assigned to:		CPU Architecture:	Any

Description:
To run code which is written to be portable between database products, support for the JDBC escape sequence for the CONVERT function is needed.  MySQL itself supports both CONVERT and CAST; however, the MySQL Connector/J product doesn't support the escape sequence to map to either of these from portable code.

How to repeat:
With this feature, the following statements should be equivalent when the second column is type SMALLINT:

statement.executeUpdate("INSERT INTO NoPortableConvert VALUES (1,{fn CONVERT('123', SQL_SMALLINT)})");

statement.executeUpdate("INSERT INTO NoPortableConvert VALUES (1,123)");

Currently, the first statement throws:
java.sql.SQLException: Syntax error or access violation,  message from server: "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 '))' at line 1"
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
	at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2027)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:1984)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1248)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1194)

With this feature, the DatabaseMetaData.supportsConvert() method would return true, and the DatabaseMetaData.supportsConvert(int, int) would specify valid combinations.

This is fixed for 3.0.10, and will be in the nightly snapshot builds of 3.0 after 00:00 GMT tomorrow, however, the types that are supported depend on the version of MySQL you are using (full JDBC-compliant support is offered for MySQL > 4.0.2), and currently the only types supported are ones specified by the JDBC spec, which according to my copy is:

BIGINT, BINARY,
BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARBINARY,
LONGVARCHAR, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY,
and VARCHAR.

(JDBC 1.1. spec, page 36)

I haven't been able to find where the JDBC 1.1 spec talks about prefacing these tokens with a 'SQL_', or any revisions to this in any newer spec. If you could point out where this is specified, I can look at implementing the SQL_ style of these tokens.

The JDBC spec also seems to be unclear as to whether DatabaseMetaData.supportsConvert() relates to the {fn convert ...} escape sequence, from the JDBC-3.0 spec:

"14.2.3.1 Data Type Conversions
The recommended ResultSet getter method for each JDBC type is shown in
TABLE B-6 on page B-182. This table also shows all of the possible conversions that a
JDBC driver may support. The method
DataBaseMetaData.supportsConvert(int fromType, int toType)
returns true if the driver supports the given conversion."

Which appears to me that the Connector/J's current version of supportsConvert() is correct (i.e not tied to escape codes). I will ask for clarification on this issue with the JDBC spec lead, however if you have evidence that this is not the case, please let me know!