Return-Path: Delivered-To: apmail-db-ddlutils-dev-archive@www.apache.org Received: (qmail 80738 invoked from network); 27 Dec 2005 00:35:06 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 27 Dec 2005 00:35:06 -0000 Received: (qmail 32602 invoked by uid 500); 27 Dec 2005 00:35:06 -0000 Delivered-To: apmail-db-ddlutils-dev-archive@db.apache.org Received: (qmail 32580 invoked by uid 500); 27 Dec 2005 00:35:05 -0000 Mailing-List: contact ddlutils-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: ddlutils-dev@db.apache.org Delivered-To: mailing list ddlutils-dev@db.apache.org Received: (qmail 32569 invoked by uid 500); 27 Dec 2005 00:35:05 -0000 Delivered-To: apmail-db-ddlutils-commits@db.apache.org Received: (qmail 32566 invoked by uid 99); 27 Dec 2005 00:35:05 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 26 Dec 2005 16:35:05 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 26 Dec 2005 16:35:04 -0800 Received: (qmail 80397 invoked by uid 65534); 27 Dec 2005 00:34:44 -0000 Message-ID: <20051227003444.80395.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r359147 - /db/ddlutils/trunk/src/java/org/apache/ddlutils/task/DumpMetadataTask.java Date: Tue, 27 Dec 2005 00:34:43 -0000 To: ddlutils-commits@db.apache.org From: tomdz@apache.org X-Mailer: svnmailer-1.0.5 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: tomdz Date: Mon Dec 26 16:34:36 2005 New Revision: 359147 URL: http://svn.apache.org/viewcvs?rev=359147&view=rev Log: Enhanced utility task for dumping a database's JDBC meta data Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/task/DumpMetadataTask.java Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/task/DumpMetadataTask.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/task/DumpMetadataTask.java?rev=359147&r1=359146&r2=359147&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/task/DumpMetadataTask.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/task/DumpMetadataTask.java Mon Dec 26 16:34:36 2005 @@ -49,7 +49,7 @@ public class DumpMetadataTask extends Task { /** Methods that are filtered when enumerating the properties. */ - private static final String[] IGNORED_PROPERTY_METHODS = { "getConnection", "getCatalogs" }; + private static final String[] IGNORED_PROPERTY_METHODS = { "getConnection", "getCatalogs", "getSchemas" }; /** The data source to use for accessing the database. */ private BasicDataSource _dataSource; @@ -136,7 +136,7 @@ * @param element The XML element * @param metaData The meta data */ - private void dumpMetaData(Element element, DatabaseMetaData metaData) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException + private void dumpMetaData(Element element, DatabaseMetaData metaData) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, SQLException { // We rather iterate over the methods because most metadata properties // do not follow the bean naming standard @@ -155,7 +155,8 @@ dumpProperty(element, metaData, methods[idx]); } } - dumpCatalogs(element, metaData); + dumpCatalogsAndSchemas(element, metaData); + dumpProcedures(element, metaData); } /** @@ -302,232 +303,636 @@ } /** - * Dumps the catalogs of the database. + * Dumps the catalogs and schemas of the database. * * @param parent The parent element * @param metaData The database meta data */ - private void dumpCatalogs(Element parent, DatabaseMetaData metaData) + private void dumpCatalogsAndSchemas(Element parent, DatabaseMetaData metaData) throws SQLException { - Element catalogsElem = parent.addElement("catalogs"); ArrayList tableTypeList = new ArrayList(); // First we need the list of supported table types - try + ResultSet result = metaData.getTableTypes(); + + while (result.next()) { - ResultSet result = metaData.getTableTypes(); - - while (result.next()) + tableTypeList.add(getString(result, "TABLE_TYPE")); + } + result.close(); + + String[] tableTypes = (String[])tableTypeList.toArray(new String[tableTypeList.size()]); + Element catalogsElem = parent.addElement("catalogs"); + + // Next we determine and dump the catalogs + result = metaData.getCatalogs(); + + while (result.next()) + { + String catalogName = getString(result, "TABLE_CAT"); + + if ((catalogName != null) && (catalogName.length() > 0)) { - tableTypeList.add(result.getString("TABLE_TYPE")); + Element catalogElem = catalogsElem.addElement("catalog"); + + catalogElem.addAttribute("name", catalogName); } } - catch (Exception ex) - {} + result.close(); - String[] tableTypes = (String[])tableTypeList.toArray(new String[tableTypeList.size()]); + Element schemasElem = parent.addElement("schemas"); - // Next we determine and dump the catalogs - try + // We also dump the schemas (some dbs only support one of the two) + result = metaData.getSchemas(); + + while (result.next()) { - ResultSet result = metaData.getCatalogs(); - - while (result.next()) + String schemaName = getString(result, "TABLE_SCHEM"); + + if ((schemaName != null) && (schemaName.length() > 0)) { - dumpCatalog(catalogsElem, metaData, result.getString("TABLE_CAT"), tableTypes); + Element schemaElem = schemasElem.addElement("schema"); + + schemaElem.addAttribute("name", schemaName); } } - catch (Exception ex) - {} + result.close(); + + dumpTables(parent, metaData, tableTypes); + } + + /** + * Dumps all tables. + * + * @param parent The parent element + * @param metaData The database metadata + * @param tableTypes The table types + */ + private void dumpTables(Element parent, DatabaseMetaData metaData, String[] tableTypes) throws SQLException + { + ResultSet result = metaData.getTables("%", "%", "%", tableTypes); + Element tablesElem = parent.addElement("tables"); + Set columns = getColumnsInResultSet(result); + + while (result.next()) + { + String tableName = getString(result, "TABLE_NAME"); + + if ((tableName == null) || (tableName.length() == 0)) + { + continue; + } + + Element tableElem = tablesElem.addElement("table"); + + tableElem.addAttribute("name", tableName); + addStringAttribute(result, columns, "TABLE_CAT", tableElem, "catalog"); + addStringAttribute(result, columns, "TABLE_SCHEM", tableElem, "schema"); + addStringAttribute(result, columns, "TABLE_TYPE", tableElem, "type"); + addStringAttribute(result, columns, "REMARKS", tableElem, "remarks"); + addStringAttribute(result, columns, "TYPE_NAME", tableElem, "typeName"); + addStringAttribute(result, columns, "TYPE_CAT", tableElem, "typeCatalog"); + addStringAttribute(result, columns, "TYPE_SCHEM", tableElem, "typeSchema"); + addStringAttribute(result, columns, "SELF_REFERENCING_COL_NAME", tableElem, "identifierColumn"); + addStringAttribute(result, columns, "REF_GENERATION", tableElem, "identifierGeneration"); + + dumpColumns(tableElem, metaData, "%", "%", tableName); + dumpPKs(tableElem, metaData, "%", "%", tableName); + dumpVersionColumns(tableElem, metaData, "%", "%", tableName); + dumpFKs(tableElem, metaData, "%", "%", tableName); + dumpIndices(tableElem, metaData, "%", "%", tableName); + } } /** - * Dumps the catalog of the given name. + * Dumps the columns of the indicated table. * - * @param parent The parent element + * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name - * @param tableTypes The table types to return + * @param tableName The table name */ - private void dumpCatalog(Element parent, DatabaseMetaData metaData, String catalogName, String[] tableTypes) + private void dumpColumns(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { - Element catalogElem = parent.addElement("catalog"); + ResultSet result = metaData.getColumns(catalogName, schemaName, tableName, "%"); + Set columns = getColumnsInResultSet(result); - catalogElem.addAttribute("name", catalogName); - try + while (result.next()) { - ResultSet result = metaData.getTables(catalogName, "%", "%", tableTypes); - Set columns = getColumnsInResultSet(result); + String columnName = getString(result, "COLUMN_NAME"); - while (result.next()) + if ((columnName == null) || (columnName.length() == 0)) { - Element tableElem = catalogElem.addElement("table"); - String tableName = result.getString("TABLE_NAME"); + continue; + } - if (columns.contains("TABLE_NAME")) - { - tableElem.addAttribute("name", tableName); - } - if (columns.contains("TABLE_CAT")) - { - tableElem.addAttribute("catalog", result.getString("TABLE_CAT")); - } - if (columns.contains("TABLE_SCHEM")) - { - tableElem.addAttribute("schema", result.getString("TABLE_SCHEM")); - } - if (columns.contains("TABLE_TYPE")) - { - tableElem.addAttribute("type", result.getString("TABLE_TYPE")); - } - if (columns.contains("REMARKS")) - { - tableElem.addAttribute("remarks", result.getString("REMARKS")); - } - if (columns.contains("TYPE_NAME")) - { - tableElem.addAttribute("typeName", result.getString("TYPE_NAME")); - } - if (columns.contains("TYPE_CAT")) + Element columnElem = tableElem.addElement("column"); + + columnElem.addAttribute("name", columnName); + addIntAttribute(result, columns, "DATA_TYPE", columnElem, "typeCode"); + addStringAttribute(result, columns, "TYPE_NAME", columnElem, "type"); + addIntAttribute(result, columns, "COLUMN_SIZE", columnElem, "size"); + addIntAttribute(result, columns, "DECIMAL_DIGITS", columnElem, "digits"); + addIntAttribute(result, columns, "NUM_PREC_RADIX", columnElem, "precision"); + if (columns.contains("NULLABLE")) + { + switch (result.getInt("NULLABLE")) { - tableElem.addAttribute("typeCatalog", result.getString("TYPE_CAT")); + case DatabaseMetaData.columnNoNulls: + columnElem.addAttribute("nullable", "false"); + break; + case DatabaseMetaData.columnNullable: + columnElem.addAttribute("nullable", "true"); + break; + default: + columnElem.addAttribute("nullable", "unknown"); + break; } - if (columns.contains("TYPE_SCHEM")) + } + addStringAttribute(result, columns, "REMARKS", columnElem, "remarks"); + addStringAttribute(result, columns, "COLUMN_DEF", columnElem, "defaultValue"); + addIntAttribute(result, columns, "CHAR_OCTET_LENGTH", columnElem, "maxByteLength"); + addIntAttribute(result, columns, "ORDINAL_POSITION", columnElem, "index"); + if (columns.contains("IS_NULLABLE")) + { + String value = getString(result, "IS_NULLABLE"); + + if ("no".equalsIgnoreCase(value)) { - tableElem.addAttribute("typeSchema", result.getString("TYPE_SCHEM")); + columnElem.addAttribute("isNullable", "false"); } - if (columns.contains("SELF_REFERENCING_COL_NAME")) + else if ("yes".equalsIgnoreCase(value)) { - tableElem.addAttribute("identifierColumn", result.getString("SELF_REFERENCING_COL_NAME")); + columnElem.addAttribute("isNullable", "true"); } - if (columns.contains("REF_GENERATION")) + else { - tableElem.addAttribute("identifierGeneration", result.getString("REF_GENERATION")); + columnElem.addAttribute("isNullable", "unknown"); } - dumpTable(tableElem, metaData, catalogName, tableName); } + addStringAttribute(result, columns, "SCOPE_CATLOG", columnElem, "refCatalog"); + addStringAttribute(result, columns, "SCOPE_SCHEMA", columnElem, "refSchema"); + addStringAttribute(result, columns, "SCOPE_TABLE", columnElem, "refTable"); + addShortAttribute(result, columns, "SOURCE_DATA_TYPE", columnElem, "sourceTypeCode"); } - catch (SQLException ex) + } + + /** + * Dumps the primary key columns of the indicated table. + * + * @param tableElem The XML element for the table + * @param metaData The database metadata + * @param catalogName The catalog name + * @param tableName The table name + */ + private void dumpPKs(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException + { + ResultSet result = metaData.getPrimaryKeys(catalogName, schemaName, tableName); + Set columns = getColumnsInResultSet(result); + + while (result.next()) { - ex.printStackTrace(); + String columnName = getString(result, "COLUMN_NAME"); + + if ((columnName == null) || (columnName.length() == 0)) + { + continue; + } + + Element pkElem = tableElem.addElement("primaryKey"); + + pkElem.addAttribute("column", columnName); + addStringAttribute(result, columns, "PK_NAME", pkElem, "name"); + addShortAttribute(result, columns, "KEY_SEQ", pkElem, "sequenceNumberInPK"); } } /** - * Dumps the contents of the indicated table. + * Dumps the versioned (auto-updating) columns of the indicated table. * * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name * @param tableName The table name */ - private void dumpTable(Element tableElem, DatabaseMetaData metaData, String catalogName, String tableName) + private void dumpVersionColumns(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { - try + ResultSet result = metaData.getVersionColumns(catalogName, schemaName, tableName); + Set columns = getColumnsInResultSet(result); + + while (result.next()) { - ResultSet result = metaData.getColumns(catalogName, "%", tableName, "%"); - Set columns = getColumnsInResultSet(result); + String columnName = getString(result, "COLUMN_NAME"); - while (result.next()) + if ((columnName == null) || (columnName.length() == 0)) { - Element columnElem = tableElem.addElement("column"); - String columnName = result.getString("COLUMN_NAME"); + continue; + } - if (columns.contains("COLUMN_NAME")) - { - columnElem.addAttribute("name", columnName); - } - if (columns.contains("DATA_TYPE")) - { - columnElem.addAttribute("typeCode", String.valueOf(result.getInt("DATA_TYPE"))); - } - if (columns.contains("TYPE_NAME")) - { - columnElem.addAttribute("type", result.getString("TYPE_NAME")); - } - if (columns.contains("COLUMN_SIZE")) - { - columnElem.addAttribute("size", String.valueOf(result.getInt("COLUMN_SIZE"))); - } - if (columns.contains("DECIMAL_DIGITS")) - { - columnElem.addAttribute("digits", String.valueOf(result.getInt("DECIMAL_DIGITS"))); - } - if (columns.contains("NUM_PREC_RADIX")) + Element columnElem = tableElem.addElement("versionedColumn"); + + columnElem.addAttribute("column", columnName); + addIntAttribute(result, columns, "DATA_TYPE", columnElem, "typeCode"); + addStringAttribute(result, columns, "TYPE_NAME", columnElem, "type"); + addIntAttribute(result, columns, "BUFFER_LENGTH", columnElem, "size"); + addIntAttribute(result, columns, "COLUMN_SIZE", columnElem, "precision"); + addShortAttribute(result, columns, "DECIMAL_DIGITS", columnElem, "scale"); + if (columns.contains("PSEUDO_COLUMN")) + { + switch (result.getShort("PSEUDO_COLUMN")) { - columnElem.addAttribute("precision", String.valueOf(result.getInt("NUM_PREC_RADIX"))); + case DatabaseMetaData.versionColumnPseudo: + columnElem.addAttribute("columnType", "pseudo column"); + break; + case DatabaseMetaData.versionColumnNotPseudo: + columnElem.addAttribute("columnType", "real column"); + break; + default: + columnElem.addAttribute("columnType", "unknown"); + break; } - if (columns.contains("NULLABLE")) - { - switch (result.getInt("NULLABLE")) - { - case DatabaseMetaData.columnNoNulls: - columnElem.addAttribute("nullable", "false"); - break; - case DatabaseMetaData.columnNullable: - columnElem.addAttribute("nullable", "true"); - break; - default: - columnElem.addAttribute("nullable", "unknown"); - break; - } + } + } + } + + /** + * Dumps the foreign key columns of the indicated table to other tables. + * + * @param tableElem The XML element for the table + * @param metaData The database metadata + * @param catalogName The catalog name + * @param tableName The table name + */ + private void dumpFKs(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException + { + ResultSet result = metaData.getImportedKeys(catalogName, schemaName, tableName); + Set columns = getColumnsInResultSet(result); + + while (result.next()) + { + Element fkElem = tableElem.addElement("foreignKey"); + + addStringAttribute(result, columns, "FK_NAME", fkElem, "name"); + addStringAttribute(result, columns, "PK_NAME", fkElem, "primaryKeyName"); + addStringAttribute(result, columns, "PKCOLUMN_NAME", fkElem, "column"); + addStringAttribute(result, columns, "FKTABLE_CAT", fkElem, "foreignCatalog"); + addStringAttribute(result, columns, "FKTABLE_SCHEM", fkElem, "foreignSchema"); + addStringAttribute(result, columns, "FKTABLE_NAME", fkElem, "foreignTable"); + addStringAttribute(result, columns, "FKCOLUMN_NAME", fkElem, "foreignColumn"); + addShortAttribute(result, columns, "KEY_SEQ", fkElem, "sequenceNumberInFK"); + if (columns.contains("UPDATE_RULE")) + { + switch (result.getShort("UPDATE_RULE")) + { + case DatabaseMetaData.importedKeyNoAction: + fkElem.addAttribute("updateRule", "no action"); + break; + case DatabaseMetaData.importedKeyCascade: + fkElem.addAttribute("updateRule", "cascade PK change"); + break; + case DatabaseMetaData.importedKeySetNull: + fkElem.addAttribute("updateRule", "set FK to NULL"); + break; + case DatabaseMetaData.importedKeySetDefault: + fkElem.addAttribute("updateRule", "set FK to default"); + break; + default: + fkElem.addAttribute("updateRule", "unknown"); + break; + } + } + if (columns.contains("DELETE_RULE")) + { + switch (result.getShort("DELETE_RULE")) + { + case DatabaseMetaData.importedKeyNoAction: + case DatabaseMetaData.importedKeyRestrict: + fkElem.addAttribute("deleteRule", "no action"); + break; + case DatabaseMetaData.importedKeyCascade: + fkElem.addAttribute("deleteRule", "cascade PK change"); + break; + case DatabaseMetaData.importedKeySetNull: + fkElem.addAttribute("deleteRule", "set FK to NULL"); + break; + case DatabaseMetaData.importedKeySetDefault: + fkElem.addAttribute("deleteRule", "set FK to default"); + break; + default: + fkElem.addAttribute("deleteRule", "unknown"); + break; + } + } + if (columns.contains("DEFERRABILITY")) + { + switch (result.getShort("DEFERRABILITY")) + { + case DatabaseMetaData.importedKeyInitiallyDeferred: + fkElem.addAttribute("deferrability", "initially deferred"); + break; + case DatabaseMetaData.importedKeyInitiallyImmediate: + fkElem.addAttribute("deferrability", "immediately deferred"); + break; + case DatabaseMetaData.importedKeyNotDeferrable: + fkElem.addAttribute("deferrability", "not deferred"); + break; + default: + fkElem.addAttribute("deferrability", "unknown"); + break; } - if (columns.contains("REMARKS")) + } + } + } + + /** + * Dumps the indices of the indicated table. + * + * @param tableElem The XML element for the table + * @param metaData The database metadata + * @param catalogName The catalog name + * @param tableName The table name + */ + private void dumpIndices(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException + { + ResultSet result = metaData.getIndexInfo(catalogName, schemaName, tableName, false, false); + Set columns = getColumnsInResultSet(result); + + while (result.next()) + { + Element indexElem = tableElem.addElement("index"); + + addStringAttribute(result, columns, "INDEX_NAME", indexElem, "name"); + addBooleanAttribute(result, columns, "NON_UNIQUE", indexElem, "nonUnique"); + addStringAttribute(result, columns, "INDEX_QUALIFIER", indexElem, "indexCatalog"); + if (columns.contains("TYPE")) + { + switch (result.getShort("TYPE")) { - columnElem.addAttribute("remarks", result.getString("REMARKS")); + case DatabaseMetaData.tableIndexStatistic: + indexElem.addAttribute("type", "table statistics"); + break; + case DatabaseMetaData.tableIndexClustered: + indexElem.addAttribute("type", "clustered"); + break; + case DatabaseMetaData.tableIndexHashed: + indexElem.addAttribute("type", "hashed"); + break; + case DatabaseMetaData.tableIndexOther: + indexElem.addAttribute("type", "other"); + break; + default: + indexElem.addAttribute("type", "unknown"); + break; } - if (columns.contains("COLUMN_DEF")) + } + addStringAttribute(result, columns, "COLUMN_NAME", indexElem, "column"); + addShortAttribute(result, columns, "ORDINAL_POSITION", indexElem, "sequenceNumberInIndex"); + if (columns.contains("ASC_OR_DESC")) + { + String value = getString(result, "ASC_OR_DESC"); + + if ("A".equalsIgnoreCase(value)) { - columnElem.addAttribute("defaultValue", result.getString("COLUMN_DEF")); + indexElem.addAttribute("sortOrder", "ascending"); } - if (columns.contains("CHAR_OCTET_LENGTH")) + else if ("D".equalsIgnoreCase(value)) { - columnElem.addAttribute("maxByteLength", String.valueOf(result.getInt("CHAR_OCTET_LENGTH"))); + indexElem.addAttribute("sortOrder", "descending"); } - if (columns.contains("ORDINAL_POSITION")) + else { - columnElem.addAttribute("index", String.valueOf(result.getInt("ORDINAL_POSITION"))); + indexElem.addAttribute("sortOrder", "unknown"); } - if (columns.contains("IS_NULLABLE")) - { - String value = result.getString("IS_NULLABLE"); + } + addIntAttribute(result, columns, "CARDINALITY", indexElem, "cardinality"); + addIntAttribute(result, columns, "PAGES", indexElem, "pages"); + addStringAttribute(result, columns, "FILTER_CONDITION", indexElem, "filter"); + } + } - if ("no".equalsIgnoreCase(value)) - { - columnElem.addAttribute("isNullable", "false"); - } - else if ("yes".equalsIgnoreCase(value)) - { - columnElem.addAttribute("isNullable", "true"); - } - else - { - columnElem.addAttribute("isNullable", "unknown"); - } - } - if (columns.contains("SCOPE_CATLOG")) - { - columnElem.addAttribute("refCatalog", result.getString("SCOPE_CATLOG")); - } - if (columns.contains("SCOPE_SCHEMA")) + /** + * Dumps all procedures. + * + * @param parent The parent element + * @param metaData The database metadata + */ + private void dumpProcedures(Element parent, DatabaseMetaData metaData) throws SQLException + { + ResultSet result = metaData.getProcedures("%", "%", "%"); + Element proceduresElem = parent.addElement("procedures"); + Set columns = getColumnsInResultSet(result); + + while (result.next()) + { + String procedureName = getString(result, "PROCEDURE_NAME"); + + if ((procedureName == null) || (procedureName.length() == 0)) + { + continue; + } + + Element procedureElem = proceduresElem.addElement("procedure"); + + procedureElem.addAttribute("name", procedureName); + addStringAttribute(result, columns, "PROCEDURE_CAT", procedureElem, "catalog"); + addStringAttribute(result, columns, "PROCEDURE_SCHEM", procedureElem, "schema"); + addStringAttribute(result, columns, "REMARKS", procedureElem, "remarks"); + if (columns.contains("PROCEDURE_TYPE")) + { + switch (result.getShort("PROCEDURE_TYPE")) { - columnElem.addAttribute("refSchema", result.getString("SCOPE_SCHEMA")); + case DatabaseMetaData.procedureReturnsResult: + procedureElem.addAttribute("type", "returns result"); + break; + case DatabaseMetaData.procedureNoResult: + procedureElem.addAttribute("type", "doesn't return result"); + break; + case DatabaseMetaData.procedureResultUnknown: + procedureElem.addAttribute("type", "may return result"); + break; + default: + procedureElem.addAttribute("type", "unknown"); + break; } - if (columns.contains("SCOPE_TABLE")) + } + + dumpProcedure(procedureElem, metaData, "%", "%", procedureName); + } + } + + /** + * Dumps the contents of the indicated procedure. + * + * @param procedureElem The XML element for the procedure + * @param metaData The database metadata + * @param catalogName The catalog name + * @param procedureName The procedure name + */ + private void dumpProcedure(Element procedureElem, DatabaseMetaData metaData, String catalogName, String schemaName, String procedureName) throws SQLException + { + ResultSet result = metaData.getProcedureColumns(catalogName, schemaName, procedureName, "%"); + Set columns = getColumnsInResultSet(result); + + while (result.next()) + { + String columnName = getString(result, "COLUMN_NAME"); + + if ((columnName == null) || (columnName.length() == 0)) + { + continue; + } + + Element columnElem = procedureElem.addElement("column"); + + columnElem.addAttribute("name", columnName); + if (columns.contains("COLUMN_TYPE")) + { + switch (result.getShort("COLUMN_TYPE")) { - columnElem.addAttribute("refTable", result.getString("SCOPE_TABLE")); + case DatabaseMetaData.procedureColumnIn: + columnElem.addAttribute("type", "in parameter"); + break; + case DatabaseMetaData.procedureColumnInOut: + columnElem.addAttribute("type", "in/out parameter"); + break; + case DatabaseMetaData.procedureColumnOut: + columnElem.addAttribute("type", "out parameter"); + break; + case DatabaseMetaData.procedureColumnReturn: + columnElem.addAttribute("type", "return value"); + break; + case DatabaseMetaData.procedureColumnResult: + columnElem.addAttribute("type", "result column in ResultSet"); + break; + default: + columnElem.addAttribute("type", "unknown"); + break; } - if (columns.contains("SOURCE_DATA_TYPE")) + } + + addIntAttribute(result, columns, "DATA_TYPE", columnElem, "typeCode"); + addStringAttribute(result, columns, "TYPE_NAME", columnElem, "type"); + addIntAttribute(result, columns, "LENGTH", columnElem, "length"); + addIntAttribute(result, columns, "PRECISION", columnElem, "precision"); + addShortAttribute(result, columns, "SCALE", columnElem, "short"); + addShortAttribute(result, columns, "RADIX", columnElem, "radix"); + if (columns.contains("NULLABLE")) + { + switch (result.getInt("NULLABLE")) { - columnElem.addAttribute("sourceTypeCode", String.valueOf(result.getShort("SOURCE_DATA_TYPE"))); + case DatabaseMetaData.procedureNoNulls: + columnElem.addAttribute("nullable", "false"); + break; + case DatabaseMetaData.procedureNullable: + columnElem.addAttribute("nullable", "true"); + break; + default: + columnElem.addAttribute("nullable", "unknown"); + break; } - } + addStringAttribute(result, columns, "REMARKS", columnElem, "remarks"); } - catch (SQLException ex) + } + + /** + * If the result set contains the indicated column, extracts its value and sets an attribute at the given element. + * + * @param result The result set + * @param columns The columns in the result set + * @param columnName The name of the column in the result set + * @param element The element to add the attribute + * @param attrName The name of the attribute to set + * @return The string value or null + */ + private String addStringAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException + { + String value = null; + + if (columns.contains(columnName)) { - ex.printStackTrace(); + value = getString(result, columnName); + element.addAttribute(attrName, value); } + return value; + } + + /** + * If the result set contains the indicated column, extracts its int value and sets an attribute at the given element. + * + * @param result The result set + * @param columns The columns in the result set + * @param columnName The name of the column in the result set + * @param element The element to add the attribute + * @param attrName The name of the attribute to set + * @return The string value or null + */ + private String addIntAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException + { + String value = null; + + if (columns.contains(columnName)) + { + value = String.valueOf(result.getInt(columnName)); + element.addAttribute(attrName, value); + } + return value; + } + + /** + * If the result set contains the indicated column, extracts its short value and sets an attribute at the given element. + * + * @param result The result set + * @param columns The columns in the result set + * @param columnName The name of the column in the result set + * @param element The element to add the attribute + * @param attrName The name of the attribute to set + * @return The string value or null + */ + private String addShortAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException + { + String value = null; + + if (columns.contains(columnName)) + { + value = String.valueOf(result.getShort(columnName)); + element.addAttribute(attrName, value); + } + return value; + } + + /** + * If the result set contains the indicated column, extracts its boolean value and sets an attribute at the given element. + * + * @param result The result set + * @param columns The columns in the result set + * @param columnName The name of the column in the result set + * @param element The element to add the attribute + * @param attrName The name of the attribute to set + * @return The string value or null + */ + private String addBooleanAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException + { + String value = null; + + if (columns.contains(columnName)) + { + value = String.valueOf(result.getBoolean(columnName)); + element.addAttribute(attrName, value); + } + return value; + } + + /** + * Extracts a string from the result set. + * + * @param result The result set + * @param columnName The name of the column in the result set + * @return The string value + */ + private String getString(ResultSet result, String columnName) throws SQLException + { + return result.getString(columnName); } /**