Problem with transaction in multiple sessions [Resolved]

[JeanR]

Hello,

In my application, i need to have multiple transaction in same time, with distinct commit/rollback capacity.
So, i have several TOraSession ( = several transactions, agree ?), i start the transaction by

Code: Select all

OraSession1.StartTransaction(ilReadCommitted,'','Session1');
OraSession2.StartTransaction(ilReadCommitted,'','Session2');

but, i have a really big problem, and i don't understand the cause ...

- I have 2 OraSession on a form, with 2 OraQuery linked to the session.
- I Start session1 and session2
- I insert a row in a table with OraQuery2 (linked to OraSession2)
- I Commit OraSession2

at this point, i can't see my new line on my db on a another session, computer etc...

- I Commit OraSession1

now i can see my record added and committed previously by OraSession2......



Thats a big problem for me, result is impossibility to have a true multi transaction application with oracle (it was to simple and efficient with Firebird!)
do you ha ve an idea, solution ?

Many thanks,

Souch

ps: here is a simple code to test this problem, create a empty form with 2 orasession, 2 oraquery and 6 btns:

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, DBAccess, Ora, OraTransaction, MemDS, OraClasses;

type
  TForm1 = class(TForm)
    Session1: TOraSession;
    OraQuery1: TOraQuery;
    Session2: TOraSession;
    OraQuery2: TOraQuery;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
  private
    { Déclarations privées }
  public
    { Déclarations publiques }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

// Session 1

procedure TForm1.Button1Click(Sender: TObject);
begin
  Session1.AutoCommit := False;
  Session1.Connect;
  Session1.StartTransaction(ilReadCommitted, '', 'Session1');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  OraQuery1.SQL.Text := 'INSERT INTO TEST (Id, Nom) VALUES (1,''Toto'')';
  OraQuery1.ExecSQL;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  Session1.Commit;
end;

// Session 2

procedure TForm1.Button4Click(Sender: TObject);
begin
  Session2.AutoCommit := False;
  Session2.Connect;
  Session2.StartTransaction(ilReadCommitted, '', 'Session2');
end;

procedure TForm1.Button5Click(Sender: TObject);
begin
  OraQuery2.SQL.Text := 'INSERT INTO TEST (Id, Nom) VALUES (2,''Titi'')';
  OraQuery2.ExecSQL;
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  Session2.Commit;
end;

// Way to test :
// Start Session1, Start Session2, Insert in Session2, Commit Session2 = nothing in db
// Commit Session1 = data in db

end.

[Plash]

We could not reproduce the problem. Please send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create server objects.
Also try to pass an empty string as the transaction name to the StartTransaction method.

[FanderlF]

I can't tell you what the problem is, but I can tell you that multi-transaction application can be built

I use multiple sessions in my program, too.

And you are sure that OraQuery1->OraSession1 and OraQuery2->OraSession2?

Have you tried to use TOraTable? You can append a new Row and enter values via the OraTable->FieldByName()->Value function.

[JeanR]

Oops, apologies !

My OraQuery was not linked to the session ...
So by default if nothing is linked, thee first session registered is used by the compo ?

Thanks for your fast reply, now i can buy my license