Thursday 13 March 2008

How to invoke a stored procedure using JDBC

The very first thing you need to know is the Stored Procedure signatures.
what is the name of the stored procedure?
does the stored procedure have any return type?
how many parameters it takes and which parameters are of type OUT or INOUT?

stored procedures are of basically 3 types "in", "out" "inout"
1) "in" is the default type. such parameters are the ones which are used to pass the value to stored procedure when we invoke the procedure.
e.g. lets us say we have int sum(n1 int, n2 int)
here n1 and n2 are the in parameters and sum is returned through return type int.

2) "out" : Some stored procedures return values through parameters. When a parameter in a SQL statement or stored procedure is declared as "out", the value of the parameter is returned back to the caller.

3) "inout" : this is both "in" and "out". when the procedure is called the value is passed in this parameter and stored procedure returns the value back to caller in this parameter.

Another SQL question... whats difference between a function and a procedure ?
simple question :) If you don't know the answer ... then you need to do some homework on your SQL skills first :)

Inovking any Stored procedure from JDBC is a 6 step process.

Step - I : Define the call for SP.
e.g. Lets us assume I have a SQL function int sum(n1 int, n2 int)
so calling sum requires n1 and n2 as inputs and it returns the result.
I would write my call as String spCall ="{ ? = call sum(?, ?)}";

If I have a SQL Procedure sum(n1 int, n2 int, n3 int OUT)
this implies the procedure requires 2 input parameters and we get the result in 3rd parameter. In this case the call would be String spCall ="{call sum(?, ?, ?)}";

I have a SQL Procedure sum(n1 int, n2 int INOUT)
This implies the procedure requires 2 input parameters and we get the result in 2nd parameter itself. In this case the call would be String spCall ="{call sum(?, ?)}";

Isn't it quite simple?

Lets us complete all the next steps with ex. (1)
So the call is String spCall = "{ ? = call sum(?, ?)}";

Step-II : The next step is to define the CallableStatement object. CallableStatements facilitate a JDBC program to execute any valid SQL Block or Procedures. So we would define a callablestatement for our spCall as follows

CallableStatement sp = con.prepareCall(spCall);

Step III : Identify all the OUT or INOUT parameters type and register them. What do I mean by register ? This meant 2 things
1) What is the index of such parameters.
2) What type of value these parameters will return. now how do we specify type. JDBC have java.sql.Types which identifies the type of a sql parameters e.g. Types.INT identifies integers, Types.LONG indentifies LONG ... what would be Types.STRING?

now given above facts what will happen with our sum example? don't you agree the first parameter is the one which would contain the results and type would be int.
so here we go ...

sp.registerOUTParameter(1, TYPES.Types);

STEP-IV : Set all the input parameters which the stored procedure needs for invocation. This is very similar to how we use prepared statements. Take this statement as a thumb rule.
e.g. let us say we want to invoke sum with n1=10 and n2=20
sp.setInt(2, 10)
sp.setInt(3, 20)
This is what i meant. I set 2nd parameter with value 10 and 3rd parameter value 20.

All is set now....

Step V: The most straightforward one .. call execute on the sp
sp.execute()
This would execute the procedure...


Step VI : Reap what you sow ... Time to get the results out ..
We would fetch the results from the stored procedure . This is very similar to how you retrieve values from ResultSet. Again a Thumb rule.

int sum_n1_n2 = sp.getInt(1)

did you noticed 2 things here. 1) I called getInt(). because I registered the out parameter parameter as an Int in step III
2) I called getInt(1) because index "1" is registered as an out parameter in step III.

and this completed the 6 step process. Now this is the most generalized set of sequences you need to invoke any stored procedure. Any of these steps become optional depending upon the fact what your SP signatures demand.

0 comments: