7.3.3 Using a PreparedStatement 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 ps_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::PreparedStatement >  pstmt;
   std::auto_ptr< sql::ResultSet > res;
   
   pstmt.reset(con->prepareStatement("CALL get_data()"));
   res.reset(pstmt->executeQuery());
   
   for(;;)
   {
     while (res->next()) {
       cout << "Name: " << res->getString("Name")
            << " Population: " << res->getInt("Population")
            << endl;
     }
     if (pstmt->getMoreResults())
     {
       res.reset(pstmt->getResultSet());
       continue;
     }
     break;
   }

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

4. Run the program:

   $> ./ps_scenario3

5. Make a note of the output generated.

The code executes the stored procedure using a PreparedStatement object. The standard do/while construct is used to ensure that all result sets are fetched. The returned values are fetched from the result sets using the getInt and getString methods.