Monday, 14 June 2004, 2:33 PM
Hi Helen,

Thank you for your reply.

HB> My guess is that the Post is independently posting and committing the work
HB> in a different (internally-generated, default) transaction. Set the
HB> ib_transaction property of the query to the actual transaction that you
HB> want it to be in, not <Default>.

Is it typical to not set the DefaultTransaction of the TIB_Connection
if you're after explicit transaction handling (which I use - or at
least thought I was using)?

Does just setting the DefaultTransaction actually cause IBO to
generate a new transaction when it needs to? (there's not much info
in the on-line help for this property :(

thanks
Mark

HB> At 12:36 PM 14/06/2004 +1000, you wrote:

>>Hello,
>> I've implemented the Auditable series in my program as outlined
>> in the TI sheet, however, I'm a little confused by the resulting
>> behaviour when rolling back a transaction.
>>
>> It seems that if I have the following sequence:
>> con.DefaultTransaction.StartTransaction;
>> try
>> sql1.Append;
>> // set some field values
>> sql.Post;
>> raise Exception.Create('Just for testing');
>> con.DefaultTransaction.Commit;
>> except
>> con.DefaultTransaction.Rollback;
>> end;
>>
>> where con (TIB_Connection) has its DefaultTransaction property set to a
>> TIB_Transaction and sql1 is a TIB_Query component with its
>> IB_Connection property set to con and the IB_Transaction
>> property left as <default>.
>>
>> The result of the AI_INSERT_INV_NUM trigger (which sets the
>> invoice number to 'USED') doesn't seem to get rolled back. Hence
>> the invoice number remains in the USED state.
>>
>> However, if the Post is removed from the above code sequence,
>> then the INV_LOG table doesn't contain an entry for the next invoice
>> number...which is expected.
>>
>> My two questions are, should the result of the trigger be
>> rolled back if the Transaction is rolled back,

HB> Yes.

>> or have I missed
>> something with the IB_Transaction links?

HB> Basically, if you find that a trigger has changed *any* data, then the
HB> commit happened without any exception.

HB> My guess is that the Post is independently posting and committing the work
HB> in a different (internally-generated, default) transaction. Set the
HB> ib_transaction property of the query to the actual transaction that you
HB> want it to be in, not <Default>.

>> The second question is when should the SP_FREE_INV_NUMBER stored
>> procedrue be called, and if it needs to be called for the above
>> scenario, then how do I ensure it is called and successfully
>> frees up the invoice number?
>> ie Even if you did the following, you could still end up with a
>> hole in the numbering sequence:
>> try
>> sql1.Append;
>> // set some field values
>> sql.Post;
>> raise Exception.Create('Just for testing');
>> con.DefaultTransaction.Commit;
>> except
>> try
>> // Call stored procedure to cancel
>> except
>> // If it fails then the invoice number would not be freed up
>> // (based on my results from the current scenario)
>> con.DefaultTransaction.Rollback;
>> raise;
>> end;
>> con.DefaultTransaction.Rollback;
>> end;
>>
>> Any insight would be greatly appreciated.

HB> I suggest that you use the TI sheet as a guideline, rather than as a
HB> framework that is portable to any situation. That said, your main hangup
HB> currently seems to be that you're not using the transactions you think you
HB> are. Having control over what gets committed and what gets rolled back,
HB> by what, and when, requires you having control over the transactions
HB> themselves.

HB> Helen




HB> ___________________________________________________________________________
HB> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
HB> without the need for BDE, ODBC or any other layer.
HB> ___________________________________________________________________________
HB> http://www.ibobjects.com - your IBO community resource for Tech Info papers,
HB> keyword-searchable FAQ, community code contributions and more !
HB> Yahoo! Groups Links