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.

When a program retrieves data from the database, it can scroll backward through the data by using one of the following techniques:

• Use a scrollable cursor to fetch backward through data by following these steps:
  1. Declare the cursor with the SCROLL keyword.
  2. Open the cursor.
  3. Execute a FETCH statement to position the cursor at the end of the result table.
  4. In a loop, execute FETCH statements that move the cursor backward and then retrieve the data.
  5. When you have retrieved all the data, close the cursor.
  For example, you can use code like the following example to retrieve department names in reverse order from table DSN8C10.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 DSN8C10.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;

• 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.
  You can use the ROWID column or identity 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 DSN8C10.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 DSN8C10.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 DSN8C10.DEPT
      WHERE DEPTROWID=:hv_dept_rowid;
  }