INF: Using Cursors to Return Results in Embedded SQL for COBOL

  - Microsoft Embedded SQL version 4.2 for COBOL

SUMMARY

The code listed below demonstrates how to use a cursor to return multiple row results from Microsoft's SQL Server in an Embedded SQL for COBOL program. The code selects all author names from the author's table in pubs.

For more information on this topic, see the "Microsoft Embedded SQL for COBOL Programmer's Reference."

MORE INFORMATION

The basic structure is as follows:

1. Declare the cursor with the SQL SELECT statement.

2. Open the cursor.

3. Fetch and display each row until you receive an error (sqlcode < 0), or all rows have been returned (sqlcode = 100).

4. Close the cursor.

Sample Code

       WORKING-STORAGE SECTION.

       EXEC SQL INCLUDE SQLCA END-EXEC

       EXEC SQL BEGIN DECLARE SECTION END-EXEC
       01  first-name  pic x(40).                *> authors.au_fname
       01  last-name   pic x(20).                *> authors.au_lname
       01  prep        pic x(38).
       EXEC SQL END DECLARE SECTION END-EXEC

       PROCEDURE DIVISION.

      * Declare the cursor, open the cursor, fetch results, and *
      * close the cursor. *
       EXEC SQL
         declare cursor-select cursor for
           select au_fname, au_lname from authors
       END-EXEC
       if sqlcode not = 0
         perform sql-error
       else
         EXEC SQL
           open cursor-select
         END-EXEC
         if sqlcode not = 0
           perform sql-error
         else
           display "The authors in the pubs..authors table are:"
      * When no more rows are returned, sqlcode = 100. *
           perform fetch-rows until sqlcode < 0 or sqlcode = 100
           EXEC SQL
             close cursor-select
           END-EXEC
           if sqlcode not = 0
             perform sql-error
           end-if
         end-if
       end-if
       stop run.

       fetch-rows.
      * Fetch the next row from the table and display it. *
       EXEC SQL
         fetch cursor-select into :first-name, :last-name
       END-EXEC
       if sqlcode = 0
         display first-name last-name
       else
         if sqlcode not = 100           *> 100 when no more rows
           perform sql-error
         end-if
       end-if.

       sql-error.
         display "SQL error SQLCODE=" sqlcode.