7.3.2 Using a PreparedStatement 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 ps_scenario2.cpp

2. Add the following code to the try block of the tutorial framework:

   vector<string> cont_vector;
   cont_vector.push_back("Europe");
   cont_vector.push_back("North America");
   cont_vector.push_back("Oceania");
   
   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());
   std::auto_ptr< sql::PreparedStatement >  pstmt;
   std::auto_ptr< sql::ResultSet > res;
   
   pstmt.reset(con->prepareStatement("CALL get_pop_continent(?,@pop)"));
   
   for (int i=0; i<3; i++)
   {
     pstmt->setString(1,cont_vector[i]);
     pstmt->execute();
     res.reset(pstmt->executeQuery("SELECT @pop AS _population"));
     while (res->next())
       cout << "Population of "
            << cont_vector[i]
            << " is "
            << res->getString("_population") << endl;
   }

   Also, uncomment #include <vector> near the top of the code, because vectors are used to store sample data.

3. Compile the program as described in Section 7.1, “Prerequisites and Background Information”.

4. Run the program:

   $> ./ps_scenario2
   Connector/C++ tutorial framework...
   
   Population of Europe is 730074600
   Population of North America is 482993000
   Population of Oceania is 30401150
   Done.

In this scenario a PreparedStatement object is created that calls the get_pop_continent stored procedure. This procedure takes an input parameter, and also returns an output parameter. The approach used is to create another statement that can be used to fetch the output parameter using a SELECT query. Note that when the PreparedStatement is created, the input parameter to the stored procedure is denoted by '?'. Prior to execution of the prepared statement, it is necessary to replace this placeholder by an actual value. This is done using the setString method:

pstmt->setString(1,cont_vector[i]);

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.