Conclusion

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.