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.

More Database Book Reviews Articles
More By Codewalkers

 

developerWorks - FREE Tools!

NEW! Driving Business Success with Rational Process Library Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance. FREE! Go There Now!

NEW! IBM – Taking Web 2.0 to Work David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve. FREE! Go There Now!

NEW! Build Web services with transport-level security using Rational Application Developer V7, Part 1: Build Web services and Web services clients Build secure Web services with transport-level security using IBM Rational Application Developer V7 and IBM WebSphere Application Server V6.1. Follow this three-part series for step-by-step instructions about how to develop Web services and clients, configure HTTP basic authentication, and configure HTTP over SSL (HTTPS). This first part of the series walks you through building a Web service for a simple calculator application. You generate and test two different types of Web services clients: a Java Platform, Enterprise Edition (Java EE) client and a stand-alone Java client. You also handle user-defined exceptions in Web services. FREE! Go There Now!

NEW! Download a free trial of Lotus Quickr 8.0 Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications. FREE! Go There Now!

NEW! Download IBM WebSphere Portal V6.1 beta code Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization. FREE! Go There Now!

NEW! Evaluate Rational Host Access Transformation Services (HATS) Toolkit V7.1 Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications. FREE! Go There Now!

NEW! Rational Modeling Extension for Microsoft.Net Rational Modeling Extension for Microsoft .NET enhances usability for code generation supporting a more intelligent refactoring. The latest enhancements enable organizations with Java and .NET systems and software development maintain architectural integrity across heterogeneous platforms. FREE! Go There Now!

NEW! Test terminal-based applications with Rational Functional Tester Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily. FREE! Go There Now!

NEW! Trial download: IBM Rational Performance Tester V7.0.1 Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads. FREE! Go There Now!

NEW! Whitepaper: Achieving consistency between business process models and operational guides Explore how Rational and WebSphere software enable enterprise documentation in SOA environments. Specifically, a new integration between IBM WebSphere® Business Modeler and IBM Rational® Method Composer software can help technical writers more easily keep enterprise operations manuals in sync with changes that are made to business processes, resulting in more accurate and timely documentation that benefits the entire enterprise. FREE! Go There Now!

All FREE IBM® developerWorks Tools!