Example: Using Client State for Data Paging

This examples demonstrates one approach to implementing a data paging mechanism in a web application, when it is not possible to use the offset option of the read statement.

This example implements a data paging mechanism that allows the user to:

Navigate through the set of data using First, Previous, and Next buttons. There is no Last button, because there is no reasonable way to calculate this without retrieving the entire data set, which is undesirable.
Specify the page size (in the PAGESIZE field). The page size is used to calculate the offset.

Component variables are used to maintain the page state information needed to navigate through data pages. The following component variables are used to hold the retrieve profile, sort order, and direction used in constructing the read statement and ordering the results:

$paging_direction$ —holds the navigation direction; "FIRST" | "NEXT" | "PREV"
$u_condition$ —retrieve profile for ID, which is the primary key
$order_by$ —sort order for ID; by default, it is ascending
$FirstOfPage$ and $LastOfPage$ —ID of the first and last records on the page, respectively

For simplicity, no user search profiles are included—the data is retrieved using the ID field (the primary key), and ordered by ID in ascending order. The ProcScript is also simplified.

Detail Triggers of Navigation Buttons

The detail trigger of each navigation button sets the calls the component variable $paging_direction$ and calls the lpRetrieve module. For example:

trigger detail ; of button FIRST
public web
$paging_direction$ = "FIRST"
end; detail

lpRetrieve Module

The lpRetrieve entry centralizes the processing required for retrieving each page of records. For each navigation button, it sets the retrieve profile, and ensures that the data is correctly ordered, before issuing the retrieve command which fires the read trigger of the entity.

entry lpRetrieve
  clear/e "<$entname>"
  selectcase $paging_direction$ ;"FIRST" | "NEXT" | "PREV"  
  case "FIRST"
    ; Retrieve first page of hits in ascending order
    $u_condition$ = ""              
    $order_by$ = "ITEMID"           
    retrieve/e "<$entname>"         
    setocc "<$entname>", -1         
  case "NEXT"
    ; Retrieve next page of hits after current page in ascending order
    setocc "<$entname>", -1         
    $LastOfPage$  = ITEMID          
    $u_condition$ = "(ITEMID > %%"%%$LastOfPage$%%%%%")" 
    $order_by$    = "ITEMID"         
    retrieve/e "<$entname>"         
    setocc "<$entname>", -1         
  case "PREV"
    ; Retrieve previous page of hits before current page in descending order 
    ; and then reverse order of hits
    setocc "<$entname>", 1          
    $FirstOfPage$ = ITEMID          
    $u_condition$ = "(ITEMID <  %%"%%$FirstOfPage$%%%%%")" 
    $order_by$    = "ITEMID desc"   
    retrieve/e "<$entname>"         
    setocc "<$entname>", -1         
    sort/e "<$entname>", "ITEMID:a" 
  endselectcase
  return 0
end

Set the retrieve profile for retrieving data, ...
...sorted in ascending order.
Retrieve the matching results (see read trigger).
Get all hits of page (see read trigger).
Go to the last hit of the current page...
...remember it.
Retrieve hits after the last hit of the current page...
Go to the first hit of current page...
...remember it.
Retrieve hits before the first hit of the current page
...in descending order
Reverse the order of hits for presentation

Read Trigger

The read trigger is fired in response to the retrieve and reconnect statements. In this example, the lpRetrieve ProcScript module contains the retrieve statements that fire the read trigger. The $paging_direction$ variable is set by the detail trigger of each navigation button, which then calls the lpRetrieve module.

trigger read 
  selectcase $paging_direction$ ; set by the detail trigger of navigation buttons
  case "FIRST", "NEXT", "PREV" 
    if ($u_condition$ = "") 
      read options "maxhits=%%PAGESIZE%%%" order by $order_by$ 
    else
      read options "maxhits=%%PAGESIZE%%%" u_condition ($u_condition$) order by $order_by$ 
    endif
  elsecase
    read  ; Used by the reconnect
  endselectcase
end; read

The read trigger is fired as a result of a navigation action
If retrieve profile is empty (for First page) …
… read the data and order it by the specified Order by profile, in this case ID ascending.
Otherwise, use the specified retrieve profile to read the data and sequence it according the Order by profile. In this case, the Order by profile may vary, depending on the paging direction.