Populating a Dataset with Selected Data from a Firebird Database

The demonstration on a previous page shows selected data from a Firebird database in a memo on the client. The client on this page uses the same server on the Raspberry Pi and populates a TW3Dataset with the results of each SQL statement..

This screenshot shows the client in action. The Chrome browser (directed to http://192.168.0.6:8079/FirebirdClientDataset.html) is running on a PC and shows the page that has been downloaded from the Raspberry Pi.

Firebird Data in Dataset

Firebird Data in Dataset

Either press the button to send the SQL statement in the text box or select a statement in the combo box to see three fields of the first of the selected records in the edit boxes. Use the buttons to navigate through the selected records.

Smart Pascal Code of Client

The output HTML file refers to the file res/socket.io, so remember to add the folder and file to the server folder.

unit Form1;

interface

uses 
  SmartCL.System, SmartCL.Graphics, SmartCL.Components, SmartCL.Forms, 
  SmartCL.Fonts, SmartCL.Borders, SmartCL.Application, SmartCL.Controls.Button,
  SmartCL.Controls.Panel, SmartCL.Controls.EditBox, SmartCL.Controls.Memo,
  socketioclient, SmartCL.Controls.ComboBox, System.Memory, System.Dataset,
  SmartCL.Controls.Label;
type
  TForm1 = class(TW3Form)
    procedure btnLastClick(Sender: TObject);
    procedure btnFirstClick(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnSQLClick(Sender: TObject);
  private
    {$I 'Form1:intf'}
    FSocket: JSocketIO;
    FResults: array of variant;
    FProgrammer: TW3Dataset;
  protected
    function IntArrayStringToString(ias: String): string;
    procedure PopulateDataset(aData: variant);
    procedure ShowRecord;
    procedure InitializeObject; override;
  end;

implementation

procedure TForm1.PopulateDataset(aData: variant);
begin
  asm
    @FResults = JSON.parse(@aData);
  end;
  FProgrammer := TW3Dataset.Create;
  FProgrammer.fieldDefs.Add('id', ftInteger);
  FProgrammer.fieldDefs.add('forename', ftString);
  FProgrammer.fieldDefs.add('surname', ftString);
  FProgrammer.CreateDataset;
  for var i := 0 to FResults.Length - 1  do
    begin
      FProgrammer.Append;
      FProgrammer.Fields.FieldByName('id').asInteger := FResults[i].id;
      FProgrammer.Fields.FieldByName('forename').asString := IntArrayStringToString(FResults[i].forename.toString());
      if FResults[i].surname = undefined then
        FProgrammer.Fields.FieldByName('surname').asString := ''
      else
        FProgrammer.Fields.FieldByName('surname').asString := IntArrayStringToString(FResults[i].surname.toString());
      FProgrammer.post;
    end;
  FProgrammer.First;
  ShowRecord;
end;

procedure TForm1.ShowRecord;
begin
  edtID.text := IntToStr(FProgrammer.Fields.FieldByName('id').asInteger);
  edtForename.Text := FProgrammer.Fields.FieldByName('forename').asString;
  edtSurname.Text := FProgrammer.Fields.FieldByName('surname').asString;
end;

function TForm1.IntArrayStringToString(IAS: String): string;
var
  Str := '                                                  ';
begin
  var StrIntArray := StrSplit(IAS, ',');
  for var i := 0 to StrintArray.Length - 1 do
    Str[i + 1] := chr(StrToInt(StrIntArray[i]));
  result := Str.TrimRight;
end;

procedure TForm1.btnSQLClick(Sender: TObject);
begin
  FSocket.emit('requestFromClient', [edtSQL.Text], PopulateDataset);
end;

procedure TForm1.btnFirstClick(Sender: TObject);
begin
  FProgrammer.First;
  ShowRecord;
end;

procedure TForm1.btnNextClick(Sender: TObject);
begin
  FProgrammer.Next;
  ShowRecord;
end;

procedure TForm1.btnLastClick(Sender: TObject);
begin
  FProgrammer.Last;
  ShowRecord;
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
  FSocket := socketio.connect('http://192.168.0.6:8079');
  W3Combobox1.Add('SELECT forename, surname, id FROM Programmer ORDER BY id');
  W3Combobox1.Add('SELECT * FROM Programmer ORDER BY Surname, Forename');
  W3Combobox1.Add('SELECT * FROM Programmer WHERE Age < 16');
  W3Combobox1.Add('SELECT * FROM Programmer WHERE ID < 7');
  W3Combobox1.OnClick := procedure(Sender: TObject)
    begin
      FSocket.emit('requestFromClient', [W3Combobox1.Items[W3Combobox1.SelectedIndex]],
        PopulateDataset);
    end;
  W3Label1.Font.Weight := 'bold';
  W3Label2.Font.Weight := 'bold';
  W3Label3.Font.Weight := 'bold';
end;

initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
end.    

XML Code of Client Form

<SMART>
  <Form version="2" subversion="2">
    <Created>2015-10-09T13:46:42.361</Created>
    <Modified>2015-11-12T10:00:07.033</Modified>
    <object type="TW3Form">
      <Caption>W3Form</Caption>
      <Name>Form1</Name>
      <object type="TW3Panel">
        <Width>520</Width>
        <Top>8</Top>
        <Left>8</Left>
        <Height>200</Height>
        <Name>W3Panel1</Name>
        <object type="TW3Button">
          <Caption>Send SQL</Caption>
          <Width>104</Width>
          <Top>8</Top>
          <Left>400</Left>
          <Height>32</Height>
          <Name>btnSQL</Name>
          <OnClick>btnSQLClick</OnClick>
        </object>
        <object type="TW3EditBox">
          <Value></Value>
          <Text>SELECT * FROM Programmer</Text>
          <Range></Range>
          <Width>376</Width>
          <Top>8</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>edtSQL</Name>
        </object>
        <object type="TW3ComboBox">
          <Width>488</Width>
          <Top>48</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>W3ComboBox1</Name>
        </object>
        <object type="TW3EditBox">
          <Value></Value>
          <Range></Range>
          <Width>32</Width>
          <Top>112</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>edtID</Name>
        </object>
        <object type="TW3EditBox">
          <Value></Value>
          <Text>W3EditBox</Text>
          <Range></Range>
          <Width>128</Width>
          <Top>112</Top>
          <Left>56</Left>
          <Height>32</Height>
          <Name>edtForename</Name>
        </object>
        <object type="TW3EditBox">
          <Value></Value>
          <Text>W3EditBox</Text>
          <Range></Range>
          <Width>128</Width>
          <Top>112</Top>
          <Left>192</Left>
          <Height>32</Height>
          <Name>edtSurname</Name>
        </object>
        <object type="TW3Button">
          <Caption>First</Caption>
          <Width>128</Width>
          <Top>-40</Top>
          <Left>-16</Left>
          <Height>32</Height>
          <Name>btnFirst</Name>
          <OnClick>btnFirstClick</OnClick>
        </object>
        <object type="TW3Button">
          <Caption>Next</Caption>
          <Width>128</Width>
          <Top>124</Top>
          <Left>376</Left>
          <Height>32</Height>
          <Name>btnNext</Name>
          <OnClick>btnNextClick</OnClick>
        </object>
        <object type="TW3Button">
          <Caption>Last</Caption>
          <Width>128</Width>
          <Top>160</Top>
          <Left>376</Left>
          <Height>32</Height>
          <Name>btnLast</Name>
          <OnClick>btnLastClick</OnClick>
        </object>
        <object type="TW3Label">
          <Caption>ID</Caption>
          <Width>128</Width>
          <Top>85</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>W3Label1</Name>
        </object>
        <object type="TW3Label">
          <Caption>Forename</Caption>
          <Width>128</Width>
          <Top>85</Top>
          <Left>56</Left>
          <Height>32</Height>
          <Name>W3Label2</Name>
        </object>
        <object type="TW3Label">
          <Caption>Surname</Caption>
          <Width>128</Width>
          <Top>85</Top>
          <Left>192</Left>
          <Height>32</Height>
          <Name>W3Label3</Name>
        </object>
        <object type="TW3Button">
          <Caption>First</Caption>
          <Width>128</Width>
          <Top>88</Top>
          <Left>376</Left>
          <Height>32</Height>
          <Name>W3Button2</Name>
          <OnClick>btnFirstClick</OnClick>
        </object>
      </object>
    </object>
  </Form>
</SMART>
Programming - a skill for life!

How to use datasets and databases in Smart Pascal applications