Phil,
I ran into a similar problem with Sybase. I had a statement prepared
using placeholders (a select statement) and I had to loop several times as
I processed transactions and each time through the loop I had to execute
the prepared statement. Perl and/or Sybase complained that I needed to
finish the statement handle.
Using finish doesn't mean you have to keep repreparing the statement
handle... according to the Cheetah book, it's a rarely used functions used
for "internal housekeeping". In my program, I prepare my frequently used
select statements in an init method and assign the statement handle to an
object attribute. I have no need to prepare the statement more than once
and explicitly finish()'ing a handle after a fetch* didn't cause any
noticable decrease in performance. So I don't think using finish is any
sort of a "nasty workaround". I would just try to remove the multiple
prepare()'s and prepare the statement up front, then just finish() after
each fetch*. Hope it helps.
--Curt
On Mon, 26 Mar 2001, Phil R Lawrence wrote:
> >From the DBI archives I found a workaround for this error (one
> which takes all the performance benefit out of placeholders and
> prepared statements, however). Any ideas as to how we can lick
> this and avoid the nasty workaround? Read on to see the
> problem...
>
> On 30 Jun 1998, Jeff Urlwin ([EMAIL PROTECTED]) wrote:
> > I am using the DBI:ODBC driver with an Access97 database. It
> seems that
> > when calling 'execute' too many times I receive this error
> > "[Microsoft][ODBC Driver Manager] Invalid Cursor State
> (SQL-24000) (DBD:
> > st_execute/SQL Execute err=-1)"
>
> On 15 Oct 1998, Thomas Yengst ([EMAIL PROTECTED]) wrote:
> > This one dogged me for over a day...finally, a solution.
> >
> > Just to add some experimental evidence to this problem with the
> > implementation or MS-SQL itself (it isn't a problem if you're
> using DBI
> > with MySQL), if you do an explicit $sth->finish after
> completing a set
> > of fetchrow_*, then the Invalid cursor state does not occur.
>
> I recieve the same error in the following test script. A redo of
> the script (also below) where I re-prepare the cursor every time
> avoids the problem. BTW, I am using Access 2000, and my Perl and
> DBI came new from ActiveState a few weeks ago.
>
> Version with the problem:
> --------------------------------------
> use warnings;
> use strict;
> use DBI;
>
> $| = 1;
>
> DBI->trace(2,'trace.log');
> my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=BLAH.mdb';
> my $dbh = DBI->connect("dbi:ODBC:$DSN", '', '', {RaiseError =>
> 1})
> or die "Couldn't connect!";
>
> my %results;
>
> my $sth = $dbh->prepare(<<"");
> SELECT count(YOQsScheduledCode)
> FROM Sites INNER JOIN
> (
> YOQs INNER JOIN tblYOQsScheduled
> ON YOQs.[YOQ Key] =
> tblYOQsScheduled.AdministeredYOQKey
> )
> ON Sites.[Site Key] = YOQs.[Site Key]
> WHERE Sites.Identifier = '1019'
> AND ScheduledDate >= #2001-01-01#
> AND ScheduledDate <= #2001-03-26#
> AND AdministeredYOQDaysVariance <= ?
> AND AdministeredYOQDaysVariance >= ?
> AND [Provider Key] = ?
>
> my $idx = 0;
> foreach (527) {
> $idx++;
> my $staff_num = 'Staff' . $idx;
>
> $results{"${staff_num}ID"} = $_;
>
> # On time count
> $sth->execute(2,-2,$_);
> $results{"${staff_num}OnTime"} = $sth->fetchrow_array;
>
> # Very Early count
> ######## DIES HERE with Invalid Cursor State! ########
> $sth->execute(-5,-365,$_);
> ######################################################
> $results{"${staff_num}VeryEarly"} = $sth->fetchrow_array;
>
> # Early count
> $sth->execute(-3,-4,$_);
> $results{"${staff_num}Early"} = $sth->fetchrow_array;
>
> # Late count
> $sth->execute(4,3,$_);
> $results{"${staff_num}Late"} = $sth->fetchrow_array;
>
> # Very Late count
> $sth->execute(365,5,$_);
> $results{"${staff_num}Late"} = $sth->fetchrow_array;
> }
> $sth->finish;
> $dbh->disconnect;
> --------------------------------------
>
>
> Version that doesn't complain:
> --------------------------------------
> use warnings;
> use strict;
> use DBI;
>
> $| = 1;
>
> DBI->trace(2,'trace.log');
> my $DSN = 'driver=Microsoft Access Driver
> (*.mdb);dbq=StudyManager_PHIL.mdb';
> my $dbh = DBI->connect("dbi:ODBC:$DSN", '', '', {RaiseError =>
> 1})
> or die "Couldn't connect!";
>
> my %results;
>
> my $sql = <<"";
> SELECT count(YOQsScheduledCode)
> FROM Sites INNER JOIN
> (
> YOQs INNER JOIN tblYOQsScheduled
> ON YOQs.[YOQ Key] =
> tblYOQsScheduled.AdministeredYOQKey
> )
> ON Sites.[Site Key] = YOQs.[Site Key]
> WHERE Sites.Identifier = '1019'
> AND ScheduledDate >= #2001-01-01#
> AND ScheduledDate <= #2001-03-26#
> AND AdministeredYOQDaysVariance <= ?
> AND AdministeredYOQDaysVariance >= ?
> AND [Provider Key] = ?
>
> my $sth;
> my $idx = 0;
> foreach (527) {
> $idx++;
> my $staff_num = 'Staff' . $idx;
>
> $results{"${staff_num}ID"} = $_;
>
> # On time count
> $sth = $dbh->prepare($sql);
> $sth->execute(2,-2,$_);
> $results{"${staff_num}OnTime"} = $sth->fetchrow_array;
> $sth->finish;
>
> # Very Early count
> $sth = $dbh->prepare($sql);
> $sth->execute(-5,-365,$_);
> $results{"${staff_num}VeryEarly"} = $sth->fetchrow_array;
> $sth->finish;
>
> # Early count
> $sth = $dbh->prepare($sql);
> $sth->execute(-3,-4,$_);
> $results{"${staff_num}Early"} = $sth->fetchrow_array;
> $sth->finish;
>
> # Late count
> $sth = $dbh->prepare($sql);
> $sth->execute(4,3,$_);
> $results{"${staff_num}Late"} = $sth->fetchrow_array;
> $sth->finish;
>
> # Very Late count
> $sth = $dbh->prepare($sql);
> $sth->execute(365,5,$_);
> $results{"${staff_num}Late"} = $sth->fetchrow_array;
> $sth->finish;
> }
> $dbh->disconnect;
> --------------------------------------
>
> Thanks,
> Phil R Lawrence
>
>
