Tutorial: Calling Stored Procedures with Statements in MariaDB Connector/C++


Stored procedures can be called using both Statements and Prepared Statements. This tutorial looks at calling stored procedures using Statements. The following tutorial , "Tutorial: Calling Stored Procedures with Prepared Statements in MariaDB Connector/C++" will cover the use of Prepared Statements.

You can construct and call various combinations of stored procedures:

  1. A stored procedure that does not return any result. For example, such a stored procedure can log non-critical information, or change database data in a straightforward way.
  2. A stored procedure that returns an output parameter. For example, such a procedure can indicate success or failure, query a single item of data, or combine multiple data items into a single result.
  3. A stored procedure that returns a result set. The procedure can execute a query that matches an arbitrary number of table rows. Your application loops through the result set to display, transform, or otherwise process each item.

The following stored procedures illustrate each of these scenarios.

The following routine adds a country into the World database, but does not return a result. This corresponds to Scenario 1 above. 

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 routine returns the population of a specified country, and corresponds to Scenario 2 above: 

CREATE PROCEDURE get_pop (IN country_name CHAR(52), OUT country_pop INT(11))
BEGIN
 SELECT Population INTO country_pop FROM Country WHERE Name = country_name;
END

The next routine is an example of a procedure returning a result set containing multiple records. This routine corresponds to Scenario 3 above. 

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 to ensure no errors have been introduced. You are now ready to start writing applications using Connector/C++ that call stored procedures.

Scenario 1 - Stored procedure does not return a result set

The first case illustrates Scenario 1, calling a Stored procedure that does not return a result set.

  1. Make a copy of the tutorial framework code.
  2. Insert the following code into the framework at the correct location (denoted by an INSERT HERE comment in the 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 don't need to check the return value explicitly, if it indicates
// an error Connector/C++ will generate an exception.
stmt->execute('CALL add_country(\'ATL\', \'Atlantis\', \'North America\')');
  3. Compile the program using the following command: 
    shell> g++ -o sp_scenario1 -I/usr/local/include/cppconn/ -lmysqlcppconn sp_scenario1.cpp
  4. Run the program by typing: 
    shell> ./sp_scenario1
  5. Using the mysql Command Line Client, or other suitable tool, check the World database to determine that it has been updated correctly. You can use a query such as: 
    SELECT Code, Name, Continent FROM Country WHERE Code='ATL';

The code in this case simply creates a statement and then invokes the execute method on it, passing the call to the stored procedure as a parameter. The stored procedure itself does not return a value, although it is important to note there will always be a return value from the call - this is simply the call status. MariaDB Connector/C++ handles this status for you, so you do not need code to handle it explicitly. If the call fails for some reason, an exception will be raised, and this will be handled by the catch statement in the code.

Scenario 2 - Stored procedure returns an output parameter

You will now see how to handle a stored procedure that returns an output parameter.

  1. Enter the following code into the tutorial framework code: 
    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_world_pop(@pop)');
res.reset(stmt->executeQuery('SELECT @pop AS _reply'));
while (res->next())
 cout << 'Population of World: ' << res->getString('_reply') << endl;
  2. Compile the program using the following command: 
    shell> g++ -o sp_scenario2 -I/usr/local/include/cppconn/ -lmysqlcppconn sp_scenario2.cpp
  3. Run the program by typing: 
    shell> ./sp_scenario2

    Note the output generated by the program.

In this scenario, the stored procedure sets an output parameter. This is not returned as such, but needs to be obtained using a query. If running the SQL statements directly, this might be similar to the following: 

CALL get_world_pop(@pop);
SELECT @pop;

In the C++ code, a similar sequence is carried out. First, the CALL is executed as seen earlier. To obtain the output parameter, an additional query must be executed. This query results in a ResultSet that can then be processed in a while loop. The simplest way to retrieve the data in this case 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.

Scenario 3 - Stored procedure returns a Result Set

You will now see how to handle a stored procedure that returns a result set.

  1. Enter the following code into the tutorial framework code: 
    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_stats()');
std::auto_ptr< sql::ResultSet > res;
do {
 res.reset(stmt->getResultSet());
 while (res->next()) {
 cout << 'Result: ' << res->getString(1) << endl;
 }
} while (stmt->getMoreResults());
  2. Compile the program using the following command: 
    shell> g++ -o sp_scenario3 -I/usr/local/include/cppconn/ -lmysqlcppconn sp_scenario3.cpp
  3. Run the program by typing: 
    shell> ./sp_scenario3

    Note the output generated by the program.

The code is similar to the examples you have previously seen. The code of particular interest in this case 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, with the results being returned into multiple ResultSets. This is because the Stored Procedure in this case uses 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. All of the Result Sets have more than one row.

The results are processed using the pattern: 

do {
 Get Result Set
 while (Get Result) {
 Process Result
 }
} while (Get More Result Sets);
Note

This pattern would be used even if the Stored Procedure carried out a single SELECT and you knew there was only one result set. This is a requirement of the underlying protocol.

Retornar