MySQL Forums
Forum List  »  Stored Procedures

error handling
Posted by: umoren christy
Date: May 26, 2011 07:53AM

Hi, i am new to this forum.
Pls i would like to know if there's any new way i can handle my errors. I usually write my error messages myself in my code but i heard mysql 5.5 has a way to handle that automatically. Please help. Below are 3 procedures that i would like anybody to help me modify using the new feature of mysql 5.5 error handling.
Procedure summary: a function to return boolean if user has or doesn't have permisson; procedure to check if user has permission and flag error if user doesn't have. thank u; error procedure

1.

DROP FUNCTION IF EXISTS hasPermission//
CREATE FUNCTION hasPermission(inUser VARCHAR(10), inPermission VARCHAR(10)) RETURNS BOOLEAN
/* return true if user has the given privilege
14.1.09 root always has privilege.
*/
BEGIN
RETURN USER() LIKE "root@%" OR
(SELECT COUNT(*) FROM Permission
WHERE `Mode`=inPermission AND RoleID = (SELECT RoleID FROM Users



2.

DROP PROCEDURE IF EXISTS checkPermission//
CREATE PROCEDURE checkPermission(inUser VARCHAR(10), in_procedure VARCHAR(255), in_Perm VARCHAR(10))
/*
* If user doesnt have the given permission, do some error thing.
*/
BEGIN
IF NOT hasPermission(inUser, in_Perm) THEN
SET @ERRMSG:=CONCAT(inUser,": Permission denied");
CALL raise_application_error(-1002, in_procedure, @ERRMSG);
ELSE
SET @ERRMSG:=NULL;
END IF;
END;//


3.

DROP PROCEDURE IF EXISTS raise_application_error//
create procedure raise_application_error(errno int, proc varchar(255), errm varchar(255))
/*
Raise an ERROR.
errno: error number
proc: stored procedure that is raising the error
errm: error message

*/
begin
set @errno := errno;
set @errm := errm;
set @stmt := concat('select raise_my_error', '(''',errno,':',proc,':',errm,''')');
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
end;//

Options: ReplyQuote


Subject
Views
Written By
Posted
error handling
3097
May 26, 2011 07:53AM
685
May 26, 2011 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.