HandlersAndConditions
Contents |
[edit] Handlers
So far the things we have looked at have been relatively simple. The functions and procedures we have written have been pretty useless really. To make them more useful it would be good if we could interact with the database more. We will be looking at how to do this soon but before we do this we need to look at handlers. The reason for this is that one of the main methods use to interact with the database is cursors and they use handlers in their processing.
So what are handlers, as the name suggests they are methods of handling conditions which need to be dealt with. These conditions are split into the following groups.
- SQLSTATE
- condition_name
- SQLWARNING
- NOT FOUND
- SQLEXCEPTION
- mysql_error_code
These groups all relate to situations where, for one reason or another, MySQL has decided to give us feedback about the processing of a function, procedure or SQL statement. Its likely you will have encountered them before without noticing.
Before we look at handling lets have a go at trying to produce some errors. We can use the tables we setup at the start of the tutorials.
insert into emps values(1,´Dave´,1); ERROR 1062 (23000): Duplicate entry ´1´ for key 1
So here we can see MySQL produced an error, in this case ERROR 1062. The number between the brackets is the SQLSTATE which can be the same for a number of errors. This can be seen here.
insert into emps values(NULL,´Dave´,1); ERROR 1048 (23000): Column ´emp_id´ cannot be null
This time we have a different error number (1048 in this case) but the same SQLSTATE. When these errors occur in our procedures and functions they will terminate our programs, what we would rather happen is for the program to deal with the error and continue processing or end more gracefully.
So lets look at how we create a handler to deal with these conditions. The syntax is as follows.
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
First we use DECLARE as we did with variable to tell the compiler that we are creating a handler, next is the handler type this can be one of the following CONTINUE, EXIT or UNDO. This is what the program will do when the condition is met. So if we use CONTINUE the program will carry on process after the handler has been called, if we use EXIT the program will end immediately. The final type UNDO is currently not supported but this will be used on transactional tables to rollback work carried out up to that point. HANDLER FOR is simply telling the compiler that we are declaring a handler. The condition_value will be one of the types we mentioned earlier, SQLSTATE, SQLWARNING etc. This is used so that the handler only fires when a specific condition is met. We can define more than one condition at the same time if we want to handle the condition in the same way. Finally we have sp_statement this is a short section of code which will run when the handler is fired. This would normally be a SET statement.
So lets construct a simple example and see how it works. We can try and deal with our duplicate entry problem. First lets create a procedure that doesn´t handle the error and see what happens.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
insert into emps VALUES (1,´Dave´,1,10);
set p_end := ´The End´;
end;
//
Download handlerproc1.myp
OK so we will be expecting the procedure to run the insert statement and then passing out a parameter with the words ´The End´. Lets call it and see what happens.
call handlerproc(@a) // ERROR 1062 (23000): Duplicate entry ´1´ for key 1 select @a // +------+ | @a | +------+ | NULL | +------+ 1 row in set (0.00 sec)
We got the error message but our parameter is empty. This is because the error stopped our procedure dead. In most cases we wouldn´t want that to happen, so we need to use a handler to deal with the error. Lets add in a handler to the procedure and see what happens then.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
declare continue handler for sqlstate ´23000´ SET @b = ´With Errors´;
insert into emps VALUES (1,´Dave´,1,10) ;
set p_end := ´The End´;
end;
//
call handlerproc(@a)
//
Query OK, 0 rows affected (0.00 sec)
select @a
//
+---------+
| @a |
+---------+
| The End |
+---------+
1 row in set (0.00 sec)
Download handlerproc2.myp
We can see that this time we didn´t get the error message and our parameter passed out the value. Thats because when the error occurred the handler took over dealt with the problem and continued processing the procedure. You may have noticed that we also had an @b variable, this was SET to ´With Errors´ when the handler was fired but we did nothing with it. We could have added this to the parameter to show that while the program got to the end there was an error. Lets try that with our program.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
declare continue handler for sqlstate ´23000´ SET @b = ´- With Errors´;
insert into emps VALUES (1,´Dave´,1,10) ;
set p_end := concat(´The End ´,@b);
end;
//
call handlerproc(@a)
//
Query OK, 0 rows affected (0.00 sec)
select @a
//
+-----------------------+
| @a |
+-----------------------+
| The End - With Errors |
+-----------------------+
1 row in set (0.00 sec)
Download handlerproc3.myp
This time the program handled the error and we output the result of the SQL that was run when the handler was called.
So we have been using a handler to deal with SQLSTATE. This will deal with a set of different errors but we might have a situation where we would want to deal with different conditions which are grouped under the same SQLTATE in a different way. Take for example the situation we looked at earlier, we had 2 errors which had the same SQLSTATE but different error numbers. We can add 2 handlers to the procedure to deal with both problems.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
declare continue handler for 1062 SET @b = ´- With Error 1062´;
declare continue handler for 1048 SET @b = ´- With Error 1048´;
insert into emps VALUES (1,´Dave´,1,10) ;
set p_end := concat(´The End ´,@b);
end;
//
Download handlerproc4.myp
Lets see what the output produced by this procedure.
call handlerproc(@out) // Query OK, 0 rows affected (0.01 sec) select @out // +---------------------------+ | @out | +---------------------------+ | The End - With Error 1062 | +---------------------------+ 1 row in set (0.00 sec)
We can easily show what would happen if we changed the procedure so that it produced the other error.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
declare continue handler for 1062 SET @b = ´- With Error 1062´;
declare continue handler for 1048 SET @b = ´- With Error 1048´;
insert into emps VALUES (NULL,´Dave´,1,10) ;
set p_end := concat(´The End ´,@b);
end;
//
call handlerproc(@out) //
Query OK, 0 rows affected (0.00 sec)
select @out //
+---------------------------+
| @out |
+---------------------------+
| The End - With Error 1048 |
+---------------------------+
1 row in set (0.00 sec)
Download handlerproc5.myp
So we have looked at SQLSTATE and specific error messages, lets now look at the remaining types of conditions that we can handle.
We can use SQLWARNING to handle all SQLSTATE codes that begin with 01 or NOT FOUND is for all SQLSTATE codes that begin with 02. SQLEXCEPTION will handle all SQLSTATE codes not caught by SQLWARNING or NOT FOUND.
A list of error messages can be found [here].
The final type of condition we can handle are user defined conditions. These are conditions that we can define ourselves. We will be looking at those in the next section.
[edit] UPDATE
A recent question in the MySQL developer forums prompted me to think what would happen if we declared a number of handlers to deal with errors at the SQLSTATE level and also at the error message level. Take for example if we had a procedure which could result in a number of error´s being raised, we might want to deal with one in a particular way but just deal with the others under a general SQLSTATE handler. I create a test procedure to see what happens.
drop procedure if exists handlerproc
//
create procedure handlerproc(OUT p_end VARCHAR(10))
begin
declare continue handler for 1062 SET @b = ´- With Error 1062´;
declare continue handler for sqlstate ´23000´ SET @b = ´With SQLSTATE´;
insert into emps VALUES (1,´Dave´,1,10);
set p_end := concat(´The End´,@b);
end;
//
call handlerproc(@a) //
Query OK, 0 rows affected (0.00 sec)
select @a //
+--------------------------+
| @a |
+--------------------------+
| The End- With Error 1062 |
+--------------------------+
1 row in set (0.00 sec)
Here we can see that we have declared two handlers to deal with a specific error 1062 and SQLSTATE 23000. You may be aware that error 1062 is contained within SQLSTATE 2300, however we can see from the resulting SQL that in this case the handler to fire was the one declared to deal with the error code. I swapped the position of the handlers to test if it was due to the position of the handlers but it seems that this irrelevant.
[edit] Conditions
We saw in the last section how we can handle various conditions that may appear in our processing. We looked at how these conditions are grouped and how to handle those groups.
In addition to the standard groups MySQL allows us to define our own named conditions. However these conditions may only be linked to SQLSTATE values or mysql_error_codes. This makes conditions at the present time rather limited and in fact rather pointless. The latest MySQL documentation has very little to say on the matter and doesn’t give any indication if conditions will be expanded in the future.
The syntax for creating a condition is as follows..
DECLARE condition_name CONDITION FOR condition_value
As mentioned before the documentation on conditions is currently very limited so we must make a few assumptions about what we can and can’t do. condition_name for example should conform to the standard object naming rules within MySQL, it’s best to stick to alphanumeric characters and keep it short but reasonably descriptive. conditon_value on the other hand is strictly controlled and should be either SQLSTATE followed by the appropriate SQLSTATE code or a mysql error code
Lets have a look at how we create a condition and how we use it within a handler.
drop procedure if exists conditionproc // create procedure conditionproc(OUT p_end VARCHAR(10)) begin declare not_null condition for SQLSTATE ’23000’; declare continue handler for not_null SET @b = ’- With not_null Error’; insert into emps VALUES (NULL,’Dave’,1,10) ; set p_end := concat(’The End ’,@b); end; //
Download conditionproc1.myp
Here we have added a line to declare our condition, in our case we are going to handle any conditions which result from SQLSTATE 23000. We have given it a name, not_null which allows us to identify it in the handler. Lets try and run it and see what we get.
call conditionproc(@a) // Query OK, 0 rows affected (0.00 sec) select @a // +-------------------------------+ | @a | +-------------------------------+ | The End - With not_null Error | +-------------------------------+ 1 row in set (0.00 sec)
We could alternatively use an error code like so.
drop procedure if exists conditionproc // create procedure conditionproc(OUT p_end VARCHAR(10)) begin declare not_null condition for 1048; declare continue handler for not_null SET @b = ’- With not_null Error’; insert into emps VALUES (NULL,’Dave’,1,10) ; set p_end := concat(’The End ’,@b); end; // call conditionproc(@a) // Query OK, 0 rows affected (0.00 sec) select @a // +-------------------------------+ | @a | +-------------------------------+ | The End - With not_null Error | +-------------------------------+ 1 row in set (0.00 sec)
Download conditionproc2.myp
[edit] SIGNAL
Conditions have been included in MySQL as they form part of the ANSI SQL standard. In addition to there use as documentation features it’s hoped that in the future they will be able to be used with the ANSI standard SIGNAL and RESIGNAL functions in future additions. SIGNAL and RESIGNAL can be used to raise error messages during stored procedure processing. Take for example the following scenario, we have a stored procedure which accepts a integer parameter and then returns that integer multiplied by 7. However we don’t want to do anything if the integer passed is a zero, in fact we want to report back an error message telling the user that 0 is not a valid input parameter. Because this error is specific to our procedure there isn’t a standard error message associated with it so we need to define a condition to do this, but because the condition is not linked to an explicit error message it will never be called. Therefore we can use SIGNAL to raise the error manually.
It must be noted that this is all hypothetical of course because as yet the SIGNAL command is not supported. But here is a sample procedure to show what the SIGNAL call could look like.
create procedure multiseven(IN p_val INT)
begin
declare zero_multiply condition for 1001;
declare exit handler for zero_multiply set @error = ’Cannot Multiply by 0’;
if p_val = 0 then
signal zero_multiply
set message_text = ’Cannot Multiply by 0’;
end if;
select 7 * p_val;
end
//
As we have said this is hypothetical as MySQL doesn’t yet support the SIGNAL statement, but the short program simply accepts a parameter, declares the condition and associated handler and then checks the parameter that was passed. If the value of the parameter is 0 it then uses the signal statement to return an error to the calling program. In this example I’ve used set message_text which is the method used in DB2 when using the SIGNAL statement, if MySQL introduce the statement this may differ some what.
As you can see declaring and using conditions is fairly easy, however currently there isn’t really a great deal to be gained by doing so. They may become more useful with the introduction of the SIGNAL statement but until then the only reason I can see you might want to use them is so that the code is a little easier to read for somebody who’s not familiar with it. For example they might not know that error code 1048 is ’Column xxxx cannot be null’ so using a condition we can make it a little clearer what the handler is actually dealing with. Having said that you could just as easily add a comment before the handler.
We have taken a fairly substantial detour over the last 2 sections to look at something fairly complex, but it is important to know about Handlers at a basic level when looking at one of our future sections. Lets move on to how we can use stored procedures to access the database tables and return information to us.