Summary of Cursor Positioning

OpenSQL Reference Guide > OpenSQL Reference Guide > Embedded OpenSQL > Data Manipulation with Cursors > Summary of Cursor Positioning

Was this helpful?

The following table summarizes the effects of cursor statements on cursor positioning:

Statement	Effect on Cursor Position
open	Cursor positioned before first row in set.
fetch	Cursor moves to next row in set. If it is already on the last row, the cursor moves beyond the set and its position becomes undefined.
update(cursor)	Cursor remains on current row.
delete(cursor)	Cursor moves to a position after the deleted row (but before the following row).
close	Cursor and set of rows become undefined.

For extended examples of the use of cursors in embedded OpenSQL, see the Embedded SQL Companion Guide.

Example of Dynamically Specified Cursor Names

A dynamically specified cursor name (a cursor name specified using a host string variable) can be used to scan a table that contains rows that are related hierarchically, such as a table of employees and managers. In a relational database, this structure must be represented as a relationship between two columns. In an employee table, typically employees are assigned an ID number. One of the columns in the employee table contains the ID number of each employee's manager. This column establishes the relationships between employees and managers.

To use dynamically specified cursor names to scan this kind of table:

• Write a routine that uses a cursor to retrieve all the employees that work for a manager.

• Create a loop that calls this routine for each row that is retrieved and dynamically specifies the name of the cursor to be used by the routine.

The following example retrieves rows from the employee table that has the following format:

exec sql declare employee table
(ename varchar(32),
title varchar(20),
manager varchar(20));

This program scans the employee table and prints out all employees and the employees that they manage:

/* This program will print out, starting with
** the top manager,
** each manager and who they manage for the entire
** company. */

exec sql include sqlca;

/* main program */
exec sql begin declare section;
topmanager character string(21)
exec sql end declare section;

exec sql connect enterprise/db2udb;

exec sql whenever not found goto closedb;
exec sql whenever sqlerror call sqlprint;

/* Retrieve top manager */
exec sql select ename into :topmanager from employeewhere title = 'President';

print 'President', topmanager
call printorg(1, topmanager);
/* start with top manager */

/* closedb */
closedb:
exec sql disconnect;

/* This subroutine retrieves and displays employees
** who report to a given manager. This subroutine is
** called recursively to determine if a given
** employee is also a manager and if so,
** it will display who reports to them.
*/

subroutine printorg(level, manager)
level integer

exec sql begin declare section;
  manager character string(21)
  ename character string(33)
  title character string(21);
  cname character string(4);
exec sql end declare section;

/* set cursor name to 'c1', 'c2', ... */
cname = 'c' + level

exec sql declare :cname cursor for
select ename, title, manager from employee
  where manager = :manager
  order by ename;

exec sql whenever not found goto closec;

exec sql open :cname;

loop
  exec sql fetch :cname into :ename, :title,
:manager;

/* Print employee's name and title */
  print title, ename
/* Find out who (if anyone) reports to this employee*/
  printorg(level+1, ename);

end loop

closec:
exec sql close :cname;
return;

Last modified date: 11/28/2023