MySQL Stored Procedures Programming By: Guy Harrison with Steven Feuerstein Published by O'Reilly
Developers who have worked with SQL Server and Oracle know the power of stored procedures. Their absence in MySQL has been a brick wall. With the release of MySQL 5.0, that last hurdle has been crossed: custom functions, triggers and stored procedures are now at your fingertips. What you need is a roadmap. MySQL's strength has been its large set of handy built-in functions. How to use them is covered in the new book from O'Reilly: MySQL Stored Procedures Programming by Guy Harrison with Steven Feuerstein.
By : Mike DeWolfe
The book begins with the standard fare: the evolution of MySQL, how it differs from the other engines and how it's a good engine. Then it jumps into the introduction to stored procedures. Stored procedures can be a learning curve. There is a divide between application code and database code: applications execute instructions including loops, conditions, variables and iterations. Database calls work to gather sets—lists-- filtered through conditions and joins. Stored procedures blur that line: they gather sets and execute instructions. That pops the top off what you can do: what would take a lot of back-and-forth between an application and its database can now be performed solely by the database. Throughout the remainder of the first six chapters on fundamentals, this book takes you school. It discusses block, iteration and conditional statements; using SQL inside of stored procedures; and some all-important material on error handling.
The second section revolves around program construction: how to perform transactions; how to build functions and triggers. This is all good stuff, especially for those who are leapfrogging from MySQL 3+ to MySQL 5 wherein MySQL 4 InnoDB makes transactions possible.
Section Three gets into the practicalities of using stored procedures. There are examples of how to integrate with PHP, Java, Perl, Python and C#/.NET. This stuff is great. While a book that solely focused on one language would have had deeper coverage, this book is likely to cater to most of the developers out there. Unfortunately, there is a bias displayed in that Python gets a complete application example and the others languages do not. With that said, many of the code examples throughout the book are rendered in PHP, Java and C#, so there is an honest effort to be cosmopolitian in code inclusion.
Section Four, "Optimizing Stored Programs" is the money shot: program security; coding tuning; optimizing code; and best practices. I've known that IN statements are the sloppy cousins of JOINs. The authors show performance graphs to spell that out in argument stopping clarity. Given the quality of these last 160 pages, if you buy this book solely for that section, you will get your money's worth. How is it that so many books have come out and there has been so little coverage on the difference between what you can do and what you should do?
This book is so good that I would argue there needs to be space on your MySQL shelf even if you aren't going to write stored procedures. As I go into my next project with a MySQL 5.0 engine in use, I am happy that this book exists.
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.