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

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

4. Run the program:

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

1. Execute the CALL statement.

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

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