Error Handling - Handler Conditions
(Page 4 of 4 )
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 | SQLSTATE code | Error message |
|---|
| 1011 | HY000 | Error on delete of ‘%s’ (errno: %d) |
| 1021 | HY000 | Disk full (%s); waiting for someone to free some space . . . |
| 1022 | 23000 | Can’t write; duplicate key in table ‘%s’ |
| 1027 | HY000 | ‘%s’ is locked against change |
| 1036 | HY000 | Table ‘%s’ is read only |
| 1048 | 23000 | Column ‘%s’ cannot be null |
| 1062 | 23000 | Duplicate entry ‘%s’ for key %d |
| 1099 | HY000 | Table ‘%s’ was locked with a READ lock and can’t be updated |
| 1100 | HY000 | Table ‘%s’ was not locked with LOCK TABLES |
| 1104 | 42000 | 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 |
| 1106 | 42000 | Incorrect parameters to procedure ‘%s’ |
| 1114 | HY000 | The table ‘%s’ is full |
| 1150 | HY000 | Delayed insert thread couldn’t get requested lock for table %s |
| 1165 | HY000 | INSERT DELAYEDcan’t be used with table ‘%s’ because it is locked withLOCK TABLES |
| 1242 | 21000 | Subquery returns more than 1 row |
| 1263 | 22004 | Column set to default value; NULL supplied to NOT NULL column ‘%s’ at row %ld |
| 1264 | 22003 | Out of range value adjusted for column ‘%s’ at row %ld |
| 1265 | 1000 | Data truncated for column ‘%s’ at row %ld |
| 1312 | 0A000 | SELECTin a stored program must haveINTO |
| 1317 | 70100 | Query execution was interrupted |
| 1319 | 42000 | Undefined CONDITION: %s |
| 1325 | 24000 | 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 |
1329 | 2000 | No data to FETCH |
1336 1337 | 42000 42000 | USEis not allowed in a stored program Variable or condition declaration after cursor or handler declaration |
1338 | 42000 | Cursor declaration after handler declaration |
1339 | 20000 | Case not found for CASE statement |
1348 1357 | HY000 HY000 | Column ‘%s’ is not updatable Can’t drop a %s from within another stored routine |
1358 1362 | HY000 HY000 | GOTOis not allowed in a stored program handler |
1363 | HY000 | 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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Check it out today at your favorite bookstore. Buy this book now.
|
|
