Skip to Content
Author's profile photo Ehud Nir

Paging records – Using SQL to get Chunks of Data

Abstract & Motivation

This example shows yet another way to allow paging behavior for our application. Here it is achieved using a Data Service that returns a subset of the data: For each Next / Previous press by the user we call the data service to get the relevant chunk of rows.

For example, for the input:

  • Start index: 6
  • Number of records: 5

The result is a list containing the rows 6 to 10.

One big advantage of this method: The application does not need to handle big amounts of data. The other benefit we see soon.

Related documents:

Paging records in the UI

Paging records in the UI – Coding our own Web Service to get Row Numbers

The application

First five records:


After pressing Next:


The Backend – SQL Example

Our data service consists of a simple SQL Query.

  • Input parameters:
    • @start – start index
    • @step – number of rows
  • Result: the requested subset of the Suppliers table.

Code specifics:

  • Step 1: The inner SELECT statement return the Suppliers data with an additional column called row, for the row number (1, 2, 3…).
  • Step 2: The outer SELECT filters part of Step 1 result, using the input parameters @start and @step against the row column.



1. Search for the relevant data service and add it to the model.

2. In case it is a JDBC Stored Procedure, as shown here:

  – Right click => Test Data Service.

  – Enter input value and press Test.

  – Press Add Fields to have the fields in the output port.


3. Add a Table View from the output of the service.

4. Add a Data Share element.

5. Connect the Data Share to the service, edit the link to “listen” to the “*get” event (we model its triggering in later steps).

6. Add two fields to the Data Share, index and step, as follows:


7. Define the field assigns in the Mapping link. Here we pass our SQL code the starting index and number of rows:


8. Go to the Layout Board and define the Actions for the “First” button:

  – ASSIGN to reset to the first chunk of records.

  – GET as a Custom Action to activate the data service (see in step 5 where we define the link activation event).


9. Define the “Previous” button: Subtract the step size from the current index:


10. Define the Enabled condition for the “Previous” button, adding the step size to the index:


11. In quite the same way, define the “Next” button:


That’s it. This solution involved:

  • A bit of SQL coding.
  • A super-simple VC model.

Benefit #2 (in addition to having No Big Data): Note how simple the modeling is comparing to the other paging solution:

  • The Filter Operator is gone, as filtering is done on the database.
  • Also gone is the Data Share.
  • Possibly an additional Web Service to get the row number to rely on is gone as well – we don’t need logic depending on row numbers here.

Related documents:

Paging records in the UI

Paging records in the UI – Coding our own Web Service to get Row Numbers

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.