Re: DBI driver and transactions

On Mon, 3 Feb 2003 greg(at)turnstep(dot)com wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > So, the question is where's the error there?
> >
> > I might well completely have the wrong idea about perl and what happens with
> > lexically scoped variables but to me that says $sth gets destroyed because it
> > drops out of scope, even if the first exit from the function is taken. If not
> > then that says all lexically scoped variables remain allocated until explicitly
> > destroyed. So I could have a function creating and storing huge amounts of data
> > in 'my' variables and that data will still be stored, adding to the processes
> > memory footprint, until my process exits. That could be years.
>
> You first assumption is correct: if a statement handle goes out of scope, it
> no longer can be "Active" or "Inactive". What happens is this: when the
> disconnect method is called, it polls all the current statement handles to
> see if any of them are still have the Active flag set. If they do, it throws
> the error you see.

Funny you should say that because one of the things I did to try and fix the
problem was basically that only I did a finish instead of throwing an error.

> However, destroying a statement handle (e.g. by leaving the
> scope in which its variable is declared) does not do any checking, and the
> Active flag is ignored. In other words:
>
> my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
> $sth->execute("Mustard","library","wrench");
> $dbh->disconnect();
>
> This throws the error. DBI is saying: Hey! You just went through all the
> trouble of making this query, don't you want to see all the results?
>
> {
> my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
> $sth->execute("Peacock","kitchen","candlestick");
> }
> ## $sth is gone
> $dbh->disconnect();
>
>
> No error is thrown in this case, as the variable $sth fails to exist at the
> end of the unnamed block, so the disconnect method has no way of knowing anything
> about the statement handle you created. In practice, finish() is not called
> very often, as you usually want to fetch everything you asked for. It's a nice
> short-circuit as in my first example, however, as it is more efficient than
> calling a fetch method if you know there is nothing there.

It doesn't explain why in your previous example that I changed slightly I had
to add an explicit call to finish before returning early in order to avoid the
error being raised. Indeed in the application the only reason I have a database
connection available in the level I am commiting, which is where I came across
the problem, is to do the transaction control. All other work is done in object
methods and all statement handles are scoped within the methods only and so out
of scope at commit time.

Unfortunately, there might also have been some other effects in play that
changed as the app. changed data (in auto commit mode) as I've now reverted to
not scanning statement handles myself before disconnecting and I've got errors
again. However, this is just one script out of two I've currently done towards
the application. Both use many of the same classes and the first does not
generate this error.

Anyway, I still think there is something very odd going on with this stuff/perl
but accept that that is how it is and am living with it, going through bloating
the code by adding finishes all over the place and being very paranoid about
where exactly I'm putting them. I will just remember for next time I use DBI
that it's got these idioscracies.

Thanks for the input on this.

--
Nigel J. Andrews