List of usage examples for java.sql DatabaseMetaData getProcedureColumns
ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern,
String columnNamePattern) throws SQLException;
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getMySqlConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("drop table survey;"); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); DatabaseMetaData dbMetaData = conn.getMetaData(); ResultSet rs = dbMetaData.getProcedureColumns(conn.getCatalog(), null, "procedureNamePattern", "columnNamePattern"); while (rs.next()) { // get stored procedure metadata String procedureCatalog = rs.getString(1); String procedureSchema = rs.getString(2); String procedureName = rs.getString(3); String columnName = rs.getString(4); short columnReturn = rs.getShort(5); int columnDataType = rs.getInt(6); String columnReturnTypeName = rs.getString(7); int columnPrecision = rs.getInt(8); int columnByteLength = rs.getInt(9); short columnScale = rs.getShort(10); short columnRadix = rs.getShort(11); short columnNullable = rs.getShort(12); String columnRemarks = rs.getString(13); System.out.println("stored Procedure name=" + procedureName); System.out.println("procedureCatalog=" + procedureCatalog); System.out.println("procedureSchema=" + procedureSchema); System.out.println("procedureName=" + procedureName); System.out.println("columnName=" + columnName); System.out.println("columnReturn=" + columnReturn); System.out.println("columnDataType=" + columnDataType); System.out.println("columnReturnTypeName=" + columnReturnTypeName); System.out.println("columnPrecision=" + columnPrecision); System.out.println("columnByteLength=" + columnByteLength); System.out.println("columnScale=" + columnScale); System.out.println("columnRadix=" + columnRadix); System.out.println("columnNullable=" + columnNullable); System.out.println("columnRemarks=" + columnRemarks); }/* w ww . j a v a 2s .c o m*/ st.close(); conn.close(); }
From source file:com.netspective.axiom.sql.StoredProcedure.java
/** * Gets the stored procedure's metadata information from the database. This will search * all available catalogs and schemas. This method will ONLY return the metadata of the * stored procedure only when the <i>procedure-name</i> attribute is set in the XML declaration. */// ww w . j a v a2 s. com public String getMetaData(ConnectionContext cc) throws NamingException, SQLException { // TODO : Using this metadata, we can determine what variables are in and out so that the developer doesn't even have to set it in XML // but currently the procedure-name attribute isn't required but the 'type' attribute is required. If we go the // metadata route we need to change some handling to accept setting the 'type' and if it's not set, we can use // the metadata to get the param type StringBuffer sb = new StringBuffer(); if (procedureName != null && procedureName.length() > 0) { // Get DatabaseMetaData Connection connection = cc.getConnection(); DatabaseMetaData dbmd = connection.getMetaData(); ResultSet rs = dbmd.getProcedureColumns(null, null, procedureName, "%"); // Printout table data while (rs.next()) { // Get procedure metadata String dbProcedureCatalog = rs.getString(1); String dbProcedureSchema = rs.getString(2); String dbProcedureName = rs.getString(3); String dbColumnName = rs.getString(4); short dbColumnReturn = rs.getShort(5); String dbColumnReturnTypeName = rs.getString(7); int dbColumnPrecision = rs.getInt(8); int dbColumnByteLength = rs.getInt(9); short dbColumnScale = rs.getShort(10); short dbColumnRadix = rs.getShort(11); String dbColumnRemarks = rs.getString(13); // Interpret the return type (readable for humans) String procReturn; switch (dbColumnReturn) { case DatabaseMetaData.procedureColumnIn: procReturn = "In"; break; case DatabaseMetaData.procedureColumnOut: procReturn = "Out"; break; case DatabaseMetaData.procedureColumnInOut: procReturn = "In/Out"; break; case DatabaseMetaData.procedureColumnReturn: procReturn = "return value"; break; case DatabaseMetaData.procedureColumnResult: procReturn = "return ResultSet"; default: procReturn = "Unknown"; } // Printout sb.append("Procedure: " + dbProcedureCatalog + "." + dbProcedureSchema + "." + dbProcedureName); sb.append(" ColumnName [ColumnType(ColumnPrecision)]: " + dbColumnName + " [" + dbColumnReturnTypeName + "(" + dbColumnPrecision + ")]"); sb.append(" ColumnReturns: " + procReturn + "(" + dbColumnReturnTypeName + ")"); sb.append(" Radix: " + dbColumnRadix + ", Scale: " + dbColumnScale); sb.append(" Remarks: " + dbColumnRemarks); } rs.close(); connection.close(); } return sb.toString(); }
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 w w w .j a va 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.ProcedureColumnLoader.java
@Override protected ResultSet getResultSet(String catalogName, String schemaName, DatabaseMetaData metaData) throws SQLException { return metaData.getProcedureColumns(catalogName, schemaName, WILDCARD, WILDCARD); }
From source file:org.apache.ddlutils.task.DumpMetadataTask.java
/** * Dumps the contents of the indicated procedure. * /*from w w w. j a v a 2 s. c o m*/ * @param xmlWriter The xml writer to write to * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param procedureName The procedure name */ private void dumpProcedure(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData, final String catalogName, final String schemaName, final String procedureName) throws SQLException { performResultSetXmlOperation(xmlWriter, null, new ResultSetXmlOperation() { public ResultSet getResultSet() throws SQLException { return metaData.getProcedureColumns(catalogName, schemaName, procedureName, _columnPattern); } public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException { Set columns = getColumnsInResultSet(result); String columnName = result.getString("COLUMN_NAME"); if ((columnName != null) && (columnName.length() > 0)) { xmlWriter.writeElementStart(null, "column"); xmlWriter.writeAttribute(null, "name", columnName); if (columns.contains("COLUMN_TYPE")) { try { switch (result.getShort("COLUMN_TYPE")) { case DatabaseMetaData.procedureColumnIn: xmlWriter.writeAttribute(null, "type", "in parameter"); break; case DatabaseMetaData.procedureColumnInOut: xmlWriter.writeAttribute(null, "type", "in/out parameter"); break; case DatabaseMetaData.procedureColumnOut: xmlWriter.writeAttribute(null, "type", "out parameter"); break; case DatabaseMetaData.procedureColumnReturn: xmlWriter.writeAttribute(null, "type", "return value"); break; case DatabaseMetaData.procedureColumnResult: xmlWriter.writeAttribute(null, "type", "result column in ResultSet"); break; default: xmlWriter.writeAttribute(null, "type", "unknown"); break; } } catch (SQLException ex) { log("Could not read the COLUMN_TYPE value for the column '" + columnName + "' of procedure '" + procedureName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } } addIntAttribute(xmlWriter, "typeCode", result, columns, "DATA_TYPE"); addStringAttribute(xmlWriter, "type", result, columns, "TYPE_NAME"); addIntAttribute(xmlWriter, "length", result, columns, "LENGTH"); addIntAttribute(xmlWriter, "precision", result, columns, "PRECISION"); addShortAttribute(xmlWriter, "short", result, columns, "SCALE"); addShortAttribute(xmlWriter, "radix", result, columns, "RADIX"); if (columns.contains("NULLABLE")) { try { switch (result.getInt("NULLABLE")) { case DatabaseMetaData.procedureNoNulls: xmlWriter.writeAttribute(null, "nullable", "false"); break; case DatabaseMetaData.procedureNullable: xmlWriter.writeAttribute(null, "nullable", "true"); break; default: xmlWriter.writeAttribute(null, "nullable", "unknown"); break; } } catch (SQLException ex) { log("Could not read the NULLABLE value for the column '" + columnName + "' of procedure '" + procedureName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } } addStringAttribute(xmlWriter, "remarks", result, columns, "REMARKS"); } } public void handleError(SQLException ex) { log("Could not read the columns for procedure '" + procedureName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } }); }
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 ww w .ja v a2 s. co 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(); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * test getProcedureColumns()//from ww w.j av a 2 s . c o m * @throws SQLException */ @Test public void testProcCols() throws SQLException { DatabaseMetaData dbmd = con.getMetaData(); assertNotNull(dbmd); // currently getProcedureColumns always returns an empty resultset for Hive ResultSet res = dbmd.getProcedureColumns(null, null, null, null); ResultSetMetaData md = res.getMetaData(); assertEquals(md.getColumnCount(), 20); assertFalse(res.next()); }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Gets the metadata of the stored procedure by the given name either from the cached version or * by enquiring the database.// w w w . jav a 2 s. co m * @param meta the database meta data * @param catalog the catalog name or null * @param schema the schema name or null * @param procedure the procedure name * @return metadata about the named procedure or null * @throws SQLException when metadata query goes wrong */ public StoredProcedure getStoredProcedure(DatabaseMetaData meta, DBIdentifier catalog, DBIdentifier schema, String procedure) throws SQLException { if (_procs.containsKey(procedure)) { return _procs.get(procedure); } ResultSet rs = meta.getProcedureColumns(getCatalogNameForMetadata(catalog), getSchemaNameForMetadata(schema), procedure, null); StoredProcedure sp = null; if (rs.next()) { sp = new StoredProcedure(rs); } _procs.put(procedure, sp); return sp; }
From source file:org.mule.module.db.internal.resolver.param.StoredProcedureParamTypeResolver.java
@Override public Map<Integer, DbType> getParameterTypes(DbConnection connection, QueryTemplate queryTemplate) throws SQLException { DatabaseMetaData dbMetaData = connection.getMetaData(); String storedProcedureName = getStoredProcedureName(dbMetaData, queryTemplate.getSqlText()); ResultSet procedureColumns = dbMetaData.getProcedureColumns(connection.getCatalog(), null, storedProcedureName, "%"); try {// w w w. j av a 2s . co m return getStoredProcedureParamTypes(connection, storedProcedureName, procedureColumns); } finally { if (procedureColumns != null) { procedureColumns.close(); } } }
From source file:org.openadaptor.auxil.connector.jdbc.writer.AbstractSQLWriter.java
/** * Get the types of the args of a stored proc. * <br>/*from ww w .j av a 2 s . c o m*/ * From javadoc on DatabaseMetaData.getProcedureColumns() * <pre> * 1. PROCEDURE_CAT String => procedure catalog (may be null) * 2. PROCEDURE_SCHEM String => procedure schema (may be null) * 3. PROCEDURE_NAME String => procedure name * 4. COLUMN_NAME String => column/parameter name * 5. COLUMN_TYPE Short => kind of column/parameter: * * procedureColumnUnknown - nobody knows * * procedureColumnIn - IN parameter * * procedureColumnInOut - INOUT parameter * * procedureColumnOut - OUT parameter * * procedureColumnReturn - procedure return value * * procedureColumnResult - result column in ResultSet * 6. DATA_TYPE int => SQL type from java.sql.Types * 7. TYPE_NAME String => SQL type name, for a UDT type the type name is fully qualified * 8. PRECISION int => precision * 9. LENGTH int => length in bytes of data *10. SCALE short => scale *11. RADIX short => radix *12. NULLABLE short => can it contain NULL. * * procedureNoNulls - does not allow NULL values * * procedureNullable - allows NULL values * * procedureNullableUnknown - nullability unknown *13. REMARKS String => comment describing parameter/column * *</pre> * */ protected int[] getStoredProcArgumentTypes(String storedProcName, Connection connection) throws SQLException { //Fix for #SC36: MapCallableStatementWriter misses first argument for Oracle databases // Now it checks each columnType, and only includes IN or INOUT types. // ToDo: Further validation of this approach. Perhaps OUT should also be included? DatabaseMetaData dmd = connection.getMetaData(); List sqlTypeList = new ArrayList(); String catalog = connection.getCatalog(); String schema = "%"; String proc = storedProcName; String column = "%"; log.debug("Catalog for stored proc " + storedProcName + " is " + catalog); ResultSet rs; //Oracle doesn't bother with catalogs at all :-( //Thus if it's an oracle db, we may need to substitute package name instead //of catalog. if ((catalog == null) && (oraclePackage != null)) { //oraclePackage will only be non-null for oracle db log.debug("Setting catalog to oracle package of: " + oraclePackage); catalog = oraclePackage; schema = null;//Oracle 'ignore' setting. Probably the same as "%" anyway. } //Check if there's a schema reference in the proc name... String[] components = storedProcName.split("\\."); int len = components.length; if (len > 1) { schema = components[len - 2]; proc = components[len - 1]; } log.debug("Resolving proc - catalog=" + catalog + ";schema=" + schema + ";proc=" + proc + ";column=" + column); rs = dmd.getProcedureColumns(catalog, schema, proc, column); //If RS is empty, then we have failed in our mission. if (!rs.next()) { //First rs is return value. rs.close(); String msg = "Failed to lookup stored procedure " + storedProcName; log.warn(msg); throw new SQLException(msg); } do { //Verify that each argument is an IN or INOUT arg type. int type = rs.getInt(5); //Need to check if it is a result, or an input arg. int dataType = rs.getInt(6); // DATA_TYPE is column six! if (log.isDebugEnabled()) { log.debug("Catalog=" + rs.getString(1) + "; Schema=" + rs.getString(2) + "; Proc=" + rs.getString(3) + "; Column=" + rs.getString(4) + "; ParamType=" + spTypeToString(type) + "(" + type + ")" + "; DataType=" + dataType + "; TypeName=" + rs.getString(7)); } if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { log.debug("Argument of type " + type + " is IN or INOUT"); sqlTypeList.add(Integer.valueOf(dataType)); // DATA_TYPE is column six! } else { log.debug("Ignoring column of type " + type + " as it is neither IN nor INOUT"); } } while (rs.next()); log.debug("Number of stored procedure parameters found: " + sqlTypeList.size()); int[] sqlTypes = new int[sqlTypeList.size()]; for (int i = 0; i < sqlTypes.length; i++) { sqlTypes[i] = ((Integer) sqlTypeList.get(i)).intValue(); } rs.close(); return sqlTypes; }