Open Web Studio supports Custom Paging, which enables the developer to  control how the system handles paging, over-riding the default paging handler. The benefit of this – on very large sets of data, or complex long running queries is that the SQL script selects only the information from the database that you require.  For instance, if you want only the records that occur in order from rows 75 to 100, because you are paging 25 records per page and are on page 4 of 10,000 – requesting only page 4 from the database would be useful.

In order to implement your own custom paging your must adhere to the following specifications:

1. The query must utilize the current page number, as well as the page size variables.
   • [PAGENUMBER,System] - Is replaced with the current page number
   • [PAGESIZE,System] - Is replaced with the current page size
2. Return a second table with one row, one column which contains a value representing the total number of records available. The first table result will be your view data for the current page, while the second selection identifies the total number of records, which is used to identify the total number of pages.

Provided here are two simple examples, one for Oracle, the other for SQL Server 2005. In both scenarios, the request returns two tables. First – the results for the current page, and second – the total number of records with the same applied filter. You can easily perform the same requests on your own.

Oracle with Custom Paging

As discussed in a previous chapter regarding the GO statement. The Oracle OLEDB connection  handler doesn’t support the GO statement  which is used to separate queries from one another.  Additionally, Oracle doesn’t support  returning more than one table result from your query. To correct this, Open Web Studio has been improved to handle both the syntax check for GO as well as custom handling of the multiple table result return.

Note that in the scenario for Oracle as well as SQL 2005, the query is fundamentally the same. Essentially, you populate the PAGENUMBER, PAGESIZE and other tags like SORTTAG directly into your query, allowing Open Web Studio to handle the rest.

SELECT ordered.*
FROM (
        SELECT  ROWNUM as RowPosition,original.*
        FROM (
            SELECT  *
            FROM YOUR_TABLE 
            ORDER BY  [SORTTAG,YOUR_DEFAULT_SORT_COLUMN_NAME]   
        ) original
    ) ordered
WHERE
    RowPosition BETWEEN
        (([PAGENUMBER,System]-1) * [PAGESIZE,System]) + 1
        and
        ( [PAGENUMBER,System]    * [PAGESIZE,System])

GO

SELECT count(*)  FROM YOUR_TABLE 

SQL 2005 with Custom Paging

The previous solution for Oracle is very similar to that of SQL 2005. With 2005 comes the ability to execute “Windowed Functions”. These functions structures provide the means to perform paged queries. Compare the following SQL syntax to that of Oracle to see the similarities.

WITH ordered AS
(
SELECT *,
ROW_NUMBER()
OVER (order by [SORTTAG,YOUR_DEFAULT_SORT_COLUMN_NAME] ) as RowNumber
FROM YOUR_TABLE
)
SELECT
*
FROM
ordered
WHERE
    RowNumber BETWEEN
        (([PAGENUMBER,System]-1) * [PAGESIZE,System]) + 1
        and
        ( [PAGENUMBER,System]    * [PAGESIZE,System])

Select count(*) as TotalCount from YOUR_TABLE