Documentation Home
MySQL Connector/C++ 1.1 Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 277.0Kb
PDF (A4) - 278.2Kb
HTML Download (TGZ) - 53.5Kb
HTML Download (Zip) - 66.8Kb


MySQL Connector/C++ 1.1 Developer Guide  /  ...  /  Using a PreparedStatement for a Stored Procedure That Returns an Output Parameter

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:

    shell> 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:

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