MySQL Stored Procedure Programming

Calling Stored Programs from Application Code

Most languages used to build applications that interact with MySQL are able to fully exploit stored programs , although in some languages, support for advanced features such as multiple result sets is a recent addition. In the following chapters we will explain in detail how to use stored programs from within PHP, Java, Perl, Python, and the .NET languages VB.NET and C#. In this section we want to give you an introduction to the general process of calling a stored program from an external programming language.

In general, the techniques for using stored programs differ from those for standard SQL statements in two significant respects:

• While SQL statement calls may take parameters , stored programs can also have OUT or INOUT parameters. This means that you need to understand how to access the value of an OUT or INOUT parameter once the stored program execution completes.

• A SELECT statement can return only one result set, while a stored program can return any number of result sets, and you might not be able to anticipate the number or structure of these result sets.

So, calling a stored program requires a slightly different program flow from standard SQL processing. The overall sequence of events is shown in the UML “retro” diagram (e.g., flowchart) in Figure 12-3.

Here’s a brief description of each of these steps. Remember that in the next five chapters, we will be showing you how to follow these steps in various languages.

Preparing a Stored ...