Example: Using Database Data Paging

This examples demonstrates the use of the read statement's offset option and other arguments to implement a data paging mechanism in a web application.

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

  • Navigate through the set of data using First, Previous, Next, and Last buttons.

  • Specify the page size (in the PAGESIZE field). The page size is used to calculate the offset.

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.

Detail Triggers

The detail trigger of each navigation button sets the value of the PAGENUM.CONTROL field and calls the lpRetrieve ProcScript module. 

trigger detail ; of button First
public web
PAGENUM.CONTROL = 0 Callout 1.1
call lpRetrieve
end; detail
trigger detail ; of button Next
public web
PAGENUM.CONTROL = PAGENUM.CONTROL+1 Callout 1.2
call lpRetrieve
end; detail
trigger detail ; of button Prev
public web
PAGENUM.CONTROL = PAGENUM.CONTROL -1 Callout 1.3
call lpRetrieve
end; detail
trigger detail ; button Last
public web
PAGENUM.CONTROL = -1 Callout 1.4
retrieve/e "<$entname>
end; detail
  1.  For the FIRST button, the page number is set to 0
  2.  For the NEXT button, the page number is incremented by 1
  3.  For the PREV button, the page number is decreased by 1
  4.  For the LAST button, the page number is set to -1. The total number of pages is unknown, so -1 indicates the last page.

lpRetrieve

The lpRetrieve module centralizes the retrieve action for all buttons, and provides extra processing for the last page.

entry lRetrieve
variables
  numeric vTotalRec
endvariables

; If the last page is requested, calculate the page number
if (PAGENUM.CONTROL = -1)  Callout 2.1
  ;Calculate the total number of pages 
  selectdb count(ID) from "<$entname>"  to vTotalRec Callout 2.2
  vTotalRec = vTotalRec / PAGESIZE.CONTROL Callout 2.3
  ; Set the page number 
  vTotalRec -= 1
  PAGENUM.CONTROL = vTotalRec[trunc]
endif
;retrieve the data, thereby executing the read trigger
clear/e "<$entname>"
retrieve/e "<$entname>" Callout 2.4

end ;lRetrieve
  1.  If the last page has been requested ($vPageNum$ = -1), …
  2.  … use selectdb to count the number of records …
  3.  … and calculate what the actual page number is. This is needed to go to the previous page. Rounding the number ensures that the number is an integer.
  4.  … but can result in a last page that is too high. If this is the case, decrease the page number by 1.
  5.  Retrieve the data, thereby firing the Read trigger of the entity.

Read trigger

The entity's read trigger is fired by retrieve statement, located in the local ProcScript module lpRetrieve.

;READ trigger 
variables
  numeric vOffset
endvariables

 vOffset = $vPageNum$ * PAGESIZE.CONTROL Callout 3.1
 read options "maxhits=%%PAGESIZE.CONTROL%%%·;offset=%%vOffset%%%" order by ID Callout 1.1
  1.  Calculate the required offset by multiplying the page number by the page size.
  2.  Read the data, ordering it in ascending order by ID.

Related Topics