View RSS Feed

My Java Tips

Calling stored procedures

Rate this Entry
by , 11-12-2011 at 06:21 PM (1628 Views)
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.

Java Code:
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);

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.

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

Submit "Calling stored procedures" to Facebook Submit "Calling stored procedures" to Digg Submit "Calling stored procedures" to Submit "Calling stored procedures" to StumbleUpon Submit "Calling stored procedures" to Google