Skip navigation links
MySQL 5.1 Reference Manual :: 21 Connectors and APIs :: 21.4 MySQL Connector/C++ :: 21.4.7 MySQL Connector/C++ Tutorials :: 21.4.7.3 Calling Stored Procedures with PreparedStatement Objects
21.4.8 MySQL Connector/C++ Debug Tracing »
Section Navigation      [Toggle]

21.4.7.3. Calling Stored Procedures with PreparedStatement Objects

This section shows how to call stored procedures using prepared statements. It is recommended that, before working through it, you first work through the previous tutorial Section 21.4.7.2, “Calling Stored Procedures with Statement Objects”. That section shows the stored procedures required by the applications in this section.

Scenario 1: Using a PreparedStatement for a Stored Procedure That Returns No Result

This example shows how to call a stored procedure that returns no result set.

  1. Make a copy of the tutorial framework code: 

    shell> cp framework.cpp ps_scenario1.cpp

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

    vector<string> code_vector;
code_vector.push_back("SLD");
code_vector.push_back("DSN");
code_vector.push_back("ATL");

vector<string> name_vector;
name_vector.push_back("Sealand");
name_vector.push_back("Disneyland");
name_vector.push_back("Atlantis");

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::PreparedStatement >  pstmt;

pstmt.reset(con->prepareStatement("CALL add_country(?,?,?)"));
for (int i=0; i<3; i++)
{
  pstmt->setString(1,code_vector[i]);
  pstmt->setString(2,name_vector[i]);
  pstmt->setString(3,cont_vector[i]);

  pstmt->execute();
}

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

  4. Run the program: 

    shell> ./ps_scenario1

  5. You can check whether the database has been updated correctly by using this query: 

    mysql> SELECT Code, Name, Continent FROM Country
    -> WHERE Code IN('DSN','ATL','SLD');
+------+------------+---------------+
| Code | Name       | Continent     |
+------+------------+---------------+
| ATL  | Atlantis   | Oceania       |
| DSN  | Disneyland | North America |
| SLD  | Sealand    | Europe        |
+------+------------+---------------+

The code is relatively simple, as no processing is required to handle result sets. The procedure call, CALL add_country(?,?,?), is made using placeholders for input parameters denoted by '?'. These placeholders are replaced by the appropriate data values using the PreparedStatement object's setString method. The for loop is set up to iterate 3 times, as there are three data sets in this example. The same PreparedStatement is executed three times, each time with different input parameters.

Scenario 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(stmt->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 21.4.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.

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

Note

This scenario requires MySQL 5.5.3 or higher. The client/server protocol does not support fetching multiple result sets from stored procedures prior to 5.5.3.

  1. Make a copy of the tutorial framework code: 

    shell> 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());

do {
  res.reset(pstmt->getResultSet());
  while (res->next()) {
    cout << "Name: " << res->getString("Name")
         << " Population: " << res->getInt("Population")
         << endl;
  }
} while (pstmt->getMoreResults());

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

  4. Run the program: 

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

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices