Solved

DBD:ODBC issue - ODBC Error - Invalid Cursor State (SQL-24000)

Posted on 2008-06-24
3
6,287 Views
Last Modified: 2012-08-14
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: [unixODBC][FreeTDS][SQL Server]Invalid 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: [unixODBC][FreeTDS][SQL Server]Invalid 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: [unixODBC][FreeTDS][SQL Server]Invalid 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

#!/usr/bin/perl
 
use DBI ;
 
my $dbh = DBI->connect("DBI:ODBC:$DSN",$user,$passwd) or die "Can't connect 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, RelHumidity 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);

Open in new window

0
Comment
Question by:Jerryleo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
sqd5597 earned 125 total points
ID: 21865157
Hi,

I believe you have run into a limitation of MS SQL Server.

I'm pasting in two things for you to look at below:

Hope this helps you.

**************************************************************************************************************************

Please excuse my ignorance, but is DBD::ODBC still limited to one running query through each Database Handle at a time? That is $sth=$dbh->prepare(...); $sth->execute; $sth1=$dbh->prepare(...); $sth1 will invalidate $sth's result set. Stubbed my toe on this when I was trying to apply DBD::mysql to DBD::ODBC (target MS-SQL server). Of course that was a number of years ago.

Thanks.

Bill

This depends on the ODBC Driver - it was never a limitation of DBD::ODBC.

By default SQL Server did not used to support multiple active statements if any of them were select statements. You could get around this by changing to a dynamic cursor (I believe there is a setting in DBD::ODBC to enable this and perhaps even a test case for it in the t subdir of the distribution in 20SqlServer.t).

In MS SQL Server 2005, there is a new thing called MARS (Multiple Active Result Sets) which allows multiple active select statements but it has some nasty implications it you are also doing transactions.

For other drivers it depends. I believe Oracle ODBC driver does support multiple active statements as myodbc does. Not sure about the rest.

If anyone wants to report success with a particular driver and multiple active statements I will collect them and add a FAQ.

Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com 

******************************************************************************************************************************

 Multiple Active Statements

SQL Server doesn't support Multiple Active Statements with the standard cursor type (Forward-only). The following script provides a workaround if you have no alternative other than to use Multiple Active Statements.

#!/usr/bin/perl -w
use strict;

use DBI;

#
# MS SQL Server does not by default allow Multiple Active Statements
# i.e. it does not allow you to create a second result set on another
# statement whilst a result-set is active on another statement
# MS SQL Server can support MASs if you use a server side cursor but
# the normal ways of enabling server side cursors are not available
# in DBD::ODBC. A workaround was found by setting SQL_ROWSET_SIZE to
# a value > 1. However,
# a) although this persuades MS SQL Server to create a server side cursor
#    server-side cursors are slower than static cursors.
# b) it is slightly dangerous as if you do not consume all the result-set
#    or call finish you can hang the MS SQL Server ODBC driver.
#    This is easily demonstrated via the PHP interface
#    /developer/languages/php/apache_odbc.html#appb2 but
#    we have never reproduced it in DBD::ODBC - perhaps because finish
#    is called for you.
# c) this workaround only works because DBD::ODBC does not use
#    SQLExtendedFetch - if that changed it would undoubtably break.
#

my $data_source = "dbi:ODBC:datasource_name";
my $user = "database_username";
my $password = "database_password";
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# if you comment out the next line this script will fail with
#
# DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server Driver]
# Connection is busy with results for another hstmt (SQL-HY000)
# (DBD: st_execute/SQLExecute err=-1) at ./mas.pl line 50.

#$dbh->{odbc_SQL_ROWSET_SIZE} = 2;

$dbh->do(q/create table "mas" (a integer)/);
$dbh->do(q/create table "mas2" (a integer)/);
$dbh->do(q/insert into "mas" values (1)/);
$dbh->do(q/insert into "mas2" values (2)/);

my $sth = $dbh->prepare(q/select * from "mas"/);
$sth->execute;

my $sth2 = $dbh->prepare(q/select * from "mas2"/);
$sth2->execute;

my $col = $sth->fetchrow_array;
print "Column from first result-set, val = $col\n";
my $othercol = $sth2->fetchrow_array;
print "Column from second result-set, val = $othercol\n";

$sth->finish;
$sth2->finish;

$dbh->do(q/drop table "mas"/);
$dbh->do(q/drop table "mas2"/);

$dbh->disconnect;
0
 

Author Comment

by:Jerryleo
ID: 21915686
Thanks for reply

I already tried to set SQL_ROWSET_SIZE  or odbc_SQL_ROWSET_SIZE to a value > 1, but It complained "DBD::ODBC::db STORE failed: [unixODBC][FreeTDS][SQL Server]Invalid option (SQL-HY092)".


PS, I failed to find the example 20SqlServer.t, where I can find it?

Thanks

0
 
LVL 2

Expert Comment

by:sqd5597
ID: 21927068
I am sorry to say that this is beyond my knowledge/experience at this point.

However, here is a link to the 20SqlServer.t
http://www.sfr-fresh.com/unix/www/DBD-ODBC-1.16.tar.gz:a/DBD-ODBC-1.16/t/20SqlServer.t

Sorry I couldn't be more help.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question