Please read DBD::ODBC pod. Specifically the section on the odbc_cursortype.
Jeff > -----Original Message----- > From: Daniel Kasak [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 22, 2005 1:22 AM > To: Jeff Urlwin; dbi-users@perl.org > Subject: Re: Attempt to initiate a new SQL Server operation > with results pending. > > > 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 >
