One solution could be moving $dbh variables to a higher level scope, so that the objects they refer to are not disposed of while you still need them. Objects may still see them as lexicals through references, aliasing, using our, whatever.

You could also use the eval BLOCK syntax to check how a certain section of code behaves and then go for a commit or a rollback. This way, you eval BLOCK acts a little "program", but when it "exits", control returns to your code and not to the operating system. Exceptions can be caught this way.

Regarding portability, I found the DBI interface to be very database independent. Unfortunately there is no SQL abstraction layer in DBI. This, in conjunction with the fact that many programmers think they're smarter that the quote method, yields to total mess when SQL statements have to be ported.

Like I said the typical example is quoting in SQL strings, happily done with the s/// operator (if done at all) and not with the database independent quote method.

Also, variable interpolation in double quotish strings is sometimes abused, while placeholders could do a much better job, e.g. deciding where a value should be enclosed in quotes or not. Some RDBMSs (for example DB2) might be very sensitive on this point, while others (namely MySQL) aren't so fussy...

The third aspect in database independence is always related to SQL statements, but this time it's about using one RDBMS' own functions. MySQL has loads of functions and they can be very useful indeed, but one must be prepared to reimplement them in another RDBMS when not available (DB2 allows this) or to find the equivalent (e.g. UPPER() in some RDBMSs is UCASE in others).

Finally, SQL can be used for querying, inserting, changing, deleting records but also for database administration tasks, and the latter aspect is almost always unportable regardless of DBI's completeness.

-- TMTOWTDI

That being said here are a couple things to ponder that may help.

It is common in DBI to wrap transactions in eval. You may already know this but I state the obvious just in case :)

eval {
   do some transcations
   $dbh->commit()
};
$dbh->rollback() if $@;
[download]

Hope this may give you some ideas. Good Luck.

mitd-Made in the Dark
'My favourite colour appears to be grey.'

I am little confused about original environment since MySQL does not support native commit and rollback. So has the original pre-port code got a lot of 'simulated' commit and rollback code ??

not true, transactions were introduced in version 3.23. so, surprising as it is, we did indeed have real transactions with mysql ;-) the only 'trick' is that we had to use the $dbh->do('begin'); ... ; $dbh->do('commit'); syntax rather than the begin and commit methods per se.

d_i_r_t_y

Hmmm.........
It is normally a Good Thing (tm) to have short transactions.
Nested transactions are often a sign of design failures.

I would be very cautious with code that forced me to do things like those you describe.

thanks for the reply...

You can do it by subclassing DBI - possible, but not easy.

i actually started down this track, until i read the source of the DBI module and realised how many objects/packages were involved/would have to be overriden...

It is normally a Good Thing (tm) to have short transactions. Nested transactions are often a sign of design failures.

i agree that transactions (and thus table/column locks) should be kept short, however, in this case, all 'mini-transactions' must either all fail, or all succeed. hence, it seems the only answer is to perform a single, rather large transaction commit/rollback right at the end.

d_i_r_t_y

I am also looking into adding transaction support to a large Perl web application. I currently think the best way to add transaction support is by passing the responsibility to the caller (client) code. The basic problem is to define how large a "unit of work" is in your application. Each unit of work should have one transaction. I don't think that the API should dictate to the caller how large a unit of work is.