How to raise an error in MySQL function

Question

I 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 a bug in a MySQL function?

Answer 1

MySQL 5.5 introduces signals 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 2

This is actually a combination of all three answers. You call a nonexistent procedure to raise the error, and then you declare an exit handler that catches the error you received. Here is an example using SQLSTATE 42000 (the procedure does not exist) to throw an error before deleting if the row to be deleted has a foreign key identifier:

 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 3

Why not just save VARCHAR in the declared variable INTEGER ?

 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:

Invalid integer value: "something failed" for the "ERROR" column in row 1

This is not ideal, but it gives a rather descriptive message, and you do not need to write any DLL extensions.

Answer 4

In MySQL 5, you can raise an error by calling a stored procedure that does not exist (CALL raise_error), or pass an invalid value to the query (for example, null in the NOT NULL contrained field). Here is an interesting article by Roland Bowman on how to create errors within the MySQL function:

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

Answer 5

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

Answer 6

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