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