ODBC Error - Invalid Cursor State (SQL-24000)

[Phil R Lawrence]

>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