Recent

Author Topic: SQL Query Open does not work when moved to new thread  (Read 9700 times)

tudi_x

  • Hero Member
  • *****
  • Posts: 538
SQL Query Open does not work when moved to new thread
« on: July 15, 2013, 03:58:26 pm »
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.
Lazarus 2.0.2 64b on Debian LXDE 10

Arbee

  • Full Member
  • ***
  • Posts: 223
Re: SQL Query Open does not work when moved to new thread
« Reply #1 on: July 15, 2013, 04:27:32 pm »
Remove the SQLQuery.ExecSQL statement.   That is for SQL statements that update a table (INSERT, UPDATE, DELETE).
1.0/2.6.0  XP SP3 & OS X 10.6.8

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Re: SQL Query Open does not work when moved to new thread
« Reply #2 on: July 15, 2013, 05:01:39 pm »
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.







Lazarus 2.0.2 64b on Debian LXDE 10

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQL Query Open does not work when moved to new thread
« Reply #3 on: July 15, 2013, 05:17:26 pm »
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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Re: SQL Query Open does not work when moved to new thread
« Reply #4 on: July 15, 2013, 05:44:17 pm »
Thank you.
I posted an update on http://bugs.freepascal.org/view.php?id=24723 .
Lazarus 2.0.2 64b on Debian LXDE 10

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQL Query Open does not work when moved to new thread
« Reply #5 on: July 15, 2013, 06:30:46 pm »
So you're saying you're doing this (copied from the bug report) to get that error:
Quote
Create 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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Re: SQL Query Open does not work when moved to new thread
« Reply #6 on: July 15, 2013, 10:17:19 pm »
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.

Lazarus 2.0.2 64b on Debian LXDE 10

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: SQL Query Open does not work when moved to new thread
« Reply #7 on: July 15, 2013, 10:29:11 pm »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Re: SQL Query Open does not work when moved to new thread
« Reply #8 on: July 15, 2013, 11:23:36 pm »
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
Lazarus 2.0.2 64b on Debian LXDE 10

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: SQL Query Open does not work when moved to new thread
« Reply #9 on: July 15, 2013, 11:51:08 pm »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQL Query Open does not work when moved to new thread
« Reply #10 on: July 16, 2013, 10:10:41 am »
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.
Ok, thanks for that. I'll remove the .txt file from the bug report then.

Let 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 ;)

Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018