1  EXECUTE
   Dynamically executes a previously prepared statement.

   The EXECUTE statement is a dynamic SQL statement. Dynamic SQL
   lets programs accept or generate SQL statements at run time,
   in contrast to SQL statements that are part of the source code
   for precompiled programs or 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.

   If a program needs to dynamically execute a statement more than
   once, the statement should be prepared first with the PREPARE
   statement and executed each time with the EXECUTE statement.
   SQL does not parse and compile prepared statements every time it
   dynamically executes them with the EXECUTE statement.
 

2  Environment
   You can use the EXECUTE statement:

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module
 

2  Format

 (B)0EXECUTE qqwq> <statement-name> qqqqqqqqwqk                   
           mq> <statement-id-parameter> j x                   
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj                   
 mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk  
   mq> INTO qqwq> DESCRIPTOR <desc-name> qqqqqqqqqqwqj x   
              mqwqwq> <parameter>  qqqqqqqqqqqqwqwqj   x
                x tq> <qualified-parameter>  qqu x     x
                x mq> <variable>   qqqqqqqqqqqqj x     x
                mqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqj     x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj    
 mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>
   mq> USING qwq> DESCRIPTOR <desc-name> qqqqqqqqqqwqj
              mqwqwq> <parameter>  qqqqqqqqqqqqwqwqj 
                x tq> <qualified-parameter>  qqu x
                x mq> <variable>   qqqqqqqqqqqqj x
                mqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqj

 

2  Arguments
 

3  INTO_DESCRIPTOR

   Specifies an SQLDA descriptor that contains addresses and data
   types that specify output parameters or variables.

   The descriptor must be a structure declared in the host language
   program as an SQLDA. If the program is precompiled and uses the
   embedded SQL statement INCLUDE SQLDA, the name of the structure
   is simply SQLDA. Programs can use multiple SQLDAs, but must
   explicitly declare them with names other than SQLDA.

   Programs can always use the INTO DESCRIPTOR clause of the EXECUTE
   statement whether or not the statement string contains output
   parameter markers, as long as the value of the SQLD field in
   the SQLDA corresponds to the number of output parameter markers.
   SQL updates the SQLD field with the correct number of output
   parameter markers when it processes the DESCRIBE statement for
   the statement string.
 

3  INTO_clause

   Syntax options:

   INTO parameter | INTO qualified-parameter | INTO variable

   Specifies output parameters or variables whose values are
   returned by a successful EXECUTE statement.

   When you specify a list of parameters or variables, the number
   of parameters in the list must be the same as the number of
   output parameter markers in the statement string of the prepared
   statement. If SQL determines that a statement string had no
   output parameter markers, the INTO clause is not allowed.
 

3  statement-name

   Specifies the name of a prepared 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.

   If the PREPARE statement for the dynamically executed statement
   specifies a parameter, use that same parameter in the EXECUTE
   statement instead of an explicit statement name.
 

3  statement-id-parameter

   Specifies the name of a prepared 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.

   If the PREPARE statement for the dynamically executed statement
   specifies the same parameter, use that parameter in the EXECUTE
   statement instead of an explicit statement name. If an SQLCA is
   used to capture completion conditions and row count information,
   an SQLCA must be used with the PREPARE statement. See the PREPARE
   statement for more information.
 

3  USING_DESCRIPTOR

   Specifies an SQLDA descriptor that contains addresses and data
   types of input parameters or variables.

   The descriptor must be a structure declared in the host language
   program as an SQLDA. If the program is precompiled and uses the
   embedded SQL statement INCLUDE SQLDA, the name of the structure
   is simply SQLDA. Programs can use multiple SQLDAs, but must
   explicitly declare them with names other than SQLDA.

   Programs can always use the USING DESCRIPTOR clause of the
   EXECUTE statement whether or not the statement string contains
   input parameter markers, as long as the value of the SQLD
   field in the SQLDA corresponds to the number of input parameter
   markers. SQL updates the SQLD field with the correct number of
   input parameter markers when it processes the DESCRIBE statement
   for the statement string.
 

3  USING_clause

   Syntax options:

   USING parameter | USING qualified-parameter | USING variable

   Specifies input parameters or variables whose values SQL uses to
   replace parameter markers in the prepared statement string.

   When you specify a list of parameters or variables, the number
   of parameters in the list must be the same as the number of
   input parameter markers in the statement string of the prepared
   statement. If SQL determines that a statement string had no input
   parameter markers, the USING clause is not allowed.
 

2  Example
   Example 1: Executing an INSERT statement with parameter markers

   These fragments from the online sample C program sql_dynamic
   illustrate using an EXECUTE statement in an SQL module procedure
   to execute a dynamically generated SQL statement.

   The program accepts input of any valid SQL statement from the
   terminal and calls the subunit shown in the following program
   excerpt:

      .
      .
      .
   /*
   **----------------------------------------------------------------------------
   **  Begin Main routine
   **----------------------------------------------------------------------------
   */

     int   sql_dynamic (psql_stmt, input_sqlda, output_sqlda, stmt_id, is_select)
     char  *psql_stmt;
     sqlda *input_sqlda;
     sqlda *output_sqlda;
     long  *stmt_id;
     int   *is_select;

   {
       sqlda sqlda_in, sqlda_out;    /* Declare the SQLDA structures. */
       int rowcount, status;
       int param;

   /* Declare arrays for storage of original data types and allocate memory. */

       mem_ptr output_save;
       mem_ptr input_save;

       /* * If a NULL SQLDA is passed, then a new statement is being prepared. */

       if ((*input_sqlda == NULL) && (*output_sqlda == NULL))
            {
            new_statement = TRUE;

            /*
            * Allocate separate SQLDAs for input parameter markers (SQLDA_IN)
            * and output list items (SQLDA_OUT).  Assign the value of the constant
            * MAXPARMS to the SQLN field of both SQLDA structures.  SQLN specifies to
            * SQL the maximum size of the SQLDA.
            */

           if ((sqlda_in = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0)
               {
               printf ("\n\n*** Error allocating memory for sqlda_in: Abort");
               return (-1);
               }
           else    /* set # of possible parameters */
               sqlda_in->sqln = MAXPARAMS;

           if ((sqlda_out = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0)
               {
               printf ("\n\n*** Error allocating memory for sqlda_out: Abort");
               return (-1);
               }

               }
           else
               /* Set # of possible select list items. */
               sqlda_out->sqln = MAXPARAMS;

           /* copy name SQLDA2 to identify the SQLDA */

           strncpy(&sqlda_in->sqldaid[0],"SQLDA2  ",8);
           strncpy(&sqlda_out->sqldaid[0],"SQLDA2  ",8);

           /*
           * Call an SQL module language procedure, prepare_stmt and
           * describe_stmt that contains a PREPARE and DESCRIBE...OUTPUT
           * statement to prepare the dynamic statement and write information
           * about any select list items in it to SQLDA_OUT.
           */

           *stmt_id = 0;  /* If <> 0 the BADPREPARE error results in the PREPARE.*/

           PREPARE_STMT (&SQLCA, stmt_id, psql_stmt);
           if (SQLCA.SQLCODE  != sql_success)
               {
               printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE",
                           SQLCA.SQLCODE);
               display_error_message();
               return (-1);
               }

           DESCRIBE_SELECT (&SQLCA, stmt_id, sqlda_out);
           if (SQLCA.SQLCODE  != sql_success)
               {
               printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE",
                           SQLCA.SQLCODE);
               display_error_message();
               return (-1);
               }

           /*
           * Call an SQL module language procedure, describe_parm, that contains a
           * DESCRIBE...INPUT statement to write information about any parameter
           * markers in the dynamic statement to sqlda_in.
           */

           DESCRIBE_PARM (&SQLCA, stmt_id, sqlda_in);
           if (SQLCA.SQLCODE  != sql_success)
               {
               printf ("\n\n*** Error %d returned from describe_parm: Abort",
                        SQLCA.SQLCODE);
               display_error_message();
               return (-1);
               }

           /* Save the value of the SQLCA.SQLERRD[1] field so that program can
            *  determine if the statement is a SELECT statement or not.
            *  If the value is 1, the statement is a SELECT statement.*/

              *is_select = SQLCA.SQLERRD[1];
               .
               .
               .

       /*
       * Check to see if the prepared dynamic statement contains any parameter
       * markers by looking at the SQLD field of sqlda_in.  SQLD contains the
       * number of parameter markers in the prepared statement. If SQLD is
       * positive, the prepared statement contains parameter markers.  The program
       * executes a local procedure, get_in_params, that prompts the user for
       * values, allocates storage for those values, and updates the SQLDATA field
       * of sqlda_in:
       */

       if (sqlda_in->sqld > 0)
           if ((status = get_in_params(sqlda_in,input_save)) != 0)
               {
               printf ("\nError returned from GET_IN_PARAMS. Abort");
               return (-1);
               }

       /* Check to see if the prepared dynamic statement is a SELECT by looking
       * at the value in is_select, which stores the value of the
       * SQLCA.SQLERRD[1] field.  If that value is equal to 1, the prepared
       * statement is a SELECT statement.  The program allocates storage for
       * rows for SQL module language procedures to open and fetch from a cursor,
       * and displays the rows on the terminal:
       */

       if (*is_select)
           {
           if (new_statement == TRUE)      /* Allocate buffers for output. */
               {
               /* assign a unique name for the cursor */
               sprintf(cursor_name,"%2d",++cursor_counter);

               if ((status = allocate_buffers(sqlda_out)) != 0)
        .
        .
        .

   /*
   * If the SQLCA.SQLERRD[1] field is not 1, then the prepared statement is not a
   * SELECT statement and only needs to be executed.  Call an SQL module language
   * procedure to execute the statement, using information about parameter
   * markers stored in sqlda_in by the local procedure get_in_params:
   */
           {
           EXECUTE_STMT (&SQLCA, stmt_id, sqlda_in);
           if (SQLCA.SQLCODE != sql_success)
      .
      .
      .

   The SQL module language procedures called by the preceding
   fragment:

      .
      .
      .
   -------------------------------------------------------------------------------
   -- Procedure Section
   -------------------------------------------------------------------------------

   -- This procedure prepares a statement for dynamic execution from the string
   -- passed to it.  It also writes information about the number and data type of
   -- any select list items in the statement to an SQLDA2 (specifically,
   -- the sqlda_out SQLDA2 passed to the procedure by the calling program).
   --

   PROCEDURE PREPARE_STMT
       SQLCA
       :DYN_STMT_ID      INTEGER
       :STMT            CHAR(1024);

       PREPARE :DYN_STMT_ID FROM :STMT;

   -- This procedure writes information to an SQLDA (specifically,
   -- the sqlda_in SQLDA passed to the procedure by the calling program)
   -- about the number and data type of any parameter markers in the
   -- prepared dynamic statement.  Note that SELECT statements may also
   -- have parameter markers.

   PROCEDURE DESCRIBE_SELECT
       SQLCA
       :DYN_STMT_ID    INTEGER
       SQLDA;

       DESCRIBE :DYN_STMT_ID OUTPUT INTO SQLDA;

   PROCEDURE DESCRIBE_PARM
       SQLCA
       :DYN_STMT_ID INTEGER
       SQLDA;

       DESCRIBE :DYN_STMT_ID INPUT INTO SQLDA;

   -- This procedure dynamically executes a non-SELECT statement.
   -- SELECT statements are processed by DECLARE CURSOR, OPEN CURSOR,
   -- and FETCH statements.
   --
   -- The EXECUTE statement specifies an SQLDA2 (specifically,
   -- the sqlda_in SQLDA2 passed to the procedure by the calling program)
   -- as the source of addresses for any parameter markers in the dynamic
   -- statement.
   --
   -- The EXECUTE statement with the USING DESCRIPTOR clause
   -- also handles statement strings that contain no parameter markers.
   -- If a statement string contains no parameter markers, SQL sets
   -- the SQLD field of the SQLDA2 to zero.

   PROCEDURE EXECUTE_STMT
       SQLCA
       :DYN_STMT_ID INTEGER
       SQLDA;

       EXECUTE :DYN_STMT_ID USING DESCRIPTOR SQLDA;
      .
      .
      .