JDBC : Executing stored procedures

Created 02 November 2004 00:00

To execute a stored procedure on your database server with JDBC, you need to prepare a CallableStatement using your active connection. Input and output parameters but be explicitly declared.

For example, a stored procedure with an output parameter called "@pRet" and two input parameters "@p1" and "@p2":

  CallableStatement cstmt = null;
  double res = 0.0;
  try {
    cstmt = conn.prepareCall("{ ? = call MyProc(?,?) }");
    cstmt.registerOutParameter(1, Types.NUMERIC);
    cstmt.setDouble(2, 1.0);
    cstmt.setDouble(3, 2.0);
    cstmt.execute();
    res = cstmt.getDouble(1);
  }
  catch (SQLException e) {
    ...
  }
  finally {
    try {
      cstmt.close();
    }
    catch (Exception e) { }
  }