Handler Conditions - MySQL

The handler condition defines the circumstances under which the handler will be invoked. The circumstance is always associated with an error condition, but you have three choices as to how you define that error:

1. As a MySQL error code.
   
2. As an ANSI-standardSQLSTATEcode.
   
3. As a named condition. You may define your own named conditions (described in the later section “Named Conditions”) or use one of the built-in conditionsSQLEXCEPTION,SQLWARNING, andNOT FOUND.

MySQL has its own set of error codes that are unique to the MySQL server. A handler condition that refers to a numeric code without qualification is referring to a MySQL error code. For instance, the following handler will fire when MySQL error code 1062 (duplicate key value) is encountered:

  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;

SQLSTATEerror codes are defined by the ANSI standard and are database-independent, meaning that they will have the same value regardless of the underlying database. So, for instance, Oracle, SQL Server, DB2, and MySQL will always report the sameSQLSTATEvalue (23000) when a duplicate key value error is encountered. Every MySQL error code has an associatedSQLSTATEcode, but the relationship is not one-to-one; someSQLSTATE codes are associated with many MySQL codes; HY000 is a general-purposeSQLSTATEcode that is raised for MySQL codes that have no specific associatedSQLSTATEcode.

The following handler will fire whenSQLSTATE23000 (duplicate key value) is encountered:

  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;

SQLSTATE or MySQL Error Code?

In theory, using the SQLSTATE codes will make your code more portable to other database platforms and might therefore seem to be the best choice. However, there are a number of reasons to use MySQL error codes rather than SQLSTATE codes when writing MySQL stored programs:

• In reality, it is unlikely that you will move your stored programs to another RDBMS. The Oracle and SQL Server stored program languages are totally incompatible with MySQL. The DB2 stored program language is somewhat compatible (both are based on the SQL:2003 standard). It is very likely, however, that you will use MySQL-specific syntax as you write your application, which will prevent your stored code from being portable.
  
• Not all MySQL error codes haveSQLSTATEequivalents. Although every MySQL error code is associated with someSQLSTATE errorcode, often it will be a general-purposeSQLSTATE that is not specific (such as HY000). Therefore, you will almost certainly have to code some handlers that refer directly to MySQL error codes. You’ll probably find that the advantages of using a consistent handler format will outweigh the theoretical portability advantage ofSQLSTATE errorcodes.

We will, for the most part, use MySQL error codes in this book.

When the MySQL client encounters an error, it will report both the MySQL error code and the associatedSQLSTATEcode, as in the following output:

  mysql> CALL nosuch_sp();

  ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

In this case, the MySQL error code is 1305 and theSQLSTATEcode is 42000.

Table 6-1 lists some of the error codes you might expect to encounter in a MySQL stored program together with theirSQLSTATEequivalents. Note, again, that many MySQL error codes map to the sameSQLSTATEcode (many map to HY000, for instance), which is why you may wish to sacrifice portability and use MySQL error codes— rather thanSQLSTATEcodes—in your error handlers.

Table 6-1. Some common MySQL error codes and SQLSTATE codes (continued)

You can find a complete and up-to-date list of error codes in Appendix B of the MySQL reference manual, available online at http://dev.mysql.com/doc/.

Please check back next week for the continuation of this article.