How can I investigate the parameters to send into and receive from a database stored procedure?
Created May 4, 2012
Lennart Jorelid
Use the method getProcedureColumns in interface DatabaseMetaData to probe a stored procedure for metadata. The exact usage is described in the code below.
NOTE! This method can only discover parameter values. For databases where a returning ResultSet is created simply by executing a SELECT statement within a stored procedure (thus not sending the return ResultSet to the java application via a declared parameter), the real return value of the stored procedure cannot be detected. This is a weakness for the JDBC metadata mining which is especially present when handling Transact-SQL databases such as those produced by SyBase and Microsoft.
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 column definitions for procedure "getFoodsEaten" in // schema "testlogin" and catalog "dbo". System.out.println("Procedures are called '" + dbmd.getProcedureTerm() +"' in the DBMS."); ResultSet rs = dbmd.getProcedureColumns("test", "dbo", "getFoodsEaten", "%"); // 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 = null; 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 System.out.println("Procedure: " + dbProcedureCatalog + "." + dbProcedureSchema + "." + dbProcedureName); System.out.println(" ColumnName [ColumnType(ColumnPrecision)]: " + dbColumnName + " [" + dbColumnReturnTypeName + "(" + dbColumnPrecision + ")]"); System.out.println(" ColumnReturns: " + procReturn + "(" + dbColumnReturnTypeName + ")"); System.out.println(" Radix: " + dbColumnRadix + ", Scale: " + dbColumnScale); System.out.println(" Remarks: " + dbColumnRemarks); } // Close database resources rs.close(); conn.close(); } |