Using MySQL Stored Programs in Applications
In the next few chapters we are going to show you how to use stored programs in a variety of external programming environmentsPHP , Java , Perl , Python , and .NET . In those chapters we'll describe how to use the MySQL drivers provided with these languages to execute stored programs, retrieve the output of stored programs, and handle any error conditions that may arise during execution. Before we delve into those specific environments, we'll start with a general discussion of using MySQL stored programs in applications .
The purpose of this preliminary chapter is twofold:
- To present the overall benefits of using stored programs in your applications.
- To outline the general principles and program flow considerations that apply when using stored programs from any programming environment. Chapters 13 through 17 will describe the details for specific programming environments.
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
- Introduction to MySQL Stored Programs
- What Is a Stored Program?
- A Quick Tour
- Resources for Developers Using Stored Programs
- Some Words of Advice for Developers
- Conclusion
MySQL Stored Programming Tutorial
- MySQL Stored Programming Tutorial
- What You Will Need
- Our First Stored Procedure
- Variables
- Parameters
- Conditional Execution
- Loops
- Dealing with Errors
- Interacting with the Database
- Calling Stored Programs from Stored Programs
- Putting It All Together
- Stored Functions
- Triggers
- Calling a Stored Procedure from PHP
- Conclusion
Language Fundamentals
- Language Fundamentals
- Variables, Literals, Parameters, and Comments
- Operators
- Expressions
- Built-in Functions
- Data Types
- MySQL 5 Strict Mode
- Conclusion
Blocks, Conditional Statements, and Iterative Programming
- Blocks, Conditional Statements, and Iterative Programming
- Block Structure of Stored Programs
- Conditional Control
- Iterative Processing with Loops
- Conclusion
Using SQL in Stored Programming
- Using SQL in Stored Programming
- Using Non-SELECT SQL in Stored Programs
- Using SELECT Statements with an INTO Clause
- Creating and Using Cursors
- Using Unbounded SELECT Statements
- Performing Dynamic SQL with Prepared Statements
- Handling SQL Errors: A Preview
- Conclusion
Error Handling
- Error Handling
- Introduction to Error Handling
- Condition Handlers
- Named Conditions
- Missing SQL:2003 Features
- Putting It All Together
- Handling Stored Program Errors in the Calling Application
- Conclusion
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
- Creating and Maintaining Stored Programs
- Creating Stored Programs
- Editing an Existing Stored Program
- SQL Statements for Managing Stored Programs
- Getting Information About Stored Programs
- Conclusion
Transaction Management
- Transaction Management
- Transactional Support in MySQL
- Defining a Transaction
- Working with Savepoints
- Transactions and Locks
- Transaction Design Guidelines
- Conclusion
MySQL Built-in Functions
- MySQL Built-in Functions
- String Functions
- Numeric Functions
- Date and Time Functions
- Other Functions
- Conclusion
Stored Functions
- Stored Functions
- Creating Stored Functions
- SQL Statements in Stored Functions
- Calling Stored Functions
- Using Stored Functions in SQL
- Conclusion
Triggers
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
- Using MySQL Stored Programs in Applications
- The Pros and Cons of Stored Programs in Modern Applications
- Advantages of Stored Programs
- Disadvantages of Stored Programs
- Calling Stored Programs from Application Code
- Conclusion
Using MySQL Stored Programs with PHP
- Using MySQL Stored Programs with PHP
- Options for Using MySQL with PHP
- Using PHP with the mysqli Extension
- Using MySQL with PHP Data Objects
- Conclusion
Using MySQL Stored Programs with Java
- Using MySQL Stored Programs with Java
- Review of JDBC Basics
- Using Stored Programs in JDBC
- Stored Programs and J2EE Applications
- Using Stored Procedures with Hibernate
- Using Stored Procedures with Spring
- Conclusion
Using MySQL Stored Programs with Perl
- Using MySQL Stored Programs with Perl
- Review of Perl DBD::mysql Basics
- Executing Stored Programs with DBD::mysql
- Conclusion
Using MySQL Stored Programs with Python
- Using MySQL Stored Programs with Python
- Installing the MySQLdb Extension
- MySQLdb Basics
- Using Stored Programs with MySQLdb
- A Complete Example
- Conclusion
Using MySQL Stored Programs with .NET
- Using MySQL Stored Programs with .NET
- Review of ADO.NET Basics
- Using Stored Programs in ADO.NET
- Using Stored Programs in ASP.NET
- Conclusion
Part IV: Optimizing Stored Programs
Stored Program Security
- Stored Program Security
- Permissions Required for Stored Programs
- Execution Mode Options for Stored Programs
- Stored Programs and Code Injection
- Conclusion
Tuning Stored Programs and Their SQL
- Tuning Stored Programs and Their SQL
- Why SQL Tuning Is So Important
- How MySQL Processes SQL
- SQL Tuning Statements and Practices
- About the Upcoming Examples
- Conclusion
Basic SQL Tuning
Advanced SQL Tuning
- Advanced SQL Tuning
- Tuning Subqueries
- Tuning Anti-Joins Using Subqueries
- Tuning Subqueries in the FROM Clause
- Tuning ORDER and GROUP BY
- Tuning DML (INSERT, UPDATE, DELETE)
- Conclusion
Optimizing Stored Program Code
- Optimizing Stored Program Code
- Performance Characteristics of Stored Programs
- How Fast Is the Stored Program Language?
- Reducing Network Traffic with Stored Programs
- Stored Programs as an Alternative to Expensive SQL
- Optimizing Loops
- IF and CASE Statements
- Recursion
- Cursors
- Trigger Overhead
- Conclusion
Best Practices in MySQL Stored Program Development
EAN: 2147483647
Pages: 208
