Please send your Questions & Answers or Feedback to "mohan@javabook.org"

How do I find all database stored procedures in a database?

Use the getProcedures method of interface java.sql.DatabaseMetaData to probe the database for stored

procedures. The exact usage is described in the code below.

public static void main(String[] args) throws Exception

{ // Load the database driver - in this case, we

// use the Jdbc/Odbc bridge driver.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Open a connection to the database

Connection conn = DriverManager.getConnection("[jdbcURL]",

"[login]", "[passwd]");

// Get DatabaseMetaData

DatabaseMetaData dbmd = conn.getMetaData();

// Get all procedures.

System.out.println("Procedures are called '"

+ dbmd.getProcedureTerm() +"' in the DBMS.");

ResultSet rs = dbmd.getProcedures(null, null, "%");

// 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 dbProcedureRemarks = rs.getString(7);

short dbProcedureType = rs.getShort(8);

// Make result readable for humans

String procReturn = (dbProcedureType == DatabaseMetaData.procedureNoResult

? "No Result" : "Result");

// Printout

System.out.println("Procedure: " + dbProcedureName

+ ", returns: " + procReturn);

System.out.println(" [Catalog | Schema]: [" + dbProcedureCatalog

+ " | " + dbProcedureSchema + "]");

System.out.println(" Comments: " + dbProcedureRemarks);

}

// Close database resources

rs.close();

conn.close();

}

Related Posts Plugin for WordPress, Blogger...
Flag Counter