Introduction

Tuesday 23rd of May 2017 10:29:53 PM

In part 3 of the ongoing series about MySQL's stored procedures, we look at handlers and cursors in particular - both logical constructs that allow added functionality. Handlers allow you to run statements if a certain condition is met, while cursors, although only nominally supported in MySQL 5, allow looping through a resultset, processing it row by row.


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. All of these things are left up to the user agent to decide.

All of the borders shown in Figure 7-30 are based ona color of gray, which makes all of the effectseasier to see. The look of a border style is always based in some wayon the color of the border, although the exact method may varybetween user agents. For example, Figure 7-30illustrates two different ways of rendering aninset border. 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.

no

Applies to

all elements

floatIE4 P/B IE5 P/Q NN4 P/P Op3 B/-

Sets thefloat direction for an element. This is generally applied to imagesin order to allow text to flow around them, but under CSS1 anyelement may be floated. Note that, for elements such as paragraph,floating the element will cause its width to tend toward zero unlessan explicit width is assigned; thus, width assignment is a crucial

The left margin is applied to the beginning of the element, and the right margin to the end of it. Margins are not applied to the right and left side of each line. Also, you can see that if not for the margins, the line may have broken after "text" instead of after "boldfaced." This is the only real way in which margins affect line-breaking.

To understand why, let's go back to the paper-and-plastic analogy employed in the previous section. Think of an inline element as a strip of paper with marginal plastic surrounding it. Displaying

Values

[ <length> | <percentage> ]{1,4}

WARNING

Percentage values refer to the width of the parent element.

Create your own Java object model that imports information from the XML document by using either SAX or DOM. This kind of object model only uses SAX or DOM to initialize itself with the information contained in the XML document(s). Once the parsing and initialization of your object model is completed, DOM or SAX isn't used anymore. You can use your own object model to accessed or modify your information without using SAX or DOM anymore. So you manipulate your information using your own objects, and rely on the SAX or DOM APIs to import the information from your ApplicationML file into memory (as a bunch of Java objects). You can think of this object model as an in-memory instance of the information that came was "serialized" in your XML document(s). Changes made to this object model are made persistent automatically, you have to deal with persistence issues (ie, write code to save your object model to a persistence layer as XML).
  • Create your own Java object model (adapter) that uses DOM to manipulate the information in your document object tree (that is created by the parser). This is slightly different from the 2nd option, because you are still using the DOM API to manipulate the document information as a tree of nodes, but you are just wrapping an application specific API around the DOM objects, so its easier for you to write the code. So your object model is an adapter on top of DOM (ie, it uses the adapter pattern). This application specific API uses DOM and actually accesses or modifies information by going to the tree of nodes. Changes made to the object model still have to be made persistence (if you want to save any changes). You are in essence creating a thin layer on top of the tree of nodes that the parser creates, where the tree of nodes is accessed or modified eventually depending on what methods you invoke on your object model.
  • Depending on which of the three options you use to access information using your Java classes, this information must at some point be saved back to a file (probably to the one from which it was read). When the user of your application invokes a File->Save action, the information in the application must be written out to an ApplicationML file. Now this information is stored in memory, either as a (DOM) tree of nodes, or in your own proprietary object model. Also note that most DOM XML parsers can generate XML code from DOM document objects (but its quite trivial to turn a tree of nodes into XML by writing the code to do it yourself). There are 2 basic ways to get this information back into an ApplicationML file: