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 cursoror 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. A prepared SELECT statement can contain parameter markers (?) to indicate that data is to 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 datatype, 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;