On Fri, Jan 03, 2003 at 06:34:36PM -0500, Paul Boutros wrote:
> See question below:
>
> On Fri, 3 Jan 2003, Tim Bunce wrote:
> > On Fri, Jan 03, 2003 at 07:57:01AM -0800, Michael Peppler wrote:
> > > On Fri, 2003-01-03 at 02:42, Tim Bunce wrote:
> > > > On Fri, Jan 03, 2003 at 05:01:53AM +0200, Boris Penchev wrote:
> > > > > Dear All,
> > > > >
> > > > > 1. Yes, I know that if you have prepare you can make a lot of execute of it,
>and that i can make this query in start of my program
> > > > > or in one module.
> > > > > 2. I think that is a good idea to declare $id and $name in while loop,
>because I use it only in this loop
> > > > > 3. I recommend you to use finish() method
> > > >
> > > > I recommend you don't unless you know that the code will not read
> > > > all the rows that would be selected.
> > >
> > > Unfortunately when using DBD::Sybase the following code breaks, even if
> > > there is only one row to fetch for the first iteration:
> > >
> > > $sth = $dbh->prepare("select ... from ... where id = ?");
> > > $sth->execute(1);
> > > $row = $sth->fetch;
> > > $sth->execute(2); # dies here.
> > >
> > > The reason is that the Sybase client libraries (and DBD::Sybase) can
> > > only tell that *all* the results have been fetched once fetch() returns
> > > an empty/undef array.
> >
> > Sure. That's true for most drivers. The 'Active' flag ($sth->{Active})
> > stays true until fetch() returns an empty/undef array, at which point
> > the driver should turn off the Active flag.
> >
> > > Adding a $sth->finish before the second execute() call fixes the
> > > problem, of course.
> >
> > The DBD::Sybase execute() method should effectively call finish() if
> > the statement handle is 'Active' when execute is called. That's one
> > of the main reasons the flag exists at all.
>
> I see this problem routinely when working with DBD::ODBC and using "Select
> count(*)" statements within a while loop. Because I know there is only
> one row to retrieve I don't bother running the fetch in a loop. And, to
> get the code to run I always seem to have to at the ->finish(). Are you
> saying that DBD::ODBC should be checking for me if the $sth is still
> active, and finishing it?
Yes.
> If so, is something else going on in the cases
> where I have to add the ->finish()?
Seems like DBD::ODBC just isn't doing-the-right-thing.
> Hope this isn't a trivial question, but I've been curious about this for a
> long time given the recommendation in the DBI perldoc not to use
> ->finish() and my own experience that it seems required in some cases.
It's not a trivial question, and this thread has helped shed some
more light on why finish persists in being an issue for people.
I will update the DBI docs to make it more clear that drivers should
effectively call finish() if the statement handle is 'Active' when
execute is called.
Thanks.
Tim.
