Web Broker Paging using ClientDataset

Many thanks to Eric Young for editing and polishing my English!

This article was published in Pascal Bulletin from Latium Software

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 have 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 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>       <#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>      <#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

Complete code listing follows.

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