| Bug #1914 | Add support for {fn CONVERT | ||
|---|---|---|---|
| Submitted: | 21 Nov 2003 16:50 | Modified: | 2 Dec 2003 12:40 |
| Reporter: | Kevin Grittner | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S4 (Feature request) |
| Version: | 3.0.9-stable | OS: | |
| Assigned to: | Target Version: | ||
| Triage: | D5 (Feature request) | ||
[2 Dec 2003 12:40]
Mark Matthews
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!
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.