Declaring Cursors

To define a cursor for row-at-a-time retrieval, use the DECLARE CURSOR statement. You can declare a cursor in one of two ways: as a static cursor or as a dynamic cursor.

Using Static Cursors

For a static cursor, the complete SELECT statement is contained in the DECLARE CURSOR statement. The SELECT statement can contain host variables for input parameters. When the OPEN statement is performed on a cursor, the values of the input parameters for the host variable are read into the SELECT statement. You cannot specify host variables and SQLDA data structures in the OPEN statement for a static cursor because the input host variables are already identified in the DECLARE CURSOR statement.

This is an example of a static cursor:

EXEC SQL BEGIN DECLARE SECTION;
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
   DECLARE author_cursor CURSOR FOR
   SELECT au_fname FROM authors WHERE au_lname = :szLastName;

EXEC SQL OPEN author_cursor;
EXEC SQL FETCH author_cursor INTO :szFirstName;

Using Dynamic Cursors

For a dynamic cursor, the SELECT statement is not contained in the DECLARE CURSOR statement. Instead, the DECLARE CURSOR statement references the name of a prepared SELECT statement that can contain parameter markers (?) to indicate that data must be supplied when a cursor is opened. You must declare a dynamic cursor by using the DECLARE CURSOR statement before you prepare a SELECT statement.

When a prepared SELECT statement contains parameter markers, the corresponding OPEN statement must specify the host variables or the name of the SQLDA data structure that will supply the values for the parameter markers. The data type, length, and address fields of the specified SQLDA data structure must already contain valid data.

This is an example of a dynamic cursor:

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
   DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
   PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;