8.2 Calling Stored Procedures with Statement Objects

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 8.3, “Calling Stored Procedures with PreparedStatement Objects”.

You can construct and call different types of stored procedures:

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

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

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

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.

  1. Make a copy of the tutorial framework code:

    shell> cp framework.cpp sp_scenario1.cpp
    
  2. Add the following code to the try block of the tutorial framework:

    
    sql::Driver* driver = get_driver_instance();
    std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass));
    con->setSchema(database);
    std::auto_ptr<sql::Statement> stmt(con->createStatement());
    
    // We need not check the return value explicitly. If it indicates
    // an error, Connector/C++ generates an exception.
    stmt->execute("CALL add_country('ATL', 'Atlantis', 'North America')");
    
    
  3. Compile the program as described in Section 8.1, “Prerequisites and Background Information”.

  4. Run the program:

    shell> ./sp_scenario1
    
  5. Using the mysql command-line client or other suitable program, check the world database to determine that it has been updated correctly. You can use this query:

    mysql> SELECT Code, Name, Continent FROM Country WHERE Code='ATL';
    +------+----------+---------------+
    | Code | Name     | Continent     |
    +------+----------+---------------+
    | ATL  | Atlantis | North America |
    +------+----------+---------------+
    

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.

  1. Make a copy of the tutorial framework code:

    shell> cp framework.cpp sp_scenario2.cpp
    
  2. Add the following code to the try block of the tutorial framework:

    
    sql::Driver* driver = get_driver_instance();
    std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass));
    con->setSchema(database);
    std::auto_ptr<sql::Statement> stmt(con->createStatement());
    
    stmt->execute("CALL get_pop('Uganda', @pop)");
    
    std::auto_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT @pop AS _reply"));
    while (res->next())
      cout << "Population of Uganda: " << res->getString("_reply") << endl;
    
    stmt->execute("CALL get_pop_continent('Asia', @pop)");
    
    res.reset(stmt->executeQuery("SELECT @pop AS _reply"));
    while (res->next())
      cout << "Population of Asia: " << res->getString("_reply") << endl;
    
    stmt->execute("CALL get_pop_world(@pop)");
    
    res.reset(stmt->executeQuery("SELECT @pop AS _reply"));
    while (res->next())
      cout << "Population of World: " << res->getString("_reply") << endl;
    
    
  3. Compile the program as described in Section 8.1, “Prerequisites and Background Information”.

  4. Run the program:

    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:

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.

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.

  1. Make a copy of the tutorial framework code:

    shell> cp framework.cpp sp_scenario3.cpp
    
  2. Add the following code to the try block of the tutorial framework:

    
    sql::Driver* driver = get_driver_instance();
    std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass));
    con->setSchema(database);
    std::auto_ptr<sql::Statement> stmt(con->createStatement());
    
    stmt->execute("CALL get_data()");
    std::auto_ptr< sql::ResultSet > res;
    do {
      res.reset(stmt->getResultSet());
      while (res->next()) {
        cout << "Name: " << res->getString("Name")
             << " Population: " << res->getInt("Population")
             << endl;
      }
    } while (stmt->getMoreResults());
    
    
  3. Compile the program as described in Section 8.1, “Prerequisites and Background Information”.

  4. Run the program:

    shell> ./sp_scenario3
    Connector/C++ tutorial framework...
    
    Name: Cocos (Keeling) Islands Population: 600
    Name: Christmas Island Population: 2500
    Name: Norfolk Island Population: 2000
    Name: Niue Population: 2000
    Name: Pitcairn Population: 50
    Name: Tokelau Population: 2000
    Name: United States Minor Outlying Islands Population: 0
    Name: Svalbard and Jan Mayen Population: 3200
    Name: Holy See (Vatican City State) Population: 1000
    Name: Anguilla Population: 8000
    Name: Atlantis Population: 0
    Name: Saint Pierre and Miquelon Population: 7000
    Done.
    

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);

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.