Callable statement Example

Callable statement example

public class CallableStmt
{
public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:uma","kworker","kworker");

//calling a stored procedure with no input/output param
/*
CREATE PROCEDURE HELLOWORLD
AS
SELECT 'HELLOWORLD' AS HELLO
*/
CallableStatement cs1 = con.prepareCall("{call HelloWorld}");
ResultSet rs1 = cs1.executeQuery();
while(rs1.next())
{
String one = rs1.getString("HELLO");
System.out.println(one);
}


//Calling a stored procedure which takes in 2 parameters for addition
/*
--EXECUTE ADDITION 10,25,NULL
ALTER PROCEDURE ADDITION
@A INT
, @B INT
, @C INT OUT
AS
SELECT @C = @A + @B
*/
CallableStatement cs2 = con.prepareCall("{call ADDITION(?,?,?)}");
cs2.registerOutParameter(3,java.sql.Types.INTEGER);
cs2.setInt(1,10);
cs2.setInt(2,25);
cs2.execute();
int res = cs2.getInt(3);
System.out.println(res);

//Another way
/*
--create table test(slno int,ques varchar(100),ans text)
--EXECUTE fetchRec 1
create procedure fetchRec
@A int
as
select * from test where slno=@A
*/
CallableStatement cs3 = con.prepareCall("{call fetchRec(?)}");
cs3.registerOutParameter(1,java.sql.Types.INTEGER);
cs3.setInt(1,5);
ResultSet rs3 = cs3.executeQuery();
while(rs3.next())
{
String ques = rs3.getString(2);
String ans = rs3.getString(3);
System.out.println(ques);
System.out.println(ans);
}


}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Click here for more samples...