Difference between revisions of "EclipseLink/Development/DBWS/ImprovedDataDictionaryQueries"

Revision as of 14:17, 22 September 2008

DBWS Improved DataDictionary Queries

Document History

DBWS utility enhancement: Improved DataDictionary Queries

bug 234681
The DBWSBuilder utility

Some Issues with JDBC metadata

The key to supporting multiple database platforms (Oracle, DB2, Sybase, MS SQLServer, etc.) lies in the ability to extract meta-data for Stored Procedures and Tables from the database. The standard JDBC meta-data APIs (available through the java.sql.DatabaseMetaData interface) unfortunately is not fully implemented across vendors, nor in a standard fashion. For example, the simple act of getting the names of available databases is different across platforms: on Oracle the getSchemas() method is used while for DB2/Sybase/MS SQLServer the getCatalogs() method is used. Another example of cross-platform meta-data issues is the getColumns method: for PostgreSQL, the name of the table must be lower-case; for Oracle, it must be upper-case; others support mixed-case. The following is a case-study that expands on these 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())