How to raise an error within a MySQL function

Question

I've created a MySQL function and would like to raise an error if the values passed for the parameters are invalid. What are my options for raising an error within a MySQL function?

Answer 1

In MySQL 5 you may raise an error by calling a stored procedure that does not exist (CALL raise_error) or passing an invalid value to a query (like null to a NOT NULL contrained field). Here is an interesting post by Roland Bouman on raising errors from within a MySQL function:

http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html

Answer 2

MySQL 5.5 introduces signals, which are similar to exceptions in other languages:

http://dev.mysql.com/doc/refman/5.5/en/signal.html

For example, in the mysql command line client:

mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';
ERROR 1644 (45000): Custom error

Answer 3

It's actually a combination of all three answers. You call a non-existent procedure to raise the error, and then declare an exit handler that catches the error you generated. Here's an example, using SQLSTATE 42000 (procedure does not exist) to throw an error before deletion if the row to be deleted has a foreign key id set:

DROP PROCEDURE IF EXISTS decount_test;

DELIMITER //

CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Invoiced barcodes may not have accounting removed.';
  IF (SELECT invoice_id 
       FROM accounted_barcodes
       WHERE id = p_id
    ) THEN
    CALL raise_error;
 END IF;
 DELETE FROM accounted_barcodes WHERE id = p_id;
END //

DELIMITER ;

Output:

call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. | 
+----------------------------------------------------+

Answer 4

Why not just store a VARCHAR in a declared INTEGER variable?

DELIMITER $$ DROP FUNCTION IF EXISTS `raise_error` $$
CREATE FUNCTION `raise_error`(MESSAGE VARCHAR(255)) 
RETURNS INTEGER DETERMINISTIC BEGIN
  DECLARE ERROR INTEGER;
  set ERROR := MESSAGE;
  RETURN 0;
END $$ DELIMITER ;
-- set @foo := raise_error('something failed'); -- or within a query

Error message is:

Incorrect integer value: 'something failed' for column 'ERROR' at row 1

It's not perfect, but it gives a pretty descriptive message and you don't have to write any extension DLLs.

Answer 5

You can also call an existing function with an invalid number of arguments.

Answer 6

You have to define exception handlers . Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html