Declaring a Cursor

Before a cursor can be used, it must be declared. This is done using the DECLARE CURSOR statement in which you specify a name for the cursor and either a SELECT statement or the name of a prepared SQL statement.

Cursor names must conform to the rules for identifiers on the database that you are connecting to, for example, some databases do not allow hyphens in cursor names.

 EXEC SQL
     DECLARE Cur1 CURSOR FOR
      SELECT first_name FROM employee 
       WHERE last_name = :last-name
 END-EXEC

This example specifies a SELECT statement using an input host variable (:last-name). When the cursor OPEN statement is executed, the values of the input host variable are read and the SELECT statement is executed.

 EXEC SQL
     DECLARE Cur2 CURSOR FOR stmt1
 END-EXEC
      . . .
     move "SELECT first_name FROM emp " &
          "WHERE last_name=?" to prep.
     EXEC SQL
        PREPARE stmt1 FROM :prep
     END-EXEC
      . . .
     EXEC SQL
        OPEN Cur2 USING :last-name
     END-EXEC

In this example, the DECLARE CURSOR statement references a prepared statement (stmt1). A prepared SELECT statement can contain question marks (?) which act as parameter markers to indicate that data is to be supplied when the cursor is opened. The cursor must be declared before the statement is prepared.

Within an Object Oriented (OO) program, you can declare a cursor anywhere that it is valid to declare a data item. Cursors are local to the object that they are opened in, that is, two instances of an object opening the "same" cursor each get their own cursor instance. You can open a cursor in one method, fetch it in a second and close it in a third but it must be declared in the Object-Storage Section if you want to do this.

COBSQL

A cursor can be declared in either the Data Division or the Procedure Division of your program. The DECLARE CURSOR statement does not generate any code but if a cursor is declared within the Procedure Division, COBSQL generates a debug breakpoint for the DECLARE CURSOR statement.

Note:
  • Object oriented COBOL syntax is not supported by COBSQL.
  • Some versions of the Informix precompiler can generate incorrect code if a cursor is declared within the Working-Storage Section. We therefore recommend that you only declare cursors in the Procedure Division when using INFORMIX.