Web Broker Paging using ClientDataset

Abstract

This paper describes a technique for showing a table in a web page. It shows a limited number of records (given by the private variable RecsPerPage) each time, with links to go to previous or next page (if possible). Here is a preview of the first page:

Why use a ClientDataset for this? ClientDatasets can bring just a small number of records every time, no matter how large the query result. You can use a ClientDataset with any technology that provides you with a TDataset descendant. And you get other goodies such as instant order reversal using indexes and aggregated fields.

This technique is usable with any database access technology including BDE, ADO, IBX, DBExpress; for simplicity, in this example I will use BDE and DBDemos' Biolife.DB table.

This example has been tested for use with Delphi 5 and 6, requiring just one conditional directive. Also, this example should be compatible with Kylix, aside from the use of the BDE.

How it works

In brief, this example:

displays a page of records of the dataset, with "Next" and/or "Prev" direction links to display additional pages
When the table is generated, the first and last values of the key field (in the example, "Species No") are stored, to track the first and last records of the current page.
After the table, two HTML forms (I called them "action forms") are generated with two hidden input fields each, one with the first or last key value and the other with the direction of movement (real world applications would surely include at least another hidden field with the connection ID).
For debugging purposes, it then displays the first and last key values in this page.
Finally, it appends a link to move a page forward or backwards (if more records are available) or a simple text telling you that you are seeing the first or the last record.

If the actual page was generated as a result of a direction link being clicked, the SQL sentence is generated so it gets the records after the last record of previous page (if we are going forward) or before the first one of previous page (if we are going backwards).

I'm using GET method in the 'action forms' so I can see the actual values passed on every page. You can change that to POST without other changes, and you will see no values following the URL.

Let's play

First of all, create a new Web Server application of any type. Add a single action to the WebModule and set it to be the default action. Now drop a DatasetTableProducer and a PageProducer from the Internet page in the component palette.

To access the database, drop the following components:

TDatabase
TSession
TQuery
TDatasetProvider
TClientDataset

In the picture is the WebModule finished (note the Name changes).

I will assume that you know how to connect the Database, Session, Query, DatasetProvider and ClientDataset to access 'BIOLIFE.DB' table in DBDemos directory; just don't forget to set Session1.AutoSessionName := true. If you can't connect, take a look at the complete WebModule textual format listing below:

object WebModule1: TWebModule1
  OldCreateOrder = False
  OnCreate = WebModuleCreate
  Actions = <
    item
      Default = True
      Name = 'WebActionItem1'
      PathInfo = '/'
      Producer = PageProducer1
    end>
  Left = 628
  Top = 119
  Height = 207
  Width = 229
  object cds: TClientDataSet
    Aggregates = <>
    FieldDefs = <
      item
        Name = 'Species No'
        DataType = ftFloat
      end
      item
        Name = 'Category'
        DataType = ftString
        Size = 15
      end
      item
        Name = 'Common_Name'
        DataType = ftString
        Size = 30
      end
      item
        Name = 'Species Name'
        DataType = ftString
        Size = 40
      end
      item
        Name = 'Length (cm)'
        DataType = ftFloat
      end
      item
        Name = 'Length_In'
        DataType = ftFloat
      end
      item
        Name = 'Notes'
        DataType = ftMemo
        Size = 50
      end
      item
        Name = 'Graphic'
        DataType = ftGraphic
      end>
    IndexDefs = <
      item
        Name = 'ixInverted'
        Fields = 'species no'
      end>
    PacketRecords = 21
    Params = <>
    ProviderName = 'dsp1'
    StoreDefs = True
    Left = 28
    Top = 16
  end

object WebModule1: TWebModule1
  object TableProducer: TDataSetTableProducer
    Caption = 'Animals'
    DataSet = cds
    OnCreateContent = TableProducerCreateContent
    OnFormatCell = TableProducerFormatCell
    Left = 92
    Top = 18
  end
  object Query1: TQuery
    DatabaseName = 'demosDB'
    SessionName = 'Session1_1'
    SQL.Strings = (
      'select *'
      'from biolife')
    UniDirectional = True
    Left = 28
    Top = 70
  end
  object Session1: TSession
    Active = True
    AutoSessionName = True
    NetFileDir = 'C:\'
    Left = 92
    Top = 70
  end
  object Database1: TDatabase
    AliasName = 'DBDEMOS'
    DatabaseName = 'demosDB'
    KeepConnection = False
    LoginPrompt = False
    SessionName = 'Session1_1'
    Left = 156
    Top = 70
  end
  object dsp1: TDataSetProvider
    DataSet = Query1
    Constraints = True
    Options = [poAutoRefresh]
    UpdateMode = upWhereKeyOnly
    Left = 156
    Top = 18
  end
  object PageProducer1: TPageProducer
    HTMLDoc.Strings = (
      '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">'
      '<HTML>'
      '<HEAD>'
      '<TITLE> Paging demo </TITLE>'
      '</HEAD>'
      '<BODY>'
      '<#table>'
      '<BR>'
      '<#PageDn>&nbsp;&nbsp;&nbsp;
        &nbsp;&nbsp;&nbsp;<#PageUp>'
      '</BODY>'
      '</HTML>')
    OnHTMLTag = PageProducer1HTMLTag
    Left = 92
    Top = 124
  end
end

Set Action1.Producer to PageProducer1: this will act as a 'content production controller', calling the DatasetTableProducer for table data when relevant.

Next, set PageProducer1.HTMLDoc to the following template:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> Paging demo </TITLE>
</HEAD>
<BODY>
<#table>
<BR>
<#PageDn>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<#PageUp>
</BODY>
</HTML>

As you can see, there are three transparent tags: table, PageDn and PageUp. It's PageProducer1's job to replace those with real content, namely:

Table gets replaced by RecsPerPage records of data, produced by the DatasetTableProducer.
PageDn gets replaced by the 'Previous' link, or simple text saying there are no previous records left (i.e. we are at the beginning of the table)
PageUp gets replaced by the 'Next' link, or simple text saying there are no later records (i.e. we are at the end of the table)

This is done in PageProducer1.OnHTMLTag event:

procedure TWebModule1.PageProducer1HTMLTag(Sender: TObject; Tag: TTag;
  const TagString: String; TagParams: TStrings; var ReplaceText: String);
begin
  if SameText('table',TagString) then
    ReplaceText:= TableProducer.Content+FormPrev+FormNext+ShowValues 
    //ShowValues is for debug only
  else
  if SameText('PageDn',TagString) then
    if FPrev then
      ReplaceText:= '<a href="javascript:formprev.submit();">&lt;&lt; Previous</a>'
    else
      ReplaceText:= 'First record shown'
  else
  if SameText('PageUp',TagString) then
    if FNext then
      ReplaceText:= '<a href="javascript:formnext.submit();">Next &gt;&gt;</a>'
    else
      ReplaceText:= 'Last record shown'
end;

The code is pretty straightforward. The auxiliary functions FormPrev, FormNext and ShowValues are shown below:

function TWebModule1.FormPrev:string;
begin
  Result:= '<form method=GET name=formprev>'+
    '<input type=hidden name=value value='+FFirstValue+'>'+
    '<input type=hidden name=dir value=prev></form>';
end;
 
function TWebModule1.FormNext:string;
begin
  Result:= '<form method=GET name=formnext>'+
    '<input type=hidden name=value value='+FLastValue+'>'+
    '<input type=hidden name=dir value=next></form>';
end;
 
function TWebModule1.ShowValues: string;
begin
  Result:= '<br>First value: '+FFirstValue+
           '<br>Last value: '+FLastValue+'<br>';
end;

When the HTML table is generated, the first and last values shown are stored in private variables, which in turn get propagated by means of hidden fields in the HTML forms FormNext and FormPrev. Here is the code to store the values:

procedure TWebModule1.TableProducerFormatCell(Sender: TObject; CellRow,
  CellColumn: Integer; var BgColor: THTMLBgColor; var Align: THTMLAlign;
  var VAlign: THTMLVAlign; var CustomAttrs, CellData: String);
begin
  if (CellColumn=0) and (CellRow>0) then //Assuming first column is order key
  begin
    if StrToInt(CellData)<StrToInt(FFirstValue) then FFirstValue:= CellData;
    if StrToInt(CellData)>StrToInt(FLastValue) then FLastValue:= CellData;
  end;
end;

Note that this code assumes that the first column of data is used as order column, and that column is of type Integer.

The variables FFirstValue and FLastValue are just strings, and get initialized on TableProducer.OnCreateContent event:

procedure TWebModule1.TableProducerCreateContent(Sender: TObject;
  var Continue: Boolean);
begin
  cds.Close;
  with Query1 do
  begin
    Close;
    SQL.Text:= 'SELECT * FROM BIOLIFE';
    if parameter('dir')='prev' then
    begin
      SQL.Add('WHERE BIOLIFE."Species No"<'+Parameter('value'));
      SQL.Add('ORDER BY BIOLIFE."Species No" desc');
      cds.IndexName:= 'ixInverted';
      cds.Open;
      FNext:= True;
      FPrev:= cds.RecordCount>RecsPerPage;
      if FPrev then cds.Next;  //show last RecsPerPage records
      //(they are inverted from query's result due to index)
    end
    else
    begin
      if parameter('dir')='next' then
      begin
        SQL.Add('WHERE BIOLIFE."Species No">'+Parameter('value'));
        FPrev:= True;
      end else //first request
        FPrev:= False;
      SQL.Add('ORDER BY BIOLIFE."Species No" asc');
      cds.IndexName:= '';
      cds.Open;
      FNext:= cds.RecordCount>RecsPerPage;
    end;
  end; //with
  FFirstValue:= '9999999';
  FLastValue:= '0';
end;

In this event the SQL query is generated to be sent to the server, using the parameters propagated from last page ('value' and 'dir' parameters). The internal variables FFirstValue and FLastValue take their default values, and flags FNext and FPrev indicates that there is more content forward or backwards, respectively.

The forward direction is easy to follow, just consider one special case: when parameter 'dir' has no value, it means we are at the first page and so the WHERE part is not required.

The backward direction is a little more complicated, as you need to invert the order of the query and the invert again the order of the result to show it. Let's see it in more detail:

Suppose you have the following values in your table (only order key field shown):

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

and RecsPerPage is set to 5. Then, for the first page you get records

1, 2, 3, 4, 5, 6

FNext gets TRUE, FPrev gets FALSE and you show just the first 5 (so FFirstValue = 1 and FLastValue = 5). The last extra record is used to quickly and easily know if you have more records ahead.

Next, you click on 'Next' link and get records

6, 7, 8, 9, 10, 11

FNext = True, FPrev = True. Click again in 'Next' link and you'll have only one record:

Now FNext becomes False and FPrev is still True. The 'Next' link is replaced by a simple text.

So far, so good. Now click on 'Previous' link, and you'll get records

10, 9, 8, 7, 6, 5

note the reversed order; we make the index ixInverted active and in the ClientDataset we see

5, 6, 7, 8, 9, 10

That's good! We have our records. BUT if we show the first 5 of them, we get

5, 6, 7, 8, 9

and we miss record 10! The code verifies that we are getting more than RecsPerPage records and if so, skip the first one to show records

6, 7, 8, 9, 10

Now we are ok.

If you have followed me, then ask yourself this question: what's going to happen when you set RecsPerPage to more records than the table actually holds?

That's all folks. Enjoy it.

You can contact me for any questions at ecullen@ciudad.com.ar

The source code of this article is available for download.

Publication of this material is allowed to anyone, provided the content isn't changed and the original source is referenced.