DB2 Server for VSE & VM: Application Programming

Using the PREPARE Statement

Figure 71. Format of the PREPARE statement

>>-PREPARE--statement_name--FROM----+-string_constant-+-------->< '-host_variable---'

Although a statement to be "prepared" cannot contain any host variables, it can contain parameters to be filled in when the statement is executed. These parameters are denoted by parameter markers (?). You can specify parameters only in places where a data value could be used. (A parameter cannot represent the name of a table or a column.) The pseudocode example below prepares an INSERT statement that has three parameters:

QSTRING='INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,ADMRDEPT) VALUES (?,?,?)'
 
PREPARE S1 FROM :QSTRING

Each time S1 is executed, values must be supplied for the three parameters that were specified with question marks.

If your program constructs dynamic SQL statements by manipulating quoted strings, remember that SQL uses two single quotation marks to represent a quotation mark inside a quoted string. The following example illustrates this rule:

   PREPARE S1 FROM 'INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,ADMRDEPT)
        VALUES ('A00','SPIFFY COMPUTER SERVICE DIV.','A00'')'

In this example, the text beginning with INSERT and ending with A00'') is a constant string. Each pair of quotation marks is collapsed to a single quotation mark.

In COBOL, a constant string-spec is treated as a COBOL character string and is affected by the Quote/APOST option. This option determines the character string delimiters. If you use the same character (" or ') in the constant string-spec as the one established by Quote/APOST option for the outer string delimiters, unexpected string termination can result.

It is best to avoid using a constant string-spec whenever it may contain quotation marks. Instead, you should build the SQL statement as a host variable string-spec, using the known host language rules for character strings. For SQL statements that contain graphic constants, be aware that some DBCS characters may contain the encodings for EBCDIC quote. This could cause unintentional termination of host language strings that contain DBCS characters.

A parameter marker (?) can appear in an SQL statement to be "prepared" in any place that a host variable may appear, with the following exceptions:

• A parameter marker cannot be used in a select_list or a FROM-clause (but it may be used in the WHERE clause of a SELECT statement).

  The following examples are invalid:

     SELECT ? FROM EMPLOYEE
     SELECT EMPNO FROM ?
  

  The following example is valid:

     SELECT * FROM EMPLOYEE WHERE EMPNO = ?
  

• At least one of the operands of the arithmetic and comparison operators, or of the BETWEEN and IN predicates, must not be a parameter marker.

  The following examples are invalid:

     SELECT * FROM EMPLOYEE WHERE SALARY > ? + ?
     SELECT * FROM EMPLOYEE WHERE ? = ?
     SELECT * FROM EMPLOYEE WHERE ? IN (?,?)
  

  The following examples are valid:

     SELECT * FROM EMPLOYEE WHERE SALARY > 20000 + ?
     SELECT * FROM EMPLOYEE WHERE SALARY = ?
     SELECT * FROM EMPLOYEE WHERE ? IN (?,?,20000)
  

• A parameter marker cannot be the sole argument of a scalar function. It, however, can be used in an arithmetic expression as long as the other parameter is a number.

  The following example is invalid:

     SELECT * FROM EMPLOYEE WHERE HIREDATE > DATE(?)
  

  The following example is valid:

     SELECT * FROM EMPLOYEE WHERE HIREDATE > DATE(14+?)
  

• A parameter marker cannot be used as the sole operand in an arithmetic expression that involves a datetime value.

  The following examples are invalid:

     SELECT * FROM EMPLOYEE WHERE HIREDATE = START_DATE + ?
     SELECT * FROM EMPLOYEE WHERE HIREDATE = 10000000. + ?
  

  The following example is valid:

     SELECT * FROM EMPLOYEE WHERE HIREDATE = HIREDATE + (1000000.+?)