1.6.2 ? parameters

Organization of this subsection
(1) Function
(2) Specifying a value to be passed to a ? parameter

(1) Function

For execution of a UAP, the ? parameter can be used to assemble SQL character strings in the program, prepare the SQL character strings using the PREPARE statement, execute them in the EXECUTE, OPEN, FETCH, or CLOSE statement, or prepare and execute them using the EXECUTE IMMEDIATE statement. In this case, specify a ? in the specific location in the SQL character string where a value is passed from the UAP, in the SQL character string that is prepared by the PREPARE statement, or prepared and executed by the EXECUTE IMMEDIATE statement. This facility is called the ? parameter.

(2) Specifying a value to be passed to a ? parameter

The value to be passed to a ? parameter is specified in terms of an embedded variable in the USING clause of the EXECUTE, OPEN, FETCH, CLOSE, or EXECUTE IMMEDIATE statement associated with the PREPARE statement. An indicator variable can be specified only in the EXECUTE or EXECUTE IMMEDIATE statement.

Following are examples of ? parameters that do not use the SQL descriptor area:

Example: Use of ? parameter (not using SQL descriptor area)
C language

      :
      :
      :
EXEC SQL BEGIN DECLARE SECTION;
   struct{
       long xcmnd_len;
       char xcmnd_txt[58];
  }xcmnd;
   char   XPCODE[5];
   char   XPNAME[21];
   char   XCOLOR[11];
   long   XPRICE;
   long   XSQUANTITY;
EXEC SQL END DECLARE SECTION;
      :
      :
      :
strcpy (xcmnd.xcmnd_txt, "INSERT INTO STOCK VALUES(?,?,?,?,?)");
xcmnd.xcmnd_len = strlen(xcmnd.xcmnd_txt);
EXEC SQL PREPARE ST1 FROM :xcmnd;

strcpy(XPCODE,"595M");
strcpy(XPNAME,"SOCKS");
strcpy(XCOLOR,"RED");
XPRICE=3.00;
XSQUANTITY=200;
EXEC SQL
   EXECUTE ST1 USING :XPCODE, :XPNAME, :XCOLOR, :XPRICE, :XSQUANTITY;
      :
      :
      :

COBOL

      :
      :
      :
DATA DIVISION.

WORKING-STORAGE SECTION.

   EXEC SQL
     BEGIN DECLARE SECTION
   END-EXEC.
01  XCMND.
 02  XCMND-LEN  PIC S9(9)  COMP VALUE 58.
 02  XCMND-TXT  PIC X(58)       VALUE SPACE.
77    XPCODE     PIC X(4).
77    XPNAME     PIC N(10).
77    XCOLOR     PIC N(5).
77    XPRICE     PIC S9(9) COMP.
77    XSQUANTITY PIC S9(9) COMP.

     EXEC SQL
       END DECLARE SECTION
     END-EXEC.
      :
      :
PROCEDURE DIVISION.
      :
      :
      :
     MOVE 'INSERT INTO STOCK VALUES(?,?,?,?,?)' TO XCMND-TXT.
     EXEC SQL
       PREPARE ST1 FROM :XCMND
     END-EXEC.

     MOVE '595M'   TO XPCODE.
     MOVE N'TSHIRTS' TO XPNAME.
     MOVE N'RED'    TO XCOLOR.
     MOVE 3.00     TO XPRICE.
     MOVE 300      TO XSQUANTITY.

     EXEC SQL
       EXECUTE ST1 USING :XPCODE, :XPNAME, :XCOLOR,
                            :XPRICE, :XSQUANTITY
     END-EXEC.
      :
      :
      :