Handling a parameterized SELECT statement

For an introduction on how to determine input parameters, see Determine unknown input parameters. This section describes how to handle a parameterized SELECT statement with a system-descriptor area. If a prepared SELECT statement has a WHERE clause with input parameters of unknown number and data type, your program must use a system-descriptor area to define the input parameters.

To use a system-descriptor area to define input parameters for a WHERE clause:

  1. Determine the number and data types of the input parameters of the SELECT statement. For more information, see Determine unknown input parameters.
  2. Allocate a system-descriptor area and assign it a name with the ALLOCATE DESCRIPTOR statement. For more information about ALLOCATE DESCRIPTOR, see Allocate memory for a system-descriptor area.
  3. Indicate the number of input parameters in the WHERE clause with the SET DESCRIPTOR statement, which sets the COUNT field.
  4. Store the definition and value of each input parameter with the SET DESCRIPTOR statement, which sets the DATA, TYPE, and LENGTH fields in the appropriate item descriptor:
    • The TYPE field must use the data type constants defined in the sqltypes.h header file to represent the data types of the input parameters. For more information, see Determine the data type of a column.
    • For a CHAR or VARCHAR value, LENGTH is the size, in bytes, of the character array; for a DATETIME or INTERVAL value, the LENGTH field stores the encoded qualifiers.
    Important: If you use X/Open code (and compile with the -xopen flag), you must use the X/Open data type values for the TYPE and ITYPE fields. For more information, see Determine the data type of a column.

    If you use an indicator variable, you also need to set the INDICATOR field and perhaps the IDATA, ILENGTH, and ITYPE fields (for non-X/Open applications only). Use the VALUE keyword of SET DESCRIPTOR to identify the item descriptor. For more information about SET DESCRIPTOR, see Assign and obtain values from a system-descriptor area.

  5. Pass the defined input parameters from the system-descriptor area to the database server with the USING SQL DESCRIPTOR clause.

    The statement that provides the input parameters depends on how many rows that the SELECT statement returns. The following sections discuss how to execute each type of SELECT statement.

  6. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement. For more information, see Free memory allocated to a system-descriptor area.
Important: If the SELECT statement has unknown columns in the select list, your program must also handle these columns with a system-descriptor area. For more information, see Handling an unknown select list.