Rajesh kannan Rajesh kannan - 2 months ago 7
Java Question

Java stored procedure with mysql

Can you help me: I'm trying to execute a stored procedure using jdbc but I keep getting an error:


java.sql.SQLException: Parameter index of 2 is out of range (1, 1)


Procedure:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `count_product`(IN p_id INT)

BEGIN
select firstname FROM product WHERE id=p_id;
END $$


Code:

public class StoredProcedure {

public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con=null;

java.sql.CallableStatement stmt = null;
Scanner input = new Scanner(System.in);
System.out.println("Enter the Id(int):");
int id = Integer.parseInt(input.nextLine());

try {
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/personsdb","root","");

System.out.println("Connected");
stmt = con.prepareCall("{call count_product(?,?)}");

System.out.println("called");
stmt.setInt(1, id);
stmt.registerOutParameter(2,java.sql.Types.VARCHAR);

stmt.execute();

String firstname = stmt.getString(2);

if(firstname != null) {
System.out.println("First Name=" + firstname);
}
else {
System.out.println("Result Not Found");
}
}

Answer

You have not defined any OUT parameter in your SP signature. Unless which, it throws an error while registering an OUT parameter from client program.

Change your SP signature as below:

CREATE DEFINER=`root`@`localhost` 
       PROCEDURE `count_product`(IN p_id INT, OUT out_param varchar)

But, in your procedure you are just selecting a set of records but not doing any other operation to find and set an OUT parameter. So, there won't be any need of such OUT parameter. Instead read the resultset after executing statement and loop through to find what you want.