how to run mysql stored procedure using java

Question

I have a mysql stored procedure.

DELIMITER //  
CREATE PROCEDURE GetRecordsByAge(IN Age INT)  
BEGIN 
  SELECT * FROM test1 WHERE age = Age;  
END //  
DELIMITER ;

When I run this stored procedure through java then it is giving all records of the table.

But if I have a condition in select statement. Why this is happening?

here is set of lines of my code:-

CallableStatement cStmt = null;
cStmt = con.prepareCall("{ CALL GetSpecificRecord(?) }");
cStmt.setInt(1, 25);
cStmt.execute();
ResultSet rs1 = cStmt.getResultSet();

When I print this result set this will give all records of the table.

Where is the problem?

Thanks

you are calling GetSpecificRecord, but your proc is GetRecordsByAge — Iswanto San
– Iswanto San, Commented Feb 22, 2013 at 7:22
Have you tried testing the sp in mysql itself.. Are you getting the desired result? Iswanto spotted it!! — SaK
– SaK, Commented Feb 22, 2013 at 7:24

John Woo · Accepted Answer · 2013-02-22 07:27:33Z

10

The reason why you are getting all records is because of column name collision causing the WHERE clause to be always true. Change the name of the parameter that is not the same with the name of the column you are comparing with,

DELIMITER //  
CREATE PROCEDURE GetRecordsByAge(IN _Age INT)  
BEGIN 
  SELECT * FROM test1 WHERE age = _Age;  
END //  
DELIMITER ;

The second is, you are calling different stored procedure. instead of GetRecordsByAge, you are calling GetSpecificRecord.

edited Feb 22, 2013 at 7:27

answered Feb 22, 2013 at 7:22

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahmoud Gamal · Accepted Answer · 2013-02-22 07:22:24Z

4

Because the condition WHERE age = Age is always ture.

Change the parameter name Age to something else:

DELIMITER //  
CREATE PROCEDURE GetRecordsByAge(IN AgeParam INT)  
BEGIN 
  SELECT * FROM test1 WHERE age = AgeParam;  
END //  
DELIMITER ;

answered Feb 22, 2013 at 7:22

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Collectives™ on Stack Overflow

how to run mysql stored procedure using java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related