Procdure Call using Java(JSP)

Database Procedure Call using Java. It requires 6 basic steps:

Step 1:Define the call to the database procedure.
As with a prepared statement, you use special syntax to define a call to a stored procedure. The procedure definition uses escape syntax, where the appropriate ? defines input and output parameters.

• Procedure with no parameters. ---- { call procedure_name }
• Procedure with input parameters.-- { call procedure_name(?, ?, ...) }
• Procedure with an output parameter.---- { ? call procedure_name }
• Procedure with input and output parameters.{ ? = call procedure_name(?, ?, ...) }



Step 2: Prepare a CallableStatement for the procedure. You obtain a CallableStatement from a Connection by calling prepareCall.

String procedure = "{ ? = call procedure_name( ?, ? ) }";
CallableStatement statement =
connection.prepareCall(procedure);


Step 3: Register the output parameter types.
Before executing the procedure, you must declare the type of each output parameter.

statement.registerOutParameter(n, type);

Step 4: Provide values for the input parameters.
Before executing the procedure, you must supply the input parameter values.

statement.setString(2, "name");
statement.setFloat(3, 26.0F);


Step 5: Execute the stored procedure.
To execute the database stored procedure, call execute on the CallableStatement.

statement.execute();

Step 6: Access the returned output parameters. Call the corresponding getXxx method, according to the output type.

int value = statement.getInt(1);