 | Long time ago, I saw a blog post by Jorge Bernal, with a simple implementation of SIGNAL for MySQL stored procedures. If you have ever tried to write MySQL stored procedures, you know how dearly missed is this feature. I discussed this feature internally, and everyone told me "don't bother, we're going to implement SIGNAL in MySQL 6.1". And indeed, the full implementation for SIGNAL and RESIGNAL is in the roadmap. |
What does that mean? Should we wait two or three more years to have a SIGNAL feature? Wouldn't it be nice to have a minimal SIGNAL working today, and get the full thing when it's ready?
A bird in the hand ...
I would like to have SIGNAL available today, and therefore I tested Jorge's patch in the latest 5.1.28 code. It needs some more twisting to get it working. If you want to try it, make sure you have the current patch.Once you apply the patch and compile the code, you will have a server that accepts a simple "SIGNAL" command, without any parameter.
Here's a simple usage example:
First, you create two stored procedures:
delimiter //
drop procedure if exists less_than_10 //
create procedure less_than_10(i int )
deterministic
begin
if ( i < 10)
then
set @comment = concat('a valid number was entered (', i,')');
else
set @comment = concat('number too high (', i,')') ;
SIGNAL ;
end if;
select concat('everything fine [',@comment,']') as comment;
end//
drop procedure if exists trapping //
create procedure trapping ()
deterministic
begin
declare mystatus varchar(20) default 'ok';
declare continue handler for SQLSTATE '38503'
set mystatus = 'ERROR';
call less_than_10(7);
select mystatus, @comment;
call less_than_10(17);
select mystatus, @comment;
select 'and this is after the error';
end //
delimiter ;
And then we'll try out the simple one, by calling directly the procedure with a SIGNAL.
call less_than_10(2);
+--------------------------------------------------+
| comment |
+--------------------------------------------------+
| everything fine [a valid number was entered (2)] |
+--------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
select @comment;
+--------------------------------+
| @comment |
+--------------------------------+
| a valid number was entered (2) |
+--------------------------------+
1 row in set (0.00 sec)
call less_than_10(12);
ERROR 1340 (38503): Exception generated from user-defined function/procedure
select @comment;
+----------------------+
| @comment |
+----------------------+
| number too high (12) |
+----------------------+
1 row in set (0.00 sec)
next, we call the procedure that traps the exception. This one has full control of the execution flow, with SIGNAL and HANDLER.
call trapping();
+--------------------------------------------------+
| comment |
+--------------------------------------------------+
| everything fine [a valid number was entered (7)] |
+--------------------------------------------------+
1 row in set (0.00 sec)
+----------+--------------------------------+
| mystatus | @comment |
+----------+--------------------------------+
| ok | a valid number was entered (7) |
+----------+--------------------------------+
1 row in set (0.00 sec)
+----------+----------------------+
| mystatus | @comment |
+----------+----------------------+
| ERROR | number too high (17) |
+----------+----------------------+
1 row in set (0.00 sec)
+-----------------------------+
| and this is after the error |
+-----------------------------+
| and this is after the error |
+-----------------------------+
1 row in set (0.00 sec)
This less than 30 lines patch is much better than many hacks that we've seen published for years.
What's your take? Would you like to have this feature in MySQL 5.1, while the full thing is being developed for 6.1?
