Example of parameter markers in a dynamically executed SQL program

In the statement string of a dynamic SQL statement, a parameter marker represents a value that will be provided by the application program. The value of a parameter marker is provided on the EXECUTE or OPEN statement that is associated with the dynamic SQL statement.

The following examples show how to use parameter markers in a dynamic SQL program:

C and C++ (dbuse.sqc/dbuse.sqC)

The function DynamicStmtWithMarkersEXECUTEusingHostVars() in the C-language sample dbuse.sqc shows how to perform a delete using a parameter marker with a host variable:

EXEC SQL BEGIN DECLARE SECTION;
  char hostVarStmt1[50];
  short hostVarDeptnumb;
EXEC SQL END DECLARE SECTION; 

/* prepare the statement with a parameter marker */
strcpy(hostVarStmt1, "DELETE FROM org WHERE deptnumb = ?"); 
EXEC SQL PREPARE Stmt1 FROM :hostVarStmt1; 

/* execute the statement for hostVarDeptnumb = 15 */
hostVarDeptnumb = 15;
EXEC SQL EXECUTE Stmt1 USING :hostVarDeptnumb;

COBOL (varinp.sqb)

The following example is from the COBOL sample varinp.sqb, and shows how to use a parameter marker in search and update conditions:

   EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 pname             pic x(10).
01 dept              pic s9(4) comp-5.
01 st                pic x(127).
01 parm-var          pic x(5).
   EXEC SQL END DECLARE SECTION END-EXEC.

   move "SELECT name, dept FROM staff
-         "   WHERE job = ? FOR UPDATE OF job" to st.
   EXEC SQL PREPARE s1 FROM :st END-EXEC. 
          
   EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. 

   move "Mgr" to parm-var.
   EXEC SQL OPEN c1 USING :parm-var END-EXEC

   move "Clerk" to parm-var.
   move "UPDATE staff SET job = ? WHERE CURRENT OF c1" to st.
   EXEC SQL PREPARE s2 from :st END-EXEC.
 
* call the FETCH and UPDATE loop.
   perform Fetch-Loop thru End-Fetch-Loop
      until SQLCODE not equal 0.

   EXEC SQL CLOSE c1 END-EXEC.