Introduction

Monday 26th of June 2017 12:16:16 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. 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>
element, it would come out like Figure 5-14:

<P>
100 <SPAN> 400 <STRONG> 700 <B> 800 <STRONG> 900
</STRONG></B></STRONG></SPAN>.
</P>
Figure 5-14

Figure 5-14. Weight numbers, again

If there were yet another B element inserted into the innermost STRONG element, its weight would also be 900, since font-weight 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.

App server developers are not restricted to using HTTP, they can transmit and recieve XML information using simple remote CORBA objects and RMI objects. The key is that by using XML, it makes these remote services or objects easier to build. And, by sticking with XML, any one of these technologies can be used in your design of your app server. You can use whatever technology is most appropriate to getting the job done, knowing that all the information flows as XML and can be processed by any part of the system. The reason Java object serialization did not achieve this is because it encodes object data to a binary format that is dependent on too many things (like the JVM version, and the existence of classes when things are deserialized, etc). XML is not limited by any of these restrictions (or problems), which makes it much easier to create systems that allow XML information to flow between different subsystems. Also by relying only on the data, large portions of the system can be replaced with better or different implementations for future-readiness.

App servers traditionally give their client apps access to information in remote databases, remote file systems, remote object repositories, remote web resources, and even other app servers. All these information sources don't even need to reside on the machine that hosts the app server. These remote resources may be on other machines on the Intranet or the Internet. Using Java and XML, RMI, JDBC, CORBA, JNDI, Servlet and Swing, you can create app servers that can integrate all kinds of remote and local information resources, and client apps that allow you to remotely or locally access this information from the app server.

In the future, with publicly available DTDs that are standardized for each vertical industry, XML based app servers will become very popular. Also when XML schema repositories become available and widely used, app servers will be able to take on a new role and provide application services that are not offered now. Companies will need to share information with other companies in related fields, and each company might have a different software system in which all their data is housed. By agreeing upon a set of DTDs or schemas (encoded in XML), these companies can exchange information with each other regardless of what systems they are using to store this information. If their app servers can exchange XML documents (based on some shared DTD or schema), then these disparate app servers can understand each other and share information. One of the uses for XML foreseen by the W3C is just this, vertical industries (like insurance and health care) creating sets of DTDs and schemas that all companies in the industry agree upon. Then these companies' app servers can talk to each other using some popular protocol (like HTTP or CORBA/IIOP) to exchange information between each other. This has the potential to save a lot of time and money in the daily business operations of these companies.

Web-based Applications