How to raise error in your MySQL application

Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround.

Here it is. Create 2 procedures as following:

DROP PROCEDURE IF EXISTS raise_application_error;
DROP PROCEDURE IF EXISTS get_last_custom_error;
DROP TABLE IF EXISTS RAISE_ERROR;

DELIMITER $$
CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);

  SELECT CODE, MESSAGE INTO @error_code, @error_message;
  INSERT INTO RAISE_ERROR VALUES(NULL);
END;
$$

CREATE PROCEDURE get_last_custom_error() SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  SELECT @error_code, @error_message;
END;
$$
DELIMITER ;

You can use them as:

CALL raise_application_error(1234, 'Custom message');
CALL get_last_custom_error();


Example: table which stores only odd numbers.

DROP TABLE IF EXISTS ex1;
DROP TRIGGER IF EXISTS ex1_bi;
DROP TRIGGER IF EXISTS ex1_bu;

CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);

DELIMITER $$

CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROW
BEGIN
  IF NEW.only_odd_numbers%2 != 0 THEN
    CALL raise_application_error(3001, 'Not odd number!');
  END IF;
END
$$

CREATE TRIGGER ex1_bu BEFORE UPDATE ON ex1 FOR EACH ROW
BEGIN
  IF NEW.only_odd_numbers%2 != 0 THEN
    CALL raise_application_error(3001, 'Not odd number!');
  END IF;
END
$$

DELIMITER ;



Usage:


mysql> INSERT INTO ex1 VALUES(2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ex1 VALUES(3);
ERROR 1048 (23000): Column 'F1' cannot be null
mysql> CALL get_last_custom_error();
+-------------+-----------------+
| @error_code | @error_message  |
+-------------+-----------------+
| 3001        | Not odd number! |
+-------------+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT \* FROM ex1;
+------------------+
| only_odd_numbers |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Comments:

Got the answer thanks.

Posted by Outsource software development on September 23, 2009 at 03:54 AM MSD #

This solution may not be as pretty, but I think it is easier to use.

1. Create a function to throw an error:

CREATE FUNCTION imxfn_RaiseError(_msg VARCHAR(256)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE a TINYINT;
-- Force an error to be raised by assigning a string to an
-- integer variable. The string will appear on the client.
SET a = _msg;
RETURN 0;
END

2. Call function with message to cause an error:

SELECT imxfn_RaiseError ('Primary key was not found.');

3. The client will see this:

Incorrect integer value: 'Primary key was not found.' for column 'a' at row 1

Posted by Doug Funk on October 14, 2009 at 09:30 PM MSD #

Yes, this is easier, but this would not work with default SQL_MODE='' as requires STRICT SQL_MODE.

Posted by Sveta Smirnova on October 15, 2009 at 05:28 AM MSD #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today