Re: DBI driver and transactions

On Mon, 3 Feb 2003 greg@turnstep.com wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > 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:
>
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
>          {AutoCommit=>0, RaiseError=>1, PrintError=>0});
>
> my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");
>
> my $count = $sth->execute(120);
>
> ## Exiting here will cause the warning described
>
> 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...


Thanks for that clarification. I hadn't been worrying about it in my code.
With the database design I either expect 1 or 0 rows from queries in which I
treat which ever is appropiate as a problem or I am expecting 0 <= n in which
case I do fetch all the results.

I presume that the issue here is that in the 1 or 0 rows returned case where 1
row indicates I have a problem then I am not fetching from or finishing that
statement. Although, in the debugger I've seen the Active flag still set after
doing a single fetch on a 1 row resultset.

Now I'm just going to added finishes everywhere. Nasty, time consuming and
probably excesive but also probably less time consuming that trying to be
selective and missing one that's necessary.


--
Nigel J. Andrews