Example usage for java.sql DatabaseMetaData getProcedures

List of usage examples for java.sql DatabaseMetaData 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:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);

    DatabaseMetaData metadata = conn.getMetaData();
    ResultSet result = metadata.getProcedures(null, "JAVA", "%");
    while (result.next()) {
        System.out.println(result.getString("PROCEDURE_CAT") + " - " + result.getString("PROCEDURE_SCHEM")
                + " - " + result.getString("PROCEDURE_NAME"));
    }//from   ww w  .  ja v a  2s . c o  m
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();

    DatabaseMetaData mtdt = conn.getMetaData();

    System.out.println(mtdt.getProcedureTerm());

    ResultSet rs = mtdt.getProcedures(conn.getCatalog(), "%", "%");

    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();
    for (int i = 1; i <= numCols; i++) {
        if (i > 1)
            System.out.print(", ");
        System.out.print(rsmd.getColumnLabel(i));
    }//w ww .j ava  2 s .c om
    System.out.println("");
    while (rs.next()) {
        for (int i = 1; i <= numCols; i++) {
            if (i > 1)
                System.out.print(", ");
            System.out.print(rs.getString(i));
        }
        System.out.println("");
    }
    conn.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);/*  w  ww  .  jav a  2s .  com*/

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);

    DatabaseMetaData dbmd = connection.getMetaData();

    // Get all stored procedures in any schema and catalog
    ResultSet resultSet = dbmd.getProcedures(null, null, "%");

    // Get stored procedure names from the result set
    while (resultSet.next()) {
        String procName = resultSet.getString(3);
        System.out.println(procName);
    }
}

From source file:com.splicemachine.derby.impl.sql.execute.operations.CallStatementOperationIT.java

@Test
public void testCallSqlProcedures() throws Exception {
    DatabaseMetaData dmd = methodWatcher.getOrCreateConnection().getMetaData();
    ResultSet rs = dmd.getProcedures(null, null, null);
    while (rs.next()) { // TODO No Test
    }//from  w w w.  j  av a  2  s  .co m
    DbUtils.closeQuietly(rs);
}

From source file:jef.database.DbMetaData.java

private List<Function> innerGetProcedures(String schema, String procdureName) throws SQLException {
    if (schema == null) {
        schema = this.schema;
    }//  w  w w.  j  av a2s. c om

    DatabaseDialect profile = getProfile();
    Connection conn = getConnection(false);
    DatabaseMetaData databaseMetaData = conn.getMetaData();
    ResultSet rs = null;
    try {
        List<Function> result = new ArrayList<Function>();
        rs = databaseMetaData.getProcedures(profile.getCatlog(schema), profile.getSchema(schema), procdureName);
        while (rs.next()) {
            Function function = new Function(ObjectType.PROCEDURE);
            function.setCatalog(rs.getString(1));
            function.setSchema(rs.getString(2));
            function.setName(rs.getString(3));
            function.setRemarks(rs.getString(7));
            function.setType(rs.getShort(8));
            function.setSpecificName(rs.getString(9));
            result.add(function);
        }
        return result;
    } finally {
        DbUtils.close(rs);
        releaseConnection(conn);
    }
}

From source file:org.apache.bigtop.itest.hive.TestJdbc.java

/**
 * Test simple DatabaseMetaData calls.  getColumns is tested elsewhere, as we need to call
 * that on a valid table.  Same with getFunctions.
 *
 * @throws SQLException//from  www.j a v  a 2  s .c o  m
 */
@Test
public void databaseMetaDataCalls() throws SQLException {
    DatabaseMetaData md = conn.getMetaData();

    boolean boolrc = md.allTablesAreSelectable();
    LOG.debug("All tables are selectable? " + boolrc);

    String strrc = md.getCatalogSeparator();
    LOG.debug("Catalog separator " + strrc);

    strrc = md.getCatalogTerm();
    LOG.debug("Catalog term " + strrc);

    ResultSet rs = md.getCatalogs();
    while (rs.next()) {
        strrc = rs.getString(1);
        LOG.debug("Found catalog " + strrc);
    }

    Connection c = md.getConnection();

    int intrc = md.getDatabaseMajorVersion();
    LOG.debug("DB major version is " + intrc);

    intrc = md.getDatabaseMinorVersion();
    LOG.debug("DB minor version is " + intrc);

    strrc = md.getDatabaseProductName();
    LOG.debug("DB product name is " + strrc);

    strrc = md.getDatabaseProductVersion();
    LOG.debug("DB product version is " + strrc);

    intrc = md.getDefaultTransactionIsolation();
    LOG.debug("Default transaction isolation is " + intrc);

    intrc = md.getDriverMajorVersion();
    LOG.debug("Driver major version is " + intrc);

    intrc = md.getDriverMinorVersion();
    LOG.debug("Driver minor version is " + intrc);

    strrc = md.getDriverName();
    LOG.debug("Driver name is " + strrc);

    strrc = md.getDriverVersion();
    LOG.debug("Driver version is " + strrc);

    strrc = md.getExtraNameCharacters();
    LOG.debug("Extra name characters is " + strrc);

    strrc = md.getIdentifierQuoteString();
    LOG.debug("Identifier quote string is " + strrc);

    // In Hive 1.2 this always returns an empty RS
    rs = md.getImportedKeys("a", "b", "d");

    // In Hive 1.2 this always returns an empty RS
    rs = md.getIndexInfo("a", "b", "d", true, true);

    intrc = md.getJDBCMajorVersion();
    LOG.debug("JDBC major version is " + intrc);

    intrc = md.getJDBCMinorVersion();
    LOG.debug("JDBC minor version is " + intrc);

    intrc = md.getMaxColumnNameLength();
    LOG.debug("Maximum column name length is " + intrc);

    strrc = md.getNumericFunctions();
    LOG.debug("Numeric functions are " + strrc);

    // In Hive 1.2 this always returns an empty RS
    rs = md.getPrimaryKeys("a", "b", "d");

    // In Hive 1.2 this always returns an empty RS
    rs = md.getProcedureColumns("a", "b", "d", "e");

    strrc = md.getProcedureTerm();
    LOG.debug("Procedures are called " + strrc);

    // In Hive 1.2 this always returns an empty RS
    rs = md.getProcedures("a", "b", "d");

    strrc = md.getSchemaTerm();
    LOG.debug("Schemas are called " + strrc);

    rs = md.getSchemas();
    while (rs.next()) {
        strrc = rs.getString(1);
        LOG.debug("Found schema " + strrc);
    }

    strrc = md.getSearchStringEscape();
    LOG.debug("Search string escape is " + strrc);

    strrc = md.getStringFunctions();
    LOG.debug("String functions are " + strrc);

    strrc = md.getSystemFunctions();
    LOG.debug("System functions are " + strrc);

    rs = md.getTableTypes();
    while (rs.next()) {
        strrc = rs.getString(1);
        LOG.debug("Found table type " + strrc);
    }

    strrc = md.getTimeDateFunctions();
    LOG.debug("Time/date functions are " + strrc);

    rs = md.getTypeInfo();
    while (rs.next()) {
        strrc = rs.getString(1);
        LOG.debug("Found type " + strrc);
    }

    // In Hive 1.2 this always returns an empty RS
    rs = md.getUDTs("a", "b", "d", null);

    boolrc = md.supportsAlterTableWithAddColumn();
    LOG.debug("Supports alter table with add column? " + boolrc);

    boolrc = md.supportsAlterTableWithDropColumn();
    LOG.debug("Supports alter table with drop column? " + boolrc);

    boolrc = md.supportsBatchUpdates();
    LOG.debug("Supports batch updates? " + boolrc);

    boolrc = md.supportsCatalogsInDataManipulation();
    LOG.debug("Supports catalogs in data manipulation? " + boolrc);

    boolrc = md.supportsCatalogsInIndexDefinitions();
    LOG.debug("Supports catalogs in index definition? " + boolrc);

    boolrc = md.supportsCatalogsInPrivilegeDefinitions();
    LOG.debug("Supports catalogs in privilege definition? " + boolrc);

    boolrc = md.supportsCatalogsInProcedureCalls();
    LOG.debug("Supports catalogs in procedure calls? " + boolrc);

    boolrc = md.supportsCatalogsInTableDefinitions();
    LOG.debug("Supports catalogs in table definition? " + boolrc);

    boolrc = md.supportsColumnAliasing();
    LOG.debug("Supports column aliasing? " + boolrc);

    boolrc = md.supportsFullOuterJoins();
    LOG.debug("Supports full outer joins? " + boolrc);

    boolrc = md.supportsGroupBy();
    LOG.debug("Supports group by? " + boolrc);

    boolrc = md.supportsLimitedOuterJoins();
    LOG.debug("Supports limited outer joins? " + boolrc);

    boolrc = md.supportsMultipleResultSets();
    LOG.debug("Supports limited outer joins? " + boolrc);

    boolrc = md.supportsNonNullableColumns();
    LOG.debug("Supports non-nullable columns? " + boolrc);

    boolrc = md.supportsOuterJoins();
    LOG.debug("Supports outer joins? " + boolrc);

    boolrc = md.supportsPositionedDelete();
    LOG.debug("Supports positioned delete? " + boolrc);

    boolrc = md.supportsPositionedUpdate();
    LOG.debug("Supports positioned update? " + boolrc);

    boolrc = md.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
    LOG.debug("Supports result set holdability? " + boolrc);

    boolrc = md.supportsResultSetType(ResultSet.HOLD_CURSORS_OVER_COMMIT);
    LOG.debug("Supports result set type? " + boolrc);

    boolrc = md.supportsSavepoints();
    LOG.debug("Supports savepoints? " + boolrc);

    boolrc = md.supportsSchemasInDataManipulation();
    LOG.debug("Supports schemas in data manipulation? " + boolrc);

    boolrc = md.supportsSchemasInIndexDefinitions();
    LOG.debug("Supports schemas in index definitions? " + boolrc);

    boolrc = md.supportsSchemasInPrivilegeDefinitions();
    LOG.debug("Supports schemas in privilege definitions? " + boolrc);

    boolrc = md.supportsSchemasInProcedureCalls();
    LOG.debug("Supports schemas in procedure calls? " + boolrc);

    boolrc = md.supportsSchemasInTableDefinitions();
    LOG.debug("Supports schemas in table definitions? " + boolrc);

    boolrc = md.supportsSelectForUpdate();
    LOG.debug("Supports select for update? " + boolrc);

    boolrc = md.supportsStoredProcedures();
    LOG.debug("Supports stored procedures? " + boolrc);

    boolrc = md.supportsTransactions();
    LOG.debug("Supports transactions? " + boolrc);

    boolrc = md.supportsUnion();
    LOG.debug("Supports union? " + boolrc);

    boolrc = md.supportsUnionAll();
    LOG.debug("Supports union all? " + boolrc);

}

From source file:org.apache.cayenne.dbsync.reverse.dbload.ProcedureLoader.java

@Override
protected ResultSet getResultSet(String catalogName, String schemaName, DatabaseMetaData metaData)
        throws SQLException {
    return metaData.getProcedures(catalogName, schemaName, WILDCARD);
}

From source file:org.apache.ddlutils.task.DumpMetadataTask.java

/**
 * Dumps all procedures./*ww  w .  j a  v  a  2  s . c  om*/
 * 
 * @param xmlWriter The xml writer to write to
 * @param metaData  The database metadata
 */
private void dumpProcedures(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData)
        throws SQLException {
    performResultSetXmlOperation(xmlWriter, "procedures", new ResultSetXmlOperation() {
        public ResultSet getResultSet() throws SQLException {
            return metaData.getProcedures(_catalogPattern, _schemaPattern, _procedurePattern);
        }

        public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException {
            Set columns = getColumnsInResultSet(result);
            String procedureName = result.getString("PROCEDURE_NAME");

            if ((procedureName != null) && (procedureName.length() > 0)) {
                String catalog = result.getString("PROCEDURE_CAT");
                String schema = result.getString("PROCEDURE_SCHEM");

                log("Reading procedure " + ((schema != null) && (schema.length() > 0) ? schema + "." : "")
                        + procedureName, Project.MSG_INFO);

                xmlWriter.writeElementStart(null, "procedure");
                xmlWriter.writeAttribute(null, "name", procedureName);
                if (catalog != null) {
                    xmlWriter.writeAttribute(null, "catalog", catalog);
                }
                if (schema != null) {
                    xmlWriter.writeAttribute(null, "schema", schema);
                }
                addStringAttribute(xmlWriter, "remarks", result, columns, "REMARKS");
                if (columns.contains("PROCEDURE_TYPE")) {
                    try {
                        switch (result.getShort("PROCEDURE_TYPE")) {
                        case DatabaseMetaData.procedureReturnsResult:
                            xmlWriter.writeAttribute(null, "type", "returns result");
                            break;
                        case DatabaseMetaData.procedureNoResult:
                            xmlWriter.writeAttribute(null, "type", "doesn't return result");
                            break;
                        case DatabaseMetaData.procedureResultUnknown:
                            xmlWriter.writeAttribute(null, "type", "may return result");
                            break;
                        default:
                            xmlWriter.writeAttribute(null, "type", "unknown");
                            break;
                        }
                    } catch (SQLException ex) {
                        log("Could not read the PROCEDURE_TYPE value for the procedure '" + procedureName
                                + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR);
                    }
                }

                dumpProcedure(xmlWriter, metaData, "%", "%", procedureName);
                xmlWriter.writeElementEnd();
            }
        }

        public void handleError(SQLException ex) {
            log("Could not read the procedures from the result set: " + ex.getStackTrace(), Project.MSG_ERR);
        }
    });
}

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

public void testDatabaseMetaData() throws SQLException {
    DatabaseMetaData meta = con.getMetaData();

    assertEquals("Hive", meta.getDatabaseProductName());
    assertEquals("1", meta.getDatabaseProductVersion());
    assertEquals(DatabaseMetaData.sqlStateSQL99, meta.getSQLStateType());
    assertNull(meta.getProcedures(null, null, null));
    assertFalse(meta.supportsCatalogsInTableDefinitions());
    assertFalse(meta.supportsSchemasInTableDefinitions());
    assertFalse(meta.supportsSchemasInDataManipulation());
    assertFalse(meta.supportsMultipleResultSets());
    assertFalse(meta.supportsStoredProcedures());
    assertTrue(meta.supportsAlterTableWithAddColumn());
}

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

@Test
public void testParentReferences() throws Exception {
    /* Test parent references from Statement */
    Statement s = this.con.createStatement();
    ResultSet rs = s.executeQuery("SELECT * FROM " + dataTypeTableName);

    assertTrue(s.getConnection() == this.con);
    assertTrue(rs.getStatement() == s);//from w  w w.j  ava2  s  .c  o  m

    rs.close();
    s.close();

    /* Test parent references from PreparedStatement */
    PreparedStatement ps = this.con.prepareStatement("SELECT * FROM " + dataTypeTableName);
    rs = ps.executeQuery();

    assertTrue(ps.getConnection() == this.con);
    assertTrue(rs.getStatement() == ps);

    rs.close();
    ps.close();

    /* Test DatabaseMetaData queries which do not have a parent Statement */
    DatabaseMetaData md = this.con.getMetaData();

    assertTrue(md.getConnection() == this.con);

    rs = md.getCatalogs();
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getColumns(null, null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getFunctions(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getImportedKeys(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getPrimaryKeys(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getProcedureColumns(null, null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getProcedures(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getSchemas();
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getTableTypes();
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getTables(null, null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getTypeInfo();
    assertNull(rs.getStatement());
    rs.close();
}