|
|
| (56 intermediate revisions by the same user not shown) |
| Line 12: |
Line 12: |
| | | Mike Norman | | | Mike Norman |
| | | 1.0 | | | 1.0 |
| | + | |- |
| | + | | 081130 |
| | + | | Mike Norman |
| | + | | 2.0 - use JPub to get metadata |
| | |} | | |} |
| | | | |
| | == DBWS utility enhancement: Improved DataDictionary Queries == | | == DBWS utility enhancement: Improved DataDictionary Queries == |
| − | | + | With the decision to use a stripped-down version of JPub to get database metadata, [https://bugs.eclipse.org/bugs/show_bug.cgi?id=234681 bug 234681 ] |
| − | === Motivation ===
| + | changes from <br /> 'Improve DBWSBuilder utility's use of database metadata to support more auto-generated cases' to <br /> |
| − | | + | 'DBWSBuilder utility auto-generate XR artifacts for complex PL/SQL arguments' |
| − | [https://bugs.eclipse.org/bugs/show_bug.cgi?id=234681 bug 234681 ] <br/> | + | |
| − | The key for the DBWSBuilder to support 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 <b><code>java.sql.DatabaseMetaData</code></b> interface) unfortunately is not fully implemented across vendors, nor in a standard fashion.
| + | |
| − | For example, the simple act of getting the <b><i>names</i></b> of available databases is different across platforms: on Oracle the <b><code>getSchemas()</code></b> method is used while for DB2/Sybase/MS SQLServer the <b><code>getCatalogs()</code></b> method is used. Another example of cross-platform meta-data issues is the <b><code>getColumns</code></b> method: for PostgreSQL, the name of the table <u>must</u> be lower-case; for Oracle, it <u>must</u> be upper-case; others support mixed-case. [http://www.sun.com/bigadmin/content/submitted/jdbc_drivers.html The following is a case-study that expands on these issues].
| + | |
| − | | + | |
| − | <b>Oracle</b><br/>
| + | |
| − | * driver returns wrong value of column size (versions 9.2.0.6,10.0.1.4)<br/>When tables are created with NLS_LENGTH_SEMANTICS='CHAR', the method <code>ResultSet DatabaseMetaData.getColumns(....)</code> 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 <code>oracle.jdbc.driver.OracleDatabaseMetaData</code> available in version 10.2.0.2.0.
| + | |
| − | * <code>databaseMetaData.getColumns</code>
| + | |
| − | ** 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 <code>setIncludeSynonyms(boolean)</code> method is used. Similar to <code>setRemarksReporting</code>, <code>getColumns</code> 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 <code>getColumns</code>. 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 <code>DriverManager.getConnection</code>.
| + | |
| − | * <code>databaseMetaData.getProcedureColumns</code> 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. <code>param1 OUT NOCOPY VARCHAR2</code>).<br/> Workaround:<br/>
| + | |
| − | <source lang=java>
| + | |
| − | ...
| + | |
| − | // use Oracle-specific type
| + | |
| − | statement.registerOutParameter(i, oracle.jdbc.OracleTypes.VARCHAR);
| + | |
| − | </source>
| + | |
| − | * <code>databaseMetaData.getIndexInfo()</code> 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.
| + | |
| − | | + | |
| − | <b>IBM AS/400 via JTOpen</b><br/>
| + | |
| − | * <code>databaseMetaData.getColumns</code>
| + | |
| − | ** 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:<br/>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 <code>getTableName()</code> and <code>getSchema()</code> 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.
| + | |
| − | | + | |
| − | <b>ODBC-JDBC Bridge</b><br/>
| + | |
| − | * MS-ACCESS: <code>getPrimaryKeys</code> not supported
| + | |
| − | | + | |
| − | <b>Apache Derby (a.k.a. JavaDB)</b><br/>
| + | |
| − | * metadata for Stored Functions are only available with a JDBC 4 compliant driver (Oracle returns metadata for storedFunctions via <code>databaseMetaData.getProcedures()</code>)
| + | |
With the decision to use a stripped-down version of JPub to get database metadata, bug 234681
changes from
'Improve DBWSBuilder utility's use of database metadata to support more auto-generated cases' to
'DBWSBuilder utility auto-generate XR artifacts for complex PL/SQL arguments'
