Saturday, March 7, 2009

Raise Exception / Error in MySQL 5.0

There is no standard way to raise exception or error in MySQL.
But there is a trick:
  • call to a not existing Stored Procedure raises MySQL exception
  • so call to not existing Stored Procedure using "error message" as "procedure name" forces MySQL to raise exception that looks like "error message"
So to have Raise Exception feature we need next stored procedures / functions:
  • procedure to execute dynamic SQL statements
  • function to format text
  • and main procedure to raise error / exception

See the code below:
DELIMITER $$


-- PROCEDURE pExecuteImmediate
-- executes dynamic SQL statement
-- Params:
-- tSQLStmt - SQL statement to be executed
DROP PROCEDURE IF EXISTS `pExecuteImmediate` $$
CREATE PROCEDURE `pExecuteImmediate`(IN tSQLStmt TEXT)
BEGIN
SET @executeImmediateSQL = tSQLStmt;
PREPARE executeImmediateSTML FROM @executeImmediateSQL;
EXECUTE executeImmediateSTML;
DEALLOCATE PREPARE executeImmediateSTML;
END $$


-- FUNCTION fFormat
-- replaces '%s' in a string with some value
-- Params:
-- sFormat - string to be formatted
-- sPar1 - value used in replacement
-- Returns:
-- formatted string
DROP FUNCTION IF EXISTS `fFormat` $$
CREATE FUNCTION fFormat(sFormat TEXT, sPar1 TEXT)
RETURNS TEXT
BEGIN
RETURN REPLACE(sFormat, '%s', sPar1);
END $$


-- PROCEDURE pRaiseError
-- raises error
-- Params:
-- sError - error message
DROP PROCEDURE IF EXISTS `pRaiseError` $$
CREATE PROCEDURE `pRaiseError`(sError VARCHAR(255))
BEGIN
-- trick
-- calling of not existing procedure with name that equals error message
-- will force MySQL exception that looks like error message
CALL pExecuteImmediate(fFormat('CALL `error: %s, solution`', sError));
END $$


DELIMITER ;

After execution of SQL:
CALL pRaiseError("this is a sample error message")

MySQL will raise exception:
PROCEDURE error: this is a sample error message, solution does not exist

Additional information could be found here: Stored Procedures in MySQL 5.x

8 comments:

José said...

Hi,

I want to use your procedures, in in a tutorial i writing, the tutorial is in Spanish language, is about creating a sign-up page with PHP and MYSQL. Is there any license copyright problems with use this code? I will include a link to the original article within the code.

Regards,
José

moisespsena said...

Exceptional!!!!!!

Anonymous said...

Useful, and yet sad at the same time that we must partake in such behavior to raise an exception from an "enterprise" database.

Arlindo said...

If this CALL pRaiseError("this is a sample error message") in a TRIGGER, this message return: ERROR 1336 (0A000) at line 18: Dynamic SQL is not allowed in stored function or trigger

Mysql 5.1

Kanhar Munshi said...

Excellent post. I implemented these functions in my project.

harshadk said...

@Arlindo: Did you get any solution to it? I am also getting same error using this in Trigger.

Peter Nosko said...

I am getting the same error reported by Arlindo.

Cognegic said...

Solve MySQL Formatting Error Message with MySQL Remote Support
Confronting MySQL organizing mistake message? Not ready to take care of this issue? At that point contact to Cognegic's MySQL Remote Service or MySQL Server 5.0 Support. We are the main help supplier in the USA who guarantees to give world's best help and arrangement with respect to MySQL and different databases including: Oracle, Cassandra, MongoDB, MS SQL Server et cetera. For an issue or issue you can specifically contact to our MySQL Enterprise Backup.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801