Create an DB-Connection from within a thread
lazarus / 16-Databases

----------------------------------------------------------------------
Roadie · 2011-10-27 11:07

Hello,

I was hoping you could help me with an example on how to start a new
tread on an event.

The situation is:
I scan a barcode with a wireless scanner. This code is read by
Lazarus/synaser. As soon as there is a barcode read there should be a
new tread.
That specific threat should create an connection to an MySQL-server,
run an Stored Procedure and return the resultset.
After that the result should be returned to the Wireless Scanner.

I've got the communication with the scanner up and running.
I've succesfully created an MySQL connection and run the Stored
Procedure, with the help of Zeos.

But since there are more than 6 scanners in our setting we would like
to make the program multi-treaded. So the creation of the MySQL-
connection should be handled without a Form.

Is there anybody out there who understands what I want and knows an
solution ?

Tnx !

----------------------------------------------------------------------
Roadie · 2011-10-27 12:59

UPDATE:

Since I notice my question is read, but no-one places a reaction,
maybe I'm not totally clear on how far I've come myself, hereby my
code(snippet)...


Code: [Select]unit UnitScanServer4;

{$mode objfpc}{$H+}

interface

uses
  {$ifdef unix}
    cthreads,
    cmem, // the c memory manager is on some systems much faster for
multi-threading
  {$endif}
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
StdCtrls,
  DBGrids, ZConnection, ZDataset, INIFiles, db, sqldb,
  Synaser, Crt, StrUtils, MTProcs;

type

  { BarCodes }
  Bars = record
    ScannerId, BaseId, BarCode, ReturnString : string;
    end;


  { TMyThread }
  TMyThread = class(TThread)
    private
      fStatusText: string;
      procedure ShowStatus;
    protected
      procedure Execute; override;
    public
      constructor Create(CreateSuspended: boolean);
    end;


  { TMyListenThread }
  TMyListenThread = class(TThread)
    private
      fBarCode: string;
      procedure DBQuery;
    protected
      procedure Execute; override;
    public
      constructor Create(CreateSuspended: boolean);
      destructor Destroy; override;
    end;


  { TMyDataBaseThread }
  TMyDataBaseThread = class(TThread)
      ZConnection: TZConnection;
      ZReadOnlyQuery: TZReadOnlyQuery;
    private
      procedure DBQuery;
    protected
      procedure Execute; override;
    public
      threadquery, threadscannerid, threadresults: string;
      constructor Create(CreateSuspended: boolean);
    end;


  { TForm1 }
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    Testknop: TButton;
    Debugger: TMemo;
    ZConnection1: TZConnection;
    ZReadOnlyQuery1: TZReadOnlyQuery;


    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure TestknopClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure LText(LogText: String);
 //   procedure Boe(BarCode: String);
    procedure Boe(Index: PtrInt; Data: Pointer; Item:
TMultiThreadProcItem);
    procedure BoeWait(BarCode: String);


  private
    { private declarations }

  public
    { public declarations }
  end;

var
  Form1: TForm1;
  INI: TINIFile;
  ser: TblockSerial;
  barcode, results: String;
  scannerid, query, newline: String;

  DebugFile: String;
  host, user, pass, dbname: String;
  comport: String;
  parity: Char;
  baud, bits, stop: Integer;
  softflow, hardflow: Boolean;
  Counter: Integer;

  ThreadArray: array [1..10] of TMyDataBaseThread; //We will create no
more than 10 threads.

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.LText(LogText: String);
begin
  LogText := Trim(LogText);
  LogText := StringReplace(LogText, CRLF, CRLF+'                    ',
[rfReplaceAll, rfIgnoreCase]);
  LogText := FormatDateTime('DD-MM-YYYY HH:NN:SS',Now)+' '+LogText;

  with TStringList.Create do
  try
    LoadFromFile(DebugFile);
    Append(LogText);
    SaveToFile(DebugFile);
  finally
    Free;
  end;

  Debugger.Append(LogText);
end;


//procedure TForm1.Boe(BarCode: String);
procedure TForm1.Boe(Index: PtrInt; Data: Pointer; Item:
TMultiThreadProcItem);
var
    S: String;
begin
  Counter :=  Counter+1;

  ZConnection1.Connected := False;
  ZConnection1.Connected := True;

 // ZReadOnlyQuery1.SQL.Clear;
 //   ZReadOnlyQuery1.SQL.Text := 'Call wirelessscan("'+BarCode+'")';
//      ZReadOnlyQuery1.SQL.Text := 'Call
wirelessscan("004000251234567890123")';
//    ZReadOnlyQuery1.Open;

//    LText(IntToStr(Counter)+'
'+ZReadOnlyQuery1.FieldByName('msg').AsString );

end;

procedure TForm1.BoeWait(BarCode: String);
begin
    Counter :=  Counter+1;
    ZConnection1.Connected := False;
    ZConnection1.Connected := True;

    ZReadOnlyQuery1.SQL.Clear;
    ZReadOnlyQuery1.SQL.Text := 'Call analyzetables()';
    ZReadOnlyQuery1.Open;

    LText(IntToStr(Counter)+'
'+ZReadOnlyQuery1.FieldByName('Msg_text').AsString );

end;


procedure TForm1.TestknopClick(Sender: TObject);
var
  BarCode : Bars;

begin
  LText('TestKnop geklikt');
  BarCode.ScannerId := '0040';
  BarCode.BaseId := '0025';
  BarCode.BarCode := '004000251234567890123';


  ProcThreadPool.DoParallel(@Boe,1,5,nil);
end;

procedure TForm1.Button1Click(Sender: TObject);

begin

  LText('MySQL geklikt');
  BoeWait('004000255000426154641');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
   LText('Knop 2');
   BoeWait('00400025901000000000');

end;

procedure TForm1.FormCreate(Sender: TObject);

var
  MyThread : TMyThread;
  MyListenThread : TMyListenThread;

begin
  MyThread := TMyThread.Create(True); // With the True parameter it
doesn't start automatically
  if Assigned(MyThread.FatalException) then
    raise MyThread.FatalException;

  // Here the code initialises anything required before the threads
starts executing

    INI := TINIFile.Create('ScanServer.ini');
    host := INI.ReadString('DataBase','host','');
    user := INI.ReadString('DataBase','user','');
    pass := INI.ReadString('DataBase','pass','');
    dbname := INI.ReadString('DataBase','dbname','');
    query := INI.ReadString('DataBase','query','');

    DebugFile := INI.ReadString('Logging','file','');

    comport := INI.ReadString('Communication','Comport','COM1');
    baud := StrToInt(INI.ReadString('Communication','baud','9600'));
    bits := StrToInt(INI.ReadString('Communication','bits','8'));
    parity := INI.ReadString('Communication','parity','N')[1];
    stop := StrToInt(INI.ReadString('Communication','stop','0'));
    softflow :=
StrToBool(INI.ReadString('Communication','softflow','True'));
    hardflow :=
StrToBool(INI.ReadString('Communication','hardflow','False'));


    //LogFile aanmaken/opruimen
      if not FileExists(DebugFile) then // If the file not exists
        with TStringList.Create do
        try
          Add('Logbestand aangemaakt');
          SaveToFile(DebugFile);
        finally
          Free;
        end
      else
        with TStringList.Create do
        try
          Clear();
          Add('Logbestand opgeruimd');
          SaveToFile(DebugFile);
        finally
          Free;
       end;




  MyThread.Resume;


  MyListenThread := TMyListenThread.Create(True); // With the True
parameter it doesn't start automatically
  if Assigned(MyListenThread.FatalException) then
    raise MyListenThread.FatalException;

  // Here the code initialises anything required before the threads
starts executing

  MyListenThread.Resume;


end;


{ TMyThread }

procedure TMyThread.ShowStatus;
// this method is only called by Synchronize(@ShowStatus) and
therefore
// executed by the main thread
// The main thread can access GUI elements, for example Form1.Caption.
begin
  Form1.Caption := fStatusText;
end;

procedure TMyThread.Execute;
var
  newStatus : string;
begin
  fStatusText := 'TMyThread Starting...';
  Synchronize(@Showstatus);
  fStatusText := 'TMyThread Running...';
  while (not Terminated) and (true {any condition required}) do begin

    //here goes the code of the main thread loop
    newStatus:='ScanServer4: '+FormatDateTime('DD-MM-YYYY
HH:NN:SS',Now);

    if NewStatus <> fStatusText then begin
      fStatusText := newStatus;
      Synchronize(@Showstatus);
    end;
  end;
end;

constructor TMyThread.Create(CreateSuspended: boolean);
begin
  FreeOnTerminate := True;
  inherited Create(CreateSuspended);
end;



{ TMyListenThread }

procedure TMyListenThread.DBQuery;
var
  newquery : String;
  thread_id : shortint;
begin

  scannerid := leftstr(barcode, 4);
  Form1.LText('Ontvangen van scanner '+scannerid+': '+ barcode);
  newquery := StringReplace(query, '[BARCODE]', barcode,
[rfReplaceAll, rfIgnoreCase]);
  Form1.LText('Query: ' + newquery);

  { BD Call Uitvoeren }


  thread_id := StrToInt(scannerid);
  ThreadArray[thread_id]:=TMyDataBaseThread.Create(True);
  ThreadArray[thread_id].threadquery:=newquery;
  ThreadArray[thread_id].threadscannerid:=scannerid;
  ThreadArray[thread_id].Resume;

  results := ThreadArray[thread_id].threadresults;

//  results := 'Blaat|Boe';
  newline := chr(27) + 'E';
  results := StringReplace(results, '|', newline, [rfReplaceAll]);

//  ser.SendString(scannerid + 'Bedankt voor ' + newline + query +
CRLF);
  Form1.LText('Antwoord aan scanner ' + scannerid + ': ' + results);

end;

procedure TMyListenThread.Execute;
var
  newBarCode : string;
  ser: TBlockSerial;

begin
  //verbinden met serial scanner
  ser:=TBlockSerial.Create;
  try
    ser.Connect(comport);

    softflow := true;
    hardflow := false;
    ser.config(baud, bits, parity, stop, softflow, hardflow);

    Form1.LText(
              'Comport: ' + comport + CRLF +
              'Baud: ' + IntToStr(baud) + CRLF +
              'Bits: ' + IntToStr(bits) + CRLF +
              'Parity: ' + parity + CRLF +
              'Stop: ' + IntToStr(stop) + CRLF +
              'Softflow: ' + BoolToStr(softflow) + CRLF +
              'Hardflow: ' + BoolToStr(hardflow) );

    Form1.LText('Device: ' + ser.Device + '   Status: ' +
ser.LastErrorDesc +' '+IntToStr(ser.LastError));
    Sleep(1000);

    Form1.LText( 'TMyListenThread Starting...');
//    Synchronize(@DBQuery);
    Form1.LText( 'TMyListenThread Running...');

    while (not Terminated) and (true {any condition required}) do
begin

      barcode := ser.Recvstring(30); //Read string with timeout in
milliseconds
      if (AnsiCompareStr(barcode, '') <> 0) then begin
        Synchronize(@DBQuery);
      end;
    end;
  finally
  end;
end;

constructor TMyListenThread.Create(CreateSuspended: boolean);
begin
  FreeOnTerminate := True;
  inherited Create(CreateSuspended);
end;

destructor TMyListenThread.Destroy();
begin
  Form1.LText('Serial Port will be freed...');
  ser.free;
  Form1.LText('Serial Port was freed successfully!');
end;


{ TMyDataBaseThread }

constructor TMyDataBaseThread.Create(CreateSuspended: boolean);

begin
  FreeOnTerminate := True;
  inherited Create(CreateSuspended);

  Form1.Debugger.Append('DataBase Thread started');

end;

procedure TMyDataBaseThread.DBQuery;

begin

{
  with ZConnection do begin
    Protocol := 'mysql-5';
    HostName := host;
    Port := 3306;
    Database := dbname;
    User := user;
    Password := pass;
    Connect;
    end;
  }
    {


  ZConnection.HostName := host;
   ZConnection.Database := dbname;
  ZConnection.User := user;
  ZConnection.Password := pass;
  ZConnection.Protocol := 'mysql-5';
  ZConnection.Port:=3306;
//  ZConnection.Connected := True;

  ZReadOnlyQuery.Connection := ZConnection;
  ZReadOnlyQuery.SQL.Clear;
  ZReadOnlyQuery.SQL.Text := query;
  ZReadOnlyQuery.Open;

  threadresults := ZReadOnlyQuery.FieldByName('msg').AsString;
  }
//  Form1.LText('Threaded query: '+scannerid+' '+results);
  threadresults := 'Blaat|Boe';
  Form1.Debugger.Append('BoeDebugger melding');

 // ZConnection.Connected := False;

end;

procedure TMyDataBaseThread.Execute;

begin
  Form1.Debugger.Append('DataBase Thread Executed');

  Synchronize(@DBQuery);

end;


end.

I hope this will help me...

----------------------------------------------------------------------
BigChimp · 2011-10-27 13:39

Ok, a very nice way to ask for responses - giving more info. I like
it 

Unfortunately no experience in thread programming, and not much with
MySQL, but it seems your form only has one ZConnection defined.

Shouldn't you (programmatically) create a new db connection in your
thread (or at least a new transaction, don't know if that works
correctly).
Then when the db thread is closing, commit the transaction, close &
destroy the connection.

(Hoping somebody who really knows this stuff will chip in)

----------------------------------------------------------------------
Mando · 2011-10-27 14:22

Hi, Roadie:I can't help you. But I'm interesting in your system. I'm
developing a sytem to control a production process. A product goes
through several sections to be completed. I want to monitor the
manufacturing process and know in every moment that section of the
factory a product is located. I had thought of putting a computer in
each section with a barcode scanner (USB) but wireless  scanners seem
a better solution. Could you advise me about them?: At what maximum
distance from the base / computer must be, how they connect to the
computer, battery life, makes and models ....

Thanks...

----------------------------------------------------------------------
goodname · 2011-10-27 16:42

I can't help you solve this as I don't use threading. Making a comment
as I have a similar bar code reader system but used a different
approach.

Hardware
The bar code readers are WinCE Motorola devices. They connect to LAN
wireless access points around the building. It took some testing to
make sure the whole building was covered by the access points. The IP
addresses are linked to the devices and not the access points.

Software
The server is using lNet to accept TCP connections. The messages are
in JSON format and handled using fpjson. There is a single SQLDB
connection which opens for the duration of each request. Since
requests are brief and infrequent there is no problem with requests
waiting for previous requests to complete. All components are on a
form and there is a single thread.

----------------------------------------------------------------------
99Percent · 2011-10-28 03:02

I recomend you try using Ararat Synapse instead of lnet because it
works synchronously and therefore more reliable in a threaded
environment.