7.3.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:

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

4. Run the program:

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