Handlers and error handling
With stored procedures allowing the DBMS to grapple with concepts that beforehand were only dealt with in the murkier programming world, there is a clear need for a more elegant way of handling errors and exceptions. Enter the handler. There are two types of handler supported by MySQL - EXIT handlers that immediately exit the current BEGIN/END block, and CONTINUE handlers that allow processing to continue after the handler actions have been performed (the UNDO handler that may be familiar to users of other DBMS' is not yet supported). Below is an example. Remember that we are still using the | character as a delimiter, as outlined in part 1 of the series.
mysql>
CREATE procedure sp3()
BEGIN
DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
DECLARE EXIT HANDLER FOR 'unknown column'
SELECT 'error error whoop whoop';
SELECT aha;
SELECT 'continuing';
END;|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp3()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
So, what happened here? We declared a condition, called 'unknown column'. It is a condition that occurs when SQLSTATE 42S22 is reached, which is when there is an unknown column. You can find a full list of error codes and messages on the MySQL site. Next, we declare an exit handler for the 'unknown column' condition, declared above. The handler simply displays the message error error whoop whoop. The actual body of the procedure consists of two statements, SELECT aha, which is designed to trigger SQLSTATE 42S22, and SELECT 'continuing', which is never actually executed as, being an exit handler, the procedure is immediately exited when the condition is met. So, when we call sp3(), the SELECT statement triggers the condition, and the message is displayed. Let's change this to use a CONTINUE handler, and see the difference.
mysql>
CREATE procedure sp4()
BEGIN
DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR 'unknown column'
SELECT 'error error whoop whoop';
SELECT aha;
SELECT 'continuing';
END;
mysql> CALL sp4()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.06 sec)
As expected, the procedure continues executing after the error, and this time the SELECT 'continuing' statement is run.
Here is another procedure. What do you think it will do? If we want to display the error error and still handling messages as part of the handler, after reaching the aha statement, and then continue with the continuing statement, will this achieve that?
CREATE procedure sp5()
BEGIN
DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR 'unknown column'
SELECT 'error error whoop whoop';
SELECT 'still handling the error';
SELECT aha;
SELECT 'continuing';
END;
mysql> CALL sp5()\G
*************************** 1. row ***************************
still handling the error: still handling the error
1 row in set (0.00 sec)
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
The answer is clearly no. I hope that you were eagle-eyed enough to spot the misleading indentation. The SELECT 'still handling the error'; is actually part of the main procedure body, and not part of the error handler. Since we have no BEGIN or END statements as part of the handler, it consists of the one statement only. Here is what will achieve what we actually intended.
mysql>
CREATE procedure sp6()
BEGIN
DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR 'unknown column'
BEGIN
SELECT 'error error whoop whoop';
SELECT 'still handling the error';
END;
SELECT aha;
SELECT 'continuing';
END;|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp6()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
still handling the error: still handling the error
1 row in set (0.00 sec)
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.00 sec)
Just to round off our examples, here is an example of a procedure where the error handler is not called, and none of the handler statements are executed.
mysql>
CREATE procedure sp7()
BEGIN
DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR 'unknown column'
BEGIN
SELECT 'error error whoop whoop';
SELECT 'still handling the error';
END;
SELECT 'continuing';
END;|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp7()\G
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Variations and uses
For the examples above, we declared a condition to detect an SQLSTATE error. There are other ways. Firstly, handlers can be declared for specific error codes directly - you don't always need to go via an intermediate condition, although doing so is more useful in that the condition name can (and should) be descriptive, so there is no need to refer to a list of error codes at a later stage. The error code can also either be the SQLSTATE error number, as above, or the MySQL error code, as well as one of the more generic SQLWARNING, for all errors with an SQLSTATE beginning with 01, NOT FOUND for all errors with an SQLSTATE beginning with 02, or SQLEXCEPTION for all others. Below is a procedure that acts in a similar manner to our earlier examples. This time we use the MySQL Error code 1054, which is almost equivalent to SQLSTATE 42S22, and we also skip the condition:
mysql>
CREATE procedure sp8()
BEGIN
DECLARE EXIT HANDLER FOR 1054
BEGIN
SELECT 'error error whoop whoop';
SELECT 'still handling the error';
END;
SELECT aha;
END;|
mysql> CALL sp8()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
still handling the error: still handling the error
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
That's enough examples for now - hopefully you are starting to consider some practical uses for this. The handlers could ROLLBACK statements, or log to an error table. Moreover, the statements could be as complex as required, incorporating all the loops and conditions we looked at in the previous article.
Cursors
Cursors are another of the frequently demanded features now supported by MySQL 5. For those familiar with other DBMS implementations, MySQL 5 does not yet support them completely. In fact, it has some quite severe restrictions. MySQL 5's cursors are asensitive (so you should not update the table while using a cursor, otherwise you will get unpredictable results), read-only (you cannot update using the cursor position) and non-scrolling (you can only move forward to the next record, not back and forth).
In general, cursors are used to access a resultset that can be retrieved one or more rows at a time. They are also used for positioning a pointer at a specific row, and can allow updates to rows based upon this current position; although as mentioned, MySQL does not support this. The term cursor is short for CUrrent Set Of Records. In our examples, we will use the same table, sp1, we used in part 1 of this series. When we left off, the table contained the following records:
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
id: 3
txt: 414243
*************************** 2. row ***************************
id: 40
txt: 444546
2 rows in set (0.00 sec)
Here is a sample procedure containing a cursor.
mysql>
CREATE PROCEDURE sp9 (OUT rx INT, OUT ry INT)
BEGIN
DECLARE x,y INT;
DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;
OPEN sp1_cursor;
FETCH sp1_cursor INTO x,y;
CLOSE sp1_cursor;
SET rx = x;
SET ry = y;
END |
mysql> CALL sp9(@x,@y)\G
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x,@y\G
*************************** 1. row ***************************
@x: 3
@y: 414243
1 row in set (0.01 sec)
Here we declared variables (x and y and a cursor (sp1_cursor). The order of declarations is important - they must be in a particular order - variables and conditions, then cursors, then handlers.
Next is a new statement - OPEN sp1_cursor, which activates the cursor we declared earlier. The FETCH statement is the one that does all the magic, returning the next row of the actual resultset. Results must be placed somewhere, and the two variables x and y are the recipients of the two columns returned by the SELECT id,txt FROM sp1 query that makes up the cursor. The cursor is then closed (although MySQL will free the resources when it reaches the end of the compound statement block for you if you do not explicitly do so), and the results assigned to the two OUT variables. By calling the procedure, and then querying the two session variables that receive the results, we can see that they have been populated with the first row from the sp1 table, as expected.
However, returning just one row is not particularly useful. We need to loop through the entire resultset and return the results. We can achieve this using a simple REPEAT UNTIL loop, which we were introduced to in part 2.
mysql>
CREATE PROCEDURE sp10 (OUT rx INT, OUT ry INT)
BEGIN
DECLARE x,y,z INT;
DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;
OPEN sp1_cursor;
REPEAT
FETCH sp1_cursor INTO x,y;
UNTIL (z=1)
END REPEAT;
CLOSE sp1_cursor;
SET rx = x;
SET ry = y;
END |
mysql> CALL sp10(@x,@y)|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x,@y\G
*************************** 1. row ***************************
@x: 40
@y: 444546
1 row in set (0.00 sec)
The handler is declared for the NOT FOUND condition. The handler sets the variable z to 1, and z=1 is the condition the REPEAT UNTIL loop tests for. As mentioned earlier, the NOT FOUND condition includes all errors with an SQLSTATE beginning with 02, one of which is the No data to FETCH error. This will apply when we have retrieved all the rows in the table. As expected, upon calling the procedure and querying the session variables, this time we see the last row of the sp1table, as in the procedure each row's data is assigned to rx and ry, overwriting the previous contents.
No comments:
Post a Comment