0001: /*
0002: * The contents of this file are subject to the terms of the Common Development
0003: * and Distribution License (the License). You may not use this file except in
0004: * compliance with the License.
0005: *
0006: * You can obtain a copy of the License at http://www.netbeans.org/cddl.html
0007: * or http://www.netbeans.org/cddl.txt.
0008: *
0009: * When distributing Covered Code, include this CDDL Header Notice in each file
0010: * and include the License file at http://www.netbeans.org/cddl.txt.
0011: * If applicable, add the following below the CDDL Header, with the fields
0012: * enclosed by brackets [] replaced by your own identifying information:
0013: * "Portions Copyrighted [year] [name of copyright owner]"
0014: *
0015: * The Original Software is NetBeans. The Initial Developer of the Original
0016: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
0017: * Microsystems, Inc. All Rights Reserved.
0018: */
0019:
0020: /*
0021: * Sun Public License Notice
0022: *
0023: * The contents of this file are subject to the Sun Public License
0024: * Version 1.0 (the "License"). You may not use this file except in
0025: * compliance with the License. A copy of the License is available at
0026: * http://www.sun.com/
0027: *
0028: * The Original Code is NetBeans. The Initial Developer of the Original
0029: * Code is Sun Microsystems, Inc. Portions Copyright 1997-2004 Sun
0030: * Microsystems, Inc. All Rights Reserved.
0031: */
0032:
0033: package org.netbeans.modules.sql.project.dbmodel;
0034:
0035: import java.sql.Connection;
0036: import java.sql.DriverManager;
0037: import java.sql.DatabaseMetaData;
0038: import java.sql.PreparedStatement;
0039: import java.sql.CallableStatement;
0040: import java.sql.ResultSet;
0041: import java.sql.Statement;
0042: import java.sql.ResultSetMetaData;
0043: import java.sql.ParameterMetaData;
0044: import java.sql.SQLException;
0045:
0046: import java.util.Collections;
0047: import java.util.HashMap;
0048: import java.util.List;
0049: import java.util.Vector;
0050: import java.util.ArrayList;
0051: import java.util.Iterator;
0052: import java.util.StringTokenizer;
0053:
0054: /**
0055: * Extracts database metadata information (table names and constraints, their
0056: * associated columns, etc.)
0057: *
0058: * @author Susan Chen
0059: * @version
0060: */
0061: public class DBMetaData {
0062: // constants
0063:
0064: /** Index to the name field for results of table/view/procedure searches */
0065: public static final int NAME = 0;
0066:
0067: /** Index to the catalog field for results of table/view/procedure searches */
0068: public static final int CATALOG = 1;
0069:
0070: /** Index to the schema field for results of table/view/procedure searches */
0071: public static final int SCHEMA = 2;
0072:
0073: /** Index to the type field for results of table/view/procedure searches */
0074: public static final int TYPE = 3;
0075:
0076: /** Database OTD type for DB2 */
0077: public static final String DB2 = "DB2"; // NOI18N
0078:
0079: /** Database OTD type for Oracle */
0080: public static final String ORACLE = "ORACLE"; // NOI18N
0081:
0082: /** Database OTD type for SQL Server */
0083: public static final String SQLSERVER = "SQLSERVER"; // NOI18N
0084:
0085: /** Database OTD type for Sybase */
0086: public static final String SYBASE = "SYBASE"; // NOI18N
0087:
0088: /** Database OTD type for VSAM */
0089: public static final String VSAM_ADABAS_IAM = "LEGACY"; // NOI18N
0090:
0091: /** Database OTD type for JDBC-ODBC */
0092: public static final String JDBC_ODBC = "JDBCODBC"; // NOI18N
0093:
0094: /** Database type display description for DB2 */
0095: public static final String DB2_TEXT = "DB2"; // NOI18N
0096:
0097: /** Database type display description for Oracle */
0098: public static final String ORACLE_TEXT = "ORACLE"; // NOI18N
0099:
0100: /** Database type display description for SQL Server */
0101: public static final String SQLSERVER_TEXT = "SQL SERVER"; // NOI18N
0102:
0103: /** Database type display description for Sybase */
0104: public static final String SYBASE_TEXT = "SYBASE"; // NOI18N
0105:
0106: /** Database type display description for VSAM/ADABAS/IAM */
0107: public static final String VSAM_ADABAS_IAM_TEXT = "VSAM/ADABAS/IAM"; // NOI18N
0108:
0109: /** Database type display description for JDBC-ODBC */
0110: public static final String JDBCODBC_TEXT = "JDBC-ODBC"; // NOI18N
0111:
0112: /** List of database type display descriptions */
0113: public static final String[] DBTYPES = { DB2_TEXT, ORACLE_TEXT,
0114: SQLSERVER_TEXT, SYBASE_TEXT, VSAM_ADABAS_IAM_TEXT,
0115: JDBCODBC_TEXT };
0116:
0117: /** List of Java types */
0118: public static final String[] JAVATYPES = { "boolean", "byte",
0119: "byte[]", "double", "float", "int", "java.lang.String",
0120: "java.lang.Object", "java.math.BigDecimal", "java.net.URL",
0121: "java.sql.Array", "java.sql.Blob", "java.sql.Clob",
0122: "java.sql.Date", "java.sql.Ref", "java.sql.Struct",
0123: "java.sql.Time", "java.sql.Timestamp", "long", "short" };
0124:
0125: /** List of JDBC SQL types */
0126: public static final String[] SQLTYPES = { "ARRAY", "BIGINT",
0127: "BINARY", "BIT", "BLOB", "BOOLEAN", "CHAR", "CLOB",
0128: "DATALINK", "DATE", "DECIMAL", "DISTINCT", "DOUBLE",
0129: "FLOAT", "INTEGER", "JAVA_OBJECT", "LONGVARBINARY",
0130: "LONGVARCHAR", "NULL", "NUMERIC", "OTHER", "REAL", "REF",
0131: "SMALLINT", "STRUCT", "TIME", "TIMESTAMP", "TINYINT",
0132: "VARBINARY", "VARCHAR",
0133: //added abey for Procedure ResultSet
0134: "RESULTSET" };
0135:
0136: public static final int[] SQLTYPE_CODES = {
0137: java.sql.Types.ARRAY,
0138: java.sql.Types.BIGINT,
0139: java.sql.Types.BINARY,
0140: java.sql.Types.BIT,
0141: java.sql.Types.BLOB,
0142: 16, // java.sql.Types.BOOLEAN,
0143: java.sql.Types.CHAR,
0144: java.sql.Types.CLOB,
0145: 70, //case java.sql.Types.DATALINK,
0146: java.sql.Types.DATE, java.sql.Types.DECIMAL,
0147: java.sql.Types.DISTINCT, java.sql.Types.DOUBLE,
0148: java.sql.Types.FLOAT, java.sql.Types.INTEGER,
0149: java.sql.Types.JAVA_OBJECT, java.sql.Types.LONGVARBINARY,
0150: java.sql.Types.LONGVARCHAR, java.sql.Types.NULL,
0151: java.sql.Types.NUMERIC, java.sql.Types.OTHER,
0152: java.sql.Types.REAL, java.sql.Types.REF,
0153: java.sql.Types.SMALLINT, java.sql.Types.STRUCT,
0154: java.sql.Types.TIME, java.sql.Types.TIMESTAMP,
0155: java.sql.Types.TINYINT, java.sql.Types.VARBINARY,
0156: java.sql.Types.VARCHAR };
0157:
0158: /** Map SQL type to Java type */
0159: public static final HashMap SQLTOJAVATYPES = new HashMap();
0160: static {
0161: SQLTOJAVATYPES.put("ARRAY", "java.sql.Array"); // NOI18N
0162: SQLTOJAVATYPES.put("BIGINT", "long"); // NOI18N
0163: SQLTOJAVATYPES.put("BINARY", "byte[]"); // NOI18N
0164: SQLTOJAVATYPES.put("BIT", "boolean"); // NOI18N
0165: SQLTOJAVATYPES.put("BLOB", "java.sql.Blob"); // NOI18N
0166: SQLTOJAVATYPES.put("BOOLEAN", "boolean"); // NOI18N
0167: SQLTOJAVATYPES.put("CHAR", "java.lang.String"); // NOI18N
0168: SQLTOJAVATYPES.put("CLOB", "java.sql.Clob"); // NOI18N
0169: SQLTOJAVATYPES.put("DATALINK", "java.net.URL"); // NOI18N
0170: SQLTOJAVATYPES.put("DATE", "java.sql.Date"); // NOI18N
0171: SQLTOJAVATYPES.put("DECIMAL", "java.math.BigDecimal"); // NOI18N
0172: SQLTOJAVATYPES.put("DISTINCT", "java.lang.String"); // NOI18N
0173: SQLTOJAVATYPES.put("DOUBLE", "double"); // NOI18N
0174: SQLTOJAVATYPES.put("FLOAT", "double"); // NOI18N
0175: SQLTOJAVATYPES.put("INTEGER", "int"); // NOI18N
0176: SQLTOJAVATYPES.put("JAVA_OBJECT", "java.lang.Object"); // NOI18N
0177: SQLTOJAVATYPES.put("LONGVARBINARY", "byte[]"); // NOI18N
0178: SQLTOJAVATYPES.put("LONGVARCHAR", "java.lang.String"); // NOI18N
0179: SQLTOJAVATYPES.put("NULL", "java.lang.String"); // NOI18N
0180: SQLTOJAVATYPES.put("NUMERIC", "java.math.BigDecimal"); // NOI18N
0181: SQLTOJAVATYPES.put("OTHER", "java.lang.String"); // NOI18N
0182: SQLTOJAVATYPES.put("REAL", "float"); // NOI18N
0183: SQLTOJAVATYPES.put("REF", "java.sql.Ref"); // NOI18N
0184: SQLTOJAVATYPES.put("SMALLINT", "short"); // NOI18N
0185: SQLTOJAVATYPES.put("STRUCT", "java.sql.Struct"); // NOI18N
0186: SQLTOJAVATYPES.put("TIME", "java.sql.Time"); // NOI18N
0187: SQLTOJAVATYPES.put("TIMESTAMP", "java.sql.Timestamp"); // NOI18N
0188: SQLTOJAVATYPES.put("TINYINT", "byte"); // NOI18N
0189: SQLTOJAVATYPES.put("VARBINARY", "byte[]"); // NOI18N
0190: SQLTOJAVATYPES.put("VARCHAR", "java.lang.String"); // NOI18N
0191: //added abey for Procedure ResultSets
0192: SQLTOJAVATYPES.put("RESULTSET", "java.sql.ResultSet"); // NOI18N
0193: }
0194:
0195: // String used in java.sql.DatabaseMetaData to indicate system tables.
0196: private static final String SYSTEM_TABLE = "SYSTEM TABLE"; // NOI18N
0197:
0198: // String used in java.sql.DatabaseMetaData to indicate system tables.
0199: private static final String TABLE = "TABLE"; // NOI18N
0200:
0201: // String used in java.sql.DatabaseMetaData to indicate system tables.
0202: private static final String VIEW = "VIEW"; // NOI18N
0203:
0204: // String used in java.sql.DatabaseMetaData to indicate aliases.
0205: private static final String ALIAS = "ALIAS"; // NOI18N
0206:
0207: // String used in java.sql.DatabaseMetaData to indicate synonyms.
0208: private static final String SYNONYM = "SYNONYM"; // NOI18N
0209:
0210: private Connection dbconn; // db connection
0211: private DatabaseMetaData dbmeta; // db metadata
0212: private String errMsg; // error message
0213: private boolean checkPrepStmtMetaData = true; // indicates driver does not
0214: // fully support finding prepared
0215: // statement metadata
0216: private boolean checkProcMetaData = true; // indicates driver does not
0217: // fully support finding prepared
0218: // statement metadata
0219: private boolean errPrepStmtParameters = false; // error getting prep. stmt. parameters
0220: private boolean errPrepStmtResultSetColumns = false; // error getting prep. stmt. resultset columns
0221:
0222: private String sqlText;
0223:
0224: /**
0225: * Gets the primary keys for a table.
0226: *
0227: * @param newTable Table to get the primary key(s) for
0228: * @throws Exception DOCUMENT ME!
0229: */
0230: public void checkPrimaryKeys(Table newTable) throws Exception {
0231: errMsg = "";
0232: try {
0233: // get the primary keys
0234: List primaryKeys = getPrimaryKeys(newTable.getCatalog(),
0235: newTable.getSchema(), newTable.getName());
0236:
0237: if (primaryKeys.size() != 0) {
0238: newTable.setPrimaryKeyColumnList(primaryKeys);
0239:
0240: // create a hash set of the keys
0241: java.util.Set primaryKeysSet = new java.util.HashSet();
0242: for (int i = 0; i < primaryKeys.size(); i++) {
0243: KeyColumn key = (KeyColumn) primaryKeys.get(i);
0244: primaryKeysSet.add(key.getColumnName());
0245: }
0246:
0247: // now loop through all the columns flagging the primary keys
0248: TableColumn[] columns = newTable.getColumns();
0249: if (columns != null) {
0250: for (int i = 0; i < columns.length; i++) {
0251: if (primaryKeysSet.contains(columns[i]
0252: .getName())) {
0253: columns[i].setIsPrimaryKey(true);
0254: }
0255: }
0256: }
0257: }
0258: } catch (Exception e) {
0259: e.printStackTrace();
0260: errMsg = e.getLocalizedMessage();
0261: throw e;
0262: }
0263: }
0264:
0265: /**
0266: * Gets the foreign keys for a table.
0267: *
0268: * @param newTable Table to get the foreign key(s) for
0269: * @throws Exception DOCUMENT ME!
0270: */
0271: public void checkForeignKeys(Table newTable) throws Exception {
0272: errMsg = "";
0273: try {
0274: // get the foreing keys
0275: List foreignKeys = getForeignKeys(newTable.getCatalog(),
0276: newTable.getSchema(), newTable.getName());
0277: if (foreignKeys != null) {
0278: newTable.setForeignKeyColumnList(foreignKeys);
0279:
0280: // create a hash set of the keys
0281: java.util.Set foreignKeysSet = new java.util.HashSet();
0282: for (int i = 0; i < foreignKeys.size(); i++) {
0283: ForeignKeyColumn key = (ForeignKeyColumn) foreignKeys
0284: .get(i);
0285: foreignKeysSet.add(key.getColumnName());
0286: }
0287:
0288: // now loop through all the columns flagging the foreign keys
0289: TableColumn[] columns = newTable.getColumns();
0290: if (columns != null) {
0291: for (int i = 0; i < columns.length; i++) {
0292: if (foreignKeysSet.contains(columns[i]
0293: .getName())) {
0294: columns[i].setIsForeignKey(true);
0295: }
0296: }
0297: }
0298: }
0299: } catch (Exception e) {
0300: e.printStackTrace();
0301: errMsg = e.getLocalizedMessage();
0302: throw e;
0303: }
0304: }
0305:
0306: /**
0307: * Establishes a connection to the database.
0308: *
0309: * @param driver Driver class
0310: * @param url JDBC connection URL
0311: * @param userName User name
0312: * @param passWord Password
0313: * @throws Exception DOCUMENT ME!
0314: */
0315: public void connectDB(String driver, String url, String userName,
0316: String passWord) throws Exception {
0317: errMsg = "";
0318: boolean isNativeDriver = false;
0319: // connect to the database
0320: try {
0321: //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
0322: //Class.forName("oracle.jdbc.OracleDriver");
0323: //String url = "jdbc:odbc:" + dataBase;
0324: Class.forName(driver);
0325: //PP: Oracle specific change for date type columns to be
0326: //compatible with 5.0.x versions of the product. See QAI 90533.
0327: java.util.Properties props = new java.util.Properties();
0328: props.put("user", userName);
0329: props.put("password", passWord);
0330: if (url.startsWith("jdbc:oracle:")) {
0331: isNativeDriver = true;
0332: props.put("oracle.jdbc.V8Compatible", "true");
0333: }
0334: dbconn = DriverManager.getConnection(url, props);
0335:
0336: // once we've connected, get the metadata
0337: getDBMetaData();
0338: } catch (ClassNotFoundException e) {
0339: e.printStackTrace();
0340: errMsg = "Could not locate JDBC driver: "
0341: + e.getLocalizedMessage();
0342: throw e;
0343: } catch (Exception e) {
0344: e.printStackTrace();
0345: errMsg = e.getLocalizedMessage();
0346: throw e;
0347: }
0348: }
0349:
0350: /**
0351: * Disconnects from the database.
0352: *
0353: * @throws Exception DOCUMENT ME!
0354: */
0355: public void disconnectDB() throws Exception {
0356: errMsg = "";
0357: // close connection to database
0358: try {
0359: if ((dbconn != null) && (!dbconn.isClosed())) {
0360: dbconn.close();
0361: }
0362: } catch (SQLException e) {
0363: e.printStackTrace();
0364: errMsg = e.getLocalizedMessage();
0365: throw e;
0366: }
0367: }
0368:
0369: private void getDBMetaData() throws Exception {
0370: errMsg = "";
0371: // get the metadata
0372: try {
0373: dbmeta = dbconn.getMetaData();
0374: } catch (SQLException e) {
0375: e.printStackTrace();
0376: errMsg = e.getLocalizedMessage();
0377: //throw e;
0378: }
0379: }
0380:
0381: /**
0382: * Returns the database product name
0383: *
0384: * @return String database product name
0385: * @throws Exception DOCUMENT ME!
0386: */
0387: public String getDBName() throws Exception {
0388: String dbname = "";
0389:
0390: errMsg = "";
0391: // get the database product name
0392: try {
0393: dbname = dbmeta.getDatabaseProductName();
0394: } catch (SQLException e) {
0395: e.printStackTrace();
0396: errMsg = e.getLocalizedMessage();
0397: throw e;
0398: }
0399: return dbname;
0400: }
0401:
0402: /**
0403: * Returns the database OTD type.
0404: *
0405: * @return String Database OTD type
0406: * @throws Exception DOCUMENT ME!
0407: */
0408: public String getDBType() throws Exception {
0409: String dbtype = "";
0410:
0411: // get the database type based on the product name converted to lowercase
0412: String dbname = getDBName().toLowerCase();
0413: if (dbname.equals("microsoft sql server")) {
0414: // Microsoft SQL Server
0415: dbtype = SQLSERVER;
0416: } else if ((dbname.equals("sql server"))
0417: || (dbname.indexOf("sybase") > -1)) {
0418: // SYBASE
0419: dbtype = SYBASE;
0420: } else if ((dbname.indexOf("db2") > -1)
0421: || (dbname.equals("as"))) {
0422: // DB2
0423: dbtype = DB2;
0424: } else if ((dbname.equals("exadas"))
0425: || (dbname.equals("attunity connect driver"))) {
0426: // VSAM
0427: dbtype = VSAM_ADABAS_IAM;
0428: } else if (dbname.indexOf("orac") > -1) {
0429: // Oracle
0430: dbtype = ORACLE;
0431: } else {
0432: // other type, default to JDBC-ODBC
0433: dbtype = JDBC_ODBC;
0434: }
0435:
0436: return dbtype;
0437: }
0438:
0439: private String getJDBCSearchPattern(String guiPattern)
0440: throws Exception {
0441: errMsg = "";
0442:
0443: // Converts the passed in GUI pattern to one understood by the
0444: // JDBC driver:
0445: // change _ to <escape char>_
0446: // change % to <escape char>%
0447: // change * to % = GUI uses * to represent 0 or more characters
0448: // change ? to _ = GUI uses ? to represent any single character
0449: try {
0450: String jdbcPattern = guiPattern;
0451: String escapeChar = dbmeta.getSearchStringEscape();
0452:
0453: // change _ to <escape char>_
0454: //PP:See bug 10718. Disabling the escape character for _
0455: //jdbcPattern = replaceAllChars(jdbcPattern, '_', escapeChar + "_");
0456:
0457: // change % to <escape char>%
0458: jdbcPattern = replaceAllChars(jdbcPattern, '%', escapeChar
0459: + "%");
0460:
0461: // change * to %
0462: jdbcPattern = jdbcPattern.replace('*', '%');
0463:
0464: // change ? to _
0465: jdbcPattern = jdbcPattern.replace('?', '_');
0466:
0467: return jdbcPattern;
0468: } catch (Exception e) {
0469: e.printStackTrace();
0470: errMsg = e.getLocalizedMessage();
0471: throw e;
0472: }
0473: }
0474:
0475: /**
0476: * Returns a list of schemas in the database.
0477: *
0478: * @return String[] List of schema names
0479: * @throws Exception DOCUMENT ME!
0480: */
0481: public String[] getSchemas() throws Exception {
0482: errMsg = "";
0483: // get all schemas
0484: try {
0485: ResultSet rs = dbmeta.getSchemas();
0486: Vector v = new Vector();
0487: String[] schemaNames = null;
0488:
0489: while (rs.next()) {
0490: String schema = rs.getString("TABLE_SCHEM");
0491: v.add(schema);
0492: }
0493: if (v.size() > 0) {
0494: // copy into array to return
0495: schemaNames = new String[v.size()];
0496: v.copyInto(schemaNames);
0497: }
0498: rs.close();
0499: return schemaNames;
0500: } catch (Exception e) {
0501: e.printStackTrace();
0502: errMsg = e.getLocalizedMessage();
0503: throw e;
0504: }
0505: }
0506:
0507: /**
0508: * Returns a list of tables matching in the passed in filters.
0509: *
0510: * @param catalog Catalog name
0511: * @param schemaPattern Schema pattern
0512: * @param tablePattern Table name pattern
0513: * @param includeSystemTables Indicate whether to include system tables in search
0514: * @return String[][] List of tables matching search filters
0515: * @throws Exception DOCUMENT ME!
0516: */
0517: public String[][] getTablesOnly(String catalog,
0518: String schemaPattern, String tablePattern,
0519: boolean includeSystemTables) throws Exception {
0520: String[] tableTypes;
0521:
0522: if (includeSystemTables) {
0523: String[] types = { TABLE, ALIAS, SYNONYM, SYSTEM_TABLE };
0524: tableTypes = types;
0525: } else {
0526: String[] types = { TABLE, ALIAS, SYNONYM };
0527: tableTypes = types;
0528: }
0529:
0530: return getTables(catalog, schemaPattern, tablePattern,
0531: tableTypes);
0532: }
0533:
0534: /**
0535: * Returns a list of views matching in the passed in filters.
0536: *
0537: * @param catalog Catalog name
0538: * @param schemaPattern Schema pattern
0539: * @param viewPattern View name pattern
0540: * @param includeSystemTables Indicate whether to include system tables in search
0541: * @return String[][] List of views matching search filters
0542: * @throws Exception DOCUMENT ME!
0543: */
0544: public String[][] getViewsOnly(String catalog,
0545: String schemaPattern, String viewPattern,
0546: boolean includeSystemTables) throws Exception {
0547: String[] tableTypes;
0548:
0549: if (includeSystemTables) {
0550: String[] types = { VIEW, ALIAS, SYNONYM, SYSTEM_TABLE };
0551: tableTypes = types;
0552: } else {
0553: String[] types = { VIEW, ALIAS, SYNONYM };
0554: tableTypes = types;
0555: }
0556:
0557: return getTables(catalog, schemaPattern, viewPattern,
0558: tableTypes);
0559: }
0560:
0561: /**
0562: * Returns a list of tables and views matching in the passed in filters.
0563: *
0564: * @param catalog Catalog name
0565: * @param schemaPattern Schema pattern
0566: * @param tablePattern Table/View name pattern
0567: * @param includeSystemTables Indicate whether to include system tables in search
0568: * @return String[][] List of tables and views matching search filters
0569: * @throws Exception DOCUMENT ME!
0570: */
0571: public String[][] getTablesAndViews(String catalog,
0572: String schemaPattern, String tablePattern,
0573: boolean includeSystemTables) throws Exception {
0574: String[] tableTypes;
0575:
0576: if (includeSystemTables) {
0577: String[] types = { TABLE, VIEW, ALIAS, SYNONYM,
0578: SYSTEM_TABLE };
0579: tableTypes = types;
0580: } else {
0581: String[] types = { TABLE, VIEW, ALIAS, SYNONYM };
0582: tableTypes = types;
0583: }
0584:
0585: return getTables(catalog, schemaPattern, tablePattern,
0586: tableTypes);
0587: }
0588:
0589: /**
0590: * Returns a list of tables/views matching in the passed in filters.
0591: *
0592: * @param catalog Catalog name
0593: * @param schemaPattern Schema pattern
0594: * @param tablePattern Table/View name pattern
0595: * @param tableTypes List of table types to include (ex. TABLE, VIEW)
0596: * @return String[][] List of tables matching search filters
0597: * @throws Exception DOCUMENT ME!
0598: */
0599: public String[][] getTables(String catalog, String schemaPattern,
0600: String tablePattern, String[] tableTypes) throws Exception {
0601: errMsg = "";
0602: try {
0603: if (catalog.equals("")) {
0604: catalog = null;
0605: }
0606: if (schemaPattern.equals("")) {
0607: schemaPattern = null;
0608: }
0609: if (tablePattern.equals("")) {
0610: tablePattern = null;
0611: }
0612:
0613: if (tablePattern != null) {
0614: tablePattern = getJDBCSearchPattern(tablePattern);
0615: }
0616:
0617: ResultSet rs = dbmeta.getTables(catalog, schemaPattern,
0618: tablePattern, tableTypes);
0619:
0620: Vector v = new Vector();
0621: String[][] tables = null; // array of table structures: Name, Catalog, Schema
0622:
0623: while (rs.next()) {
0624: String tableCatalog = rs.getString("TABLE_CAT");
0625: String tableSchema = rs.getString("TABLE_SCHEM");
0626: String tableName = rs.getString("TABLE_NAME");
0627: String tableType = rs.getString("TABLE_TYPE");
0628:
0629: if (tableCatalog == null) {
0630: tableCatalog = "";
0631: }
0632: if (tableSchema == null) {
0633: tableSchema = "";
0634: }
0635:
0636: // fill in table info
0637: String[] tableItem = new String[4]; // hold info for each table
0638: tableItem[NAME] = tableName;
0639: tableItem[CATALOG] = tableCatalog;
0640: tableItem[SCHEMA] = tableSchema;
0641: tableItem[TYPE] = tableType;
0642:
0643: // add table to Vector
0644: v.add(tableItem);
0645: }
0646:
0647: // now copy Vector to array to return back
0648: if (v.size() > 0) {
0649: tables = new String[v.size()][4];
0650: v.copyInto(tables);
0651: }
0652: rs.close();
0653: return tables;
0654: } catch (Exception e) {
0655: e.printStackTrace();
0656: errMsg = e.getLocalizedMessage();
0657: throw e;
0658: }
0659: }
0660:
0661: /**
0662: * Gets the prepared statement metadata (parameters, resultsets).
0663: *
0664: * @param catalog Catalog name
0665: * @param schema Schema name
0666: * @param name Prepared statement name
0667: * @param sqlText SQL text of prepared statement
0668: * @return PrepStmt Prepared statement object
0669: * @throws Exception DOCUMENT ME!
0670: */
0671: public PrepStmt getPrepStmtMetaData(String catalog, String schema,
0672: String name, String sqlText) throws Exception {
0673:
0674: errMsg = "";
0675: checkPrepStmtMetaData = false;
0676: PrepStmt newPrepStmt = null;
0677: try {
0678:
0679: // make sure there is some sql text for the prepared statement
0680: if ((sqlText == null) || (sqlText.equals(""))) {
0681: return null;
0682: }
0683:
0684: // fill in name and sql text
0685: newPrepStmt = new PrepStmt(name, catalog, schema, sqlText);
0686:
0687: // prepare the statement
0688: PreparedStatement pstmt = dbconn.prepareStatement(sqlText);
0689:
0690: // Parameter metadata only available through JDBC 3.0, JDK 1.4
0691: // get parameter meta data of the prepared statment from the DB connection
0692: Parameter[] parameters = null;
0693: parameters = getPrepStmtParameters(pstmt);
0694: newPrepStmt.setParameters(parameters);
0695:
0696: ResultSetColumn[] cols = null;
0697:
0698: // get the resultset metadata
0699: // of the prepared statment from the DB connection
0700: StringTokenizer tok = new StringTokenizer(sqlText);
0701: if (tok.hasMoreElements()) {
0702: String firstTok = (String) tok.nextElement();
0703: if (firstTok.equalsIgnoreCase("select")) {
0704: cols = getPrepStmtResultSetColumns(pstmt);
0705: }
0706: } else {
0707: cols = null;
0708: }
0709:
0710: // set the prepared statement's resultset columns
0711: newPrepStmt.setResultSetColumns(cols);
0712:
0713: checkPrepStmtMetaData = errPrepStmtParameters
0714: && errPrepStmtResultSetColumns;
0715:
0716: pstmt.close();
0717:
0718: } catch (Exception e) {
0719: e.printStackTrace();
0720: errMsg = e.getLocalizedMessage();
0721: checkPrepStmtMetaData = true;
0722: //throw e;
0723: }
0724: return newPrepStmt;
0725: }
0726:
0727: public PrepStmt getPrepStmtMetaData() throws Exception {
0728: PrepStmt newPrepStmt = null;
0729: try {
0730: newPrepStmt = getPrepStmtMetaData(null, null, null, sqlText);
0731: } catch (Exception e) {
0732: errMsg = e.getLocalizedMessage();
0733: //throw e;
0734: }
0735: return newPrepStmt;
0736: }
0737:
0738: /**
0739: * Returns a list of procedures matching in the passed in filters.
0740: *
0741: * @param catalog Catalog name
0742: * @param schemaPattern Schema pattern
0743: * @param procedurePattern Procedure name pattern
0744: * @return String[][] List of procedures matching search filters
0745: * @throws Exception DOCUMENT ME!
0746: */
0747: public String[][] getProcedures(String catalog,
0748: String schemaPattern, String procedurePattern)
0749: throws Exception {
0750: errMsg = "";
0751: String[][] procedures = null; // array of procedure structures: Name, Catalog, Schema, Type
0752: try {
0753: if (catalog.equals("")) {
0754: catalog = null;
0755: }
0756: if (schemaPattern.equals("")) {
0757: schemaPattern = null;
0758: }
0759: if (procedurePattern.equals("")) {
0760: procedurePattern = null;
0761: }
0762:
0763: if (procedurePattern != null) {
0764: procedurePattern = getJDBCSearchPattern(procedurePattern);
0765: }
0766:
0767: Vector v = new Vector();
0768:
0769: ResultSet rs = dbmeta.getProcedures(catalog, schemaPattern,
0770: procedurePattern);
0771: while (rs.next()) {
0772: String procedureCatalog = rs.getString("PROCEDURE_CAT");
0773: String procedureSchema = rs
0774: .getString("PROCEDURE_SCHEM");
0775: String procedureName = rs.getString("PROCEDURE_NAME");
0776: String procedureType = getProcedureTypeDescription(rs
0777: .getShort("PROCEDURE_TYPE"));
0778:
0779: if (procedureCatalog == null) {
0780: procedureCatalog = "";
0781: }
0782: if (procedureSchema == null) {
0783: procedureSchema = "";
0784: }
0785:
0786: // fill in procedure info
0787: String[] procedureItem = new String[4]; // hold info for each procedure
0788: procedureItem[NAME] = procedureName;
0789: procedureItem[CATALOG] = procedureCatalog;
0790: procedureItem[SCHEMA] = procedureSchema;
0791: procedureItem[TYPE] = procedureType;
0792:
0793: // add procedure to Vector
0794: v.add(procedureItem);
0795: }
0796:
0797: // now copy Vector to array to return back
0798: if (v.size() > 0) {
0799: procedures = new String[v.size()][4];
0800: v.copyInto(procedures);
0801: }
0802: rs.close();
0803: } catch (Exception e) {
0804: e.printStackTrace();
0805: errMsg = e.getLocalizedMessage();
0806: //throw e;
0807: }
0808: return procedures;
0809: }
0810:
0811: /**
0812: * Returns a list of primary keys for a table.
0813: *
0814: * @param tcatalog Catalog name
0815: * @param tschema Schema name
0816: * @param tname Table name
0817: * @return List List of primary keys
0818: * @throws Exception DOCUMENT ME!
0819: */
0820: public List getPrimaryKeys(String tcatalog, String tschema,
0821: String tname) throws Exception {
0822: List pkList = Collections.EMPTY_LIST;
0823: ResultSet rs = null;
0824:
0825: errMsg = "";
0826: try {
0827: if (tcatalog.equals("")) {
0828: tcatalog = null;
0829: }
0830: if (tschema.equals("")) {
0831: tschema = null;
0832: }
0833:
0834: rs = dbmeta.getPrimaryKeys(tcatalog, tschema, tname);
0835: pkList = KeyColumn.createPrimaryKeyColumnList(rs);
0836: } catch (Exception e) {
0837: e.printStackTrace();
0838: errMsg = e.getLocalizedMessage();
0839: throw e;
0840: } finally {
0841: if (rs != null) {
0842: try {
0843: rs.close();
0844: } catch (SQLException e) {
0845: /* Ignore */;
0846: }
0847: }
0848: }
0849:
0850: return pkList;
0851: }
0852:
0853: /**
0854: * Returns a list of foreign keys for a table.
0855: *
0856: * @param tcatalog Catalog name
0857: * @param tschema Schema name
0858: * @param tname Table name
0859: * @return List List of foreign keys
0860: * @throws Exception DOCUMENT ME!
0861: */
0862: public List getForeignKeys(String tcatalog, String tschema,
0863: String tname) throws Exception {
0864: errMsg = "";
0865: List fkList = Collections.EMPTY_LIST;
0866: ResultSet rs = null;
0867:
0868: try {
0869: if (tcatalog.equals("")) {
0870: tcatalog = null;
0871: }
0872: if (tschema.equals("")) {
0873: tschema = null;
0874: }
0875: rs = dbmeta.getImportedKeys(tcatalog, tschema, tname);
0876: fkList = ForeignKeyColumn.createForeignKeyColumnList(rs);
0877: } catch (Exception e) {
0878: e.printStackTrace();
0879: errMsg = e.getLocalizedMessage();
0880: throw e;
0881: } finally {
0882: if (rs != null) {
0883: try {
0884: rs.close();
0885: } catch (SQLException e) {
0886: /* Ignore */;
0887: }
0888: }
0889: }
0890:
0891: return fkList;
0892: }
0893:
0894: /**
0895: * Gets the procedure metadata (parameters).
0896: *
0897: * @param pcatalog Catalog name
0898: * @param pschema Schema name
0899: * @param pname Procedure name
0900: * @param ptype Procedure type
0901: * @return Procedure object
0902: * @throws Exception DOCUMENT ME!
0903: */
0904: public Procedure getProcedureMetaData() throws Exception {
0905: return getProcedureMetaData(null, null, null, sqlText);
0906: }
0907:
0908: /**
0909: * Gets the procedure metadata (parameters).
0910: *
0911: * @param pcatalog Catalog name
0912: * @param pschema Schema name
0913: * @param pname Procedure name
0914: * @param ptype Procedure type
0915: * @return Procedure object
0916: * @throws Exception DOCUMENT ME!
0917: */
0918: public Procedure getProcedureMetaData(String pcatalog,
0919: String pschema, String pname, String ptype)
0920: throws Exception {
0921: Procedure newProcedure = new Procedure(pname, pcatalog,
0922: pschema, ptype);
0923:
0924: try {
0925: // create a new procedure object
0926: Vector v = new Vector();
0927:
0928: if (pcatalog.equals("")) {
0929: pcatalog = null;
0930: }
0931: if (pschema.equals("")) {
0932: pschema = null;
0933: }
0934:
0935: int colCount = 0;
0936: boolean isFunction = false;
0937: boolean hasParameters = true;
0938: // indicates if the procedure is within a package or standalone
0939: boolean isPackaged = true;
0940:
0941: ResultSetColumn resultCol = new ResultSetColumn();
0942: ArrayList paramIndices = new ArrayList(); // arraylist to hold the indices of the paramters that return resultsets
0943: ArrayList result = new ArrayList(); // arraylist to hold ResultSetColumns objects
0944:
0945: // check if the procedure is within a package or not
0946: if (pcatalog == null
0947: || pcatalog.trim().equalsIgnoreCase("")) {
0948: isPackaged = false;
0949: }
0950:
0951: dbmeta = dbconn.getMetaData();
0952: // get procedure parameter information
0953: ResultSet rs = dbmeta.getProcedureColumns(pcatalog,
0954: pschema, pname, "%");
0955:
0956: Parameter[] parameters = null;
0957: int pos = 0;
0958: int paramIndex = 0;
0959: boolean hasReturn = false;
0960: CallableStatement cstmt = dbconn.prepareCall(sqlText);
0961: while (rs.next()) {
0962: pos++;
0963: String parmName = rs.getString("COLUMN_NAME");
0964: if (rs.getShort("COLUMN_TYPE") == DatabaseMetaData.procedureColumnReturn) {
0965: // this is a function, so set the flag to true
0966: isFunction = true;
0967: }
0968: colCount++;
0969: if (parmName != null) {
0970: // strip off "@" in front of parameter name
0971: if (parmName.charAt(0) == '@') {
0972: parmName = parmName.substring(1);
0973: }
0974: } else {
0975: // parameter name is not return - call it "param<pos>"
0976: parmName = "param" + String.valueOf(pos);
0977: }
0978: String sqlType = getSQLTypeDescription(rs
0979: .getInt("DATA_TYPE"));
0980: String javaType = getJavaFromSQLTypeDescription(sqlType);
0981: //added abey for Procedure ResultSet
0982: int dataType = rs.getInt("DATA_TYPE");
0983: if ((dataType == java.sql.Types.OTHER)
0984: && (rs.getString("TYPE_NAME")
0985: .equalsIgnoreCase("REF CURSOR"))) {
0986: sqlType = "RESULTSET";
0987: javaType = "java.sql.ResultSet";
0988: }
0989: String paramType = getParamTypeDescription(rs
0990: .getShort("COLUMN_TYPE"));
0991: int nullable = rs.getShort("NULLABLE");
0992: int numericPrecision = rs.getInt("PRECISION");
0993: short numericScale = rs.getShort("SCALE");
0994:
0995: // create a parameter and add it to the vector
0996: Parameter parm = new Parameter(parmName, javaType);
0997: boolean isNullable = false;
0998: if (nullable == DatabaseMetaData.procedureNullable) {
0999: isNullable = true;
1000: }
1001: parm.setJavaType(javaType);
1002: parm.setSqlType(sqlType);
1003: parm.setParamType(paramType);
1004: parm.setOrdinalPosition(pos);
1005: parm.setNumericPrecision(numericPrecision);
1006: parm.setNumericScale(numericScale);
1007: parm.setIsNullable(isNullable);
1008:
1009: if (paramType.equals("RETURN")) {
1010: hasReturn = true;
1011: }
1012: paramIndex++;
1013: String parameterName = rs.getString("COLUMN_NAME");
1014: int targetSqlType = rs.getInt("DATA_TYPE");
1015: int colType = rs.getShort("COLUMN_TYPE");
1016: String type_Name = rs.getString("TYPE_NAME");
1017:
1018: if (colType == DatabaseMetaData.procedureColumnIn) {
1019: if ((targetSqlType == 1111)
1020: && (type_Name.equals("PL/SQL TABLE"))) {
1021: targetSqlType = -14;
1022: }
1023:
1024: if ((targetSqlType == 1111)
1025: && (type_Name.equals("PL/SQL RECORD"))) {
1026: targetSqlType = -14;
1027: }
1028: cstmt.setNull(paramIndex, targetSqlType);
1029: }
1030:
1031: if (colType == DatabaseMetaData.procedureColumnInOut
1032: || colType == DatabaseMetaData.procedureColumnOut) {
1033: try {
1034: // if the parameter is a cursor type, add its index to the arraylist
1035: if ((targetSqlType == 1111)
1036: && (type_Name.equals("REF CURSOR"))) {
1037: targetSqlType = -10;
1038: paramIndices.add(new Integer(paramIndex));
1039: }
1040: cstmt.registerOutParameter(paramIndex,
1041: targetSqlType);
1042: } catch (SQLException e) {
1043: System.out.println(e.getMessage());
1044: e.printStackTrace();
1045: checkProcMetaData = true;
1046: //throw e;
1047: }
1048: }
1049:
1050: // check if the parameter is RETURN type (i.e. it is a function)
1051: if (colType == DatabaseMetaData.procedureColumnReturn) {
1052: try {
1053: // if the parameter is a cursor type, add its index to the arraylist
1054: if ((targetSqlType == 1111)
1055: && (type_Name.equals("REF CURSOR"))) {
1056: targetSqlType = -10;
1057: paramIndices.add(new Integer(paramIndex));
1058: }
1059: cstmt.registerOutParameter(paramIndex,
1060: targetSqlType);
1061: } catch (SQLException e) {
1062: System.out.println(e.getMessage());
1063: e.printStackTrace();
1064: checkProcMetaData = true;
1065: //throw e;
1066: }
1067: }
1068:
1069: // add to vector
1070: v.add(parm);
1071: }
1072:
1073: rs.close();
1074:
1075: // now copy Vector to array
1076: if (v.size() > 0) {
1077: parameters = new Parameter[v.size()];
1078: v.copyInto(parameters);
1079: }
1080:
1081: // now set up parameters in the procedure to return
1082: newProcedure.setParameters(parameters);
1083: newProcedure.setHasReturn(hasReturn);
1084: ///////////////////////////////////////////////////
1085: try {
1086: if (hasReturn) {
1087: boolean resultsAvailable = cstmt.execute();
1088: int count = -1;
1089: int numResults = paramIndices.size();
1090:
1091: Iterator paramIdxIter = paramIndices.iterator();
1092:
1093: // iterate through the resultsets returned, whose indices are stored in the arraylist
1094: while (paramIdxIter.hasNext()) {
1095: ArrayList resultArray = new ArrayList(); // arraylist to hold the objects of ResultSetColumn
1096: count += 1;
1097: // get the index (from the arraylist) of the parameter which is a resultset
1098: int index = ((Integer) paramIdxIter.next())
1099: .intValue();
1100: ResultSet paramRS;
1101: ResultSetMetaData rsmd;
1102: // if the resultset returns nothing, set the metadata object to null
1103: try {
1104: paramRS = (ResultSet) cstmt
1105: .getObject(index);
1106: rsmd = paramRS.getMetaData();
1107: } catch (SQLException e) {
1108: rsmd = null;
1109: checkProcMetaData = true;
1110: }
1111:
1112: int rsmdColCount = 0;
1113: if (rsmd != null) {
1114: rsmdColCount = rsmd.getColumnCount();
1115: }
1116: // scroll through the resultset column information
1117: for (int i = 1; i <= rsmdColCount; i++) {
1118: ResultSetColumn currCol = new ResultSetColumn();
1119: currCol.setOrdinalPosition(i);
1120: currCol.setName(rsmd.getColumnName(i));
1121: currCol.setLabel(rsmd.getColumnLabel(i));
1122: currCol
1123: .setSqlType(getSQLTypeDescription(rsmd
1124: .getColumnType(i)));
1125: currCol.setJavaType((String) SQLTOJAVATYPES
1126: .get(getSQLTypeDescription(rsmd
1127: .getColumnType(i))));
1128:
1129: if (rsmd.isNullable(i) == DatabaseMetaData.columnNullable) {
1130: currCol.setIsNullable(true);
1131: } else {
1132: currCol.setIsNullable(false);
1133: }
1134: // add ResultSetColumn object to the arraylist
1135: boolean addToArray = resultArray
1136: .add(currCol);
1137: }
1138:
1139: // add the arraylist having ResultSetColumn objects to the ResultSetColumns object
1140: // now add this ResultSetColumns object to the arraylist object (result)
1141: if (resultArray.size() > 0) {
1142: ResultSetColumns rsColbj = new ResultSetColumns();
1143: rsColbj.setColumns(resultArray);
1144: rsColbj.setName(pname + "_" + count);
1145: result.add(rsColbj);
1146: }
1147: }
1148: }
1149: } catch (SQLException e) {
1150: // resultset column metadata not supported
1151: System.out.println("\nException occurred: "
1152: + e.getClass().getName() + ", "
1153: + e.getMessage());
1154: e.printStackTrace();
1155: errMsg = e.getLocalizedMessage();
1156: checkProcMetaData = true;
1157: //throw e;
1158: } catch (NullPointerException npe) {
1159: System.out.println("\nException occurred: "
1160: + npe.getClass().getName() + ", "
1161: + npe.getMessage());
1162: npe.printStackTrace();
1163: errMsg = npe.getLocalizedMessage();
1164: checkProcMetaData = true;
1165: //throw npe;
1166: } catch (Exception e) {
1167: // resultset column metadata not supported
1168: System.out.println("\nException occurred: "
1169: + e.getClass().getName() + ", "
1170: + e.getMessage());
1171: e.printStackTrace();
1172: errMsg = e.getLocalizedMessage();
1173: checkProcMetaData = true;
1174: }
1175:
1176: // add the arraylist object to the Procedure object
1177: newProcedure.setResultSetColumns(result);
1178:
1179: ///////////////////////////////////////////////////
1180: } catch (Exception e) {
1181: e.printStackTrace();
1182: checkProcMetaData = true;
1183: //throw e;
1184: }
1185: return newProcedure;
1186:
1187: }
1188:
1189: /**
1190: * Gets the table metadata (columns).
1191: *
1192: * @param tcatalog Catalog name
1193: * @param tschema Schema name
1194: * @param tname Table name
1195: * @param ttype Table type
1196: * @return Table object
1197: * @throws Exception DOCUMENT ME!
1198: */
1199: public Table getTableMetaData(String tcatalog, String tschema,
1200: String tname, String ttype) throws Exception {
1201: errMsg = "";
1202: ResultSet rs = null;
1203:
1204: try {
1205: // create a new Table object
1206: Table newTable = new Table(tname, tcatalog, tschema, ttype);
1207: Vector v = new Vector();
1208:
1209: if (tcatalog.equals("")) {
1210: tcatalog = null;
1211: }
1212:
1213: if (tschema.equals("")) {
1214: tschema = null;
1215: }
1216:
1217: // get table column information
1218: rs = dbmeta.getColumns(tcatalog, tschema, tname, "%");
1219:
1220: TableColumn[] columns = null;
1221:
1222: while (rs.next()) {
1223: String defaultValue = rs.getString("COLUMN_DEF");
1224:
1225: int sqlTypeCode = rs.getInt("DATA_TYPE");
1226:
1227: String colName = rs.getString("COLUMN_NAME");
1228: String sqlType = getSQLTypeDescription(sqlTypeCode);
1229: String javaType = getJavaFromSQLTypeDescription(sqlType);
1230:
1231: int position = rs.getInt("ORDINAL_POSITION");
1232:
1233: int scale = rs.getInt("DECIMAL_DIGITS");
1234: int precision = rs.getInt("COLUMN_SIZE");
1235: int radix = rs.getInt("NUM_PREC_RADIX");
1236:
1237: // create a table column and add it to the vector
1238: TableColumn col = new TableColumn(colName, javaType);
1239: boolean isNullable = false;
1240: if (rs.getString("IS_NULLABLE").equals("YES")) {
1241: isNullable = true;
1242: }
1243: col.setJavaType(javaType);
1244: col.setSqlType(sqlType);
1245: col.setIsNullable(isNullable);
1246: col.setIsSelected(true);
1247: col.setIsPrimaryKey(false);
1248: col.setIsForeignKey(false);
1249: col.setSqlTypeCode(sqlTypeCode);
1250:
1251: col.setOrdinalPosition(position);
1252: col.setNumericPrecision(precision);
1253: col.setNumericScale(scale);
1254: col.setNumericRadix(radix);
1255:
1256: if (defaultValue != null) {
1257: col.setDefaultValue(defaultValue.trim());
1258: }
1259:
1260: // add to vector
1261: v.add(col);
1262: }
1263:
1264: // now copy Vector to array
1265: if (v.size() > 0) {
1266: columns = new TableColumn[v.size()];
1267: v.copyInto(columns);
1268: }
1269:
1270: // now set up columns in the table to return
1271: newTable.setColumns(columns);
1272:
1273: // now check the columns that are primary keys
1274: checkPrimaryKeys(newTable);
1275:
1276: // now check the columns that are foreign keys
1277: checkForeignKeys(newTable);
1278:
1279: // catch exceptions for this as index only makes sense for
1280: // tables and not views (can't check the table type because it's dependent on driver)
1281: try {
1282: // get index info for this table
1283: rs = dbmeta.getIndexInfo(tcatalog, tschema, tname,
1284: false, true);
1285: newTable.setIndexList(IndexColumn.createIndexList(rs));
1286: } catch (Exception e) {
1287: // ignore and continue
1288: errMsg = e.getLocalizedMessage();
1289: }
1290:
1291: return newTable;
1292: } catch (Exception e) {
1293: e.printStackTrace();
1294: errMsg = e.getLocalizedMessage();
1295: throw e;
1296: } finally {
1297: if (rs != null) {
1298: try {
1299: rs.close();
1300: } catch (SQLException e) {
1301: /* Ignore... */;
1302: }
1303: }
1304: }
1305: }
1306:
1307: /**
1308: * Converts a JDBC SQL Type to a Java Type.
1309: *
1310: * @param sqlType JDBC SQL Type
1311: * @return Java Type
1312: */
1313: public String getJavaFromSQLTypeDescription(String sqlType) {
1314: Object t;
1315: String javaType = "java.lang.String"; // default value
1316: t = SQLTOJAVATYPES.get(sqlType);
1317:
1318: if (t != null) {
1319: javaType = t.toString();
1320: }
1321:
1322: return javaType;
1323: }
1324:
1325: /**
1326: * Converts the numeric value of a JDBC SQL type to
1327: * a display string.
1328: *
1329: * @param type JDBC numeric SQL type value
1330: * @return JDBC SQL type string
1331: */
1332: public static String getSQLTypeDescription(int type) {
1333: // returns a String representing the passed in numeric
1334: // SQL type
1335: switch (type) {
1336: case java.sql.Types.ARRAY:
1337: return "ARRAY";
1338: case java.sql.Types.BIGINT:
1339: return "BIGINT";
1340: case java.sql.Types.BINARY:
1341: return "BINARY";
1342: case java.sql.Types.BIT:
1343: return "BIT";
1344: case java.sql.Types.BLOB:
1345: return "BLOB";
1346: case 16:
1347: //case java.sql.Types.BOOLEAN:
1348: return "BOOLEAN";
1349: case java.sql.Types.CHAR:
1350: return "CHAR";
1351: case java.sql.Types.CLOB:
1352: return "CLOB";
1353: case 70:
1354: //case java.sql.Types.DATALINK:
1355: return "DATALINK";
1356: case java.sql.Types.DATE:
1357: return "DATE";
1358: case java.sql.Types.DECIMAL:
1359: return "DECIMAL";
1360: case java.sql.Types.DOUBLE:
1361: return "DOUBLE";
1362: case java.sql.Types.FLOAT:
1363: return "FLOAT";
1364: case java.sql.Types.INTEGER:
1365: return "INTEGER";
1366: case java.sql.Types.JAVA_OBJECT:
1367: return "JAVA_OBJECT";
1368: case java.sql.Types.LONGVARBINARY:
1369: return "LONGVARBINARY";
1370: case java.sql.Types.LONGVARCHAR:
1371: return "LONGVARCHAR";
1372: case java.sql.Types.NULL:
1373: return "NULL";
1374: case java.sql.Types.NUMERIC:
1375: return "NUMERIC";
1376: case java.sql.Types.OTHER:
1377: return "OTHER";
1378: case java.sql.Types.REAL:
1379: return "REAL";
1380: case java.sql.Types.REF:
1381: return "REF";
1382: case java.sql.Types.SMALLINT:
1383: return "SMALLINT";
1384: case java.sql.Types.STRUCT:
1385: return "STRUCT";
1386: case java.sql.Types.TIME:
1387: return "TIME";
1388: case java.sql.Types.TIMESTAMP:
1389: return "TIMESTAMP";
1390: case java.sql.Types.TINYINT:
1391: return "TINYINT";
1392: case java.sql.Types.VARBINARY:
1393: return "VARBINARY";
1394: case java.sql.Types.VARCHAR:
1395: return "VARCHAR";
1396: }
1397: // all others default to OTHER
1398: return "OTHER";
1399: }
1400:
1401: /**
1402: * Converts a text representation of a JDBC SQL type to
1403: * a display string.
1404: *
1405: * @param sqlText JDBC SQL type string
1406: * @return JDBC numeric SQL type value
1407: */
1408: public static int getSQLTypeCode(String sqlText) {
1409: if (sqlText == null) {
1410: throw new IllegalArgumentException(
1411: "Must supply non-null String value for sqlText.");
1412: }
1413:
1414: sqlText = sqlText.trim().toUpperCase();
1415: for (int i = 0; i < SQLTYPES.length; i++) {
1416: if (SQLTYPES[i].equals(sqlText)) {
1417: return SQLTYPE_CODES[i];
1418: }
1419: }
1420:
1421: return java.sql.Types.OTHER;
1422: }
1423:
1424: private String getJavaTypeDescription(int type) {
1425: // converts a numeric SQL type to a Java type
1426: String javaType = "java.lang.String";
1427:
1428: switch (type) {
1429: case java.sql.Types.ARRAY:
1430: javaType = "java.sql.ARRAY";
1431: case java.sql.Types.BIGINT:
1432: javaType = "long";
1433: case java.sql.Types.BINARY:
1434: javaType = "byte[]";
1435: case java.sql.Types.BIT:
1436: javaType = "boolean";
1437: case java.sql.Types.BLOB:
1438: javaType = "java.sql.Blob";
1439: //case java.sql.Types.BOOLEAN:
1440: // javaType = "boolean";
1441: case java.sql.Types.CHAR:
1442: javaType = "java.lang.String";
1443: case java.sql.Types.CLOB:
1444: javaType = "java.sql.Clob";
1445: case java.sql.Types.DATE:
1446: javaType = "java.sql.Date";
1447: case java.sql.Types.DECIMAL:
1448: javaType = "java.math.BigDecimal";
1449: case java.sql.Types.DOUBLE:
1450: javaType = "double";
1451: case java.sql.Types.FLOAT:
1452: javaType = "double";
1453: case java.sql.Types.INTEGER:
1454: javaType = "int";
1455: case java.sql.Types.LONGVARBINARY:
1456: javaType = "byte[]";
1457: case java.sql.Types.LONGVARCHAR:
1458: javaType = "java.lang.String";
1459: case java.sql.Types.NUMERIC:
1460: javaType = "java.math.BigDecimal";
1461: //case java.sql.Types.OTHER:
1462: // javaType = "java.sql.Blob";
1463: case java.sql.Types.REAL:
1464: javaType = "float";
1465: case java.sql.Types.REF:
1466: javaType = "java.sql.Ref";
1467: case java.sql.Types.SMALLINT:
1468: javaType = "short";
1469: case java.sql.Types.STRUCT:
1470: javaType = "java.sql.Struct";
1471: case java.sql.Types.TIME:
1472: javaType = "java.sql.Time";
1473: case java.sql.Types.TIMESTAMP:
1474: javaType = "java.sql.Timestamp";
1475: case java.sql.Types.TINYINT:
1476: javaType = "byte";
1477: case java.sql.Types.VARBINARY:
1478: javaType = "byte[]";
1479: case java.sql.Types.VARCHAR:
1480: javaType = "java.lang.String";
1481: }
1482: return javaType;
1483: }
1484:
1485: private String getParamTypeDescription(int type) {
1486: String descr = "";
1487:
1488: if (type == DatabaseMetaData.procedureColumnIn) {
1489: descr = "IN";
1490: } else if (type == DatabaseMetaData.procedureColumnInOut) {
1491: descr = "INOUT";
1492: } else if (type == DatabaseMetaData.procedureColumnOut) {
1493: descr = "OUT";
1494: } else if (type == DatabaseMetaData.procedureColumnReturn) {
1495: descr = "RETURN";
1496: } else if (type == DatabaseMetaData.procedureColumnResult) {
1497: descr = "RESULT";
1498: } else {
1499: descr = "UNKNOWN";
1500: }
1501:
1502: return descr;
1503: }
1504:
1505: private String getProcedureTypeDescription(int type) {
1506: // converts the numeric procedure type code to a string description
1507: String descr = "";
1508: if (type == DatabaseMetaData.procedureNoResult) {
1509: descr = Procedure.PROCEDURE;
1510: } else if (type == DatabaseMetaData.procedureReturnsResult) {
1511: descr = Procedure.FUNCTION;
1512: } else if (type == DatabaseMetaData.procedureResultUnknown) {
1513: descr = Procedure.UNKNOWN;
1514: } else {
1515: descr = Procedure.UNKNOWN;
1516: }
1517:
1518: return descr;
1519: }
1520:
1521: private String getPrepStmtParamTypeDescription(int type) {
1522: String descr = "";
1523:
1524: if (type == ParameterMetaData.parameterModeIn) {
1525: descr = "IN";
1526: } else if (type == ParameterMetaData.parameterModeInOut) {
1527: descr = "INOUT";
1528: } else if (type == ParameterMetaData.parameterModeOut) {
1529: descr = "OUT";
1530: } else if (type == ParameterMetaData.parameterModeUnknown) {
1531: descr = "UNKNOWN";
1532: } else {
1533: descr = "UNKNOWN";
1534: }
1535:
1536: return descr;
1537: }
1538:
1539: private String replaceAllChars(String orig, char oldChar,
1540: String replStr) {
1541: String newString = "";
1542:
1543: for (int i = 0; i < orig.length(); i++) {
1544: if (orig.charAt(i) == oldChar) {
1545: newString = newString + replStr;
1546: } else {
1547: newString = newString + orig.charAt(i);
1548: }
1549: }
1550: return newString;
1551: }
1552:
1553: /** Main routine
1554: *
1555: * @param args command line arguments
1556: */
1557: public static void main(String args[]) {
1558: DBMetaData myDB = new DBMetaData();
1559:
1560: String driver = "";
1561: String url = "";
1562: String user = "";
1563: String pwd = "";
1564:
1565: // connect and get metadata
1566:
1567: // Oracle Native
1568: //
1569: driver = "oracle.jdbc.OracleDriver";
1570: url = "jdbc:oracle:thin:@jlongbgx:1521:jlongbgx";
1571: user = "dgdb";
1572: pwd = "dgdb";
1573: //
1574:
1575: // Sybase Native
1576: /*
1577: driver = "com.sybase.jdbc2.jdbc.SybDriver";
1578: url = "jdbc:sybase:Tds:Atlas:4100?DYNAMIC_PREPARE=true";
1579: user = "dgdb";
1580: pwd = "dgdbdgdb";
1581: */
1582:
1583: //DataDirect Oracle branded
1584: /*
1585: driver = "com.SeeBeyond.jdbc.oracle.OracleDriver";
1586: url = "jdbc:SeeBeyond:oracle://jlongbgx:1521;SID=jlongbgx";
1587: user = "dgdb";
1588: pwd = "dgdb";
1589: */
1590:
1591: //Merant Sybase
1592: /*
1593: driver = "com.SeeBeyond.jdbc.sybase.SybaseDriver";
1594: url = "jdbc:SeeBeyond:sybase://Atlas:4100";
1595: user = "dgdb";
1596: pwd = "dgdbdgdb";
1597: */
1598:
1599: //Merant SQL Server
1600: /*
1601: driver = "com.ddtek.jdbc.sqlserver.SQLServerDriver";
1602: url = "jdbc:datadirect:sqlserver://rpoon";
1603: user = "dgdb";
1604: pwd = "dgdbdgdb";
1605: */
1606:
1607: //Merant DB2
1608: /*
1609: driver = "com.ddtek.jdbc.db2.DB2Driver";
1610: url = "jdbc:datadirect:db2://testdb2:50000;DatabaseName=SAMPLE;"
1611: + "CollectionId=DEFAULT;PackageName=SUSAN";
1612: user = "db2admin";
1613: pwd = "db2admin";
1614: */
1615:
1616: try {
1617: myDB.connectDB(driver, url, user, pwd);
1618: System.out
1619: .println("Successfully connected to " + url + ".");
1620:
1621: // get DB name
1622: System.out.println("Database product name: "
1623: + myDB.getDBName());
1624:
1625: // get DB type
1626: System.out.println("Database Type: " + myDB.getDBType());
1627:
1628: // get the schema names
1629: String[] schemaList = myDB.getSchemas();
1630: // iterate and print schemas
1631: if (schemaList != null) {
1632: for (int i = 0; i < schemaList.length; i++) {
1633: System.out.println(schemaList[i]);
1634: }
1635: }
1636: System.out.println();
1637:
1638: // get tables - pattern matching
1639: System.out.println("TABLES:");
1640: String[][] tableList = myDB.getTablesAndViews("", "",
1641: "DB*", false);
1642: // iterate and print tables
1643: if (tableList != null) {
1644: for (int i = 0; i < tableList.length; i++) {
1645: String[] currTable = tableList[i];
1646: System.out.println(currTable[myDB.CATALOG] + "."
1647: + currTable[myDB.SCHEMA] + "."
1648: + currTable[myDB.NAME] + ":Type="
1649: + currTable[myDB.TYPE]);
1650: }
1651: System.out.println("Table Count = " + tableList.length);
1652: }
1653:
1654: System.out.println();
1655:
1656: // table columns - with datatypes
1657: System.out.println("TABLE COLUMNS:");
1658: // Table mytable = myDB.getTableMetaData("dgdb","dbo","db_employee");
1659: Table mytable = myDB.getTableMetaData("", "DGDB",
1660: "DB_EMPLOYEE", TABLE);
1661: if (mytable != null) {
1662: TableColumn[] tableColumns = mytable.getColumns();
1663: // iterate and print table columns
1664: if (tableColumns != null) {
1665: for (int i = 0; i < tableColumns.length; i++) {
1666: TableColumn currColumn = tableColumns[i];
1667: System.out.println("Column " + (i + 1) + ":"
1668: + currColumn.getName() + "("
1669: + currColumn.getJavaType()
1670: + "):IsNullable="
1671: + currColumn.getIsNullable()
1672: + ":IsSelected="
1673: + currColumn.getIsSelected()
1674: + ":IsPrimaryKey="
1675: + currColumn.getIsPrimaryKey()
1676: + ":IsForeignKey="
1677: + currColumn.getIsForeignKey()
1678: + ":Precision="
1679: + currColumn.getNumericPrecision()
1680: + ":Scale="
1681: + currColumn.getNumericScale());
1682: }
1683: }
1684: }
1685:
1686: System.out.println();
1687:
1688: // get procedures - pattern matching
1689: System.out.println("PROCEDURES:");
1690: String[][] procList = myDB.getProcedures("", "", "P*");
1691: // iterate and print procedures
1692: if (procList != null) {
1693: for (int i = 0; i < procList.length; i++) {
1694: String[] currProc = procList[i];
1695: System.out.println(currProc[myDB.CATALOG] + "."
1696: + currProc[myDB.SCHEMA] + "."
1697: + currProc[myDB.NAME] + ":"
1698: + currProc[myDB.TYPE]);
1699: }
1700: System.out.println("Procedure Count = "
1701: + procList.length);
1702: }
1703:
1704: System.out.println();
1705:
1706: // procedure parameters - with datatypes
1707: System.out.println("PROCEDURE PARAMETERS:");
1708: Procedure myproc = myDB.getProcedureMetaData("", "DGDB",
1709: "PLUSONE", "PROCEDURE");
1710: if (myproc != null) {
1711: Parameter[] procParams = myproc.getParameters();
1712: // iterate and print procedure parameters
1713: if (procParams != null) {
1714: for (int i = 0; i < procParams.length; i++) {
1715: Parameter currParam = procParams[i];
1716: System.out.println("Parameter "
1717: + currParam.getOrdinalPosition() + ":"
1718: + currParam.getName() + "("
1719: + currParam.getJavaType()
1720: + "):ParamType="
1721: + currParam.getParamType()
1722: + ":NumericPrecision="
1723: + currParam.getNumericPrecision()
1724: + ":NumericScale="
1725: + currParam.getNumericScale()
1726: + ":IsNullable="
1727: + currParam.getIsNullable());
1728: }
1729: }
1730: }
1731:
1732: System.out.println();
1733:
1734: // get prepared statement metadata and iterate through resultset columns
1735: PrepStmt myPrep = myDB.getPrepStmtMetaData("", "", "prep1",
1736: "select * from db_employee where RATE=?");
1737: if (myPrep != null) {
1738: System.out.println("PREPARED STATMENT:"
1739: + myPrep.getName());
1740: ResultSetColumn[] rsCols = myPrep.getResultSetColumns();
1741: // iterate and print procedure parameters
1742: if (rsCols != null) {
1743: for (int i = 0; i < rsCols.length; i++) {
1744: ResultSetColumn currCol = rsCols[i];
1745: System.out.println("RS Col "
1746: + currCol.getOrdinalPosition() + ":"
1747: + currCol.getName() + "("
1748: + currCol.getJavaType()
1749: + "):NumericPrecision="
1750: + currCol.getNumericPrecision()
1751: + ":NumericScale="
1752: + currCol.getNumericScale()
1753: + ":IsNullable="
1754: + currCol.getIsNullable());
1755: }
1756: }
1757: }
1758:
1759: System.out.println();
1760:
1761: myDB.disconnectDB();
1762: System.out.println("Successfully disconnected from " + url
1763: + ".");
1764:
1765: } catch (Exception e) {
1766: // get error msg to display
1767: String errMsg = myDB.getErrString();
1768: System.out.println("Exception: " + errMsg);
1769: }
1770: }
1771:
1772: /**
1773: * Get String representing current error message, if any.
1774: *
1775: * @return error message
1776: */
1777: public String getErrString() {
1778: return errMsg;
1779: }
1780:
1781: private Parameter[] getPrepStmtParameters(PreparedStatement pstmt)
1782: throws Exception {
1783: String errMsg = "";
1784: errPrepStmtParameters = false;
1785: Parameter[] parameters = null;
1786:
1787: try {
1788:
1789: ParameterMetaData pmeta = pstmt.getParameterMetaData();
1790: if (pmeta != null) {
1791: int numParams = pmeta.getParameterCount();
1792: if (numParams > 0) {
1793: parameters = new Parameter[numParams];
1794: // get info for each parameter
1795: for (int i = 1; i <= numParams; i++) {
1796: Parameter currParam = new Parameter();
1797: String paramname = "param" + String.valueOf(i);
1798: currParam.setName(paramname);
1799:
1800: // try to get the sql type info - default to VARCHAR
1801: String sqltype = "VARCHAR";
1802: try {
1803: sqltype = getSQLTypeDescription(pmeta
1804: .getParameterType(i));
1805: } catch (SQLException e) {
1806: // default to VARCHAR if we can't get the type
1807: errPrepStmtParameters = true;
1808: e.printStackTrace();
1809: errMsg = e.getLocalizedMessage();
1810: //throw e;
1811: }
1812:
1813: // try to get the java type info - default to String
1814: /**
1815: * Changing it to not use metadata class name and instead use the HashMap SQLTOJAVATYPES.
1816: * Without the change the parameter datatypes java.lang.Double and WSDLGenerator look up list
1817: * exepects native type double, float, short etc.
1818: **/
1819: String javatype = "java.lang.String";
1820: javatype = getJavaFromSQLTypeDescription(sqltype);
1821:
1822: // try to get the numeric precision, default to 0
1823: int precision = 0;
1824: try {
1825: precision = pmeta.getPrecision(i);
1826: } catch (SQLException e) {
1827: errPrepStmtParameters = true;
1828: e.printStackTrace();
1829: errMsg = e.getLocalizedMessage();
1830: //throw e;
1831: }
1832:
1833: // try to get the numeric scale, default to 0
1834: int scale = 0;
1835: try {
1836: scale = pmeta.getScale(i);
1837: } catch (SQLException e) {
1838: errPrepStmtParameters = true;
1839: e.printStackTrace();
1840: errMsg = e.getLocalizedMessage();
1841: //throw e;
1842: }
1843:
1844: // try to get the param type, default to IN
1845: // always default it since getParameterMode() in data direct 3.3 throws exception
1846: // and 3.4 return UNKNOWN type
1847: String paramType = "IN";
1848: /*
1849: try {
1850: paramType = getPrepStmtParamTypeDescription(pmeta.getParameterMode((i)));
1851: } catch (SQLException e) {
1852: errPrepStmtParameters = true;
1853: e.printStackTrace();
1854: errMsg = e.getLocalizedMessage();
1855: }
1856: */
1857:
1858: // try to get is nullable, default to TRUE
1859: boolean isNullable = true;
1860: try {
1861: if (pmeta.isNullable(i) == java.sql.ParameterMetaData.parameterNullable) {
1862: isNullable = true;
1863: } else {
1864: isNullable = false;
1865: }
1866: } catch (SQLException e) {
1867: errPrepStmtParameters = true;
1868: e.printStackTrace();
1869: errMsg = e.getLocalizedMessage();
1870: //throw e;
1871: }
1872:
1873: currParam.setJavaType(javatype);
1874: currParam.setSqlType(sqltype);
1875: currParam.setNumericPrecision(precision);
1876: currParam.setNumericScale(scale);
1877: currParam.setOrdinalPosition(i);
1878: currParam.setParamType(paramType);
1879: currParam.setIsNullable(isNullable);
1880:
1881: parameters[i - 1] = currParam;
1882: }
1883: }
1884: }
1885: } catch (Exception e) {
1886: // parameter metadata not supported
1887: parameters = null;
1888: errPrepStmtParameters = true;
1889: e.printStackTrace();
1890: errMsg = e.getLocalizedMessage();
1891: //throw e;
1892: }
1893:
1894: return parameters;
1895: }
1896:
1897: private ResultSetColumn[] getPrepStmtResultSetColumns(
1898: PreparedStatement pstmt) throws SQLException {
1899: String errMsg = "";
1900: errPrepStmtResultSetColumns = false;
1901: ResultSetColumn[] cols = null;
1902: try {
1903: ResultSetMetaData rsmd = pstmt.getMetaData();
1904: int count = 0;
1905: if (rsmd != null) {
1906: count = rsmd.getColumnCount();
1907: } else {
1908: errPrepStmtResultSetColumns = true;
1909: }
1910: if (count > 0) {
1911: // scroll through the resultset column information
1912: cols = new ResultSetColumn[count];
1913: for (int i = 1; i <= count; i++) {
1914: ResultSetColumn currCol = new ResultSetColumn();
1915: currCol.setName(rsmd.getColumnName(i));
1916: currCol.setSqlType(getSQLTypeDescription(rsmd
1917: .getColumnType(i)));
1918: currCol
1919: .setJavaType(getJavaFromSQLTypeDescription(currCol
1920: .getSqlType()));
1921: currCol.setOrdinalPosition(i);
1922: currCol.setNumericPrecision(rsmd.getPrecision(i));
1923: currCol.setNumericScale(rsmd.getScale(i));
1924:
1925: if (rsmd.isNullable(i) == DatabaseMetaData.columnNullable) {
1926: currCol.setIsNullable(true);
1927: } else {
1928: currCol.setIsNullable(false);
1929: }
1930:
1931: cols[i - 1] = currCol;
1932: }
1933: }
1934:
1935: } catch (SQLException e) {
1936: // resultset column metadata not supported
1937: errPrepStmtResultSetColumns = true;
1938: cols = null;
1939: e.printStackTrace();
1940: errMsg = e.getLocalizedMessage();
1941: throw e;
1942: }
1943:
1944: return cols;
1945: }
1946:
1947: public void getProcResultSetColumns(CallableStatement cstmt,
1948: Procedure proc) throws SQLException, NullPointerException {
1949: String errMsg = "";
1950: int colCount = 0;
1951: boolean isFunction = false;
1952: boolean hasReturn = false;
1953: boolean hasParameters = true;
1954: // indicates if the procedure is within a package or standalone
1955: boolean isPackaged = true;
1956: cstmt = dbconn.prepareCall(sqlText);
1957: ArrayList paramIndices = new ArrayList();
1958: ArrayList result = new ArrayList();
1959: int paramIndex = 0;
1960: try {
1961: Parameter[] parameters = proc.getParameters();
1962: colCount = proc.getNumParameters();
1963: // loop through the list of parameters and register them
1964: if (colCount > 0) {
1965: for (int j = 0; j < colCount; j++) {
1966: paramIndex++;
1967: Parameter param = parameters[j];
1968: String parameterName = param.getName();
1969: String sqlType = param.getSqlType();
1970: int sqlTypeCode = getSQLTypeCode(sqlType);
1971: String colType = param.getParamType();
1972: cstmt.setNull(paramIndex, sqlTypeCode);
1973:
1974: if (colType.equalsIgnoreCase("INOUT")
1975: || colType.equalsIgnoreCase("OUT")) {
1976: try {
1977: // if the parameter is a cursor type, add its index to the arraylist
1978: if ((sqlTypeCode == 1111)
1979: && (colType.equals("OTHER"))) {
1980: sqlTypeCode = java.sql.Types.OTHER;
1981: paramIndices
1982: .add(new Integer(paramIndex));
1983: }
1984: cstmt.registerOutParameter(paramIndex,
1985: sqlTypeCode);
1986: } catch (SQLException e) {
1987: System.out.println(e.getMessage());
1988: e.printStackTrace();
1989: throw e;
1990: }
1991: }
1992:
1993: // check if the parameter is RETURN type (i.e. it is a function)
1994: if (colType == "RETURN") {
1995: try {
1996:
1997: // if the parameter is a cursor type, add its index to the arraylist
1998: if ((sqlTypeCode == 1111)
1999: && (colType.equals("OTHER"))) {
2000: sqlTypeCode = java.sql.Types.OTHER;
2001: paramIndices
2002: .add(new Integer(paramIndex));
2003: }
2004: hasReturn = true;
2005: cstmt.registerOutParameter(paramIndex,
2006: sqlTypeCode);
2007: } catch (SQLException e) {
2008: System.out.println(e.getMessage());
2009: e.printStackTrace();
2010: throw e;
2011: }
2012: }
2013: }
2014: }
2015: // execute the stored procedure
2016: if (hasReturn) {
2017: boolean resultsAvailable = cstmt.execute();
2018:
2019: int count = -1;
2020: int numResults = paramIndices.size();
2021:
2022: Iterator paramIdxIter = paramIndices.iterator();
2023:
2024: // iterate through the resultsets returned, whose indices are stored in the arraylist
2025: while (paramIdxIter.hasNext()) {
2026: ArrayList resultArray = new ArrayList(); // arraylist to hold the objects of ResultSetColumn
2027: count += 1;
2028: // get the index (from the arraylist) of the parameter which is a resultset
2029: int index = ((Integer) paramIdxIter.next())
2030: .intValue();
2031: ResultSet paramRS;
2032: ResultSetMetaData rsmd;
2033: // if the resultset returns nothing, set the metadata object to null
2034: try {
2035: paramRS = (ResultSet) cstmt.getObject(index);
2036: rsmd = paramRS.getMetaData();
2037: } catch (SQLException e) {
2038: rsmd = null;
2039: }
2040:
2041: int rsmdColCount = 0;
2042: if (rsmd != null) {
2043: rsmdColCount = rsmd.getColumnCount();
2044: }
2045: // scroll through the resultset column information
2046: for (int i = 1; i <= rsmdColCount; i++) {
2047: ResultSetColumn currCol = new ResultSetColumn();
2048: currCol.setOrdinalPosition(i);
2049: currCol.setName(rsmd.getColumnName(i));
2050: currCol.setLabel(rsmd.getColumnLabel(i));
2051: currCol.setSqlType(getSQLTypeDescription(rsmd
2052: .getColumnType(i)));
2053: currCol.setJavaType((String) SQLTOJAVATYPES
2054: .get(getSQLTypeDescription(rsmd
2055: .getColumnType(i))));
2056:
2057: if (rsmd.isNullable(i) == DatabaseMetaData.columnNullable) {
2058: currCol.setIsNullable(true);
2059: } else {
2060: currCol.setIsNullable(false);
2061: }
2062: // add ResultSetColumn object to the arraylist
2063: boolean addToArray = resultArray.add(currCol);
2064: }
2065:
2066: // add the arraylist having ResultSetColumn objects to the ResultSetColumns object
2067: // now add this ResultSetColumns object to the arraylist object (result)
2068: if (resultArray.size() > 0) {
2069: ResultSetColumns rsColbj = new ResultSetColumns();
2070: rsColbj.setColumns(resultArray);
2071: rsColbj.setName("proc_" + count);
2072: result.add(rsColbj);
2073: }
2074: }
2075: }
2076: } catch (SQLException e) {
2077: // resultset column metadata not supported
2078: System.out.println("\nException occurred: "
2079: + e.getClass().getName() + ", " + e.getMessage());
2080: e.printStackTrace();
2081: errMsg = e.getLocalizedMessage();
2082: throw e;
2083: } catch (NullPointerException npe) {
2084: System.out.println("\nException occurred: "
2085: + npe.getClass().getName() + ", "
2086: + npe.getMessage());
2087: npe.printStackTrace();
2088: errMsg = npe.getLocalizedMessage();
2089: throw npe;
2090: } catch (Exception e) {
2091: // resultset column metadata not supported
2092: System.out.println("\nException occurred: "
2093: + e.getClass().getName() + ", " + e.getMessage());
2094: e.printStackTrace();
2095: errMsg = e.getLocalizedMessage();
2096: }
2097:
2098: // add the arraylist object to the Procedure object
2099: proc.setResultSetColumns(result);
2100:
2101: }
2102:
2103: /**
2104: * added by Bobby to retrieve the resultset metadata of an SQL query
2105: *
2106: * @param pcatalog Catalog (package) name of the procedure
2107: * @param pschema Schema name of the procdure
2108: * @param pname Name of the procedure
2109: * @param sqlText Text of the procedure/function
2110: *
2111: * @return Procedure resultset encapsulated in a Procedure object
2112: *
2113: * @throws SQLException, NullPointerException
2114: */
2115: public Procedure getQueryResultSet(String pcatalog, String pschema,
2116: String pname, String sqlText) throws SQLException,
2117: NullPointerException {
2118: String errMsg = "";
2119: Procedure procResult = new Procedure(pname, pcatalog, pschema,
2120: new String("PROCEDURE"));
2121: ResultSetColumns[] result = null;
2122: ArrayList resultList = new ArrayList();
2123:
2124: try {
2125: DatabaseMetaData dbmeta = dbconn.getMetaData();
2126: Statement stmt = dbconn.createStatement();
2127:
2128: // retrieve the names of the fields in the select query
2129: // required if the query contains calculated fields
2130: String[] queryFields = getQueryFields(sqlText);
2131:
2132: // execute the SQL query and retrieve the resultset
2133: ResultSet rs = stmt.executeQuery(sqlText);
2134: ResultSetMetaData rsmd = rs.getMetaData();
2135: int numColumns = rsmd.getColumnCount();
2136:
2137: for (int i = 1; i <= numColumns; i++) {
2138: ResultSetColumn resultCol = new ResultSetColumn();
2139: resultCol.setOrdinalPosition(i);
2140: String colName = rsmd.getColumnName(i).trim();
2141: String colLabel = rsmd.getColumnLabel(i).trim();
2142:
2143: // check if the column names/labels are returned as null
2144: // (this happens in the case of derived/calculated fields and no aliases are provided)
2145: if (colName.equalsIgnoreCase("") || colName == null) {
2146: // parse the query string to extract derived field names
2147: String strFieldName = queryFields[i - 1];
2148: resultCol.setName(strFieldName);
2149: } else {
2150: resultCol.setName(colName);
2151: }
2152:
2153: if (colLabel.equalsIgnoreCase("") || colLabel == null) {
2154: // parse the query string to extract derived field names
2155: String strFieldName = queryFields[i - 1];
2156: resultCol.setLabel(strFieldName);
2157: } else {
2158: resultCol.setLabel(colLabel);
2159: }
2160:
2161: resultCol.setSqlType(getSQLTypeDescription(rsmd
2162: .getColumnType(i)));
2163: resultCol.setJavaType((String) SQLTOJAVATYPES
2164: .get(getSQLTypeDescription(rsmd
2165: .getColumnType(i))));
2166:
2167: if (rsmd.isNullable(i) == DatabaseMetaData.columnNullable) {
2168: resultCol.setIsNullable(true);
2169: } else {
2170: resultCol.setIsNullable(false);
2171: }
2172:
2173: // add ResultSetColumn object to the arraylist
2174: boolean addToArray = resultList.add(resultCol);
2175: }
2176:
2177: result = new ResultSetColumns[1];
2178: result[0] = new ResultSetColumns();
2179: // add the arraylist to the ResultSetColumns object
2180: result[0].setColumns(resultList);
2181: result[0].setName(pname + "_0");
2182: } catch (SQLException e) {
2183: System.out.println("\nException occurred: "
2184: + e.getClass().getName() + ", " + e.getMessage());
2185: e.printStackTrace();
2186: errMsg = e.getLocalizedMessage();
2187: throw e;
2188: } catch (NullPointerException npe) {
2189: System.out.println("\nException occurred: "
2190: + npe.getClass().getName() + ", "
2191: + npe.getMessage());
2192: npe.printStackTrace();
2193: errMsg = npe.getLocalizedMessage();
2194: throw npe;
2195: } catch (Exception e) {
2196: // resultset column metadata not supported
2197: System.out.println("\nException occurred: "
2198: + e.getClass().getName() + ", " + e.getMessage());
2199: e.printStackTrace();
2200: errMsg = e.getLocalizedMessage();
2201: }
2202:
2203: // add the ResultSetColumns array to the Procedure object
2204: procResult.setResultSetColumns(result);
2205: return procResult;
2206: }
2207:
2208: /**
2209: * added by Bobby to retrieve the text of a procedure/function
2210: *
2211: * @param Procedure Procedure object representing a procedure or function
2212: *
2213: * @return String Text of the procedure or function
2214: */
2215: public String getProcedureText(Procedure proc) {
2216: String procText = "";
2217: String stmtString = "";
2218: String procName = proc.getName();
2219: String packageName = proc.getCatalog();
2220:
2221: // construct the SQL select query depending on whether
2222: // the procedure or function is part of a package or not
2223: if (packageName.equals("") || packageName == null) {
2224: stmtString = "select text from user_source where name = '"
2225: + procName + "'";
2226: } else {
2227: stmtString = "select text from user_source where name = '"
2228: + packageName + "'";
2229: }
2230:
2231: try {
2232: Statement stmt = dbconn.createStatement();
2233: ResultSet rsProcText = stmt.executeQuery(stmtString);
2234:
2235: while (rsProcText.next()) {
2236: procText += rsProcText.getString(1);
2237: }
2238: } catch (SQLException e) {
2239: System.out.println("\nException occurred: "
2240: + e.getClass().getName() + ", " + e.getMessage());
2241: e.printStackTrace();
2242: errMsg = e.getLocalizedMessage();
2243: }
2244:
2245: return procText;
2246: }
2247:
2248: /**
2249: * added by Bobby to parse an SQL query string
2250: * and return a String array containing the names of the select fields
2251: *
2252: * @param sqlQuery the SQL query string to be parsed
2253: *
2254: * @return String array containing the list of derived field names
2255: */
2256: private String[] getQueryFields(String sqlQuery) {
2257: String[] strFieldNames = null;
2258:
2259: String queryString = sqlQuery.toUpperCase().trim();
2260: int fromIndex = queryString.indexOf("FROM");
2261:
2262: // extract the part of the query between the SELECT and the FROM keywords
2263: String searchString = sqlQuery.substring(7, fromIndex);
2264:
2265: StringTokenizer stFields = new StringTokenizer(searchString,
2266: ",");
2267: int noTokens = stFields.countTokens();
2268: strFieldNames = new String[noTokens];
2269:
2270: int tokenNo = 0;
2271: // extract the string tokens fom the query (the derived columns)
2272: while (stFields.hasMoreTokens()) {
2273: strFieldNames[tokenNo] = stFields.nextToken().trim();
2274: tokenNo++;
2275: }
2276:
2277: return strFieldNames;
2278: }
2279:
2280: public DBMetaData(Connection conn, String sqlText) {
2281: this .dbconn = conn;
2282: this .sqlText = sqlText;
2283: }
2284:
2285: public DBMetaData() {
2286: }
2287:
2288: public String getSQLText() {
2289: return this .sqlText;
2290: }
2291:
2292: public Procedure getProcResultSetColumns(String pcatalog,
2293: String pschema, String pname, String columnName,
2294: Procedure procResult) throws SQLException,
2295: NullPointerException {
2296:
2297: checkProcMetaData = false;
2298: int colCount = 0;
2299: boolean isFunction = false;
2300: boolean hasParameters = true;
2301: // indicates if the procedure is within a package or standalone
2302: boolean isPackaged = true;
2303:
2304: //Procedure procResult = new Procedure(pname, pcatalog, pschema, new String("PROCEDURE"));
2305: ResultSetColumn resultCol = new ResultSetColumn();
2306: ArrayList paramIndices = new ArrayList(); // arraylist to hold the indices of the paramters that return resultsets
2307: ArrayList result = new ArrayList(); // arraylist to hold ResultSetColumns objects
2308:
2309: // check if the procedure is within a package or not
2310: if (pcatalog.trim().equalsIgnoreCase("") || pcatalog == null) {
2311: isPackaged = false;
2312: }
2313: try {
2314: ResultSet rs = dbmeta.getProcedureColumns(pcatalog,
2315: pschema, pname, columnName);
2316:
2317: // loop to identify if the procedure is actually a function
2318: while (rs.next()) {
2319: if (rs.getShort("COLUMN_TYPE") == DatabaseMetaData.procedureColumnReturn) {
2320: // this is a function, so set the flag to true
2321: isFunction = true;
2322: }
2323: }
2324:
2325: rs = dbmeta.getProcedureColumns(pcatalog, pschema, pname,
2326: columnName);
2327:
2328: // get the count of the parameters
2329: while (rs.next()) {
2330: colCount++;
2331: }
2332:
2333: // check if the procedure has parameters or not
2334: if (colCount == 0) {
2335: hasParameters = false;
2336: }
2337:
2338: // construct the procedure execution command string
2339: if (isFunction == true) {
2340: } else {
2341: }
2342:
2343: CallableStatement cstmt = dbconn.prepareCall(sqlText);
2344:
2345: rs = dbmeta.getProcedureColumns(pcatalog, pschema, pname,
2346: columnName);
2347: int paramIndex = 0;
2348:
2349: // loop through the list of parameters and register them
2350: for (int j = 0; j < colCount; j++) {
2351: rs.next();
2352: paramIndex++;
2353: String parameterName = rs.getString("COLUMN_NAME");
2354: int targetSqlType = rs.getInt("DATA_TYPE");
2355: int colType = rs.getShort("COLUMN_TYPE");
2356: String type_Name = rs.getString("TYPE_NAME");
2357:
2358: if (colType == DatabaseMetaData.procedureColumnIn) {
2359: if ((targetSqlType == 1111)
2360: && (type_Name.equals("PL/SQL TABLE"))) {
2361: targetSqlType = -14;
2362: }
2363:
2364: if ((targetSqlType == 1111)
2365: && (type_Name.equals("PL/SQL RECORD"))) {
2366: targetSqlType = -14;
2367: }
2368: cstmt.setNull(paramIndex, targetSqlType);
2369: }
2370:
2371: if (colType == DatabaseMetaData.procedureColumnInOut
2372: || colType == DatabaseMetaData.procedureColumnOut) {
2373: try {
2374: // if the parameter is a cursor type, add its index to the arraylist
2375: if ((targetSqlType == 1111)
2376: && (type_Name.equals("REF CURSOR"))) {
2377: targetSqlType = -10;
2378: paramIndices.add(new Integer(paramIndex));
2379: }
2380: cstmt.registerOutParameter(paramIndex,
2381: targetSqlType);
2382: } catch (SQLException e) {
2383: System.out.println(e.getMessage());
2384: e.printStackTrace();
2385: checkProcMetaData = true;
2386: //throw e;
2387: }
2388: }
2389:
2390: // check if the parameter is RETURN type (i.e. it is a function)
2391: if (colType == DatabaseMetaData.procedureColumnReturn) {
2392: try {
2393: // if the parameter is a cursor type, add its index to the arraylist
2394: if ((targetSqlType == 1111)
2395: && (type_Name.equals("REF CURSOR"))) {
2396: targetSqlType = -10;
2397: paramIndices.add(new Integer(paramIndex));
2398: }
2399: cstmt.registerOutParameter(paramIndex,
2400: targetSqlType);
2401: } catch (SQLException e) {
2402: System.out.println(e.getMessage());
2403: e.printStackTrace();
2404: //throw e;
2405: checkProcMetaData = true;
2406: }
2407: }
2408: }
2409:
2410: // execute the stored procedure
2411: boolean resultsAvailable = cstmt.execute();
2412: int count = -1;
2413: int numResults = paramIndices.size();
2414:
2415: Iterator paramIdxIter = paramIndices.iterator();
2416:
2417: // iterate through the resultsets returned, whose indices are stored in the arraylist
2418: while (paramIdxIter.hasNext()) {
2419: ArrayList resultArray = new ArrayList(); // arraylist to hold the objects of ResultSetColumn
2420: count += 1;
2421: // get the index (from the arraylist) of the parameter which is a resultset
2422: int index = ((Integer) paramIdxIter.next()).intValue();
2423: ResultSet paramRS;
2424: ResultSetMetaData rsmd;
2425: // if the resultset returns nothing, set the metadata object to null
2426: try {
2427: paramRS = (ResultSet) cstmt.getObject(index);
2428: rsmd = paramRS.getMetaData();
2429: } catch (SQLException e) {
2430: rsmd = null;
2431: }
2432:
2433: int rsmdColCount = 0;
2434: if (rsmd != null) {
2435: rsmdColCount = rsmd.getColumnCount();
2436: }
2437: // scroll through the resultset column information
2438: for (int i = 1; i <= rsmdColCount; i++) {
2439: ResultSetColumn currCol = new ResultSetColumn();
2440: currCol.setOrdinalPosition(i);
2441: currCol.setName(rsmd.getColumnName(i));
2442: currCol.setLabel(rsmd.getColumnLabel(i));
2443: currCol.setSqlType(getSQLTypeDescription(rsmd
2444: .getColumnType(i)));
2445: currCol.setJavaType((String) SQLTOJAVATYPES
2446: .get(getSQLTypeDescription(rsmd
2447: .getColumnType(i))));
2448:
2449: if (rsmd.isNullable(i) == DatabaseMetaData.columnNullable) {
2450: currCol.setIsNullable(true);
2451: } else {
2452: currCol.setIsNullable(false);
2453: }
2454: // add ResultSetColumn object to the arraylist
2455: boolean addToArray = resultArray.add(currCol);
2456: }
2457:
2458: // add the arraylist having ResultSetColumn objects to the ResultSetColumns object
2459: // now add this ResultSetColumns object to the arraylist object (result)
2460: if (resultArray.size() > 0) {
2461: ResultSetColumns rsColbj = new ResultSetColumns();
2462: rsColbj.setColumns(resultArray);
2463: rsColbj.setName(pname + "_" + count);
2464: result.add(rsColbj);
2465: }
2466: }
2467: } catch (SQLException e) {
2468: // resultset column metadata not supported
2469: System.out.println("\nException occurred: "
2470: + e.getClass().getName() + ", " + e.getMessage());
2471: e.printStackTrace();
2472: errMsg = e.getLocalizedMessage();
2473: checkProcMetaData = true;
2474: //throw e;
2475: } catch (NullPointerException npe) {
2476: System.out.println("\nException occurred: "
2477: + npe.getClass().getName() + ", "
2478: + npe.getMessage());
2479: npe.printStackTrace();
2480: errMsg = npe.getLocalizedMessage();
2481: checkProcMetaData = true;
2482: //throw npe;
2483: } catch (Exception e) {
2484: // resultset column metadata not supported
2485: System.out.println("\nException occurred: "
2486: + e.getClass().getName() + ", " + e.getMessage());
2487: e.printStackTrace();
2488: errMsg = e.getLocalizedMessage();
2489: checkProcMetaData = true;
2490: }
2491:
2492: // add the arraylist object to the Procedure object
2493: procResult.setResultSetColumns(result);
2494: return procResult;
2495: }
2496:
2497: public boolean getErrPrepStmtMetaData() {
2498: return this .checkPrepStmtMetaData;
2499: }
2500:
2501: public boolean getErrProcMetaData() {
2502: return this.checkProcMetaData;
2503: }
2504:
2505: }
|