Documentation Home
MySQL Connector/C++ Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.7Mb
PDF (A4) - 0.7Mb
EPUB - 0.7Mb
HTML Download (TGZ) - 0.6Mb
HTML Download (Zip) - 0.7Mb


MySQL Connector/C++ Developer Guide  /  Connector/C++ Tutorials  /  Calling Stored Procedures with Statement Objects

8.2 Calling Stored Procedures with Statement Objects

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 8.3, “Calling Stored Procedures with PreparedStatement Objects”.

The following list describes different types of stored procedures that you can construct and call, along with example stored procedures that illustrate each type:

  1. 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.

    The following procedure adds a country to the world database, but does not return a result:

    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;
    
  2. 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.

    The following procedures use an output parameter to return the population of a specified country or continent, or the entire world:

    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;
    
  3. 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.

    This procedure returns several result sets:

    
    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.


User Comments
Sign Up Login You must be logged in to post a comment.