Skip navigation links
MySQL 5.1 with Maria Reference Manual :: 20 Connectors and APIs :: 20.6 MySQL Connector/C++ :: 20.6.6 MySQL Connector/C++ Tutorials :: 20.6.6.2 Tutorial: Calling Stored Procedures with Prepared Statements in MySQL Connector/C++
20.6.7 MySQL Connector/C++ Debug Tracing »
Section Navigation      [Toggle]

20.6.6.2. Tutorial: Calling Stored Procedures with Prepared Statements in MySQL Connector/C++

Before working through this tutorial it is recommended you first work through the previous tutorial Section 20.6.6.1, “Tutorial: Calling Stored Procedures with Statements in MySQL Connector/C++”.

Scenario 1 - Using a Prepared Statement to prepare a Stored Procedure that does not return a result set

  1. 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();            
}

    You will also need to add #include <vector> to the top of your code as vectors are used to store sample data.

  2. Compile the code using the following command: 

    g++ -o ps_scenario1 -I/usr/local/include/cppconn/ -lmysqlcppconn ps_scenario1.cpp

  3. Run the code using the command: 

    ./ps_scenario1

  4. You can test the dataase has been updated correctly by using a query such as: 

    SELECT Code, Name, Continent FROM Country WHERE Code = "DSN" OR Code="ATL" OR Code="SLD";

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 values using the Prepared Statement's setString method in this case. The for loop is set up to iterate 3 times, as there are three data sets in this example. The same Prepared Statement is executed three times, each time with different input parameters.

Scenario 2 - Using a Prepared Statement to prepare a Stored Procedure that uses an output parameter

In this scenario a different Stored Procedure is going to be used compared to the one use in the the tutorial Section 20.6.6.1, “Tutorial: Calling Stored Procedures with Statements in MySQL Connector/C++”. This is to illustrate passing an input parameter as well as fetching an output parameter. The stored routine is as follows: 

CREATE PROCEDURE get_pop_continent (IN continent_name CHAR(30), OUT continent_pop INT(11))
BEGIN
        SELECT SUM(Population) INTO continent_pop FROM Country WHERE Continent = continent_name;
END

  1. Copy the following code into the try block of the tutorial framework code: 

    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;
}

    You will also need to add the line #include <vector> to the top of the code, as vectors are used in this example.

  2. Compile the code using: 

    shell> g++ -o ps_scenario2 -I/usr/local/include/cppconn/ -lmysqlcppconn ps_scenario2.cpp

  3. Run the code using: 

    shell> ./ps_scenario2

  4. Make a note of the output.

In this scenario a Prepared Statement is created that calls the Stored Procedure get_pop_continent. 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 Prepared Statement is created, the input parameter to the Stored Procedure is denoted by '?'. Prior to execution of Prepared Statement it is necessary to replace this placeholder by an actual value. This is done using methods such as setString and setInt, for example: 

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

Although for the query used to obtain the output parameter a single result set is expected, it is important to use the while loop to catch more than one result, to avoid the possibility of the connection becoming unstable.

Scenario 3 - Using a Prepared Statement to prepare a Stored Procedure that returns multiple Result Sets

Note

Note this scenario is not supported on current versions of MySQL (5.4.1 or previous). This is due to a limitation in the client-server protocol.