Subject Re: [IBO] understanding transactions
Author Helen Borrie
At 06:19 AM 5/05/2007, Kelly wrote:
>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 wrapped
>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, Access,
etc. in a physical sense (other than in a context where you are using
cached updates). For Paradox, calling Post causes inserts, edits and
deletes to be written permanently to the database and there is no Rollback.

In Fb and IB, an insert, edit or delete happens in two steps: first,
the call to Post writes a new record on the server's hard disk that
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, each
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 for
Paradox programmers, there are really good reasons not to use it with
Fb/IB. (However, for learning purposes, the sky won't fall if you do!)

>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, without
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 transactions
and b) tempts one to acquire the belief that knowing SQL is optional.

>all my dmMain.IBOQueries.IBTransaction = <default> should they be
>pointing to IBOTransaction (TIBOTransaction which i have on dmMain)
>which is the dbInterbase.Default_Transaction setting (TIBConnection)?

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 learn
about "Life without the BDE", may I suggest that you keep things as
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 an
underscore after ("TIB_"). "TIB" components are IBX, "TIB_" are IBO
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 the
Delphi VCL's visual controls (TDBEdit, TDBGrid and so forth).

Later, when you come to grips with client/server computing, you will
be able to incorporate some non-visual "TIB_" components into your
TDataset-compatible applications, or do a full transition to native
"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. On
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 IB_Connection
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 model
that you are used to with the BDE. You can add more IBOTransaction
objects later, if and when you find out why you might need them.

For now, don't complicate things by adding more transactions. Work
with the one that is embedded in the TIBODatabase. You can control
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) you
should track back to these ancestors for detailed help on those
inherited methods and properties. Use the green Hierarchy button in
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 which
>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 explicit
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 are
methods of the transaction, whereas Insert, Edit, Delete and Post are
methods of a statement. If Autocommit is true, then the transaction
gets committed every time you post a statement. If it is false, you
can post multiple statements and none of them will be committed until
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 admin
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 you
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, you
are not going to learn and understand everything about transactions
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 articles
around that address the different aspects of transactions.