Calling stored procedures

24 December 2007

Stored procedures contain set of SQL statements and are stored on the database server. Their query execution plan is already define, so they run faster and improve the performance. In this post, I will talk about how to call stored procedures.

An object of CallebleStatement (extends PrepareStatement) is used to execute store procedures. PrepareCall() method is use to create CallebleStatement’s object.

CallebleStatement cst;
Cst = conn.prepareCall(“{call mySProc()}”);

The syntax for calling store procedure is vendor specific. It may very for MS SQL server, Oracle, MySQL and Postgre SQL. So check the syntax before use.

Stored procedures may also take arguments therefoe, CallebleStatement can create placeholders for the arguments.

CallebleStatement cst;
Cst = conn.prepareCall(“{call mySProc(?,?)}”);

setXXX(…) methods can be used to set the arguments. Index is important when setting the placeholders.

cst.setString(1, “Java”);
cst.setInt(2, 1000);
cst.executeUpdate();

After providing all the arguments, executeUpdate() is called to execute the stored procedure.

Stored procedure may also return some value. For that, we have to use registerOutParameter before executing the stored procedure.

CallebleStatement cst;
Cst = conn.prepareCall(“{call mySProc(?,?,?)}”);
cst.setString(1, “Java”);
cst.setInt(2, 1000);
cst. RegisterOutParameter(3, java.sql.Types.INTEGER);
cst.execute();
int rValue = cst.getInt(3);