7.2 Calling Stored Procedures with Statement Objects

7.2.1 Using a Statement for a Stored Procedure That Returns No Result

7.2.2 Using a Statement for a Stored Procedure That Returns an Output Parameter

7.2.3 Using a Statement for a Stored Procedure That Returns a Result Set

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