Determine unknown input parameters

If you know the data types and number of input parameters of an SQL statement, use the USING host_var clause (see Execute statements with input parameters). However, if you do not know the data types and number of these input parameters at compile time, you cannot use host variables to provide the parameter values; you do not have enough information about the parameters to declare the host variables.

Neither can you use the DESCRIBE statement to define the unknown parameters because DESCRIBE does not examine:
  • A WHERE clause (for a SELECT, UPDATE, or DELETE statement
  • The arguments of a user-defined routine (for an EXECUTE FUNCTION or EXECUTE PROCEDURE statement)
Your program must follow these steps to define the input parameters in any of the preceding statements:
  1. Determine the number and data types of the input parameters. Unless you write a general-purpose, interactive interpreter, you usually have this information. If you do not have it, you must write C code that analyzes the statement string and obtains the following information:
    • The number of input parameters [question marks (?)] that appear in the WHERE clause of the statement string or as arguments of a user-defined routine
    • The data type of each input parameter based on the column (for WHERE clauses) or parameter (for arguments) to which it corresponds
  2. Store the definitions and values of the input parameters in a dynamic-management structure. This structure can be either a system-descriptor area or an sqlda structure.

    Use of the system-descriptor area conforms to X/Open standards.

  3. Specify the dynamic-management structure as the location of the input parameter values when the statement executes.
Important: Use a dynamic-management structure only if you do not know the number and data types of the input parameters at compile time. For information about how to execute a parameterized SQL statement if you do know the number and data types of column-list columns, see Execute statements with input parameters.

For information about how to handle input parameters in the WHERE clause of a dynamic SELECT statement with a system-descriptor area, see Handling a parameterized SELECT statement; to use an sqlda structure, see Handling a parameterized SELECT statement. For information about how to handle input parameters as arguments of a user-defined function with a system-descriptor area, see Handling a parameterized user-defined routine; to use an sqlda structure, see Handling a parameterized user-defined routine. For information about how to handle input parameters in the WHERE clause of a dynamic UPDATE or DELETE statement with a system-descriptor area, see Handling a parameterized UPDATE or DELETE statement; to use an sqlda structure, see Handling a parameterized UPDATE or DELETE statement.