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 – 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.
Modeling
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 – Coding our own Web Service to get Row Numbers