Using a Cursor (Multiple-Row Operation)

Retrieving Multiple Rows

Declaring a Cursor

Opening a Cursor

Using a Cursor to Retrieve Data

Deleting Data with a Cursor

Updating Data with a Cursor

Closing the Cursor

Using a Cursor (Multiple-Row Operation)

Retrieving Multiple Rows

When a query may return more than one row, the program must execute the query differently. Multiple-row queries are handled in two stages. First, the program starts the query. (No data is returned immediately.) Then, the program requests the rows of data one at a time via a cursor.

A cursor, as used in an application program, is a data selector that can operate on specified rows from the database. The following list is the general sequence for using a cursor.

Declare the cursor and its associated SELECT statement. This merely allocates storage to hold the cursor.
Open the cursor. This starts the execution of the associated SELECT statement. It actually contains three substeps, namely parse the SQL statements, bind the host variables, and begin execution of the statement.
Fetch (or delete, update) a row of data into host variables and process it. Repeat this step until all rows are fetched.
Close the cursor.

These operations are performed with the DECLARE, OPEN, FETCH, and CLOSE statements.

Declaring a Cursor

A new cursor must be declared for each SELECT command within an application program, except for those that retrieve a single row.

The syntax for DECLARE is:

 EXEC SQL DECLARE cursor_name [SCROLL] CURSOR FOR select_statement  
         [INTO :output_host_var :indicator_var [, :output_host_var :indicator_var]]

The INTO host_variable clause can also be defined in the FETCH statement. If you want to use all available methods of fetching data (i.e., FETCH NEXT, PREVIOUS, LAST, FIRST, ABSOLUTE, and RELATIVE), you must specify SCROLL when declaring a cursor.

If you would like to fetch more than one rows in a single FETCH statement, you must also specify the SCROLL keyword when declaring the cursor.

The following is an example of a SQL command for declaring a cursor:

EXEC SQL DECLARE vendCursor CURSOR FOR  
         SELECT vendorName FROM vendors     
         WHERE vendorNumber = :inputNo;

Or you can put the INTO clause in the DECLARE statement. In this case, the INTO clause can be omitted in the FETCH statement.

EXEC SQL DECLARE vendCursor CURSOR FOR  
         SELECT vendorName FROM vendors     
         WHERE vendorNumber = :inputNo    
         INTO :vendName;

Opening a Cursor

A cursor must be in an open state to operate on the contents of the specified rows. The OPEN command is followed by the name of the cursor given in the DECLARE command.

The syntax for OPEN is:

EXEC SQL OPEN    cursor_name 
       [USING :input_host_var [:indicator_var] 
           [, :input_host_var [:indicator_var]]]

The following is an example of an SQL command for opening a cursor:

EXEC SQL OPEN vendCursor;

When the OPEN command is executed, the cursor finds and points to the first row of the result set that satisfies the search condition. If there are input host variables in the cursor, you must specify the values for all input host variables before or in the OPEN cursor statement. In the above case, because the input host variables have been defined in the DECLARE statement, there's no need to specify the input host variables again in the OPEN cursor statement.

Using a Cursor to Retrieve Data

A cursor retrieves data by using the FETCH command. In a loop, FETCH advances the cursor in the result set and retrieves the current row, copying the values of the columns specified in the SELECT list into the host variables designated by the INTO clause.

The syntax for FETCH is:

EXEC SQL FETCH [NEXT | PREVIOUS | FIRST | LAST | ABSOLUTE nth_position
    | RELATIVE nth_position] [num_rows ROWS] cursor_name  
   [INTO :input_host_var [:indicator_var] 
   [, :input_host_var [:indicator_var], ...]]

where nth_position and num_rows can be a host variable or a constant integer.

NOTE: PREVIOUS, FIRST, LAST, ABSOLUTE nth_position, RELATIVE nth_position and num_rows ROWS are available only with cursors defined with cursors defined with the SCROLL option.

Command Parameters

Returns the next row within the results set. NEXT is the default cursor fetch.

For example:

EXEC SQL FETCH cur1; /* default is    fetch next */ 
EXEC SQL FETCH NEXT cur1 INTO :c1, :c2;

Returns the previous row within the results set.

For example:

EXEC SQL FETCH PREVIOUS cur1 INTO :c1,    :c2;

FIRST:

Moves the cursor to the first row within the result set and returns the first row.

For example:

EXEC SQL FETCH FIRST cur1 INTO :c1, :c2;

LAST:

Moves the cursor to the last row within the results set and returns the last row.

For example:

EXEC SQL FETCH LAST cur1 INTO :c1, :c2;

ABSOLUTE n:

Returns the nth row within the results set. If n is a negative value, the returned row will be the nth row counting backward from the last row of the results set.

For example:

n = 10; 
EXEC SQL FETCH ABSOLUTE :n cur1 INTO :c1, :c2;

RELATIVE n:

Returns the nth row after the currently fetched row. If n is a negative value, the returned row will be the nth row counting backward from the relative position of the cursor.

For example:

n = 10; 
EXEC SQL FETCH RELATIVE :n cur1 INTO :c1, :c2;

The following is an example of an SQL command for fetching results from a cursor:

EXEC SQL FETCH vendCursor INTO :vendName;

You must use a FETCH command for each row to be retrieved.

After all rows in the result set have been retrieved, DBMaker sets the SQLCODE field of SQLCA to the value SQL_NO_DATA_FOUND to indicate that no more rows are found. Indicated variables can be declared with the host variables to detect null values.

FETCH Multiple rows in a FETCH statement:
:num_rows ROW: When you specified this syntax in FETCH statement INTO arrayed host variable, you can retrieve more than one data values in a FETCH statement.

EXEC SQL BEGIN DECLARE SETION;    
int nrows; 
int host1[50]; 
int host2[50]; 
char host3[50][100]; /* 50: means the maximum available fetched data values, 100: means
 the maximum data buffer length of each element. */ 
EXEC SQL END DECLARE SECTION;    
EXEC SQL DECLARE myCur SCROLL CURSOR FOR SELECT c1,c2,c3 FROM table1 INTO :host1, :host2,
 :host3; 
EXEC SQL OPEN myCur; 
nrows = 50; 
/* loop until no data found */    
while (SQLCODE != SQL_NO_DATA_FOUND)    
   {  
    EXEC SQL FETCH :nrows ROWS myCur; /* This will fetch 50 rows into host1, host2, host3,
     because the maximum fetched  */ 
    for (j = 0; j < sqlca.sqlerrd[3]; j++) /* print out according to the number of returned
     rows */ 
    printout(host1, host2, host3);    
   } 
EXEC SQL CLOSE myCur;

Deleting Data with a Cursor

With a cursor, you can select one row at a time to be deleted from a table. Any additional rows must be fetched individually for deletion.

To delete a row, declare the cursor with a SELECT command. Then, use the OPEN and FETCH commands to open the cursor and position it on the row you want to delete. Finally, execute the DELETE command. To delete another row, you must reposition the cursor with another FETCH command.

EXEC SQL DELETE FROM supplier WHERE CURRENT OF vendCursor;

Do not use the COMMIT WORK command between consecutive deletions. Executing this command implicitly closes the cursor and terminates the deletion process. Working with autocommit mode on will have the same results.

Turn autocommit mode off before using a DELETE or UPDATE WHERE CURRENT OF cursor statement. For information on how to set autocommit mode, please refer to "Transaction processing" in Chapter 5.

Updating Data with a Cursor

You can use a cursor to select one row at a time to update. You must reposition the cursor to update additional rows.

To update a row, declare the cursor with a SELECT command. Use the OPEN and FETCH commands to open the cursor and position it on the row you want to update. Finally, execute the UPDATE command. To update another row, you must reposition the cursor with another FETCH command.

EXEC SQL UPDATE supplier SET price = price + 10 
          WHERE CURRENT OF vendCursor;

Do not use the COMMIT WORK command between consecutive updates, and make sure autocommit mode is off. Either of these will close the cursor and terminate the update process.

Closing the Cursor

COMMIT WORK and ROLLBACK WORK commands will implicitly close a cursor. You can explicitly close a cursor with the CLOSE CURSOR command. When you no longer need a cursor, you should close it to free any allocated resources.

The syntax for CLOSE CURSOR is:

EXEC SQL CLOSE cursor_name

The following is an example of an SQL command for closing a cursor:

EXEC SQL CLOSE vendCursor;

The cursor is dropped and cannot be used (opened or fetched) again.

Using a Cursor (Multiple-Row Operation)

Retrieving Multiple Rows

Declaring a Cursor

Opening a Cursor

Using a Cursor to Retrieve Data

Command Parameters

NEXT:

PREVIOUS:

FIRST:

LAST:

ABSOLUTE n:

RELATIVE n:

Deleting Data with a Cursor

Updating Data with a Cursor

Closing the Cursor