jerryleo has asked for the wisdom of the Perl Monks concerning the following question:

I'm on a FC3 box with DBD-ODBC-1.16, DBI-1.605, unixODBC-2.2.9-1 , freetds-0.82 and perl 5.8.5. I'm struggling on connect Perl on Linux to Microsoft SQL Server 2000. I can successfully access MS SQL Server from a Perl script and do the single statement query. While I do the multiple concurrent statements on MS SQL Server, it only returned the 1st query result and complained 
DBD::ODBC::st execute failed: unixODBCFreeTDSSQL ServerInvalid cursor state (SQL-24000) at fetch_ptu.pl line 38.
I tried following ways

1. add { RaiseError => 1, odbc_cursortype => 2} to my connection

my script halted until timeout. 

2. setting of SQL_ROWSET_SIZE sql_rowset_size / odbc_SQL_ROWSET_SIZE to a value > 1

It complained 

DBD::ODBC::db STORE failed: unixODBCFreeTDSSQL ServerInvalid option (SQL-HY092) at fetch_ptu.pl line 8.

And returned 1st query with complaint Invalid cursor state
56778   2008062300      8061    206     75
DBD::ODBC::st execute failed: unixODBCFreeTDSSQL ServerInvalid cursor state (SQL-24000) at fetch_ptu.pl line 38.

3. $dbh->{odbc_exec_direct} = 1

It also didn't make sense.
I did hard search on Internet, but failed to make sense. Are there any missing?
Could anyone can give me kindly help?
thanks
Jerry
Here are my code 
#!/usr/bin/perl

use DBI ;

my $dbh = DBI->connect("DBI:ODBC:$DSN",$user,$passwd) or die "Can't co
+nnect to $DSN: $DBI::errstr" ;

#$dbh->{odbc_sql_rowset_size} = 2;
#$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
#$dbh->{SQL_ROWSET_SIZE} = 2; 
#$dbh->{sql_rowset_size} = 2; 
#$dbh->{odbc_exec_direct} = 1;
#$dbh->{odbc_cursortype} = 2;

$dbh->do("use $database");

my $sql = qq/SELECT StationNum, ObservTimes, StationPress, DryBulTemp,
+ RelHumidity FROM tabTimeData /;
$sql .= qq/ WHERE StationNum= ? /;
$sql .= qq/ AND ( ObservTimes >= ? AND ObservTimes <= ?) / ;
$sql .= qq/ ORDER BY ObservTimes ASC/ ; 

my $sth1 = $dbh->prepare($sql) or die "Can't prepare statement: $DBI::
+errstr";

$sql = qq/SELECT StationNum, ObservTimes, StationPress, DryBulTemp, Re
+lHumidity FROM tabRealTimeData /;
$sql .= qq/ WHERE StationNum= ? /;
$sql .= qq/ AND ( ObservTimes >= ?  AND ObservTimes <= ?) / ;
$sql .= qq/ ORDER BY ObservTimes ASC/ ;

my $sth2 = $dbh->prepare($sql) or die "Can't prepare statement: $DBI::
+errstr";

$sth1->execute(($SN,$sEPOCH,$eEPOCH)) ;

while ( my @row00 = $sth1->fetchrow_array ) {

    my ($STID,$EPOCH,$P,$T,$U) = @row00;

    print "$STID\t$EPOCH\t$P\t$T\t$U\n";

    $sth2->execute(($SN,${sEPOCH}.'00',${eEPOCH}.'59')) ;

    while ( my @rownn = $sth2->fetchrow_array ) {

            ($STID,$EPOCH,$P,$T,$U) = @rownn;
        print "$STID\t$EPOCH\t$P\t$T\t$U\n";
    }

    $sth2->finish;
}

$sth1->finish;
$dbh->disconnect if ($dbh) ;

exit(0);

[download]

Replies are listed 'Best First'.
Re: ODBC Error - Invalid Cursor State (SQL-24000)
by Anonymous Monk on Jun 25, 2008 at 06:42 UTC
    Anon here again. A brief explanation of the one-sth-at-a-time is here: http://www.freetds.org/faq.html#Are.there.any.known.issues
[reply]
Re: ODBC Error - Invalid Cursor State (SQL-24000)
by Anonymous Monk on Jun 25, 2008 at 06:38 UTC
    I have had similar problems with freetds / iodbc / DBD::ODBC / MSSQL. Not sure where the problem is (I think it's MSSQL ) but you can't have two statement handles executing at the same time. You will need to cache your results from $sth1 then finish it, then loop through those results executing $sth2.
[reply]
Re: ODBC Error - Invalid Cursor State (SQL-24000)
by roboticus (Chancellor) on Jun 27, 2008 at 13:19 UTC
    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
[reply]
[d/l]

      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]
Re: ODBC Error - Invalid Cursor State (SQL-24000)
by mje (Curate) on Mar 21, 2011 at 10:04 UTC

    That is a very old DBD::ODBC and a lot of issues have been fixed since then. I would seriously consider upgrading.

    You are attempting to use Multiple Active Statements and freeTDS does not support MARS so you need to try some of the workarounds. Setting the cursor type to 2 is one of them but you have to do it on the connect call.

    Read Multiple Active Statements (MAS) and DBD::ODBC and try running the test script under "Driver specific ways around MAS restrictions" which will tell you which methods you can use to get MAS support.

    odbc_exec_direct has nothing to do with multiple active statements.

[reply]
Re: ODBC Error - Invalid Cursor State (SQL-24000)
by Anonymous Monk on Jun 25, 2008 at 06:48 UTC
    Doesn't odbc_cursortype fix that?
[reply]

Back to Seekers of Perl Wisdom