Example of a PREPARE statement in an SPL routine

The HCL Informix® SPL language supports single-statement prepared objects.

For example, the following SQL and SPL statements perform these tasks:

Create the cities table.
Populates the cities table with four rows of data.
Creates the order_city SPL routine that defines a prepared statement and a cursor to query the cities table:

CREATE TABLE cities    -- defines a table
(
  id INT,
  city_name CHAR(50)
);

INSERT INTO cities VALUES (1, 'Chicago');
INSERT INTO cities VALUES (2, 'New York');
INSERT INTO cities VALUES (3, 'San Francisco');
INSERT INTO cities VALUES (4, 'Atlanta');

UPDATE STATISTICS HIGH;

CREATE PROCEDURE order_city() -- defines a UDR
RETURNING INT, CHAR(50);
DEFINE c_num INT;
DEFINE c_name CHAR(50);
DEFINE c_query VARCHAR(250);
LET c_query = 
 "SELECT id, city_name FROM cities ORDER BY city_name;";

PREPARE c_stmt FROM c_query;
DECLARE c_cur CURSOR FOR c_stmt;

OPEN c_cur ;
while (1 = 1)
  FETCH c_cur INTO c_num, c_name;
  IF (SQLCODE != 100) THEN
    RETURN c_num, c_name WITH RESUME;
  ELSE
    EXIT;
  END IF
END WHILE

CLOSE c_cur;
FREE c_cur;
FREE c_stmt;

END PROCEDURE;

The following SQL statement invokes the order_city routine:

EXECUTE PROCEDURE order_city();

If the order_city function is called from the dbaccess utility, this output is displayed:

(expression) (expression)

          4 Atlanta
          1 Chicago
          2 New York
          3 San Francisco

For an overview with detailed examples of how to create and use prepared objects and Dynamic SQL in SPL routines, see this IBM® developerWorks® article: Dynamic SQL support in Informix Dynamic Server Stored Procedure Language