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:
As a MySQL error code.
As an ANSI-standardSQLSTATEcode.
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
MySQL error code
Error on delete of ‘%s’ (errno: %d)
Disk full (%s); waiting for someone to free some space . . .
Can’t write; duplicate key in table ‘%s’
‘%s’ is locked against change
Table ‘%s’ is read only
Column ‘%s’ cannot be null
Duplicate entry ‘%s’ for key %d
Table ‘%s’ was locked with a READ lock and can’t be updated
Table ‘%s’ was not locked with LOCK TABLES
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
Incorrect parameters to procedure ‘%s’
The table ‘%s’ is full
Delayed insert thread couldn’t get requested lock for table %s
INSERT DELAYEDcan’t be used with table ‘%s’ because it is locked withLOCK TABLES
Subquery returns more than 1 row
Column set to default value; NULL supplied to NOT NULL column ‘%s’ at row %ld
Out of range value adjusted for column ‘%s’ at row %ld
Data truncated for column ‘%s’ at row %ld
SELECTin a stored program must haveINTO
Query execution was interrupted
Undefined CONDITION: %s
Cursor is already open
Table 6-1. Some common MySQL error codes and SQLSTATE codes (continued)
MySQL error code 1326 1328
SQLSTATE code 24000 HY000
Error message Cursor is not open Incorrect number of FETCHvariables
No data to FETCH
USEis not allowed in a stored program Variable or condition declaration after cursor or handler declaration
Cursor declaration after handler declaration
Case not found for CASE statement
Column ‘%s’ is not updatable Can’t drop a %s from within another stored routine
GOTOis not allowed in a stored program handler
Updating of %s row is not allowed in %s trigger There is no %s row in %s trigger
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.