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.

12.4.1. Preparing a Stored Program Call for Execution

We'll normally want to call a stored program more than once in our application. Typically, we first create a statement handle for the stored program. We then iteratively execute the program, perhaps providing different values for the program's parameters with each execution.

It's usually possible to bypass the preparation stage and execute a stored program directlyat least if the stored program returns no result sets. However, if the stored program takes parameters and you execute the stored program more than once in your program, we recommend that you go to the extra effort of preparing the statement that includes your stored program call.

12.4.2. Registering Parameters

We can pass parameters into stored programs that require them as literals (e.g., concatenate the text of the parameter values into the stored program CALL statement).

Figure 12-3. General processing flow when calling a stored program from an external language

However, in all of the languages we discuss in subsequent chapters, there are specific parameter-handling methods that allow us to re-execute a stored program with new parameters without having to re-prepare the stored program call. As we said previously, it's best to use these explicit methods if you are going to execute the stored program more than onceboth because it is slightly more efficient and because, in some cases, only the prepared statement methods offer full support for bidirectional parameters and multiple result sets.

The methods for passing parameters to stored programs are usually the same as the methods used to pass parameters (or "bind variables") to normal SQL statements.

12.4.3. Setting Output Parameters

Some languages allow us to specifically define and process output parameters . In other languages, we can only access the values of OUT or INOUT parameters by employing "user variables" (variables prefixed with @) to set and retrieve the parameter values.

Both techniquesthe direct API calls provided by .NET and JDBC and the session variable solution required by other languagesare documented in the relevant language-specific chapters that follow.

12.4.4. Executing the Stored Program

Once the input parameters are set andin the case of .NET and Javaonce the output parameters are registered, we can execute the stored program. The method for executing a stored program is usually the same as the method for executing a standard SQL statement.

If the stored program returns no result sets , output parameters can immediately be accessed. If the stored program returns one or more result sets, all of those result sets must be processed before the output parameter values can be retrieved.

12.4.5. Retrieving Result Sets

The process of retrieving a single result set from a stored program is identical to the process of retrieving a result set from other SQL statementssuch as SELECT or SHOWthat return result sets.

However, unlike SELECT and SHOW statements, a stored program may return multiple result sets, and this requires a different flow of control in our application. To correctly process all of the result sets that may be returned from a stored program, the programming language API must include a method to switch to the "next" result set and possibly a separate method for determining if there are any more result sets to return.

JDBC and ADO.NET languages have included these methods since their earliest incarnations (for use with SQL Server and other RDBMSs that support multiple result sets), and these interfaces have been fully implemented for use with MySQL stored programs . Methods exist to retrieve multiple result sets in PHP, Perl, and Python, but these methods are relatively immaturein some cases, they were implemented only in response to the need to support stored programs in MySQL 5.0.

12.4.6. Retrieving Output Parameters

Once all result sets have been retrieved, we are able to retrieve any stored program output parameters. Not all languages provide methods for directly retrieving the values of output parameterssee the "Setting Output Parameters" section earlier for a description of a language-independent method of retrieving output parameters indirectly through user variables.

JDBC and ADO.NET provide specific calls that allow you to directly retrieve the value of an output parameter.

12.4.7. Closing or Re-Executing the Stored Program

Now that we have retrieved the output parameters, the current stored program execution is complete. If we are sure that we are not going to re-execute the stored program, we should close it using language-specific methods to release all resources associated with the stored program execution. This usually means closing the prepared statement object associated with the stored program call. If we want to re-execute the stored program, we can modify the input parameters and use the language-specific execute method to run the stored program as many times as needed. Then you should close the prepared statement and release resources.

12.4.8. Calling Stored Functions

In some languagesJDBC and .NET, in particularstored functions can be invoked directly, and you have language-specific techniques for obtaining the stored function return value. However, in other languages, you would normally need to embed the stored function in a statement that supports an appropriate expression such as a single-line SELECT statement.