There is no "one-size-fits-all" answer to the question "Should I use stored programs in my application?" There are those who believe that virtually all of an application's database interactions should be made through stored program calls, and those who believe that stored programs should be used only in very special circumstances. You will need to make your own determination as to the value of using MySQL stored programs in your application.

As we've discussed in this chapter, the use of stored programs can provide significant advantages:

  • Stored programs can substantially improve the security of your application.
  • Stored programs can be used to provide an abstract data access layer that can improve the separation between business logic and data access logic (of course, stored programs are not required to do thisthey are just one means to do so).
  • Stored programs can reduce network traffic.
  • Stored programs can be used to implement common routines accessible from multiple applications.
  • Stored programs allow for a convenient division of duties between those whose skills are database-centric and those whose skills are programming-centric.
  • The use of stored programs can (sometimes) improve application portability.

But you also need to consider the potential disadvantages of using stored programs:

  • Stored programs are often slowerespecially for computationally expensive operationsthan equivalent middle-tier code.
  • The use of stored programs can lead to fragmentation of your application logiclogic may be split between the database and application server tier, making it difficult to track down design flaws or implementation bugs.
  • The use of stored programs usually results in your application's leveraging more than one programming language, requiring additional skills in your development team.
  • Most object-relational mapping systems (e.g., J2EE CMP and Hibernate) do not know how to work with stored programs.
  • Although stored program calls may sometimes be more portable than native SQL, in practice this is not true for all implementations. In particular, Oracle stored program calls often look and act substantially different from calls made in MySQL, DB2, or Microsoft SQL Server.

In this chapter we briefly reviewed the general programming logic involved in calling stored programs from external programming languages. In subsequent chapters we will explain the detailed techniques for handling stored program calls in PHP, Java, Perl, Python, C#, and VB.NET.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208 © 2008-2020.
If you may any questions please contact us: