Displaying Data from a Database in a Grid

We start by giving details of a client-server system taking data from a MySQL database on a Raspberry Pi. We followed clear instructions to set up MySQL on our Raspberry Pi and used our commands to create and populate the test database. We use a standalone socket.io server with requires node-mysql. On a Raspberry Pi with node installed, the instruction sudo npm install node-mysql issued from your home folder will install node-mysql in the node-modules folder. See below the JavaScript code of the server. The client compiles with Version 3.0 of Smart Mobile Studio. 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.

JavaScript code of Server

const io = require('socket.io')(3000);
io.on('connection', function(socket){ 
  console.log('user connected');
  socket.on('SQL', function(data){
    console.log('Received from client: ' + data);
    var mysql = require('mysql'); 
    var connection = mysql.createConnection(
      {
       host     : 'localhost',
       user     : 'student',
       password : 'pp4s',
       database : 'test',
      }
    ); 
    connection.connect();
    connection.query(data.toString(), function(err, rows, fields) {
      if (err) {
        socket.emit('dbdata', err.toString());
        return;
      };         
      socket.emit('dbdata', JSON.stringify(rows));
      console.log(rows);
    }); 
    connection.end();
  });
});
console.log('Server running on Pi port 3000');

 

Smart Pascal Code of Form

For details of the form components see the XML code of the Firebird client below.

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,
  SmartCL.Net.SocketIO, SmartCL.Controls.ComboBox, SmartCL.Grid, SmartCL.Grid.Columns;

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

implementation

procedure TForm1.FillGrid(const 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 := FResults[i].Forename.toString();
      if FResults[i].Surname <> undefined then
        W3Grid1.Cell[2, i].Value := FResults[i].Surname.toString();
    end;
end;

procedure TForm1.W3Button1Click(Sender: TObject);
begin
  FSocket.Emit('SQL', W3EditBox1.Text);
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
        FillGrid(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;
  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.    

Displaying Data from a Firebird Database (2015)

The second (2015) 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