22.5.6.1. Tutorial: Calling Stored Procedures with Statements in MySQL 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 Section 22.5.6.2, “Tutorial: Calling Stored Procedures with Prepared Statements in MySQL Connector/C++” will cover the use of Prepared Statements.

When considering calling Stored Procedures there are several scenarios that can occur:

  1. A Stored Procedure that does not return a result set.

  2. A Stored Procedure that returns an output parameter.

  3. A Stored Procedure that returns a result set.

Stored Procedures are given below that illustrate each of the above scenarios.

The following routine enables you to add a country into the World database, but does not return a result. This corresonds 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 routines to test out the use of Stored Procedures using Connector/C++.

Scenario 1 - Stored Procedure does not return a result set

In the first case you will examine Scenario 1, you call 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. MySQL Connector/C++ handles this status for you, so you do not need code to handle it explicitly. If the call should fail 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.

Studying the code it should be noted that the results are processed using the pattern:

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

Note

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.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout