How can I investigate the parameters to send into and receive from a database stored procedure?

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();

}