Sunday, October 18, 2009

Raising / throwing custom exceptions in MySQL

I failed to find an official way to explicitly raise or throw exceptions in stored procedures in MySQL.

My workaround works by calling an undefined function with a meaningful name for an error.

For example:

DROP TRIGGER IF EXISTS users_before_insert;
DROP TRIGGER IF EXISTS users_before_update;
DROP PROCEDURE IF EXISTS validate_password;

delimiter $$
CREATE PROCEDURE validate_password (IN passwd VARCHAR(64))
BEGIN
IF LENGTH(passwd) < 5 THEN
CALL TRIGGER_DUMMY_INVALID_PASSWORD;
END IF;
END$$

CREATE TRIGGER users_before_insert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
CALL validate_password(NEW.password_cleartext);
END$$

CREATE TRIGGER users_before_update BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
CALL validate_password(NEW.password_cleartext);
END$$
delimiter ;


Most of the code in the specific example above was actually written by my colleague Per Fuglsang Møller.