Before working through this tutorial it is recommended you first work through the previous tutorial Section 22.5.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
Add the following code to the
tryblock 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.Compile the code using the following command:
g++ -o ps_scenario1 -I/usr/local/include/cppconn/ -lmysqlcppconn ps_scenario1.cpp
Run the code using the command:
./ps_scenario1
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 used in the tutorial Section 22.5.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;
ENDCopy 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.Compile the code using:
shell> g++ -o ps_scenario2 -I/usr/local/include/cppconn/ -lmysqlcppconn ps_scenario2.cpp
Run the code using:
shell> ./ps_scenario2
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 this scenario is not supported on versions of MySQL prior to 5.5.3. This is due to a limitation in the client/server protocol.
Enter the following code into the
tryblock 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());Compile the code using the following command:
shell> g++ -o ps_scenario3 -I/usr/local/include/cppconn/ -lmysqlcppconn ps_scenario3.cpp
Run the program using the command:
shell> ./ps_scenario3
Make a note of the output generated.
The code executes the Stored Procedure using a Prepared Statement.
The standard do-while construct is used to ensure that all Result
Sets are fetched. In this case the returned values are fetched
from the Result Sets using the getInt and
getString methods.