Scrolling through previously retrieved data

To scroll backward through data, use a scrollable cursor, or use a ROWID column or identity column to retrieve data in reverse order.

About this task

Question: When a program retrieves data from the database, how can the program scroll backward through the data?

Answer: Use one of the following techniques:

Use a scrollable cursor.
If the table contains a ROWID or an identity column, retrieve the values from that column into an array. Then use the ROWID or identity column values to retrieve the rows in reverse order.

Using a scrollable cursor: Using a scrollable cursor to fetch backward through data involves these basic steps:

Declare the cursor with the SCROLL keyword.
Open the cursor.
Execute a FETCH statement to position the cursor at the end of the result table.
In a loop, execute FETCH statements that move the cursor backward and then retrieve the data.
When you have retrieved all the data, close the cursor.

You can use code like the following example to retrieve department names in reverse order from table DSN8A10.DEPT:

/**************************/
/* Declare host variables */
/**************************/
EXEC SQL BEGIN DECLARE SECTION;
  char[37] hv_deptname;
EXEC SQL END DECLARE SECTION;
/**********************************************************/
/* Declare scrollable cursor to retrieve department names */
/**********************************************************/
EXEC SQL DECLARE C1 SCROLL CURSOR FOR
  SELECT DEPTNAME FROM DSN8A10.DEPT;
⋮
/**********************************************************/
/* Open the cursor and position it after the end of the   */
/* result table.                                          */
/**********************************************************/
EXEC SQL OPEN C1;
EXEC SQL FETCH AFTER FROM C1;
/**********************************************************/
/* Fetch rows backward until all rows are fetched.        */
/**********************************************************/
while(SQLCODE==0) {
  EXEC SQL FETCH PRIOR FROM C1 INTO :hv_deptname;
  ⋮
}
EXEC SQL CLOSE C1;

Using a ROWID or identity column: If your table contains a ROWID column or an identity column, you can use that column to rapidly retrieve the rows in reverse order. When you perform the original SELECT, you can store the ROWID or identity column value for each row you retrieve. Then, to retrieve the values in reverse order, you can execute SELECT statements with a WHERE clause that compares the ROWID or identity column value to each stored value.

For example, suppose you add ROWID column DEPTROWID to table DSN8A10.DEPT. You can use code like the following example to select all department names, then retrieve the names in reverse order:

/**************************/
/* Declare host variables */
/**************************/
EXEC SQL BEGIN DECLARE SECTION;
  SQL TYPE IS ROWID  hv_dept_rowid;
  char[37] hv_deptname;
EXEC SQL END DECLARE SECTION;
/***************************/
/* Declare other variables */
/***************************/
struct rowid_struct {
  short int length;
  char data[40];    /* ROWID variable structure */
}
struct rowid_struct rowid_array[200];
                              /* Array to hold retrieved  */
                              /* ROWIDs.  Assume no more  */
                              /* than 200 rows will be    */
                              /* retrieved.               */
short int i,j,n;
/***********************************************/
/* Declare cursor to retrieve department names */
/***********************************************/
EXEC SQL DECLARE C1 CURSOR FOR
  SELECT DEPTNAME, DEPTROWID FROM DSN8A10.DEPT;
⋮
/**********************************************************/
/* Retrieve the department name and ROWID from DEPT table */
/* and store the ROWID in an array.                       */
/**********************************************************/
EXEC SQL OPEN C1;
i=0;
while(SQLCODE==0) {
  EXEC SQL FETCH C1 INTO :hv_deptname, :hv_dept_rowid;
  rowid_array[i].length=hv_dept_rowid.length;
  for(j=0;j<hv_dept_rowid.length;j++)
    rowid_array[i].data[j]=hv_dept_rowid.data[j];
  i++;
}
EXEC SQL CLOSE C1;
n=i-1;                /* Get the number of array elements */
/**********************************************************/
/* Use the ROWID values to retrieve the department names  */
/* in reverse order.                                      */
/**********************************************************/
for(i=n;i>=0;i--) {
  hv_dept_rowid.length=rowid_array[i].length;
  for(j=0;j<hv_dept_rowid.length;j++)
    hv_dept_rowid.data[j]=rowid_array[i].data[j];
  EXEC SQL SELECT DEPTNAME INTO :hv_deptname
    FROM DSN8A10.DEPT
    WHERE DEPTROWID=:hv_dept_rowid;
}

Parent topic: Retrieving a set of rows by using a cursor