CODEWITHSUNDEEP
javamysqlsqlstored-procedures
user591790
user591790

Reputation: 555

how to run mysql stored procedure using java

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

Upvotes: 1

Views: 441

Answers (2)

John Woo
John Woo

Reputation: 263893

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.

Upvotes: 10

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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 ;

Upvotes: 4

Related Questions