in reply to ODBC Error - Invalid Cursor State (SQL-24000)

jerryleo:

I've run into the same issue, but not using freetds, et. al. Most of the time, I simply open a different handle to the same database for each statement. It won't help you in every case, but it works for me most of the time.

my $DB1 = DBI->connect($DSN,$UID,$PWD);
my $DB2 = DBI->connect($DSN,$UID,$PWD);
...
my $ST1 = $DB1->prepare("select ID from FOO");
my $ST2 = $DB2->prepare("update FOO set Bar=? where ID=?");
$ST1->execute();
while (my @row = $ST1->fetchrow_array) {
   $ST2->execute(++$cnt, $row[0]);
}

[download]
...roboticus

Replies are listed 'Best First'.
Re^2: ODBC Error - Invalid Cursor State (SQL-24000)
by Anonymous Monk on Sep 03, 2010 at 22:12 UTC

    I ran into the same Invalid Cursor State (SQL-24000) error while using freetds. The only way I could get my program to run error-free was to connect and disconnect from the database for every iteration through a while loop. Ugly, bad form, abhorrent, yes, but it works.

    It seems that this points to an incorrect reset of the cursor when a statement handle is finished running, though I've no clue how to fix it...

[reply]
      I also saw this using FreeTDS. It was while reading through a result set in a while loop. My stored proc returned two sets of data (2 SELECTs). When I fit all of the data into a single SELECT that particular error went away.
[reply]
[reply]

In Section Seekers of Perl Wisdom