Remote Access to a MySQL Database

This has similar functionality to the Firebird demonstration. See that page for the XML code of the client form and for a screenshot of the output. You should find this demonstration easier to reproduce if you have worked through our Smart Pascal section on networking. After installing Node.js on the Raspberry Pi we changed directory to the server folder and typed the instructions npm install mysql and then npm install node-mysql. Our PHP demonstration page outlines how to set up MySQL on a Raspberry Pi and then create the test database that we use here.

See below the Smart Pascal code of the server and client 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.

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 := '/MySQLClientMemo.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);
          var connection: variant;
          asm
            var mysql = require('mysql');
            @connection = mysql.createConnection(
              {
                host     : 'localhost',
                user     : 'student',
                password : 'pp4s',
                database : 'test',
              }
            );
            (@connection).connect();
            (@connection).query(data.toString(), function(err, rows, fields) {
              if (err) {
                callback(err.toString());
                return;
              };
              callback(JSON.stringify(rows));
            });
          end;
          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 = '/MySQLClientMemo.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 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) {
            callback(err.toString());
            return;
          };         
          callback(JSON.stringify(rows));
        }); 
        connection.end();     
     }); 
  }); 
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
    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.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 += FResults[i].Forename.toString();
              if FResults[i].Surname <> undefined then
                MemoStr += ' ' + FResults[i].Surname.toString();
              MemoStr +=  #13#10;
            end;
          W3Memo1.Text := MemoStr;
        end);
    end;
end;

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

Programming - a skill for life!

How to use datasets and databases in Smart Pascal applications