SQL Query Open does not work when moved to new thread
lazarus / 16-Databases

----------------------------------------------------------------------
tudi_x · 2013-07-15 13:58

Hi All,
I am using Lazarus 1.0.10 with FPC 2.6.2 SVN Rev 41613 on Windows XP
SP3.
I want to create an application that would run an SQL against an
Oracle DB and spool the results in a file. Due to the fact that the
SQL run is taking more than 10 minutes most of the time I want to run
the SQL from a thread.
Below is the code that just runs a simple query.
The issue is that the code works while in the main thread but for some
reason it stops when it gets to the SQLQuery.Open command.
No error message is provided, the code compiles with no issues.
Please advise what I am missing.

Thank you

unit Uthread_example;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqldb, oracleconnection, FileUtil, Forms,
  Controls, Graphics, Dialogs, StdCtrls;

  { TMyThread }
  Type

  TMySQL = class(TThread)
  private
    parsedSQL:string;
    procedure parseSQL;
  protected
    procedure Execute; override;
  public
    constructor Create(CreateSuspended: boolean);
  end;

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;

    OracleConnection1: TOracleConnection;
    OracleTransaction: TSQLTransaction;
    SQLQuery: TSQLQuery;
    ConnOracle: TOracleConnection;

    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;


var
  Form1: TForm1;
  fs: TFileStream;

implementation

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  SQL: TMySQL;

  begin
  SQL := TMySQL.Create(True);
  SQL.Start;

end;

{$R *.lfm}

procedure TMySQL.parseSQL;
begin
  Form1.Memo1.Append(parsedSQL);
end;

procedure TMySQL.Execute;
begin

  Form1.ConnOracle.UserName:= 'user';
  Form1.ConnOracle.Password:= 'pwd';
  Form1.ConnOracle.DatabaseName:= 'SID';
  Form1.ConnOracle.HostName:= 'localhost';

  Form1.ConnOracle.Close;
  Form1.ConnOracle.KeepConnection:= True;
  Form1.ConnOracle.Open;
  Form1.OracleTransaction.DataBase:= Form1.ConnOracle;

  Form1.SQLQuery.DataBase:= Form1.ConnOracle;
  Form1.SQLQuery.Transaction:= Form1.OracleTransaction;
  Form1.OracleTransaction.Active:= True;
  Form1.SQLQuery.Close;
  Form1.SQLQuery.SQL.Text:= 'select sysdate from dual';
  Form1.SQLQuery.ExecSQL;
  Form1.SQLQuery.Open;  //does not seem to go beyong this point

  parsedSQL := Form1.SQLQuery.Fields[0].AsString + '-' +
FormatDateTime('YYYY-MM-DD HH-NN-SS',Now);
  parsedSQL := FormatDateTime('YYYY-MM-DD HH-NN-SS',Now);
  fs := TFileStream.Create('c:\ostemp\' + parsedSQL + '.txt',
fmCreate);
  //Synchronize(@parseSQL);
  Form1.SQLQuery.Close;
  Form1.ConnOracle.Close;
end;

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

end.

----------------------------------------------------------------------
Arbee · 2013-07-15 14:27

Remove the SQLQuery.ExecSQL statement.   That is for SQL statements
that update a table (INSERT, UPDATE, DELETE).

----------------------------------------------------------------------
tudi_x · 2013-07-15 15:01

I commented the SQLQuery.ExecSQL statement.
I am getting now ORA-01062 : unable to allocate memory for the define
buffer - attached screen capture.
Oracle version 11.2.0.1.0.
Code as per below:

unit thread_example_main_unit;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
StdCtrls, sqldb, oracleconnection;

type

  { TMyThread }

  TMySQL = class(TThread)
  private
    parsedSQL:string;
    procedure parseSQL;
  protected
    procedure Execute; override;
  public
    constructor Create(CreateSuspended: boolean);
  end;

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    ConnOracle: TOracleConnection;
    Memo1: TMemo;
    SQLQuery: TSQLQuery;
    OracleTransaction: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;
  fs: TFileStream;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  SQL: TMySQL;
begin
  SQL := TMySQL.Create(True);
  SQL.Start;

end;
procedure TMySQL.parseSQL;
begin
  Form1.Memo1.Append(parsedSQL);
end;

procedure TMySQL.Execute;
begin

  Form1.ConnOracle.UserName:= 'user';
  Form1.ConnOracle.Password:= 'pwd';
  Form1.ConnOracle.DatabaseName:= 'SID';
  Form1.ConnOracle.HostName:= 'localhost';

  Form1.ConnOracle.Close;
  Form1.ConnOracle.KeepConnection:= True;
  Form1.ConnOracle.Open;
  Form1.OracleTransaction.DataBase:= Form1.ConnOracle;

  Form1.SQLQuery.DataBase:= Form1.ConnOracle;
  Form1.SQLQuery.Transaction:= Form1.OracleTransaction;
  Form1.OracleTransaction.Active:= True;
  Form1.SQLQuery.Close;
  Form1.SQLQuery.SQL.Text:= 'select sysdate from dual';
  //Form1.SQLQuery.ExecSQL;
  Form1.SQLQuery.Open;

  parsedSQL := Form1.SQLQuery.Fields[0].AsString + '-' +
FormatDateTime('YYYY-MM-DD HH-NN-SS',Now);
  parsedSQL := FormatDateTime('YYYY-MM-DD HH-NN-SS',Now);
  fs := TFileStream.Create('c:\ostemp\' + parsedSQL + '.txt',
fmCreate);
  //Synchronize(@parseSQL);
  Form1.SQLQuery.Close;
  Form1.ConnOracle.Close;
end;

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

end.

----------------------------------------------------------------------
BigChimp · 2013-07-15 15:17

TL;DR => too much code to scroll etc.

When posting, please use code tags (the button marked # when editing a
post).

FYI: a bug with the same error message has been filed:
http://bugs.freepascal.org/view.php?id=24723

----------------------------------------------------------------------
tudi_x · 2013-07-15 15:44

Thank you.
I posted an update on http://bugs.freepascal.org/view.php?id=24723 .

----------------------------------------------------------------------
BigChimp · 2013-07-15 16:30

So you're saying you're doing this (copied from the bug report) to get
that error:
QuoteCreate new project
Add TOracleConnection and configure for valid tnsnames alias
Add TSQLTransaction linked to the TOracleConnection
Add TSQLQuery linked to the TOracleConnection with SQL: select * from
[tablename]
OracleConnection Active set to True [OK]
SQLTransaction Active set to True [OK]
SQLQuery Active set to True [ORA-01062 error]

If not, you've just copied a random text file with some code without
explanation in a bug report that just happens to indicate the same
Oracle error code
Perhaps the underlying cause is the same, but to know that I'd rather
have a *minimal* test program (i.e. without threads) that shows the
same problem.

Sorry for the grumbling but this way of doing things isn't the best
way of troubleshooting.

See also
http://wiki.lazarus.freepascal.org/How_do_I_create_a_bug_report
http://wiki.lazarus.freepascal.org/Tips_on_writing_bug_reports

----------------------------------------------------------------------
tudi_x · 2013-07-15 20:17

My bad.
There is no relation to the steps indicated in the bug and by you.

The issue is happening only while the SQLQuery opens in a thread, as
per the code i uploaded.
Let me know what the next step could be.

----------------------------------------------------------------------
taazz · 2013-07-15 20:29

your bad indeed. 1) Most of the connection libraries do not allow
multiple threads to access a single connection so it is adviced to
have one connection per thread and if that wasn't enough you wend and
access a forms global variable which might or might not be initialized
at the time the thread is executed or even worst the object it
referenced is now freed and on top of that you accessed forms
published properties with out any protection at all.

In short as far as I am aware lazarus database components are not
thread safe to be accessed across threads you need one set of
connection/transaction/query per thread. I would be very much like to
be proven wrong on this.

----------------------------------------------------------------------
tudi_x · 2013-07-15 21:23

Could you please help me with how you would structure the code on a
more low level?
I am newbie and i did not find any tutorial on the net with working
with database components for a multi threaded application.

Thank you

----------------------------------------------------------------------
taazz · 2013-07-15 21:51

Depends on how you indent to use the database components. For example
it is customary to have all the queries that return data to a dbgrid
or some sort of visual representation in the main application thread
and all the queries that execute some action on the database on
threads to avoid freazing of the usr interface.

You could think of this as follows
Code: [Select]TMyDBThread = class(Thread)
private
  FUserNAme :string;
  fPassword :String;
  MYconnection :TSQLConnection;
  MYTransaction: TSQLTransaction;
  MYQuery         : TSQLQuery.
 
  procedure Execute;override;
public
  Property UserName:string;
  Property Password : String;
  Property SQLCommand:String;
end;
implementation.
TMyDBTHread.Execute;
begin
  MyConnection := TSQLConnection.Create;
  MyConnection.Username := FUsername;
  MYConnection.Password := fPassword;
  MyTransaction := TSQLTransaction.Create;
  MyTransaction.Database := Myconnection;
  MyQuery := TSQLQuery.Create;
  MyQuery.Database := MyConnection;
  MyQuery.transaction := MyTransaction;
  while not terminated do begin
      if MyQuery.SQL.Text <> FSqlCommand then begin
          MyQuery.SQL.Text := FSqlCommand;
          MyQuery.Execute;
          asynccall(@myProcedureQueryComplete);
     end;
  end;
  Myquery.free;
  MyTransaction.free;
  Myconnection.Free;
end;
[code]

As you can see when the execution procedure triggers for the first
time it executes the query given the assynccall is made to inform the
main thread that the query execution is finised and you can decide
what to do next with thread eg calling terminate will effectively exit
the thread execution loop and you are free to destroy it or you can
pass a new sql command to execute.

That's the basic outline of a threaded query execution. in the event
you want to get data back to the application then things get a bit
more complicated. You need to have a memory dataset that can hold data
and update the database when needed with out holding a cursor open in
the server in delphi that was clientdataset in lazarus I think that is
TBuffDataset. I don't have details on how those work together I would
need to create some kind of application and that takes time that I do
not have at this point.

----------------------------------------------------------------------
BigChimp · 2013-07-16 08:10

Quote from: tudi_x on July 15, 2013, 10:17:19 pmMy bad.
There is no relation to the steps indicated in the bug and by you.

The issue is happening only while the SQLQuery opens in a thread, as
per the code i uploaded.
Ok, thanks for that. I'll remove the .txt file from the bug report
then.

Quote from: tudi_x on July 15, 2013, 10:17:19 pmLet me know what the
next step could be.
What taazz said: I don't suspect the sqldb components are necessarily
thread-safe. Generally, the guy gives good advice, so I'd follow him