Subject Re: [firebird-php] First Steps and Transaction Handling
Author masotti
Hello Matthias,

maybe someone answer before me, with better knowledge and writing speed.

Matthias Hanft ha scritto:
> In the support mailing list, I was told that PHP uses connection
> pooling (can I switch that off? "ibase.allow_persistent = Off"
> seems not to be the setting for it), but why do connections have
> something to do with transactions?
Connections pooling try to semplify and speed up connection timings
between PHP app and server.
Nothing to have with transactions.
PHP ibase_* docs states that transactions are committed or rolled back
at the end of the script.
First problem: web server caching. I'm not sure that web servers (i.e.
Apache) really releases resources.
Check who is allready connect to your server to verify which
transaction aren't committed yet.
I noted same behavihour, and restarting Apache cleaned all problems (no
data lost).
> In this respect, I just don't quite understand the mapping between
> PHP and FB transactions fully. (I do with Delphi.) Currently, in
> PHO, I'm using
> $dbhandle = ibase_connect("host:database", "user", "pass", "latin1", 0, 3);
> // does opening a database cause the creation
> // of some "implicit" or "default" transaction?
With ibase_pconnect() you can reuse same connection if it was allready
opened before by the PHP ibase client library.
Transactions are rolled back at end of PHP script. You must use
ibase_commit() on default transaction to commit.
Autocommit isn't an option, AFAIR.
> $stmt = "select ID, MODEL from ACCOUNTS where...";
> $trans = ibase_trans( IBASE_DEFAULT, $dbhandle );
> // no explicit need for ibase_trans? ibase_query seems
> // to work without transaction, too
Yes: I use it only for INSERT / UPDATE / DELETE to be sure to commit ASAP.
> $sth = ibase_query($dbhandle, $stmt);
> // some sources tell me to use "$trans" instead of "$dbhandle"
> // here, but this does _not_ adjust the transaction counters
> // in "gstat -h"!
There are many overloading of this functions, and AFAIR $dbhandle and
$trans are optional.
Both are mandatory when you are working on more than one database
because system is unable to detect a "default" for your query.
There is a ibase_trans() overload with more than one resource link
parameter, when you need a transaction on more that one db to perform a
two phase commit.
> $row = ibase_fetch_object($sth));
> $myresult["ID"]=$row->ID;
> $myresult["MODEL"]=$row->MODEL;
> ibase_free_result($sth);
> ibase_commit($trans);
> // any need for committing any "implicit" transaction here?
> // ibase_commit(); and/or ibase_commit($dbhandle); ?
For SELECT only transaction (without updating of data) you can prepare a
READ_ONLY connections so this can be rolled back safely every time you
use it.

> ibase_close($dbhandle);
> // really closed now? or just put back into some pool?
Connections aren't needed to be closed, or every time you restart a
script (loading another page) you need to reconnect and that is a time
consuming activity. Pool speeds up things.

> Is the above code generally correct?
I'd not close connections, but use only pconnect(), but remaining code
is quite regular.
> I'd really *love* to see the "gstat -h" transaction counters
> always "clean" (Oldest=X, X+1, X+1, Next=X+2) after each PHP
> access (as I do after each Delphi access) - is this possible
> at all?
Try to verify if your server keeps alive transactions that are instead
officially "closed" by scripts.
My web database are now:
Oldest transaction 198406
Oldest active 198407
Oldest snapshot 198407
Next transaction 198408

Oldest transaction 12498
Oldest active 12499
Oldest snapshot 12499
Next transaction 12500

after navigating a while, becomes

Oldest transaction 12562
Oldest active 12563
Oldest snapshot 12563
Next transaction 12564