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