Displaying Data from a Firebird Database in a Grid

The demonstration on the preceding 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 presents the results neatly in a grid. We suggest that you try the memo demo first and then quickly convert a copy of the memo client to the grid client.

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

Firebird Data in Grid

Firebird Data in Grid

Either press the button to send the SQL statement in the text box or select a statement in the combo box to see a display of the parsed results in the grid. You could try a statement such as INSERT INTO Programmer VALUES (999,'NewForename','NewSurname','L6',16) then DELETE FROM Programmer WHERE id = 999 (after viewing the inserted test record).

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, SmartCL.Grid, SmartCL.Grid.Columns;

type
  TForm1 = class(TW3Form)
    procedure W3Button1Click(Sender: TObject);
  private
    {$I 'Form1:intf'}
    FSocket: JSocketIO;
    FResults: array of variant;
    TextCols : array [1 .. 3] of TW3TextColumn;
    RowCaptions : array[1 .. 3] of string := ['ID', 'Forename', 'Surname'];
  protected
    function IntArrayStringToString(ias: String): string;
    procedure FillGrid(aData: variant);
    procedure InitializeObject; override;
  end;

implementation

procedure TForm1.FillGrid(aData: variant);
begin
  asm
    @FResults = JSON.parse(@aData);
  end;
  W3Grid1.ClearRows;
  W3Grid1.AddRow(FResults.Length);
  for var i := 0 to FResults.Length - 1 do
    begin
      var CurrentID := FResults[i].id;
      W3Grid1.Cell[0, i].Value := CurrentID.toString();
      if CurrentID < 10 then
        W3Grid1.Cell[0, i].Value := '0' + W3Grid1.Cell[0, i].Value;
      W3Grid1.Cell[1, i].Value := IntArrayStringToString(FResults[i].forename.toString());
      if FResults[i].surname <> undefined then
         W3Grid1.Cell[2, i].Value := IntArrayStringToString(FResults[i].surname.toString());
    end;
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.W3Button1Click(Sender: TObject);
begin
  FSocket.emit('requestFromClient', [W3EditBox1.Text], FillGrid);
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]],
        FillGrid);
    end;
  for var i := 1 to 3 do
    begin
      TextCols[i] := TW3TextColumn.Create(W3Grid1 as IW3ColumnsControl);
      TextCols[i].Header.Caption := RowCaptions[i];
      (TextCols[i].Header as TW3CustomControl).Font.Weight := 'bold';
      if i = 1 then
        TextCols[i].Width := 30
      else
        TextCols[i].Width := 110;
      W3Grid1.Columns.Add(TextCols[i]);
    end;
end;

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

XML Code of Client Form

<SMART>
  <Form version="2" subversion="1">
    <Created>2015-10-09T13:46:42.361</Created>
    <Modified>2015-11-11T11:19:53.974</Modified>
    <object type="TW3Form">
      <Caption>W3Form</Caption>
      <Name>Form1</Name>
      <object type="TW3Panel">
        <Width>520</Width>
        <Top>8</Top>
        <Left>8</Left>
        <Height>232</Height>
        <Name>W3Panel1</Name>
        <object type="TW3Button">
          <Caption>Send SQL</Caption>
          <Width>104</Width>
          <Top>8</Top>
          <Left>400</Left>
          <Height>32</Height>
          <Name>W3Button1</Name>
          <OnClick>W3Button1Click</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>W3EditBox1</Name>
        </object>
        <object type="TW3ComboBox">
          <Width>488</Width>
          <Top>48</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>W3ComboBox1</Name>
        </object>
        <object type="TW3Grid">
          <Width>488</Width>
          <Top>88</Top>
          <Left>16</Left>
          <Height>136</Height>
          <Name>W3Grid1</Name>
        </object>
      </object>
    </object>
  </Form>
</SMART>

Programming - a skill for life!

How to use datasets and databases in Smart Pascal applications