If you add a new datatype to a server release (e.g., BOOLEAN or XML), then you must specify the following:
1) A legacy datatype to which the new type maps when the server talks to old clients. The legacy type is some datatype in JDBC 2.0 java.sql.Types. This is the type which old clients see in DatabaseMetaData and ResultSetMetaData. 2) A pair of ResultSet.get() and PreparedStatement.set() methods which old clients can use to access the new datavalues. These must be get() and set() methods which appear in JDBC 2.0 ResultSet and PreparedStatement. They should be the get() and set() methods most natural to the legacy datatype in (1). These methods determine how the datavalues flow across DRDA.
I'm attaching a table which shows mappings for XML and the other new 10.2 datatypes. I have omitted ROWID right now because I don't understand it yet.
Cheers, -Rick Lance J. Andersen wrote:
Satheesh Bandaram wrote:Hi Rick, Rick Hillegas wrote:We will probably have to collaborate here. If you select an XML column, the ResultSetMetaData needs to say that the column is of type java.sql.SQLXML. This, at least, was the consensus of the community which prevented me from re-enabling the BOOLEAN datatype a couple months ago: Kathey and David pointed out that it was not OK for ResultSetMetaData to report a boolean column's type as SMALLINT. Similarly, it's not going to be OK for ResultSetMetaData to report the type of an XML column as java.sql.LONGVARCHAR.It doesn't seem right to expose a JDBC 4.0 type to a JDBC 3.0 client. What good would that do, since JDBC 3.0 clients are probably written to expect JDBC 3.0 functionality only? I wonder if we might even break GUIs and other generic tools that expect 3.0 types only. Since we may be close to enabling full JDBC 4.0 in the near term, why not expose only JDBC 3.0 types to 3.0 clients? That is to expose XML type as a CLOB under JDBC 3.0 and as SQLXML for a JDBC 4.0 client. This would expose a new type to Derby server, XML, to JDBC by mapping it to it's closest match. Remember the TINYINT discussion and Dan's point?A JDBC 3 driver should not return SQLXML as a type in ResultSetMetaData.I am not sure TINYINT is the same discussion though as JDBC has provided support for this since JDBC 1.0.x.A JDBC 3 driver should only return results defined in classes for the given version of JDBC and since JDBC 3 did not have SQLXML in java.sql.Types, you need to return a different value.Correct, if the datatype is in Types.java and the backend datatype is also there, they should map to the same (unless there is something major league wrong with what the meaning of the type is on the backend)I don't remember what was discussed about this during the BOOLEAN discussion... but if JDBC already supports a boolean type, that would seem correct to map it to, rather than say SMALLINT.Regards LanceSatheesh
Title: New 10.2 Derby Datatypes
New 10.2 Derby Datatypes
This table describes how to map new 10.2 datatypes to legacy types supported by old Derby clients under JDBC 2.0:
New type | Legacy type | Legacy get() | Legacy set() |
TINYINT | SMALLINT | ResultSet.getShort() | PreparedStatement.setShort() |
BOOLEAN | SMALLINT | ResultSet.getShort() | PreparedStatement.setShort() |
NCHAR | CHAR | ResultSet.getString() | PreparedStatement.setString() |
NVARCHAR | VARCHAR | ResultSet.getString() | PreparedStatement.setString() |
LONGNVARCHAR | LONGVARCHAR | ResultSet.getCharacterStream() | PreparedStatement.setCharacterStream() |
NCLOB | CLOB | ResultSet.getCharacterStream() | PreparedStatement.setCharacterStream() |
SQLXML | CLOB | ResultSet.getCharacterStream() | PreparedStatement.setCharacterStream() |