Subject Re: [IBO] Re: Questions about Using IB_Transaction
Author Jason Wharton
You don't want to "cache" transactions. You want to avoid holding a
transaction open any longer than necessary.

So, my advice is to make it so that you only keep a transaction alive if you
are dedicating it to a single session only.

Otherwise, explicitly code the transactions connections each time there is a

Jason Wharton
CPS - Mesa AZ

----- Original Message -----
From: <joel.gauvreau@...>
To: <>
Sent: Monday, December 04, 2000 1:50 PM
Subject: [IBO] Re: Questions about Using IB_Transaction

I'll try to explain a little more hoping you might point me in the
right direction..

We made an automation server that encapsulate TIB_Connection and
TIB_Dataset. This allow us to share connection among many clients in
a 3 tiers environment..
so we open a few connection and kind of pool them and the client
request for a connection and the server give them a free one.
This was done because openning a connection was very slow on our
system since we have a lot of trigger and the system is already under
heavy use by powerhouse applications..Openning a connection could
take as much as 20-30 seconds. Having the connection already open
gave a big performance boost. Up to that points that worked well.

Now we are trying to include transaction in that model..the problem
is we release the connection everytime so other client could use it..

So I tought i could keep the transaction even if the Connection has
changed but it does not work. (not the way i did it anyway..)

I'm little confuse so any idea/tought on this would help
thanks a lot.

Joel Gauvreau.
Programmer - UQAT.

--- In, "Jason Wharton" <jwharton@i...> wrote:
> Take a close look at the transaction and connection components the
> is referencing.
> It may be pointing to a connection that the transaction does not
> included in its list of connections.
> Your code should look something like this:
> tr.IB_Connection := cn;
> tr.IB_Connection1 := cn1;
> tr.StartTransaction;
> try
> <do your stuff here>
> tr.Commit;
> except
> tr.Rollback;
> raise;
> end;
> HTH,
> Jason Wharton
> CPS - Mesa AZ
> ----- Original Message -----
> From: "Joel Gauvreau" <joel.gauvreau@u...>
> To: <>
> Sent: Monday, December 04, 2000 8:29 AM
> Subject: [IBO] Questions about Using IB_Transaction
> Hi,
> I want to execute many different SQL statement within the same
> Transaction and be able to rollback everything or commit
> The First Statements works fine, but the second I get an
Error "Invalid
> Transaction Handle (expecting explicit Transaction start)".
> Is it wrong to use the same transaction for to separate connection
to the
> same database?
> Is there some other way to achieve this?
> example :
> Var
> Con1 : TIB_Connection;
> Con2 : TIB_Connection;
> Dts1 : TIB_Dataset;
> Dts2 : TIB_Dataset;
> Tr1 : TIB_Transaction;`
> begin
> ...
> Dts1.Ib_Connection := Con1;
> dts1.Ib_Transaction := Tr1;
> Dts1.SQL.Text := 'select * from table1';
> Dts1.Open;
> Showmessage(Dts1.FieldByname('Field1').asString);
> Dts1.close;
> dts2.ib_Connection := Con2;
> Dts2.ib_Transaction := Tr1;
> Dts2.Sql.text := 'update Table1 set field1="123abc";';
> Dts2.Open; <-- I get the error here.
> Dts2.Close;
> Tr1.Commit;
> end;
> Any help would be greatly appreciated!
> Thanks
> Joël Gauvreau.
> Programmer - UQAT