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!

IBM – Taking Web 2.0 to Work You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David 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! Accelerating Software Innovation on i on Power Systems Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time. FREE! Go There Now!

NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo. 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! IBM Rational AppScan Standard Edition V7.7 Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code. FREE! Go There Now!

NEW! Innovate don't duplicate! Asset reuse strategies for success Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository. FREE! Go There Now!

NEW! Krugle, developerWorks, and code search Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users. FREE! Go There Now!

NEW! Using Rational Business Developer to enhance your developer productivity Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components. FREE! Go There Now!

NEW! Webcast: Eclipse: Empowering the universal platform The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now. FREE! Go There Now!

All FREE IBM® developerWorks Tools!