Remote Access to a Firebird Database

We start with a workaround to connection problems with Firebird 3.0 and node-firebird. Instead, we use our existing WebSocket server to execute commands on the Pi. (The server, developed on Windows, performs equally well with appropriate commands on Linux). We adapted the client to send a command to write the SQL command supplied by the user, prefaced by the standard connection command, to a file named temp.sql on the server. Shortly afterwards, the client sends a command to use the isql-fb tool on the server to input the contents of temp.sql to the Firebird database. The results are returned to the memo on the client. Adjust the IP addresses and the three delay periods (two on the server) to suit your system and widen the edit box and memo as necessary. See the following page for a more straightforward client-server solution using MySQL instead of Firebird.

For those that would like to stick with our earlier approach, the details follow the Smart Pascal code of the WebSockets client form. The code compiles with Version 3.0 of Smart Mobile Studio.

unit Form1;

interface

uses 
  System.Types, System.Types.Convert, System.Objects, System.Time, SmartCL.System,
  SmartCL.Graphics, SmartCL.Components, SmartCL.FileUtils, SmartCL.Forms, SmartCL.Fonts,
  SmartCL.Theme, SmartCL.Borders, SmartCL.Net.Websocket, SmartCL.Application, 
  SmartCL.Controls.EditBox, SmartCL.Controls.Button, SmartCL.Controls.Memo;

type
  TForm1 = class(TW3Form)
    procedure W3Button1Click(Sender: TObject);
  private
    {$I 'Form1:intf'}
    FSocket: TW3WebSocket;
    FTimer: TW3Timer;
  protected
    procedure InitializeObject; override;
  end;

implementation

procedure TForm1.W3Button1Click(Sender: TObject);
begin
  FSocket.Connect('ws://192.168.1.4:1881');
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
  FSocket := TW3WebSocket.Create;  
  FSocket.OnOpen := procedure (sender: TW3WebSocket)
  begin
    // Prepare the command to put the connect and sql statements into the file temp.sql.
    var msg = 'echo ' + CHR(34) + 'CONNECT /var/lib/firebird/3.0/data/CONTRIBUTIONS_PI.FDB' +
      ' user ' + CHR(39) + 'student' + CHR(39) + ' password ' + CHR(39) + 'pp4s'+ CHR(39) +
      ';' + edtMsg.text + CHR(34) + ' > temp.sql';   
    FSocket.Write(msg);
    FTimer := TW3Timer.Create(Self);
    FTimer.Delay := 100;    
    FTimer.OnTime := procedure(Sender: TObject)
      begin
        // Use the isql-fb tool in quiet mode to use temp.sql for input to database.
        FSocket.Write('sudo isql-fb -q -i temp.sql');  
        FTimer.Enabled := False;    
     end;
    FTimer.Enabled := True;
  end;

  FSocket.OnMessage := procedure (Sender: TW3WebSocket; Message: TWebSocketMessageData)
  begin
    if Message.mdType = wsText then
      TextLog.Add(Message.mdText, true);
  end;
end; 
 
initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
end.    

Demonstration of access to Firebird 2.5 Super using node-firebird and socket.io in 2015

You should find this demonstration easier to reproduce if you have worked through our Smart Pascal section on networking. The preceding page has instructions for installations of Firebird and node-firebird on the Raspberry Pi.

We supply the database CONTRIBUTIONS.FDB in db_contributions.zip and (more beneficial for you) instructions for creating it on a PC.

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

Firebird Data in Memo

Pressing the button to send the SQL statement in the text box results in a display in the memo of the actual transferred data whereas results from the combo box SQL statements are parsed to give an easily readable list in the memo as shown above.

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 := '/FirebirdClientMemo.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 Firebird = require('node-firebird');
            var options = {};
            options.host = '192.168.0.6';
            options.port = 3050;
            options.database = '/var/lib/firebird/2.5/data/CONTRIBUTIONS.FDB';
            options.user = 'student';
            options.password = 'pp4s';
            Firebird.attach(options, function(err, db) {
              if (err)
                throw err;
              db.query(data.toString(), function(err, result) {
                if (err){
                  callback(err.toString());
                  return;
                };
                callback(JSON.stringify(result));
                db.detach(); // closes the connection
              });
            });
          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 = '/FirebirdClientMemo.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 Firebird = require('node-firebird');
        var options = {}; 
        options.host = '192.168.0.6';
        options.port = 3050;
        options.database = '/var/lib/firebird/2.5/data/CONTRIBUTIONS.FDB';
        options.user = 'student';
        options.password = 'pp4s'; 
        Firebird.attach(options, function(err, db) {
          if (err)
            throw err;
          db.query(data.toString(), function(err, result) {
            if (err){
              callback(err.toString());
              return;
            }; 
            callback(JSON.stringify(result));
            db.detach(); // closes the connection
          }); 
        });
    }); 
  }); 
server.listen(port);
console.log('Server running on Pi at http://192.168.0.6: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
    function IntArrayStringToString(ias: String): string;
    procedure InitializeObject; override;
  end;

implementation

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],
    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.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]],
        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 += IntArrayStringToString(FResults[i].forename.toString());
              if FResults[i].surname <> undefined then
                MemoStr += ' ' + IntArrayStringToString(FResults[i].surname.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-10T10:25:40.340</Modified>
    <object type="TW3Form">
      <Caption>W3Form</Caption>
      <Name>Form1</Name>
      <object type="TW3Panel">
        <Width>520</Width>
        <Top>8</Top>
        <Left>8</Left>
        <Height>216</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="TW3Memo">
          <Text>W3Memo</Text>
          <Width>488</Width>
          <Top>89</Top>
          <Left>16</Left>
          <Height>112</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>