stored procedure permission in mysql
by Ranganathan[ Edit ] 2013-06-10 16:22:23
Procedure
DELIMITER //
CREATE PROCEDURE mdata(IN mid INT,OUT p VARCHAR(100))
BEGIN
DECLARE told, tnew,mcount;
IF mcount = 0 THEN
SELECT COUNT(*) INTO tnew FROM score_update where match_id=mid;
SELECT COUNT(*) INTO told FROM score_update_old where match_id=mid;
IF tnew = told THEN
SET p='data moved';
END IF;
ELSE
SET p='not moved';
END IF;
END //
DELIMITER ;
Procedure Permission :
GRANT EXECUTE ON PROCEDURE myDB.mdata TO 'dbuser'@'localhost' IDENTIFIED BY 'passwordxxxx';
Execute Procedure in PHP
$move=mysql_query("CALL mdata($mid, @p);");
$move1=mysql_query("select @p as result;");
Read Ouput Procedure
while ($obj = mysql_fetch_object($move1))
{
echo $obj->result;
}
