1  PREPARE
   Prepares an SQL statement dynamically generated by a program for
   execution, and assigns a name to that statement.

   Dynamic SQL lets programs accept or generate SQL statements
   at run time, in contrast to SQL module language procedures.
   Unlike precompiled SQL or SQL module language statements, such
   dynamically executed SQL statements are not necessarily part of a
   program's source code, but can be generated while the program is
   running. Dynamic SQL is useful when you cannot predict the type
   of SQL statement your program will need to process.

   The PREPARE . . . INTO statement stores in the SQLDA the number
   and data types of any select list items of a prepared statement.
   The SQLDA provides information about dynamic SQL statements to
   the program and information about memory allocated by the program
   to SQL.

   The Oracle Rdb SQL Reference Manual describes in more detail
   the specific fields of the SQLDA, and how programs use it to
   communicate about select list items in prepared statements.
 

2  Environment
   You can use the PREPARE statement:

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module
 

2  Format

 PREPARE -+-> <statement-name> --------+--------------+
          +-> <statement-id-parameter> +              |
          +-------------------------------------------+
          ++----------------------------------------+-+
           +-> SELECT LIST INTO <descriptor-name>   + |
          +-------------------------------------------+
          +-> FROM --+-> ' -> <statement-string> -> ' -+->
                     +-> <parameter> ------------------+
 

 

2  Arguments
 

3  descriptor-name

   Specifies the name of a structure declared in the host program
   as an SQLDA to which SQL writes information about select list
   items. Precompiled programs can use the embedded SQL statement
   INCLUDE SQLDA to automatically insert a declaration of an SQLDA
   structure, called SQLDA, in the program when it precompiles
   the program. Programs that use the SQL module language must
   explicitly declare an SQLDA. Either precompiled or SQL module
   language programs can explicitly declare additional SQLDAs, but
   must declare them with unique names.
 

3  FROM

   Syntax options:

      FROM statement-string
      FROM parameter

   Specifies the SQL statement to be prepared for dynamic execution.
   You either specify the statement string directly enclosed in
   single quotation marks, or in a parameter (a host language
   variable in a precompiled PREPARE statement or a formal parameter
   in a PREPARE statement that is part of an SQL module language
   procedure) that contains the statement string.

   Whether specified directly or by a parameter, the statement
   string must be a character string that is a dynamically
   executable SQL statement. If you specify the statement string
   directly, the maximum length is 1,024 characters. If you specify
   the statement string as a parameter, the maximum length of the
   statement string is 65,535 characters.

   The form for the statement is the same as for embedded SQL
   statements, except that:

   o  You must not begin the string with EXEC SQL.

   o  In places where SQL allows host language variables in an
      embedded statement, you must specify parameter markers
      instead.

   If you try to prepare an invalid statement, you will find a value
   in the SQLCODE, the SQLCODE field of the SQLCA, or the SQLSTATE
   status parameter indicating an error.

   The values returned to the SQLCODE field are described in the
   Oracle Rdb SQL Reference Manual. Check the message vector to
   see which error message was returned. If necessary, refer to the
   error message explanations and user actions located by default in
   the SQL HELP ERRORS.

   Parameter markers are question marks (?)  that denote parameters
   in the statement string of a PREPARE statement. Parameter markers
   are replaced by values in parameters or dynamic memory when the
   prepared statement is executed by an EXECUTE or OPEN statement.
 

3  SELECT_LIST_INTO

   Specifies that SQL writes information about the number and data
   type of select list items in the statement string to the SQLDA.
   The SELECT LIST keywords clarify the effect of the INTO clause
   and are optional.

   Using the SELECT LIST clause in a PREPARE statement is an
   alternative to issuing a separate DESCRIBE . . . INPUT statement.
   See the DESCRIBE statement for more information.

   The SELECT LIST clause in a PREPARE statement is deprecated
   syntax. For more information about deprecated syntax, see the
   Oracle Rdb SQL Reference Manual.

                                  NOTE

      The PREPARE statement LIST keyword is not related to the
      LIST data type or list cursors.
 

3  statement-name

   Identifies the prepared version of the SQL statement specified in
   the FROM clause. Depending on the type of SQL statement prepared,
   DESCRIBE, EXECUTE, and dynamic DECLARE CURSOR statements can
   refer to the statement name assigned in a PREPARE statement.

   You can supply either a parameter or a compile-time statement
   name. Specifying a parameter lets SQL supply identifiers to
   programs at run time. Use an integer parameter to contain the
   statement identifier returned by SQL, or a character string
   parameter to contain the name of the statement that you pass
   to SQL.

   A single set of dynamic SQL statements (PREPARE, DESCRIBE,
   EXECUTE, Extended Dynamic DECLARE CURSOR) can handle any
   number of dynamically executed statements. If you decide to
   use parameters, statements that refer to the prepared statement
   (DESCRIBE, EXECUTE, extended dynamic DECLARE CURSOR) must also
   use a parameter instead of the explicit statement name.

   Refer to the DECLARE Dynamic_CURSOR statement for an example
   demonstrating the PREPARE statement used with a dynamic DECLARE
   CURSOR statement.
 

3  statement-id-parameter

   Identifies the prepared version of the SQL statement specified in
   the FROM clause. Depending on the type of SQL statement prepared,
   DESCRIBE, EXECUTE, and dynamic DECLARE CURSOR statements can
   refer to the statement name assigned in a PREPARE statement.

   You can supply either a parameter or a compile-time statement
   name. Specifying a parameter lets SQL supply identifiers to
   programs at run time. Use an integer parameter to contain the
   statement identifier returned by SQL, or a character string
   parameter to contain the name of the statement that you pass
   to SQL.

   A single set of dynamic SQL statements (PREPARE, DESCRIBE,
   EXECUTE, Extended Dynamic DECLARE CURSOR) can handle any
   number of dynamically executed statements. If you decide to
   use parameters, statements that refer to the prepared statement
   (DESCRIBE, EXECUTE, extended dynamic DECLARE CURSOR) must also
   use a parameter instead of the explicit statement name.

   See the DECLARE Dynamic_CURSOR statement for an example
   demonstrating the PREPARE statement used with a dynamic DECLARE
   CURSOR statement.
 

2  Examples

   Example 1: Preparing an INSERT statement with parameter markers

   This PL/I program illustrates using a PREPARE statement to
   prepare an INSERT statement for dynamic execution. Because the
   statement string stored in COMMAND_STRING has parameter markers,
   the program needs to assign values to host language variables
   that will be substituted for the parameter markers during dynamic
   execution.

   In this case, a DESCRIBE statement writes information about
   the parameter markers to the SQLDA and the program writes the
   addresses of the variables to the SQLDA. The program stores
   values in the variables and an EXECUTE statement substitutes the
   values for the parameter markers in the INSERT statement using
   the addresses in the SQLDA.

   To shorten the example, this program is simplified:

   o  The program includes the INSERT statement as part of the
      program source code. A program with such coded SQL statements
      does not need to use dynamic SQL at all, but can simply embed
      the INSERT statement directly in the program. A program that
      must process SQL statements generated as it executes is the
      only type of program that requires dynamic SQL.

   o  The program declares host language variables for the
      parameter markers without first checking the SQLDA for their
      description. Typically, an application needs to look in the
      SQLDA to determine the number and data type of parameter
      markers in the statement string before allocating memory for
      them.

   PREP_INTO: procedure options(main);
   /*
   *       Illustrate a dynamic INSERT statement
   *       with parameter markers:
   */
   declare FILESPEC char(20),
           EMP_ID CHAR(5),
           FNAME CHAR(10),
           LNAME CHAR(14),
           CITY CHAR(20),
           COMMAND_STRING char(256);

   /* Declare communication area (SQLCA)
    * and descriptor area (SQLDA): */
   EXEC SQL        INCLUDE SQLDA;
   EXEC SQL        INCLUDE SQLCA;

   /* Declare the database: */
   EXEC SQL DECLARE SCHEMA RUNTIME FILENAME :FILESPEC;

   /*
    *
    * procedure division
    *
    */

   /*
    * Assign values to FILESPEC and COMMAND_STRING,
    * and allocate memory for the SQLDA:
    */
   FILESPEC = 'SQL$DATABASE';
   COMMAND_STRING =
           'INSERT INTO EMPLOYEES
           (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY)
           VALUES (?,?,?,?)';
   SQLSIZE = 10;
   ALLOCATE SQLDA SET (SQLDAPTR);
   SQLN = 10;

   /*
    * Prepare the statement assigned to COMMAND_STRING:
    */
   EXEC SQL PREPARE STMT3
           FROM COMMAND_STRING;

   /* Use a DESCRIBE statement to write information
    * about the parameter markers in the statement string
    * to the SQLDA:
    */
   EXEC SQL DESCRIBE STMT3 MARKERS INTO SQLDA;

   /* Assign values to the variables: */
           EMP_ID = '99999';
           FNAME = 'Bob';
           LNAME = 'Addams';
           CITY = 'Francestown';

   /*
    * Assign the addresses of the variables to the SQLDATA field
    * of the SQLDA:
    */
           SQLDATA(1) = ADDR(EMP_ID);
           SQLDATA(2) = ADDR(FNAME);
           SQLDATA(3) = ADDR(LNAME);
           SQLDATA(4) = ADDR(CITY);

   /* Execute STMT3:*/
   EXEC SQL EXECUTE STMT3 USING DESCRIPTOR SQLDA;

   /*
   * Display the contents of table S to make sure
   * it has the proper contents and clean it up:
   */
   CALL DUMP_S;
   EXEC SQL DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = "99999";
   EXEC SQL COMMIT WORK;
   RETURN;

   DUMP_S: PROC;
   EXEC SQL DECLARE X CURSOR FOR SELECT
           EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY
           FROM EMPLOYEES WHERE EMPLOYEE_ID = "99999";

   /*
    * Declare a structure to hold values of rows from the table:
   */
   DCL 1 S,
           2 EMP_ID CHAR(5),
           2 FNAME CHAR(10),
           2 LNAME CHAR(14),
           2 CITY CHAR(20);

   /* Declare indicator vector for the preceding structure: */
   DCL S_IND (4) FIXED(15) BIN;

   PUT EDIT ('Dump the contents of S') (SKIP, SKIP, A);
   EXEC SQL OPEN X;
   EXEC SQL FETCH X INTO :S:S_IND;
   DO WHILE (SQLCODE = 0);
      PUT EDIT (S_IND(1), ' ', S.EMP_ID, ' ')   (SKIP, F(6), A, A, A);
      PUT EDIT (S_IND(2), ' ', S.FNAME, ' ')  (F(6), A, A, A);
      PUT EDIT (S_IND(3), ' ', S.LNAME, ' ') (F(6), A, A, A);
      PUT EDIT (S_IND(4), ' ', S.CITY)   (F(6), A, A);
      EXEC SQL FETCH X INTO :S:S_IND;
   END;
   EXEC SQL CLOSE X;
   RETURN;
   END DUMP_S;

   END PREP_INTO;

   Example 2: Showing the effect of the SQLCA support.

   #include <stdio.h>
   #include <sql_rdb_headers.h>

   exec sql
       declare alias filename 'db$:mf_personnel';

   exec sql
       include SQLCA;

   char * s1 = "begin insert into work_status values (?, ?, ?);\
                select count(*) into ? from work_status; end";

   void main ()
   {
   int i;
   SQLCA.SQLERRD[2] = SQLCA.SQLERRD[3] = 1;
   exec sql
       prepare stmt from :s1;
   if (SQLCA.SQLCODE != 0) sql_signal ();
   printf( "SQLCA:\n  SQLCODE:    %9d\n", SQLCA.SQLCODE);
   for (i = 0; i < 6; i++)
       printf( "  SQLERRD[%d]: %9d\n", i, SQLCA.SQLERRD[i]);
   }

   The results below show that there are three input arguments and
   one output argument.

   SQLCA:
     SQLCODE:            0
     SQLERRD[0]:         0
     SQLERRD[1]:         0
     SQLERRD[2]:         1
     SQLERRD[3]:         3
     SQLERRD[4]:         0
     SQLERRD[5]:         0