ID Number: Q66749
1.10 1.11 4.20
OS/2
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.
Additional reference words: Embedded SQL for COBOL