I know mysql 5.5 allows using SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: ...';
to raise a user defined error. And this error will stop an INSERT
operation if it was put in the BEFORE INSERT TRIGGER
on some tables. And it's also convenient for PDO
to catch the PDOException
and output the errorinfo() i.e. MESSAGE_TEXT
defined in SIGNAL SQLSTATE
.
However, the version of mysql on the server I rent is mysql 5.1. And I want to know how can I raise a user defined error with the features like the SIGNAL SQLSTATEMENT
in mysql 5.5.
- interrupt a
insert
operation when it's inbefore insert
trigger - can be caught by
PDO
I've found some topics on similar problems, and I've tried these:
call a nonexist procedure
call `sp_raise_error`;
use a function to throw an error
https://blogs.oracle.com/svetasmirnova/entry/how_to_raise_error_in
Both can't be caught by PDO
. So what's a solution? ( I tested on MySQL5.5 )
If you use
SIGNAL SQLSTATE
in a lower version of MySQL (like 5.1), you will get a1064
error. So, in order to use the function likeSIGNAL SQLSTATE
does, you can try the following steps.1. Create Auxiliary Table Named "TBL_DUMMY"
2. Create BEFORE INSERT Trigger on TBL_DUMMY
3. Create Procedure Named "SP_RAISE_ERROR"
4. Usage
Just execute
SP_RAISE_ERROR
instead ofSIGNAL SQLSTATE
. For instance,CALL SP_RAISE_ERROR ('Password incorrect.')
will throw an exception and the message is:And you can use it in procedures:
After that, you can extract error texts from the messages in an external program.