Remote Access to a SQLite Database on a PC

You should find this demonstration easier to reproduce if you have worked through our Smart Pascal section on networking. We followed excellent instructions to set up SQLite on a PC (running Windows 10) and to obtain the tiny test file that we use here. We used the GUI rather than the Command Prompt to add C:\SQLite3 to our PATH variable. After installing Node.js on the PC we changed directory to the server folder and using official documentation typed the instruction npm install sqlite3.

As an alternative to the command prompt for preparing the test.sqlite database, you may prefer to use this node.js program:

const sqlite3 = require('sqlite3').verbose(); 

let db = new sqlite3.Database('test.sqlite', (err) => {
  if (err) {
    console.error(err.message)
  }
  console.log('Created test.sqlite database.');
});
db.serialize(function() {
  db.run("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");
  db.run("INSERT INTO users (name) VALUES('John Lennon')");
  db.run("INSERT INTO users (name) VALUES('Paul McCartney')");
  db.run("INSERT INTO users (name) VALUES('George Harrison')");
  db.run("INSERT INTO users (name) VALUES('Ringo Starr')");
});
db.close();

We now use a standalone SocketIO server with the following JavaScript (node.js) code in order to send data from the sqlite database to the client.

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 sqlite3 = require('sqlite3').verbose();
    var db = new sqlite3.Database('./test.sqlite');
    db.all(data.toString(), function(err,rows){
      if (err) {
            socket.emit('dbdata', err.toString());
            return;
          };         
          socket.emit('dbdata', JSON.stringify(rows))
          console.log(rows);
        });
        db.close();    
  });
});
console.log('Server running on PC at http://192.168.1.5:3000');

The Smart Pascal code of the client, set up with the localhost URL for testing on the same PC, compiles with Version 3.0 of Smart Mobile Studio. Make sure that a copy of 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.

Details of our original demonstration in 2015, including the XML form code that we have reused, follow the new Smart Pascal form code. See the following page for the transfer of data from a SQLite database to a dataset.

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;

type
  TForm1 = class(TW3Form)
    procedure W3Button1Click(Sender: TObject);
  private
    FSocket: TW3SocketIO;
    FResults: array of variant;
    {$I 'Form1:intf'}
  protected
    procedure InitializeObject; override;
  end;

implementation

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://127.0.0.1:3000', True);
  FSocket.OnConnected := procedure (Sender: TW3SocketIO)
    begin
      FSocket.On('dbdata', procedure (const Data: Variant)
        begin
          asm
            @FResults = JSON.parse(@Data);
          end;
          var MemoStr := '';
          for var i := 0 to FResults.Length - 1 do
            begin
              MemoStr += 'ID: ' + FResults[i].id.toString() + ' ';
              MemoStr += FResults[i].name.toString();
              MemoStr +=  #13#10;
            end;
            W3Memo1.Text := MemoStr;
        end);       
     end;
  W3Combobox1.Add('SELECT * FROM users ORDER BY id');
  W3Combobox1.Add('SELECT * FROM users ORDER BY name');
  W3Combobox1.Add('SELECT * FROM users WHERE name > "J"');
  W3Combobox1.Add('SELECT * FROM users WHERE ID < 3');
  W3Combobox1.OnClick := procedure(Sender: TObject)
    begin
      FSocket.emit('SQL', W3Combobox1.Items[W3Combobox1.SelectedIndex]);       
    end;
end;

initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
  {$R 'socket.io.client.js'}
end.    

Original Demonstration (2015)

See below the screenshot the Smart Pascal code of the server and client, the XML code of the client form and the alternative hand-coded JavaScript code of the server. You will see from the Smart Pascal code of the client that pressing the button to send the SQL statement in the text box results in a display in the memo of the actual transferred data (or an error message) whereas results from the combo box SQL statements are parsed to give an easily readable list in the memo.

Screenshot of SQLite Data in Memo in Chromium browser on Raspberry Pi

Screenshot of SQLite Data in Memo in Chromium browser on Raspberry Pi

Smart Pascal Code of Server

unit Unit1;

interface

type
  TServer = class
  public
    procedure Run;
  end;

implementation

uses
  NodeJS.Core, NodeJS.http, Node_Static, socket.io;

procedure TServer.Run;
begin
  var fileserver := TNodeStaticServer.Create('./public');
  // start http server
  var server: JServer := http.createServer(
    procedure(request: JServerRequest; response: JServerResponse)
    begin
      Console.log('http request: ' + request.url);
      if request.url = '/' then
        request.url := '/SQLiteClientMemo.html';
      fileserver.serve(request, response);
    end);
  server.listen(8079, '');
  Console.log('Server running at http://192.168.0.6:8079');

  var value := 0;
  var io := socketio().listen(server);
  io.sockets.on('connection',    // waiting for connections
    procedure(socket: JSocketIO)
    begin
      socket.on('requestFromClient',  // waiting for a special request from the client
        procedure(data: Variant; callback: JSocketIODataFunction)
        begin
          Console.log('Received from client: ' + data);
          asm
            var sqlite3 = require('sqlite3').verbose();
            var db = new sqlite3.Database('./test.sqlite');
            db.all(data.toString(), function(err,rows){
              if (err) {
                callback(err.toString());
                return;
              };
              callback(JSON.stringify(rows))
            });
            db.close();
          end;
        end);
    end);
end;

end.    

JavaScript Code of Server

This is an alternative, directly hand coded version of the server.

var static = require('node-static'),
  port = 8079,
  http = require('http');   

var webserver = new static.Server('./public',{
  cache: 3600,
  gzip: true
});

var server = http.createServer(function(request, response){
  request.addListener('end', function(){
    console.log('http request: ' + request.url);
    if (request.url == '/') 
      request.url = '/SQLiteClientMemo.html';
    webserver.serve(request, response);
  }).resume();
});

var io = require('socket.io').listen(server);
io.sockets.on('connection',    // waiting for connections
  function(socket){    
    socket.on('requestFromClient',  // waiting for a special request from the client
      function(data, callback){
        console.log('Received from client: ' + data);
        var sqlite3 = require('sqlite3').verbose();
        var db = new sqlite3.Database('./test.sqlite');
        db.all(data.toString(), function(err,rows){
          if (err) {
            callback(err.toString());
            return;
          };         
          callback(JSON.stringify(rows))
        });
        db.close();  
     }); 
  }); 
server.listen(port);
console.log('Server running on PC at http://192.168.0.3:8079');

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;

type
  TForm1 = class(TW3Form)
    procedure W3Button1Click(Sender: TObject);
  private
    FSocket: JSocketIO;
    FResults: array of variant;
    {$I 'Form1:intf'}
  protected
    procedure InitializeObject; override;
  end;

implementation

procedure TForm1.W3Button1Click(Sender: TObject);
begin
  FSocket.emit('requestFromClient', [W3EditBox1.Text],
    procedure(aData: variant)
    begin
      W3Memo1.Text := aData; // Raw data without parsing
    end);
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
  FSocket := socketio.connect('http://192.168.0.3:8079');
  W3Combobox1.Add('SELECT * FROM users ORDER BY id');
  W3Combobox1.Add('SELECT * FROM users ORDER BY name');
  W3Combobox1.Add('SELECT * FROM users WHERE name > "J"');
  W3Combobox1.Add('SELECT * FROM users WHERE ID < 3');
  W3Combobox1.OnClick := procedure(Sender: TObject)
    begin
      FSocket.emit('requestFromClient', [W3Combobox1.Items[W3Combobox1.SelectedIndex]],
        procedure(aData: variant)
        begin
          asm
            @FResults = JSON.parse(@aData);
          end;
          var MemoStr := '';
          for var i := 0 to FResults.Length - 1 do
            begin
              MemoStr += 'ID: ' + FResults[i].id.toString() + ' ';
              MemoStr += FResults[i].name.toString();
              MemoStr +=  #13#10;
            end;
          W3Memo1.Text := MemoStr;
        end);
    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-15T11:24:49.581</Modified>
    <object type="TW3Form">
      <Caption>W3Form</Caption>
      <Name>Form1</Name>
      <object type="TW3Panel">
        <Width>520</Width>
        <Top>8</Top>
        <Left>8</Left>
        <Height>192</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 users</Text>
          <Range></Range>
          <Width>376</Width>
          <Top>8</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>W3EditBox1</Name>
        </object>
        <object type="TW3Memo">
          <Text>W3Memo</Text>
          <Width>488</Width>
          <Top>88</Top>
          <Left>16</Left>
          <Height>96</Height>
          <Name>W3Memo1</Name>
        </object>
        <object type="TW3ComboBox">
          <Width>488</Width>
          <Top>48</Top>
          <Left>16</Left>
          <Height>32</Height>
          <Name>W3ComboBox1</Name>
        </object>
      </object>
    </object>
  </Form>
</SMART>

Programming - a skill for life!

How to use datasets and databases in Smart Pascal applications