Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Development/DBWS/ImprovedDataDictionaryQueries"
(→Motivation) |
(→Motivation) |
||
Line 20: | Line 20: | ||
[https://bugs.eclipse.org/bugs/show_bug.cgi?id=234681 bug 234681 ] <br/> | [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. | 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] and a | + | 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] and a [[EclipseLink/Development/DBWS/NamingConventionTransformer/OutstandingJDBCDriverIssues|list of outstanding JDBC driver issues is here]]. |
− | + | ||
− | + | ||
<b>Oracle</b><br/> | <b>Oracle</b><br/> |
Revision as of 09:34, 25 September 2008
DBWS Improved DataDictionary Queries
Document History
Date | Author | Version Description & Notes |
---|---|---|
080922 | Mike Norman | 1.0 |
DBWS utility enhancement: Improved DataDictionary Queries
Motivation
bug 234681
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 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 and a list of outstanding JDBC driver issues is here.
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 methodResultSet 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 oforacle.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 tosetRemarksReporting
,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 togetColumns
. 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" toDriverManager.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:
- a PL/SQL stored procedure with an OUT parameter of type VARCHAR2 with a NOCOPY hint (e.g.
... // 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()
andgetSchema()
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()
)
Database Metadata Model Objects Refactoring
Database metadata is represented by Java model objects. In EclipseLink, these model classes are spread across several packages and components:
- o.e.p.internal.helper - DatabaseType, DatabaseTable, DatabaseField
- o.e.p.tools.schemaframework - TableDefinition, FieldDefinition, TypeDefinition
- o.e.p.platform.database.oracle.plsql - PLSQLrecord, PLSQLargument, OraclePLSQLTypes
- (in Utils component) o.e.p.tools.dbws.jdbc - DbTable, DbColumn, DbStoredProcedure
In addition to the above classes not being sufficient for DBWSBuilder's needs, there are some issues:
- code duplication: 3 different classes describe Database tables
- limited or no code reuse: no common base class/interface to extend of implement
- OSGi split-packaging restriction: any revision of class hierarchy structure is 'tricky'