Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from MySQL Stored Procedure Programming

Conclusion

In this chapter we examined the MySQL error handlers that allow you to catch error conditions and take appropriate corrective actions. Without error handlers, your stored programs will abort whenever they encounter SQL errors, returning control to the calling program. While this might be acceptable for some simple stored programs, it is more likely that you will want to trap and handle errors within the stored program environment, especially if you plan to call one stored program from another. In addition, you need to declare handlers for cursor loops so that an error is not thrown when the last row is retrieved from the cursor.

Handlers can be constructed to catch all errors, although this is currently not best practice in MySQL, since you do not have access to an error code variable that would allow you to differentiate between possible error conditions or to report an appropriate diagnostic to the calling program. Instead, you should declare individual handlers for error conditions that can reasonably be anticipated. When an unexpected error occurs, it is best to let the stored program abort so that the calling program has access to the error codes and messages.

Handlers can be constructed that catch either ANSI-standard SQLSTATE codes or MySQL-specific error codes. Using the SQLSTATE codes leads to more portable code, but because specific SQLSTATE codes are not available for all MySQL error conditions, you should feel free to construct handlers against MySQL-specific ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page