As far as I am aware this is a limitation of the TDS protocol used and
your example code will not work unless you force a server-side cursor (see
DBD::ODBC pod and look for odbc_SQL_ROWSET_SIZE but I warn you that you really
will need to close everything i.e. force SQLFreeStmt(SQL_CLOSE) or you will get
hangs).
BTW, the example in the pod should be $dbh->{odbc_SQL_ROWSET_SIZE} = 2 not
$dbh->{SQL_ROWSET_SIZE} = 2.
I would seriously suggest you try and avoid multiple active statements - there
is nearly always a SQL or some other way.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 22-Jun-2005 Daniel Kasak wrote:
> Jeff Urlwin wrote:
>
>>>Hi all.
>>>
>>>I've just ( barely ) managed to get DBD::ODBC ==> UnixODBC
>>>==> FreeTDS
>>>working on my system.
>>>
>>>I took a Perl-Gtk2 app which has been running fine, and
>>>substituted the
>>>old DBD::Sybase connection with the DBD::ODBC one, and then
>>>discovered I
>>>could only run one query on SQL Server, and then all
>>>subsequent queries
>>>gave:
>>>
>>>DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL
>>>Server]Attempt to
>>>initiate a new SQL Server operation with results pending.
>>>(SQL-07005)(DBD: st_execute/SQLExecute err=-1)
>>>
>>>Damn!
>>>
>>>So I started going through my code and adding $sth->finish after all
>>>recordset operations, and that seems to be fixing things.
>>>
>>>
>>
>>Please see if you can provide a small, self-contained example. (i.e. one
>>that creates
>>tables and inserts data, then reproduces the problem, such that it can be run
>>simply (or,
>>feel free to add it to some of the DBD::ODBC t/20sqlserver.t tests, if you
>>feel slightly
>>more inclined), then I can refine the implementation to ensure that you don't
>>have to call
>>finish each time. It's supposed to be that way, but if it's not working, I'd
>>like to
>>know.
>>
>>
>>
> It's been quite a while, but I've gotten around to building an example
> application.
> The application gives the 'Attempt to initiate a new SQL Server
> operation with results pending.' error and then exits when it can't
> fetch any data. It *should* fetch each row and dump the values to the
> console.
>
> I'm using DBD::ODBC, compiled against UnixODBC & FreeTDS. The code below
> works if I use DBD::Sybase instead of DBD::ODBC
>
> You'll have to create a 'test' database in SQL Server, and change the
> password in the script below. Other than that ( and having a working
> DBD::ODBC setup ), it should be right to go.
>
> ---
>
>#!/usr/bin/perl
>
> use strict;
> use DBI;
>
># Connect to SQL Server
> my $dbh = DBI->connect("dbi:ODBC:SQLServer", 'sa',
> '_ENTER_PASSWORD_HERE', {PrintError => 1})
> || die "Database connectin not made: $DBI::errstr";
>
> $dbh->do("use test");
>
> $dbh->do("create table some_table (
> ID integer identity (1,1),
> CompanyName varchar(50),
> SomeNote varchar(255)
> )");
>
> my $sth = $dbh->prepare("insert into some_table ( CompanyName, SomeNote
> ) values ( ?, ? )");
>
> foreach my $company (
> [ "McDonalds", "Wants monopoly on fast
> foods" ],
> [ "Monsanto", "All-round evil company in
> food production" ],
> [ "BAE Systems", "Manufactures Weapons of
> Mass Destruction (TM)" ],
> [ "Advanta BV", "Poisons food supply with GM
> products" ]
> )
> {
> $sth->execute( $$company[0], $$company[1] );
> }
>
> $sth = $dbh->prepare("select ID from some_table") || die $dbh->errstr;
>
> $sth->execute || die $dbh->errstr;
>
> while (my $row = $sth->fetchrow_hashref) {
>
> my $little_sth = $dbh->prepare("select * from some_table where ID="
> . $row->{ID});
> $little_sth->execute;
> my $little_row = $little_sth->fetchrow_array;
>
> print "Got some data: \n"
> . " Company: " . $$little_row[0] . " ... Note: " . $$little_row[1];
>
> }
>
> ---
>
> --
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: [EMAIL PROTECTED]
> website: http://www.nusconsulting.com.au
