5/6/1996

Informix Stored Procedures
Tested using:
  IDAPI/SQL Links 2.51
  INET5.01 to Informix OL Server 5.01
And
  IDAPI 3.0 with 
  Intersolv Informix driver version. 2.00.00
  INET5.01 NT to Informix OL Server 7.1

INTRODUCTION
The following information is intended to give brief examples of 
Informix stored procedures and any found issues related to
how the Borland Database Engine (BDE) treats them.  These examples
do not define the limits of Informix stored procedures; exception
handling and conditionals are two areas not discussed here.

The examples use this table,

CREATE TABLE cjones1 (fieldchar CHAR(10), field1int INTEGER,
field2int INTEGER, field3int INTEGER)


DEFINING INPUT AND OUTPUT PARAMETERS.
The TStoredProc Params property shows the input parameters  using
the following convention,

1,2,...,n

The actual names of the input variables, as defined within the Informix
stored procedure, are not shown. The programmer needs to know what
the first, second,... arguments are for the stored procedure to properly
define the data type and value for each argument.

It appears that an Informix stored procedure only returns a result
set, defined as either a singleton or multiple row result set.  When using
Delphi TStoredProcs or TQuerys components you must use the open method to 
execute an Informix stored procedure that returns values.

The column names of the returned result set will following the format,
  (expression),(expression)_1...(expression)_n

This naming convention is the same as used by the Informix program
DBACCESS (located on the server).  This indicates an Informix limitation.


RETURNING A SINGLETON RESULT SET
The following is an example of a stored procedure that returns a single row
from a SELECT:

  CREATE PROCEDURE cjproc1into (x int, y int)
  RETURNING char(20), int;
 
  DEFINE name char(20);
  DEFINE intvalue int;
 
  SELECT fieldchar, field2int
  INTO name, intvalue
  FROM cjones1
  WHERE field1int=x OR field3int=y;
  RETURN name,intvalue;
 
  END PROCEDURE

The argument list (x int, y int) is for input variables only.  You
do not need to explicitly define input variables with a DEFINE
statement. All other variables must be declared with the
DEFINE statement.

If the procedure is returning a set of  values, the second line of the
procedure must define this set's data types using the RETURNING
statement. You must then use the RETURN statement to return the
desired values.  The data types of the values in the RETURN
statement must match, in order, the data types defined by the
RETURNING statement. Besides returning values, RETURN will
also exit the stored procedure, except when followed by WITH RESUME
(see "Returning A Multiple Row Result Set" below).

If you want to return values from a dataset use the INTO statement,
after the SELECT statement, to map the column values to variables.  You then
use the RETURN statement to output these variables .

Even though the RETURN statement has 'output' variables, Informix maps the
values of these variables to a result set and returns the result set to the client (BDE).

Note the semicolons at the end of the Where and Return statement.


RETURNING A MULTIPLE ROW RESULT SET
This stored procedure example is similar to the stored procedure
in the previous section except that it returns multiple rows:

  CREATE PROCEDURE cjproc3multiple (x int, y int)
  RETURNING char(20), int;
 
  DEFINE name char(20);
  DEFINE intvalue int;
 
  FOREACH
    SELECT fieldchar, field2int
    INTO name, intvalue
    FROM cjones1
    WHERE field1int=x OR field3int=y
    RETURN name,intvalue
  WITH RESUME;
  END FOREACH;

  END PROCEDURE

The FOREACH...END FOREACH and WITH RESUME statements
allow this stored procedure to return a multiple row result set.

Note the semicolons at the end of the WITH RESUME and END FOREACH
statements.

If you want to explicitly limit the number of rows returned,
you can use a FOR loops as shown in the following example.

  CREATE PROCEDURE x()
  RETURNING CHAR(7), CHAR(1), CHAR(1), INT;

  DEFINE i int;
  FOR i IN (1 to 3)
  BEGIN
    RETURN "testing", "1", "2", 3
    WITH RESUME;
  END
  END FOR;

  END PROCEDURE  


RETURNING CALCULATIONS.
If the stored procedure is doing a calculation you can use the
LET keyword to assign a value to a variable.  Example:

  CREATE PROCEDURE cjproc2let (x int, y int)
  RETURNING int;
 
  DEFINE result int;
 
  LET result= x*y;
  RETURN result;
 
  END PROCEDURE

LET also allows you to assign more than one variable at a
time.  For example,
  
  LET a,b = y,z
  LET m,n = (SELECT first, last FROM cust WHERE id=1001);


PUB
CJ 5/6/96

ID:CS1014