Friday, June 10, 2011

How to Call Procedure

package org.best.example;

import java.sql.*;
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();
         }
     }
}

No comments: