MySQL Connector/C++ 1.1 Developer Guide
This example shows how to handle a stored procedure that returns an output parameter.
Make a copy of the tutorial framework code:
shell> cp framework.cpp sp_scenario2.cpp
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::Statement> stmt(con->createStatement()); stmt->execute("CALL get_pop('Uganda', @pop)"); std::auto_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT @pop AS _reply")); while (res->next()) cout << "Population of Uganda: " << res->getString("_reply") << endl; stmt->execute("CALL get_pop_continent('Asia', @pop)"); res.reset(stmt->executeQuery("SELECT @pop AS _reply")); while (res->next()) cout << "Population of Asia: " << res->getString("_reply") << endl; stmt->execute("CALL get_pop_world(@pop)"); res.reset(stmt->executeQuery("SELECT @pop AS _reply")); while (res->next()) cout << "Population of World: " << res->getString("_reply") << endl;
Compile the program as described in Section 7.1, “Prerequisites and Background Information”.
Run the program:
shell> ./sp_scenario2
Connector/C++ tutorial framework...
Population of Uganda: 21778000
Population of Asia: 3705025700
Population of World: 6078749450
Done.
In this scenario, each stored procedure sets the value of an
output parameter. This is not returned directly to the
execute
method, but needs to be obtained
using a subsequent query. If you were executing the SQL
statements directly, you might use statements similar to these:
CALL get_pop('Uganda', @pop); SELECT @pop; CALL get_pop_continent('Asia', @pop); SELECT @pop; CALL get_pop_world(@pop); SELECT @pop;
In the C++ code, a similar sequence is carried out for each procedure call:
Execute the CALL
statement.
Obtain the output parameter by executing an additional
query. The query produces a ResultSet
object.
Retrieve the data using a while
loop. The
simplest way to do this is to use a
getString
method on the
ResultSet
, passing the name of the
variable to access. In this example
_reply
is used as a placeholder for the
variable and therefore is used as the key to access the
correct element of the result dictionary.
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.