These operations are performed using a special data object called a cursor. A cursor is a data structure that represents the current state of a query. The following list shows the general sequence of program operations:

1. The program declares the cursor and its associated SELECT statement, which merely allocates storage to hold the cursor.

2. The program opens the cursor, which starts the execution of the associated SELECT statement and detects any errors in it.

3. The program fetches a row of data into host variables and processes it.

4. The program closes the cursor after the last row is fetched.

5. When the cursor is no longer needed, the program frees the cursor to deallocate the resources it uses.

Declaring a Cursor

EXEC SQL DECLARE the_item CURSOR FOR

	SELECT order_num, item_num, stock_num

	INTO o_num, i_num, s_num

	FROM items
	FOR READ ONLY;

The SELECT statement in this example contains an INTO clause. The INTO clause specifies which variables receive data. You can also specify which variables receive data by using the FETCH statement as discussed in "Locating the INTO Clause".

The DECLARE statement is not an active statement; it merely establishes the features of the cursor and allocates storage for it. You can use the cursor declared in the preceding example to read once through the items table. Cursors can be declared to read backward and forward (see "Cursor Input Modes"). This cursor, because it lacks a FOR UPDATE clause and because it is designated FOR READ ONLY, is used only to read data, not to modify it. (The use of cursors to modify data is covered in Chapter 6, "Modifying Data Through SQL Programs.")

Opening a Cursor

EXEC SQL OPEN the_item;

If SQLSTATE is equal to 00000, or SQLCODE contains a zero, the SELECT statement is syntactically valid, and the cursor is ready for use. At this point, however, the program does not know if the cursor can produce any rows.

Fetching Rows

EXEC SQL DECLARE the_item CURSOR FOR

	SELECT order_num, item_num, stock_num

		INTO :o_num, :i_num, :s_num

		FROM items;

EXEC SQL OPEN the_item;

while(SQLCODE == 0)

{

	EXEC SQL FETCH the_item;

	if(SQLCODE == 0)

		printf("%d, %d, %d", o_num, i_num, s_num);

}

EXEC SQL DECLARE the_item CURSOR FOR

	SELECT order_num, item_num, stock_num

	INTO :o_num, :i_num, :s_num 

	FROM items;

EXEC SQL OPEN the_item;

if(SQLCODE == 0)

	EXEC SQL FETCH the_item;        /* fetch 1st row

while(SQLCODE == 0)

{

	printf("%d, %d, %d", o_num, i_num, s_num);

	EXEC SQL FETCH the_item;

}

Locating the INTO Clause

EXEC SQL DECLARE the_item CURSOR FOR

	SELECT order_num, item_num, stock_num

		FROM items;

EXEC SQL OPEN the_item;   

while(SQLCODE == 0)   

{

	EXEC SQL FETCH the_item INTO :o_num, :i_num, :s_num;

	if(SQLCODE == 0)

		printf("%d, %d, %d", o_num, i_num, s_num);

}

Cursor Input Modes

EXEC SQL declare pcurs cursor for

	select customer_num, lname, city

		from customer;

EXEC SQL fetch p_curs into :cnum, :clname, :ccity;

A scroll cursor is declared with the keywords SCROLL CURSOR, as the following example from INFORMIX-ESQL/C shows:

	EXEC SQL DECLARE s_curs SCROLL CURSOR FOR

   		SELECT order_num, order_date FROM orders

   			WHERE customer_num > 104

	EXEC SQL FETCH ABSOLUTE :numrow s_curs

  		INTO :nordr, :nodat

The Active Set of a Cursor

Creating the Active Set

EXEC SQL DECLARE easy CURSOR FOR

	SELECT fname, lname FROM customer

		WHERE state = 'NJ'

EXEC SQL DECLARE hard SCROLL CURSOR FOR

	SELECT C.customer_num, O.order_num, sum (items.total_price)

		FROM customer C, orders O, items I

		WHERE C.customer_num = O.customer_num

			AND O.order_num = I.order_num

			AND O.paid_date is null

		GROUP BY C.customer_num, O.order_num 

In cases where the active set is entirely generated and saved in a temporary table, the database server can take quite some time to open the cursor. Afterward, it can tell the program exactly how many rows the active set contains. This information is not made available, however. One reason is that you can never be sure which method the optimizer uses. If the optimizer can avoid sorts and temporary tables, it does; but very small changes in the query, in the sizes of the tables, or in the available indexes can change its methods.

The Active Set for a Sequential Cursor

The Active Set for a Scroll Cursor

Most frequently, the database server implements the active set of a scroll cursor as a temporary table. The database server might not fill this table immediately, however (unless it created a temporary table to process the query). Usually it creates the temporary table when the cursor is opened. Then, the first time a row is fetched, the database server copies it into the temporary table and returns it to the program. When a row is fetched for a second time, it can be taken from the temporary table. This scheme uses the fewest resources in the event that the program abandons the query before it fetches all the rows. Rows that are never fetched are not created or saved.

The Active Set and Concurrency

When other programs can update the tables while your cursor is open, the idea of the active set becomes less useful. Your program can see only one row of data at a time, but all other rows in the table can be changing.

In the case of a simple query, when the database server holds only one row of the active set, any other row can change. The instant after your program fetches a row, another program can delete the same row or update it so that if it is examined again, it is no longer part of the active set.

When the active set, or part of it, is saved in a temporary table, stale data can present a problem. That is, the rows in the actual tables, from which the active-set rows are derived, can change. If they do, some of the active-set rows no longer reflect the current table contents.

These ideas seems unsettling at first, but as long as your program only reads the data, stale data does not exist, or rather, all data is equally stale. The active set is a snapshot of the data as it is at one moment in time. A row is different the next day; it does not matter if it is also different in the next millisecond. To put it another way, no practical difference exists between changes that occur while the program is running and changes that are saved and applied the instant that the program terminates.

The only time that stale data can cause a problem is when the program intends to use the input data to modify the same database; for example, when a banking application must read an account balance, change it, and write it back. Chapter 6, "Modifying Data Through SQL Programs," discusses programs that modify data.

Using a Cursor: A Parts Explosion

The problem is usually stated in terms of a manufacturing inventory. A company makes a variety of parts, for example. Some parts are discrete, but some are assemblages of other parts.

These relationships are documented in a single table, which might be called contains. The column contains.parent holds the part numbers of parts that are assemblages. The column contains.child has the part number of a part that is a component of the parent. If part #123400 is an assembly of nine parts, nine rows exist with 123400 in the first column and other part numbers in the second.

Figure 5-5 Parts-Explosion Problem

Figure 5-5 shows one of the rows that describe part #123400.

Here is the parts-explosion problem: given a part number, produce a list of all parts that are components of that part. The following is a sketch of one solution, as implemented in INFORMIX-ESQL/C:

int part_list[200];



boom(top_part)

int top_part;

{

	long this_part, child_part;

	int next_to_do = 0, next_free = 1;

	part_list[next_to_do] = top_part;



	EXEC SQL DECLARE part_scan CURSOR FOR

		SELECT child INTO child_part FROM contains

			WHERE parent = this_part;

	while(next_to_do < next_free)

	{

		this_part = part_list[next_to_do];

		EXEC SQL OPEN part_scan;

		while(SQLCODE == 0)

		{

			EXEC SQL FETCH part_scan;

			if(SQLCODE == 0)	

			{

				part_list[next_free] = child_part;

				next_free += 1;

			}

		}

		EXEC SQL CLOSE part_scan;

		next_to_do += 1;

	}

	return (next_free - 1);

}

This function addresses the heart of the parts-explosion problem, but the function is not a complete solution. For example, it does not allow for components that appear at more than one level in the tree. Furthermore, a practical contains table would also have a column count, giving the count of child parts used in each parent. A program that returns a total count of each component part is much more complicated.

The iterative approach described earlier is not the only way to approach the parts-explosion problem. If the number of generations has a fixed limit, you can solve the problem with a single SELECT statement using nested, outer self-joins.

If up to four generations of parts can be contained within one top-level part, the following SELECT statement returns all of them:

SELECT a.parent, a.child, b.child, c.child, d.child

	FROM contains a

		OUTER (contains b,

			OUTER (contains c, outer contains d))

	WHERE a.parent = top_part_number

		AND a.child = b.parent

		AND b.child = c.parent

		AND c.child = d.parent