Populating a Dataset with Selected Data from a Database

The following Smart Pascal client code, accessing data in a MySQL database on a Raspberry Pi using the JavaScript server on the preceding page, compiles with Version 3.0 of Smart Mobile Studio. See the old Firebird demonstration below it for the XML code of the form components. Make sure that the line <script src="lib/socket.io.client.js" type="text/javascript"></script> is under the title tag of the HTML output and that socket.io.client.js (stored in a location such as C:\ProgramData\The Smart Company\Smart Mobile Studio\Libraries\socket.io) is in the www/lib 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.Net.SocketIO,
  SmartCL.Controls.ComboBox, SmartCL.Controls.Label, System.Dataset;

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: TW3SocketIO;
    FResults: array of Variant; 
    FProgrammer: TW3Dataset;
  protected
    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 := FResults[i].Forename.toString();
      if FResults[i].Surname = undefined then
        FProgrammer.Fields.FieldByName('Surname').asString := ''
      else
        FProgrammer.Fields.FieldByName('Surname').asString := 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;


procedure TForm1.btnSQLClick(Sender: TObject);
begin
  FSocket.Emit('SQL', edtSQL.Text);
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 := TW3SocketIO.Create;  
  FSocket.connect('http://192.168.1.4:3000', True);  
  FSocket.OnConnected := procedure (sender: TW3SocketIO)
    begin
      FSocket.On('dbdata', procedure (const Data: Variant)
      begin
        PopulateDataset(Data);
      end);        
    end;
  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 ORDER BY Forename;');
  W3Combobox1.Add('SELECT * FROM Programmer WHERE ID < 10;');
  W3Combobox1.OnClick := procedure(Sender: TObject)
    begin
      FSocket.Emit('SQL', W3Combobox1.Items[W3Combobox1.SelectedIndex]);
    end;    
  W3Label1.Font.Weight := 'bold';
  W3Label2.Font.Weight := 'bold';
  W3Label3.Font.Weight := 'bold';    
end;

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

Populating a Dataset with Selected Data from a Firebird Database (2015)

A demonstration (2015) 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