Stored Procedures can be called using both Statements and Prepared Statements. This tutorial looks at calling Stored Procedures using Statements. The following tutorial Section 21.5.6.2, “Tutorial: Calling Stored Procedures with Prepared Statements in MySQL Connector/C++” will cover the use of Prepared Statements.

When considering calling Stored Procedures there are several scenarios that can occur:

Stored Procedures are given below that illustrate each of the above scenarios.

The following routine allows you to add a country into the World database, but does not return a result. This corresonds to Scenario 1 above.

CREATE PROCEDURE add_country (IN country_code CHAR(3), IN country_name CHAR(52), IN continent_name CHAR(30))
BEGIN
   INSERT INTO Country(Code, Name, Continent) VALUES (country_code, country_name, continent_name);
END

The next routine returns the population of a specified country, and corresponds to Scenario 2 above:

CREATE PROCEDURE get_pop (IN country_name CHAR(52), OUT country_pop INT(11))
BEGIN
   SELECT Population INTO country_pop FROM Country WHERE Name = country_name;
END

The next routine is an example of a procedure returning a result set containing multiple records. This routine corresponds to Scenario 3 above.

CREATE PROCEDURE get_data ()
BEGIN
        SELECT Code, Name, Population, Continent FROM Country WHERE Continent = "Oceania" AND Population < 10000;
        SELECT Code, Name, Population, Continent FROM Country WHERE Continent = "Europe" AND Population < 10000;
        SELECT Code, Name, Population, Continent FROM Country WHERE Continent = "North America" AND Population < 10000;
END

Enter and test the Stored Procedures to ensure no errors have been introduced. You are now ready to start writing routines to test out the use of Stored Procedures using Connector/C++.

Scenario 1 - Stored Procedure does not return a result set

In the first case you will examine Scenario 1, you call a Stored procedure that does not return a result set.

The code in this case simply creates a statement and then invokes the execute method on it, passing the call to the Stored Procedure as a parameter. The Stored Procedure itself does not return a value, although it is important to note there will always be a return value from the call - this is simply the call status. MySQL Connector/C++ handles this status for you, so you do not need code to handle it explicitly. If the call should fail for some reason an exception will be raised, and this will be handled by the catch statement in the code.

Scenario 2 - Stored Procedure returns an output parameter

You will now see how to handle a Stored Procedure that returns an output parameter.

In this scenario the Stored Procedure sets an output parameter. This is not returned as such, but needs to be obtained via a query. If running the SQL statements directly this might be similar to the following:

CALL get_world_pop(@pop);
SELECT @pop;

In the C++ code a similar sequence is carried out. First, the CALL is executed as seen earlier. To obtain the output parameter an additional query must be executed. This query results in a ResultSet that can then be processed in a while loop. The simplest way to retrieve the data in this case 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.

Scenario 3 - Stored Procedure returns a Result Set

You will now see how to handle a Stored Procedure that returns a result set.

The code is similar to the examples you have previously seen. The code of particular interest in this case is:

do {
   res.reset(stmt->getResultSet());
   while (res->next()) {
      cout << "Name: " << res->getString("Name") 
           << " Population: " << res->getInt("Population")
           << endl;
   }
} while (stmt->getMoreResults());

The CALL is executed as before, with the results being returned into multiple ResultSets. This is because the Stored Procedure in this case uses 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. All of the Result Sets have more than one row.

Studying the code it should be noted that the results are processed using the pattern:

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