USING Clause

The USING clause is required when the cursor is associated with a prepared statement that includes question-mark ( ? ) placeholders, as follows:
  • A SELECT statement with input parameters in its WHERE clause
  • An EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with input parameters as arguments to its user-defined function
  • An INSERT statement with input parameters in its VALUES clause (in ESQL/C).

In SPL routines, you must specify these parameters as SPL variables.

In ESQL/C, you can supply values for these parameters in one of the following ways:
  • You can specify one or more host variables.
  • You can specify a system-descriptor area.
  • You can specify a pointer to an sqlda structure.

For more information, see PREPARE statement.

If you know the number and the order of parameters to be supplied at runtime and their data types, you can define the parameters that are needed by the statement as host variables in your program. You pass parameters to the database server positionally, by opening the cursor with the USING keyword, followed by the names of the variables in their sequential order. These variables are matched with the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement question-mark ( ? ) placeholders in a one-to-one correspondence, from left to right.

You cannot include indicator variables of ESQL/C in the list of variables. To use an indicator variable, you must include the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement text as part of the DECLARE statement, rather than the identifier of a prepared statement.

You must supply one host variable name for each placeholder. The data type of each variable must be compatible with the corresponding type that the prepared statement requires. The following code fragment opens a Select cursor and specifies host variables in the USING clause: 
sprintf (select_1, "%s %s %s %s %s",
   "SELECT o.order_num, sum(total price)",
   "FROM orders o, items i",
   "WHERE o.order_date > ? AND o.customer_num = ?",
   "AND o.order_num = i.order_num",
   "GROUP BY o.order_num");
EXEC SQL prepare statement_1 from :select_1;
EXEC SQL declare q_curs cursor for statement_1;
EXEC SQL open q_curs using :o_date, :o.customer_num;
The following example illustrates the USING clause of the OPEN statement with an EXECUTE FUNCTION statement in the code fragment: 
stcopy ("EXECUTE FUNCTION one_func(?, ?)", exfunc_stmt);
EXEC SQL prepare exfunc_id from :exfunc_stmt;
EXEC SQL declare func_curs cursor for exfunc_id;
EXEC SQL open func_curs using :arg1, :arg2;