Before your program can use a cursor, you must declare it with the declare cursor command. A cursor’s declaration governs the binding behavior of all statements that use the cursor—in all source files of your program. The reason for this control is that the command structure for a cursor’s declaration is shared by all statements that use the cursor.
When a statement uses a cursor, the cursor’s declaration—not the statement using the cursor—controls how long the statement’s bindings persist. The bindings persist only if you use the -b and -p options when precompiling the file that declares the cursor. If you use these options, all statements that use the cursor have persistent bindings as specified by the options.
Strictly speaking, a cursor’s declaration controls binding behavior only if the cursor is a dynamic cursor—a cursor for a dynamic SQL statement. In cursors for all other SQL statements (static cursors), the statement that most recently opened the cursor (open cursor) controls the binding behavior, not the statement that declares the cursor.
For a static cursor, the generated code for open cursor both
declares and opens the cursor. For a dynamic cursor, the generated
code for open cursor only opens the
cursor.
Except for this difference, the binding rules for static cursors and dynamic cursors are the same. Unless you use a particular cursor in more than one source file of your program, the binding behavior of static cursors and dynamic cursors is the same.
In statements that use a cursor, bindings never persist after the cursor is deallocated, even if you use persistent binding. Also, deallocated cursors cannot be reopened. Declaring a new cursor with the name of a deallocated cursor does not reopen the deallocated cursor, nor does it retain bindings associated with that cursor. For more information, refer to the description of the deallocate cursor command in Chapter 10, “Embedded SQL Statements: Reference Pages.”
The following example shows how the -b and -p options affect a cursor—in this example, curs1. The fetch statement in the example contains host variables. The paragraphs following the example describes how the -b and-p options affect the bindings of these host variables.
#include <stdio.h>
int SQLCODE;
void
main()
{
exec sql begin declare section;
char title[100], pub_id[8];
exec sql end declare section;
exec sql connect “sa”;
exec sql use pubs2;
/*
** The options used to precompile a cursor’s declaration
** control whether host variables persist in statements,
** such as FETCH, that use the cursor.
*/
exec sql declare curs1 cursor for select title, pub_id from
titles;
exec sql open curs1;
while (SQLCODE == 0)
{
/* If the declaration of curs1 was precompiled without
** the -b option, rebind the FETCH statement’s variables
** each time the statement repeats. Otherwise, bind only
** the first time, and let the bindings persist for
** subsequent repetitions.
*/
exec sql fetch curs1 into :title, :pub_id;
printf(“%s, %s\n”, title, pub_id);
}
/* If the declaration of curs1 was precompiled without
** the -p option, cancel the bindings of the FETCH
** statement’s variables when curs1 is closed.
** Otherwise, let the bindings persist until the
** program deallocates curs1 or, as here, until the
** program ends.
*/
exec sql close curs1;
exec sql disconnect CURRENT;
exit(0);
