Re: DBI driver and transactions

Ok, bug found, and I didn't even have to go through all my code inserting
finish() calls. Reusing Greg's example code, the problem was in a method
(actually a constructor) like:


> On Mon, 3 Feb 2003 greg@turnstep.com wrote:
> >
> > > Yep before you disconnect/quit, you're supposed to finish active
> > > statements. e.g. prepare, execute, use up results, or call finish if you
> > > don't need the rest of the results.
> >
> > Exactly. The error only appears after you have done a prepare *and* a
> > select, with no concomitant finish or fetching. Here is a code sample:
> >
package blah;

use DBI;

sub new {

   my $self = {};

   my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
                          {AutoCommit=>0, RaiseError=>1, PrintError=>0});

   $self->{dbh} = $dbh;

   my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");

   my $count = $sth->execute(120);

   ## Exiting here will cause the warning described
   return undef unless $count;

   if ($count eq "0E0") {
      $sth->finish();
   }
   else {
      my $info = $sth->fetchall_arrayref({});
      ## Do something with info...
   }

   ## Exiting is now safe: commit and disconnect are separate issues...

   return bless($self,'blah');
}


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.


--
Nigel J. Andrews