A stored procedure can be called using a Statement or PreparedStatement object. This section shows how to call stored procedures using Statement objects. To see how to use PreparedStatement objects, see Section 22.4.7.3, “Calling Stored Procedures with PreparedStatement Objects”.

You can construct and call different types of stored procedures:

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.

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 procedures use an output parameter to return the population of a specified country or continent, or the entire world. These correspond to Scenario 2 described earlier.

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

CREATE PROCEDURE get_pop_continent (IN continent_name CHAR(30),
                                    OUT continent_pop BIGINT)
BEGIN
  SELECT SUM(Population) INTO continent_pop FROM Country
    WHERE Continent = continent_name;
END;

CREATE PROCEDURE get_pop_world (OUT world_pop BIGINT)
BEGIN
  SELECT SUM(Population) INTO world_pop FROM Country;
END;

The next procedure returns several result sets. This corresponds to Scenario 3 described earlier.

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 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.

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:

CALL get_pop('Uganda', @pop);
SELECT @pop;
CALL get_pop_continent('Asia', @pop);
SELECT @pop;
CALL get_pop_world(@pop);
SELECT @pop;

In the C++ code, a similar sequence is carried out for each procedure call:

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.

The code is similar to the examples shown previously. The code of particular interest here 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, 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);