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 of its user-defined function
• An INSERT statement with input parameters in its VALUES clause

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.

If you know the number 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 by opening the cursor with the USING keyword, followed by the names of the variables. These variables are matched with the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement question-mark ( ? ) parameters in a one-to-one correspondence, from left to right.

You cannot include indicator variables in the list of variable names. To use an indicator variable, you must include the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement as part of the DECLARE 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 ESQL/C 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 an ESQL/C 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;

In Extended Parallel Server, to re-create this example use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.