The preceding example showed how the -b and -p options affect statements that use host variables with a cursor. The example’s only host variables were host output variables. The following code is an example that shows how the -b and -p options affect statements that use host input variables with a cursor in this case, a dynamic cursor named dyn_curs1.
The open statement in the following example contains a host input variable, min_price. The following sections describe how the -b and-p options affect the bindings of this host input variable.
#include <stdio.h>
long SQLCODE = 0;
void main()
{
int i = 0;
exec sql begin declare section;
CS_CHAR sql_string[200];
CS_FLOAT min_price;
CS_CHAR book_title[200];
exec sql end declare section;
exec sql connect "sa";
exec sql use pubs2;
strcpy(sql_string,
"select title from titles where price > ?");
exec sql prepare sel_stmt from :sql_string;
/* The options used to precompile a cursor's declaration
** control whether host variables persist in statements,
** such as OPEN, that use the cursor.
*/
exec sql declare dyn_curs1 cursor for sel_stmt;
min_price = 10.00;
/* If the declaration of dyn_curs1 was precompiled
** without -p, bind the OPEN statement's input variable
** (min_price) each time the statement repeats. Otherwise,
** bind only the first time, letting the binding persist
** until dyn_curs1 is deallocated.
*/
for (i = 10; i <= 21; ++i)
{ min_price = min_price + 1.00;
exec sql open dyn_curs1 using :min_price;
while (SQLCODE != 100)
{
exec sql fetch dyn_curs1 into :book_title;
if (SQLCODE != 100) printf("%s\n", book_title);
}
printf("\n");
exec sql close dyn_curs1;
}
exec sql deallocate cursor dyn_curs1;
exec sql disconnect CURRENT;
exit(0);
}
If you use -p but omit -b when precompiling the preceding example, the generated code binds min_price only once—the first time that the open statement executes. The binding persists because you used the -p option, which controls host input variables.
The binding for min_price persists throughout all subsequent iterations of the statement, until the program deallocates dyn_curs1. The binding persist even if your program closes dyn_curs1 and then reopens it.
