Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
A stored procedure can be called using a Statement or PreparedStatement
object. This section shows how to call stored procedures using Statement objects.
To see how to use PreparedStatement objects, see Section
22.4.7.3, "Calling Stored Procedures with PreparedStatement Objects".
You can construct and call different types of stored procedures:
A stored procedure that returns no result. For example, such a stored procedure can log non-critical information, or change database data in a straightforward way.
A stored procedure that returns one or more values using output parameters. For example, such a procedure can indicate success or failure, or retrieve and return data items.
A stored procedure that returns one or more result sets. The procedure can execute one or more queries, each of which returns an arbitrary number of rows. Your application loops through each result set to display, transform, or otherwise process each row in it.
The following stored procedures illustrate each of these scenarios.
The following procedure adds a country to the world database, but does not return a
result. This corresponds to Scenario 1 described earlier.
CREATE PROCEDURE add_country (IN country_code CHAR(3), IN country_name CHAR(52), IN continent_name CHAR(30))BEGIN INSERT INTO Country(Code, Name, Continent) VALUES (country_code, country_name, continent_name);END;
The next procedures use an output parameter to return the population of a specified country or continent, or the entire world. These correspond to Scenario 2 described earlier.
CREATE PROCEDURE get_pop (IN country_name CHAR(52), OUT country_pop BIGINT)BEGIN SELECT Population INTO country_pop FROM Country WHERE Name = country_name;END;
CREATE PROCEDURE get_pop_continent (IN continent_name CHAR(30), OUT continent_pop BIGINT)BEGIN SELECT SUM(Population) INTO continent_pop FROM Country WHERE Continent = continent_name;END;
CREATE PROCEDURE get_pop_world (OUT world_pop BIGINT)BEGIN SELECT SUM(Population) INTO world_pop FROM Country;END;
The next procedure returns several result sets. This corresponds to Scenario 3 described earlier.
CREATE PROCEDURE get_data ()BEGIN SELECT Code, Name, Population, Continent FROM Country WHERE Continent = 'Oceania' AND Population < 10000; SELECT Code, Name, Population, Continent FROM Country WHERE Continent = 'Europe' AND Population < 10000; SELECT Code, Name, Population, Continent FROM Country WHERE Continent = 'North America' AND Population < 10000;END;
Enter and test the stored procedures manually to ensure that they will be available to your C++ applications.
(Select world as the default database before you create them.) You are now ready to
start writing applications using Connector/C++ that call stored procedures.
Statement for a Stored Procedure
That Returns No ResultThis example shows how to call a stored procedure that returns no result set.
Make a copy of the tutorial framework code:
shell> cp framework.cpp
sp_scenario1.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());// We need not check the return value explicitly. If it indicates// an error, Connector/C++ generates an exception.stmt->execute("CALL add_country('ATL', 'Atlantis', 'North America')");
Compile the program as described in Section 22.4.7.1, "Prerequisites and Background Information".
Run the program:
shell> ./sp_scenario1
Using the mysql command-line client or other suitable program,
check the world database to determine that it has been updated correctly.
You can use this query:
mysql> SELECT Code, Name, Continent FROM
Country WHERE Code='ATL';+------+----------+---------------+| Code | Name | Continent |+------+----------+---------------+| ATL | Atlantis | North America |+------+----------+---------------+
The code in this application simply invokes the execute method, passing to it a
statement that calls the stored procedure. The procedure itself returns no value, although it is important to
note there is always a return value from the CALL statement; this is the execute status.
MySQL Connector/C++ handles this status for you, so you need not handle it explicitly. If the execute call fails for some reason, it raises an exception that the catch block handles.
Statement for a Stored Procedure
That Returns an Output ParameterThis 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 22.4.7.1, "Prerequisites and Background Information".
Run the program:
shell> ./sp_scenario2Connector/C++ tutorial framework...Population of Uganda: 21778000Population of Asia: 3705025700Population of World: 6078749450Done.
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.
Statement for a Stored Procedure
That Returns a Result SetThis example shows how to handle result sets produced by a stored procedure.
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.
Make a copy of the tutorial framework code:
shell> cp framework.cpp
sp_scenario3.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_data()");std::auto_ptr< sql::ResultSet > res;do { res.reset(stmt->getResultSet()); while (res->next()) { cout << "Name: " << res->getString("Name") << " Population: " << res->getInt("Population") << endl; }} while (stmt->getMoreResults());
Compile the program as described in Section 22.4.7.1, "Prerequisites and Background Information".
Run the program:
shell> ./sp_scenario3Connector/C++ tutorial framework...Name: Cocos (Keeling) Islands Population: 600Name: Christmas Island Population: 2500Name: Norfolk Island Population: 2000Name: Niue Population: 2000Name: Pitcairn Population: 50Name: Tokelau Population: 2000Name: United States Minor Outlying Islands Population: 0Name: Svalbard and Jan Mayen Population: 3200Name: Holy See (Vatican City State) Population: 1000Name: Anguilla Population: 8000Name: Atlantis Population: 0Name: Saint Pierre and Miquelon Population: 7000Done.
The code is similar to the examples shown previously. The code of particular interest here is:
do { res.reset(stmt->getResultSet()); while (res->next()) { cout << "Name: " << res->getString("Name") << " Population: " << res->getInt("Population") << endl; }} while (stmt->getMoreResults());
The CALL is executed as before, but this time the results are returned into multiple
ResultSet objects because the stored procedure executes multiple SELECT statements. In this example, the output shows that three result sets are
processed, because there are three SELECT statements in the stored procedure. Each
result set returns more than one row.
The results are processed using this code pattern:
do { Get Result Set while (Get Result) { Process Result }} while (Get More Result Sets);
Use this pattern even if the stored procedure executes only a single SELECT and produces only one result set. This is a requirement of the underlying
protocol.