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

1. Make a copy of the tutorial framework code:

   $> 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 7.1, “Prerequisites and Background Information”.

4. Run the program:

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