Subject Re: [IBO] understanding transactions
Author bwc3068
Hi Helen--

thank you very much for the information in your response.

I will go through the info sheets and web site (along with the help)
with a much finer toothed comb with regard to Transactions.

And the tip on not mixing 'TIB' into my 'TIB_' and 'TIBO'
components...thanks. i'll verify that i'm not bringing in any IBX

My BDE/Paradox/Query/SQL does handle transactions. At
least what I think are transactions.

In my BDE code I


i then MainQuery.edit and let the user go into the main table and
make edits as required. they can also go into any of the related
tables and make changes which would include SubTableQuery.Post.
This could be the addition of a record, delete of one or editing of
one. They could do numerours changes to several tables .Post'ing
each one. Thus, changing the other tables.

Then, if the user hit "Accept" on the main record form, i'd
MainQuery.Post to it and tDatabase.Commit. all the changes in all
the tables would be done.

IF the user hit "Cancel" on the main record form, i'd
MainQuery.Cancel and then tDatabase.Rollback. Obviously the main
table wouldn't be effected BUT all the changes to any other tables
done during this edit-cycle would be "un-done". Deleted records
would return, additions would be gone, edit's would be undone.

Anyway....i know i don't want to AutoCommit. I need the ability to
rollback changes in related tables after they've been Posted.

From what I've read, what i think is hapenning is, FB is setting up
an implicit transaction, when i do a
dmMain.dbInterbase.DefaultTransaction.InTransaction it's coming back
False. Thus, I'm not doing a .Commit.

here's the code

sql.add('set MostRecent ='+quotedStr('T') +', DateArchived
RequestLive := True;
sql.add('set MostRecent = True, DateArchived ='+quotedstr(''));
requestLive := True;
sql.add('where UniqueKey ='+quotedstr(sUniqueID));

ExecSQL; // trans handled

if dmMain.dbInterbase.DefaultTransaction.InTransaction then
//v5...false here? without commit, the records don't hold
the changes.
// gotta rework the transaction stuff. or figure out
why .InTransaction
// is False and how many other places we need
InTransaction to be true and
// it's not. MR's = T for all still unless .commit

dmMain.dbInterbase.DefaultTransaction.Commit; // seems to have
committed it properly
if dmMain.dbEquip.InTransaction then

so, i suppose my question is:

1) does ExecSQL put FB into a transaction?
2) if it does, why doesn't .InTransation return true?

my BDE stuff isn't in a transaction because i didn't wrap the
ExecSQL in a .StartTransaction as in the BDE, ExecSQL doesn't put
the BDE into a transaction.

In my BDE stuff, i'm pretty consistent in wrapping add's, delete's
and edit's in .StartTransaction. I always use .InTransaction and
then .Commit and .Rollback depending on what's what.

All of my ExecSQL aren't thus covered....

again thanks helen, i really appreciate this and i will read the
suggested material.


--- In, Helen Borrie <helebor@...> wrote:
> At 06:19 AM 5/05/2007, Kelly wrote:
> >Hi--
> >
> >i'm pretty new to FB (1.5ish) and IBO (4.6?) i use D6.
> >
> >My old BDE / Paradox / SQL app used transactions to commit or roll
> >back multiple table changes depending on what the user wanted. it
> >all worked pretty good. (I used .StartTransaction
> >and .InTransaction and .Commit and .Rollback and pretty much
> >everything i did and needed in a .StartTransaction.
> Paradox doesn't have transactions. The BDE provides a logical
> framework for a transaction but it doesn't apply to Paradox,
> etc. in a physical sense (other than in a context where you are
> cached updates). For Paradox, calling Post causes inserts, edits
> deletes to be written permanently to the database and there is no
> In Fb and IB, an insert, edit or delete happens in two steps:
> the call to Post writes a new record on the server's hard disk
> only your transaction can see. Then, afterwards, a call to Commit
> the transaction makes the change permanent; or a call to Rollback
> removes the temporary record version from the server.
> The transaction can be set with Autocommit true. In that case,
> time you Post a change, the transaction is committed
> automatically. The idea of Autocommit is to make Fb or IB behave
> like a desktop database. While Autocommit makes things seem easy
> Paradox programmers, there are really good reasons not to use it
> Fb/IB. (However, for learning purposes, the sky won't fall if you
> >my FB / IBO stuff seems to be a bit flakey. changes aren't
> >committing, rollingback, etc. as they did in BDE.
> You are *wise* to try to understand transactions. However,
> knowing what your settings are, nobody can help you to try to find
> out what's going on in your test apps.
> The other thing that is of equal importance is the SQL
> language. You're used to Paradox, which a) doesn't have
> and b) tempts one to acquire the belief that knowing SQL is
> >all my dmMain.IBOQueries.IBTransaction = <default> should they be
> >pointing to IBOTransaction (TIBOTransaction which i have on
> >which is the dbInterbase.Default_Transaction setting
> OK. Because you are giving yourself a double-whammy, of trying to
> learn about a client/server DBMS at the same time as trying to
> about "Life without the BDE", may I suggest that you keep things
> simple as possible to start with?
> First, make absolutely certain that you are not mixing pieces of
> InterBaseXpress (IBX) with pieces of IBO. You should not have any
> components around with names starting with "TIB" if there is not
> underscore after ("TIB_"). "TIB" components are IBX, "TIB_" are
> and the two are not compatible in any way.
> Secondly, for now, don't try to mix components starting
with "TIB_"
> and components starting with "TIBO". IBObjects has TWO separate
> series of components. Those starting with "TIB_" are native
> IBObjects, while those starting with "TIBO" are the
> TDataset-compatible series. To get yourself started, use
the "TIBO"
> components, with TIBODatabase, the Delphi VCL's TDatasource and
> Delphi VCL's visual controls (TDBEdit, TDBGrid and so forth).
> Later, when you come to grips with client/server computing, you
> be able to incorporate some non-visual "TIB_" components into your
> TDataset-compatible applications, or do a full transition to
> "TIB_". For now, stick with the familiar.
> Start with a TIBODatabase and configure it to connect to your
> database. Don't use the AliasName property on the IBODatabase.
> the IBODatabase, use the Server, Path and Protocol
> properties. *Don't* use the DatabaseName properties of either the
> IBODatabase or your IBOQueries.
> Use TIBOQuery objects and select <default> for both the
> and IB_Transaction properties.
> >Should I be handling the transactions explicitly (ie. Starting,
> >nameing, etc.?) or let FB handle them?
> Right, use TIBODatabase, which wraps a TIB_Connection and a
> TIBOTransaction. This will give you the single-transaction VCL
> that you are used to with the BDE. You can add more
> objects later, if and when you find out why you might need them.
> For now, don't complicate things by adding more transactions.
> with the one that is embedded in the TIBODatabase. You can
> this transaction explicitly using the transaction methods that are
> surfaced in TIBODatabase.
> You must study the IBO help to work out exactly what these
> are. Since TIBODatabase wraps *both* a TIB_Connection and a
> TIBOTransaction (which is a direct descendant of TIB_Transaction)
> should track back to these ancestors for detailed help on those
> inherited methods and properties. Use the green Hierarchy button
> the toolbar of a class to track back through the ancestors.
> >I know there's help on transactions...i have read it a bit but am
> >kind of confused on implicit, explicit and the other one. And
> >set of methods and propr's apply to each.
> To get started, all you need to understand is that, by using the
> StartTransaction method of the IBODatabase, you are taking
> control of the transaction. If you call a method that won't work
> unless a transaction is started, the IBODatabase will start the
> embedded transaction for you. In that case, you are using an
> implicit transaction. You can do it either way to start with.
> Another important thing to understand is that Commit and Rollback
> methods of the transaction, whereas Insert, Edit, Delete and Post
> methods of a statement. If Autocommit is true, then the
> gets committed every time you post a statement. If it is false,
> can post multiple statements and none of them will be committed
> you call Commit. Inserts, edits and updates that have been posted
> but not committed are known as "pending". If your application has
> work pending, other transactions can't see it.
> However, even if your application *has* committed its work, the
> tool you are using to watch the database won't see those results
> until it commits the transaction that it is running in.
> >is there another explaination on transacations someone could point
> >me to?
> Work your way through the TI Sheets at
> Transactions are kind of
> fundamental in Fb and IB so there's no TI sheet that doesn't tell
> something about transactions. You would probably get some benefit
> from reading the one on "Moving to Client/Server".
> There is a Getting Started Guide, which can be purchased from the
> community site for $29.95.
> There's The Firebird Book, of course, which has an entire "Part"
> consisting of three chapters about transactions. That is to say,
> are not going to learn and understand everything about
> from a single posting on a list.
> If you don't have The Book and you don't understand the concept of
> transactions, isolation and multi-user client/server computing, it
> would be worth visiting the IBPhoenix site and searching on the
> keywords "transactions" and "isolation". There are various
> around that address the different aspects of transactions.
> Helen