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
