Example usage for java.sql DatabaseMetaData getProcedures

List of usage examples for java.sql DatabaseMetaData getProcedures

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getProcedures

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getProcedures.

Prototype

ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException;

Source Link

Document

Retrieves a description of the stored procedures available in the given catalog.

Usage

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

/**
 * test testProccedures()//from  w  w  w. j  a  va2s  .  c  o  m
 * @throws SQLException
 */
@Test
public void testProccedures() throws SQLException {
    DatabaseMetaData dbmd = con.getMetaData();
    assertNotNull(dbmd);
    // currently testProccedures always returns an empty resultset for Hive
    ResultSet res = dbmd.getProcedures(null, null, null);
    ResultSetMetaData md = res.getMetaData();
    assertEquals(md.getColumnCount(), 9);
    assertFalse(res.next());
}

From source file:org.executequery.databaseobjects.impl.DefaultDatabaseMetaTag.java

private ResultSet getProceduresResultSet() throws SQLException {

    String catalogName = catalogNameForQuery();
    String schemaName = schemaNameForQuery();

    DatabaseMetaData dmd = getHost().getDatabaseMetaData();
    return dmd.getProcedures(catalogName, schemaName, null);
}

From source file:org.executequery.databaseobjects.impl.DefaultDatabaseMetaTag.java

private ResultSet getFunctionResultSetOldImpl() throws SQLException {

    DatabaseMetaData dmd = getHost().getDatabaseMetaData();
    return dmd.getProcedures(getCatalogName(), getSchemaName(), null);
}

From source file:org.executequery.gui.editor.autocomplete.AutoCompleteSelectionsFactory.java

private ResultSet getProcedures(DatabaseMetaData databaseMetaData, String catalog, String schema)
        throws SQLException {
    ResultSet rs;/*from w  w w.  java 2  s . c o m*/
    rs = databaseMetaData.getProcedures(catalog, schema, null);
    return rs;
}

From source file:org.openconcerto.sql.model.JDBCStructureSource.java

protected void _fillTables(final Set<String> newSchemas, final Connection conn) throws SQLException {
    // for new tables, add ; for existing, refresh
    final DatabaseMetaData metaData = conn.getMetaData();
    // getColumns() only supports pattern (eg LIKE) so we must make multiple calls
    for (final String s : newSchemas) {
        final ResultSet rs = metaData.getColumns(this.getBase().getMDName(), s, "%", null);

        // handle tables becoming empty (possible in pg)
        final Set<SQLName> tablesWithColumns = new HashSet<SQLName>();

        boolean hasNext = rs.next();
        while (hasNext) {
            final String schemaName = rs.getString("TABLE_SCHEM");
            final String tableName = rs.getString("TABLE_NAME");
            tablesWithColumns.add(new SQLName(schemaName, tableName));

            final Boolean moved;
            if (newSchemas.contains(schemaName)) {
                final SQLSchema schema = getNewSchema(schemaName);
                moved = schema.refreshTable(metaData, rs);
            } else {
                moved = null;/*from   w w w.j  a  v a  2 s .  com*/
            }
            hasNext = moved == null ? rs.next() : moved;
        }

        // tables with no column = all tables - tables with column
        final SQLSchema schema = getNewSchema(s);
        for (final String t : schema.getTableNames()) {
            if (!tablesWithColumns.contains(new SQLName(s, t))) {
                // empty table with no db access
                schema.getTable(t).emptyFields();
            }
        }
    }

    // type & comment
    for (final SQLName tName : getTablesNames()) {
        final SQLTable t = getNewSchema(tName.getItemLenient(-2)).getTable(tName.getName());
        final List<String> l = (List<String>) this.tableNames.getNonNull(tName);
        t.setType(l.get(0));
        t.setComment(l.get(1));
    }

    final SQLSystem system = getBase().getServer().getSQLSystem();
    // procedures
    final CollectionMap<String, String> proceduresBySchema = new CollectionMap<String, String>();
    for (final String s : newSchemas) {
        final ResultSet rsProc = metaData.getProcedures(this.getBase().getMDName(), s, "%");
        while (rsProc.next()) {
            // to ignore case : pg.AbstractJdbc2DatabaseMetaData doesn't quote aliases
            final Map map = BasicRowProcessor.instance().toMap(rsProc);
            final String schemaName = (String) map.get("PROCEDURE_SCHEM");
            if (newSchemas.contains(schemaName)) {
                final String procName = (String) map.get("PROCEDURE_NAME");
                proceduresBySchema.put(schemaName, procName);
                getNewSchema(schemaName).addProcedure(procName);
            }
        }
    }
    // try to find out more about those procedures
    if (proceduresBySchema.size() > 0) {
        final String sel = system.getSyntax().getFunctionQuery(getBase(), proceduresBySchema.keySet());
        if (sel != null) {
            // don't cache since we don't listen on system tables
            for (final Object o : (List) getBase().getDataSource().execute(sel,
                    new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false))) {
                final Map m = (Map) o;
                final SQLSchema newSchema = getNewSchema((String) m.get("schema"));
                if (newSchema != null)
                    newSchema.setProcedureSource((String) m.get("name"), (String) m.get("src"));
            }
        }
    }

    // if no schemas exist, there can be no triggers
    // (avoid a query and a special case since "in ()" is not valid)
    if (newSchemas.size() > 0) {
        final ITransformer<Tuple2<String, String>, SQLTable> tableFinder = new ITransformer<Tuple2<String, String>, SQLTable>() {
            @Override
            public SQLTable transformChecked(Tuple2<String, String> input) {
                return getNewTable(input.get0(), input.get1());
            }
        };
        new JDBCStructureSource.TriggerQueryExecutor(tableFinder).apply(getBase(), newSchemas);
        new JDBCStructureSource.ColumnsQueryExecutor(tableFinder).apply(getBase(), newSchemas);
        try {
            new JDBCStructureSource.ConstraintsExecutor(tableFinder).apply(getBase(), newSchemas);
        } catch (QueryExn e1) {
            // constraints are not essentials, continue
            e1.printStackTrace();
            for (final SQLName tName : getTablesNames()) {
                final SQLTable t = getNewSchema(tName.getItemLenient(-2)).getTable(tName.getName());
                t.addConstraint(null);
            }
        }
    }
}

From source file:org.pentaho.di.core.database.Database.java

public String[] getProcedures() throws KettleDatabaseException {
    String sql = databaseMeta.getSQLListOfProcedures();
    if (sql != null) {
        // System.out.println("SQL= "+sql);
        List<Object[]> procs = getRows(sql, 1000);
        // System.out.println("Found "+procs.size()+" rows");
        String[] str = new String[procs.size()];
        for (int i = 0; i < procs.size(); i++) {
            str[i] = procs.get(i)[0].toString();
        }//  w w w .  ja va2s  .  c  o m
        return str;
    } else {
        ResultSet rs = null;
        try {
            DatabaseMetaData dbmd = getDatabaseMetaData();
            rs = dbmd.getProcedures(null, null, null);
            List<Object[]> rows = getRows(rs, 0, null);
            String[] result = new String[rows.size()];
            for (int i = 0; i < rows.size(); i++) {
                Object[] row = rows.get(i);
                String procCatalog = rowMeta.getString(row, "PROCEDURE_CAT", null);
                String procSchema = rowMeta.getString(row, "PROCEDURE_SCHEMA", null);
                String procName = rowMeta.getString(row, "PROCEDURE_NAME", "");

                String name = "";
                if (procCatalog != null) {
                    name += procCatalog + ".";
                } else if (procSchema != null) {
                    name += procSchema + ".";
                }

                name += procName;

                result[i] = name;
            }
            return result;
        } catch (Exception e) {
            throw new KettleDatabaseException("Unable to get list of procedures from database meta-data: ", e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                    // ignore the error.
                }
            }
        }
    }
}

From source file:org.seasar.dbflute.logic.jdbc.metadata.basic.DfProcedureExtractor.java

protected ResultSet doGetProcedures(DatabaseMetaData metaData, UnifiedSchema unifiedSchema)
        throws SQLException {
    final String catalogName = unifiedSchema.getPureCatalog();
    final String schemaName = unifiedSchema.getPureSchema();
    return metaData.getProcedures(catalogName, schemaName, null);
}

From source file:org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.java

/**
 * Process the procedure column metadata
 *//* w  w w  . ja  va  2s.co m*/
private void processProcedureColumns(DatabaseMetaData databaseMetaData, @Nullable String catalogName,
        @Nullable String schemaName, @Nullable String procedureName) {

    String metaDataCatalogName = metaDataCatalogNameToUse(catalogName);
    String metaDataSchemaName = metaDataSchemaNameToUse(schemaName);
    String metaDataProcedureName = procedureNameToUse(procedureName);
    if (logger.isDebugEnabled()) {
        logger.debug("Retrieving metadata for " + metaDataCatalogName + '/' + metaDataSchemaName + '/'
                + metaDataProcedureName);
    }

    ResultSet procs = null;
    try {
        procs = databaseMetaData.getProcedures(metaDataCatalogName, metaDataSchemaName, metaDataProcedureName);
        List<String> found = new ArrayList<>();
        while (procs.next()) {
            found.add(procs.getString("PROCEDURE_CAT") + '.' + procs.getString("PROCEDURE_SCHEM") + '.'
                    + procs.getString("PROCEDURE_NAME"));
        }
        procs.close();

        if (found.size() > 1) {
            throw new InvalidDataAccessApiUsageException(
                    "Unable to determine the correct call signature - multiple "
                            + "procedures/functions/signatures for '" + metaDataProcedureName + "': found "
                            + found);
        } else if (found.isEmpty()) {
            if (metaDataProcedureName != null && metaDataProcedureName.contains(".")
                    && !StringUtils.hasText(metaDataCatalogName)) {
                String packageName = metaDataProcedureName.substring(0, metaDataProcedureName.indexOf("."));
                throw new InvalidDataAccessApiUsageException(
                        "Unable to determine the correct call signature for '" + metaDataProcedureName
                                + "' - package name should be specified separately using '.withCatalogName(\""
                                + packageName + "\")'");
            } else if ("Oracle".equals(databaseMetaData.getDatabaseProductName())) {
                if (logger.isDebugEnabled()) {
                    logger.debug("Oracle JDBC driver did not return procedure/function/signature for '"
                            + metaDataProcedureName + "' - assuming a non-exposed synonym");
                }
            } else {
                throw new InvalidDataAccessApiUsageException(
                        "Unable to determine the correct call signature - no "
                                + "procedure/function/signature for '" + metaDataProcedureName + "'");
            }
        }

        procs = databaseMetaData.getProcedureColumns(metaDataCatalogName, metaDataSchemaName,
                metaDataProcedureName, null);
        while (procs.next()) {
            String columnName = procs.getString("COLUMN_NAME");
            int columnType = procs.getInt("COLUMN_TYPE");
            if (columnName == null && (columnType == DatabaseMetaData.procedureColumnIn
                    || columnType == DatabaseMetaData.procedureColumnInOut
                    || columnType == DatabaseMetaData.procedureColumnOut)) {
                if (logger.isDebugEnabled()) {
                    logger.debug("Skipping metadata for: " + columnType + " " + procs.getInt("DATA_TYPE") + " "
                            + procs.getString("TYPE_NAME") + " " + procs.getInt("NULLABLE")
                            + " (probably a member of a collection)");
                }
            } else {
                CallParameterMetaData meta = new CallParameterMetaData(columnName, columnType,
                        procs.getInt("DATA_TYPE"), procs.getString("TYPE_NAME"),
                        procs.getInt("NULLABLE") == DatabaseMetaData.procedureNullable);
                this.callParameterMetaData.add(meta);
                if (logger.isDebugEnabled()) {
                    logger.debug("Retrieved metadata: " + meta.getParameterName() + " "
                            + meta.getParameterType() + " " + meta.getSqlType() + " " + meta.getTypeName() + " "
                            + meta.isNullable());
                }
            }
        }
    } catch (SQLException ex) {
        if (logger.isWarnEnabled()) {
            logger.warn("Error while retrieving metadata for procedure columns: " + ex);
        }
    } finally {
        try {
            if (procs != null) {
                procs.close();
            }
        } catch (SQLException ex) {
            if (logger.isWarnEnabled()) {
                logger.warn("Problem closing ResultSet for procedure column metadata: " + ex);
            }
        }
    }
}

From source file:org.xenei.bloomgraph.bloom.sql.MySQLCommands.java

/**
 * Create any necessary procedures.//from  w  ww . ja v  a 2  s.  co  m
 * 
 * @param metadata
 *            the metadata for the database.
 * @throws SQLException
 *             on error
 */
private void createProcedures(final DatabaseMetaData metadata) throws SQLException {
    ResultSet rs = null;
    Statement stmt = null;
    final Connection connection = metadata.getConnection();
    try {
        rs = metadata.getProcedures(connection.getCatalog(), connection.getSchema(), "add_triple");
        if (!rs.next()) {
            stmt = connection.createStatement();
            stmt.executeUpdate(CREATE_ADD_TRIPLE_PROCEDURE);
        }
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
    }
}

From source file:pingpong.db.DBAccess.java

public TResultSet getProcedure(String schema, String procedureName) {

    Connection con = null;//from   w ww  .  j  av a 2 s.c  om
    DatabaseMetaData dbMeta = null;
    TResultSet rs = null;
    try {
        con = getConnection();
        dbMeta = con.getMetaData();
        rs = new TResultSet(dbMeta.getProcedures(null, getSchema(), null));
    } catch (SQLException e) {
        rs = null;
    }
    return rs;
}