Parameter markers

In the example used, the SELECT statement that was dynamically run had a constant value in the WHERE clause:

    WHERE LASTNAME = 'PARKER'

If you want to run the same SELECT statement several times, using different values for LASTNAME, you can use an SQL statement that looks like this:

    SELECT WORKDEPT, PHONENO 
        FROM CORPDATA.EMPLOYEE 
        WHERE LASTNAME = ?

When using parameter markers, your application does not need to set the data types and values for the parameters until run time. By specifying a descriptor on the OPEN statement, you can substitute the values for the parameter markers in the SELECT statement.

To code such a program, you need to use the OPEN statement with a descriptor clause. This SQL statement is used to not only open a cursor, but to replace each parameter marker with the value of the corresponding descriptor entry. The descriptor name that you specify with this statement must identify a descriptor that contains a valid definition of the values. This descriptor is not used to return information about data items that are part of a SELECT list. It provides information about values that are used to replace parameter markers in the SELECT statement. It gets this information from the application, which must be designed to place appropriate values into the fields of the descriptor. The descriptor is then ready to be used by SQL for replacing parameter markers with the actual values.

When you use an SQLDA for input to the OPEN statement with the USING DESCRIPTOR clause, not all of its fields need to be filled in. Specifically, SQLDAID, SQLRES, and SQLNAME can be left blank (SQLNAME can be set if a specific CCSID is needed.) Therefore, when you use this method for replacing parameter markers with values, you need to determine:

• How many parameter markers there are
• The data types and attributes of these parameters markers (SQLTYPE, SQLLEN, and SQLNAME)
• Whether an indicator variable is needed

In addition, if the routine is to handle both SELECT and non-SELECT statements, you might want to determine what category of statement it is.

If your application uses parameter markers, your program has to perform the following steps. This can be done using either an SQLDA or an allocated descriptor.

1. Read a statement into the DSTRING varying-length character string host variable.
2. Determine the number of parameter markers.
3. Allocate an SQLDA of that size or use ALLOCATE DESCRIPTOR to allocate a descriptor with that number of entries. This is not applicable in REXX.
4. For an SQLDA, set SQLN and SQLD to the number of parameter markers. SQLN is not applicable in REXX. For an allocated descriptor, use SET DESCRIPTOR to set the COUNT entry to the number of parameter markers.
5. For an SQLDA, set SQLDABC equal to SQLN*LENGTH(SQLVAR) + 16. This is not applicable in REXX.
6. For each parameter marker:
   1. Determine the data types, lengths, and indicators.
   2. For an SQLDA, set SQLTYPE and SQLLEN for each parameter marker. For an allocated descriptor, use SET DESCRIPTOR to set the entries for TYPE, LENGTH, PRECISION, and SCALE for each parameter marker.
   3. For an SQLDA, allocate storage to hold the input values.
   4. For an SQLDA, set these values in storage.
   5. For an SQLDA, set SQLDATA and SQLIND (if applicable) for each parameter marker. For an allocated descriptor, use SET DESCRIPTOR to set entries for DATA and INDICATOR (if applicable) for each parameter marker.
   6. If character variables are used and they have a CCSID other than the job default CCSID, or graphic variables are used and they have a CCSID other than the associated DBCS CCSID for the job CCSID,
      • For an SQLDA, set SQLNAME (SQLCCSID in REXX) accordingly.
      • For an allocated SQL descriptor, use SET DESCRIPTOR to set the DB2_CCSID value.
   7. Issue the OPEN statement with a USING DESCRIPTOR clause (for an SQLDA) or USING SQL DESCRIPTOR clause (for an allocated descriptor) to open your cursor and substitute values for each of the parameter markers.

The statement can then be processed normally.