MySql COUNT (*) is different from stored procedure

Question

I am working on a “grading” system and try to prevent a person from submitting an assessment twice using a stored procedure that will check whether a person has rated a particular item before allowing a new one to be saved. Strange, I pass in the user ID and object ID, but when my stored procedure selects COUNT (*), I get the wrong number.

Here is my stored procedure:

CREATE PROCEDURE `ADD_GRADE`(IN objectID int, IN grader int) BEGIN DECLARE gradeCount INT; SELECT COUNT(*) FROM GRADES WHERE Obj_ID = objectID AND Grader = grader INTO gradeCount; IF gradeCount <= 0 THEN INSERT INTO Grades (Obj_ID, Grader) VALUES (objectID, grader); END IF; 

The IF statement works, but for some reason, my Count class seems to be ignoring the Grader ID and just checking based on Obj_ID. I added options to make sure that my parameters remain at the correct value, and they are. If I copy only the selection and do it manually elsewhere, I will get the correct number.

Although new to MySql, I am not new to SQL. Am I just losing my mind? Or am I doing something wrong?

thanks

Answer 1

Even if this code worked (I don’t know why it is not), this is not the right way to make sure that something is entered only once.

The right way is to apply a unique constraint on the objectID and grader columns. Then try pasting the line. If a row is inserted, then the values ​​are unique. If you receive a unique violation, then the values ​​are already entered.

If you do not have unique restrictions, you should lock the table to make sure that no other updates occur between your teams.

Answer 2

My guess is that MySQL is insensitive to field names, and the code

 Grader = grader 

just compares the column to itself, which is always true. you may need to rename the parameter you pass so that it does not have the same name as the existing column. Usually I have all the arguments of the stored procedure continue with __ (douple underscore), so I do not encounter such situations.