EclipseLink/Development/DBWS/ImprovedDataDictionaryQueries/OutstandingJDBCIssues

Outstanding JDBC Issues

Oracle

• driver returns wrong value of column size (versions 9.2.0.6,10.0.1.4)
  When tables are created with NLS_LENGTH_SEMANTICS='CHAR', the method ResultSet DatabaseMetaData.getColumns(....) returns the maximum number of characters (for char/date types; for numeric/decimal types returns precision). In actual fact, the method returns the number of bytes, not the number of characters. This problem is solved by an updated version of oracle.jdbc.driver.OracleDatabaseMetaData available in version 10.2.0.2.0.
• databaseMetaData.getColumns
  • driver doesn't return INTEGER data type, instead it returns 3 ("NUMBER") with column precision of 22 and scale 0.
  • does not work for SYNONYMS unless the setIncludeSynonyms(boolean) method is used. Similar to setRemarksReporting, getColumns performs slowly if information about synonyms is to be included as it requires an expensive outer join. By default, the JDBC driver will not report information about table columns when you pass a synonym to getColumns. You can get synonym information by passing true to this method, and turn it off by passing false. You can also control this behavior by passing a property named "includeSynonyms" as "true" to DriverManager.getConnection.
• databaseMetaData.getProcedureColumns will not return the 'null' column for Stored Procedures that have no arguments, while the Oracle data dictionary view USER_PROCEDURES will (since 9.x).
• An exception is thrown when the size of an OUT parameter exceeds ~2000 characters.
  • a PL/SQL stored procedure with an OUT parameter of type VARCHAR2 with a NOCOPY hint (e.g. param1 OUT NOCOPY VARCHAR2).
    Workaround:
    

...
// use Oracle-specific type
statement.registerOutParameter(i, oracle.jdbc.OracleTypes.VARCHAR);

• databaseMetaData.getIndexInfo() does not work for VIEWS. A SQLException with the following message is thrown: "ORA-01702: a view is not appropriate here: can't retrieve indexInfo"; other platforms just ignore.

IBM AS/400 via JTOpen

• databaseMetaData.getColumns
  • the CHAR_OCTET_LENGTH and ORDINAL_POSITION columns of the result set are not supported in Toolbox versions prior to V5R1 (JTOpen 2.x).
  • possible workarounds:
    For CHAR_OCTET_LENGTH, which is supposed to indicate the maximum number of bytes in the column, use the value in the COLUMN_SIZE. Multiply this value by 2 if the column holds DBCS characters. For ORDINAL_POSITION, which is supposed to indicate the index of the column in a table, run a query for all columns in the table ("SELECT * FROM table where 1=0"), and then issue findColumn() on the result set to determine the column index from the column name.
• In the ResultSetMetaData object, the getTableName() and getSchema() methods are not supported in Toolbox versions prior to V5R2 (JTOpen 3.x).
  • In supported Toolbox versions, these methods work only if the "extended metadata" connection property has been set to True and only when the target server is at the V5R2 operating system or later. Because retrieving the additional information may incur a performance penalty, this property is set to False by default.

ODBC-JDBC Bridge

• MS-ACCESS: getPrimaryKeys not supported

Apache Derby (a.k.a. JavaDB)

• metadata for Stored Functions are only available with a JDBC 4 compliant driver (Oracle returns metadata for storedFunctions via databaseMetaData.getProcedures())