A stored procedure that returns no result. For example, such a
stored procedure can log non-critical information, or change
database data in a straightforward way.

A stored procedure that returns one or more values using
output parameters. For example, such a procedure can indicate
success or failure, or retrieve and return data items.

A stored procedure that returns one or more result sets. The
procedure can execute one or more queries, each of which
returns an arbitrary number of rows. Your application loops
through each result set to display, transform, or otherwise
process each row in it.

The following stored procedures illustrate each of these
scenarios.

The following procedure adds a country to the
world database, but does not return a result.
This corresponds to Scenario 1 described earlier.

Enter and test the stored procedures manually to ensure that they
will be available to your C++ applications. (Select
world as the default database before you create
them.) You are now ready to start writing applications using
Connector/C++ that call stored procedures.

Scenario 1: Using a Statement for a Stored Procedure
That Returns No Result

This example shows how to call a stored procedure that returns no
result set.

The code in this application simply invokes the
execute method, passing to it a statement that
calls the stored procedure. The procedure itself returns no value,
although it is important to note there is always a return value
from the CALL statement; this is
the execute status. MySQL Connector/C++ handles this status
for you, so you need not handle it explicitly. If the
execute call fails for some reason, it raises
an exception that the catch block handles.

Scenario 2: Using a Statement for a Stored Procedure
That Returns an Output Parameter

This example shows how to handle a stored procedure that returns
an output parameter.

shell> ./sp_scenario2
Connector/C++ tutorial framework...
Population of Uganda: 21778000
Population of Asia: 3705025700
Population of World: 6078749450
Done.

In this scenario, each stored procedure sets the value of an
output parameter. This is not returned directly to the
execute method, but needs to be obtained using
a subsequent query. If you were executing the SQL statements
directly, you might use statements similar to these:

Obtain the output parameter by executing an additional query.
The query produces a ResultSet object.

Retrieve the data using a while loop. The
simplest way to do this is to use a
getString method on the
ResultSet, passing the name of the variable
to access. In this example _reply is used
as a placeholder for the variable and therefore is used as the
key to access the correct element of the result dictionary.

Although the query used to obtain the output parameter returns
only a single row, it is important to use the
while loop to catch more than one row, to
avoid the possibility of the connection becoming unstable.

Scenario 3: Using a Statement for a Stored Procedure
That Returns a Result Set

This example shows how to handle result sets produced by a stored
procedure.

Note

This scenario requires MySQL 5.5.3 or higher. The client/server
protocol does not support fetching multiple result sets from
stored procedures prior to 5.5.3.

The CALL is executed as before, but
this time the results are returned into multiple
ResultSet objects because the stored procedure
executes multiple SELECT statements. In this
example, the output shows that three result sets are processed,
because there are three SELECT statements in
the stored procedure. Each result set returns more than one row.

The results are processed using this code pattern:

do {
Get Result Set
while (Get Result) {
Process Result
}
} while (Get More Result Sets);

Note

Use this pattern even if the stored procedure executes only a
single SELECT and produces only one result
set. This is a requirement of the underlying protocol.