Hi all,
Since we seem to be on the subject of Stored Procedures, I thought
I'd throw my problem out there, since I can't figure out what I'm
doing wrong.
The SP builds a temp table, updates the source table from the temp
table and then returns a recordset with the contents of the temp
table.
The error I'm getting is 'statement handle not active' on the fetchrow_array
call. The update statement executes.
What I find interesting is that the execute returns the number of rows
affected (per the docs). I wonder if that is what's killing the
fact that I have a recordset there, too.
I realize I'm probably missing something obvious. Thanks in advance.
Dave
This is SQL Server 2000, DBI 1.43, DBD::ADO 2.91, all on XP:
create procedure p
@p int
as
select top 10 c1, c2
into #tmp
from t
where c3 = @p
update t
set c4 = 1
from t, #tmp t2
where t.c1 = t2.c1
select c1, c2
from #tmp
order by c2, c1
go
and the perl that runs this (I removed the error handling for brevity):
#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("dbi:ADO:Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=db", "user","pass");
die "Connect failed: " . $DBI::errstr if (!defined $dbh);
my $sh = $dbh->prepare("p 1");
$sh->trace(3);
$sh->execute();
while (my @ar = $sh->fetchrow_array()) {
print join(":", @ar), "\n";
}
$dbh->disconnect;
The trace:
DBI::st=HASH(0x1a5d8e0) trace level set to 0x0/3 (DBI @ 0x0/0) in DBI 1.43-i
thread (pid 2552)
-> execute for DBD::ADO::st (DBI::st=HASH(0x1a5d5a4)~0x1a5d8e0) thr#22433c
1 <> FETCH= 'p 1' ('Statement' from cache) at ADO.pm line 1338 via t.pl line 1
0
<- execute= 10 at t.pl line 10
-> fetchrow_array in DBD::_::st for DBD::ADO::st (DBI::st=HASH(0x1a5d5a4)~0x
1a5d8e0) thr#22433c
1 -> fetch for DBD::ADO::st (DBI::st=HASH(0x1a5d8e0)~INNER) thr#22433c
2 -> FETCH for DBD::ADO::st (DBI::st=HASH(0x1a5d8e0)~INNER 'Active') thr#22433
c
.. FETCH DBI::st=HASH(0x1a5d8e0) 'Active' = ''
2 <- FETCH= '' at ADO.pm line 1468 via t.pl line 12
2 -> set_err in DBD::_::common for DBD::ADO::st (DBI::st=HASH(0x1a5d8e0)~INNER
-900 'Statement handle not marked as Active.') thr#22433c
!! ERROR: -900 'Statement handle not marked as Active.' (err#1)
2 <- set_err= undef at ADO.pm line 1468 via t.pl line 12
!! ERROR: -900 'Statement handle not marked as Active.' (err#1)
1 <- fetch= undef row-1 at t.pl line 12
!! ERROR: -900 'Statement handle not marked as Active.' (err#1)
<- fetchrow_array= ( ) [0 items] row-1 at t.pl line 12
DBD::ADO::st fetchrow_array failed: Statement handle not marked as Active. at t.
pl line 12.
-> DESTROY for DBD::ADO::st (DBI::st=HASH(0x1a5d8e0)~INNER) thr#22433c
-> finish for DBD::ADO::st (DBI::st=HASH(0x1a5d8e0)~INNER) thr#22433c
<- finish= 1 at ADO.pm line 1542 via t.pl line 0
<- DESTROY= undef
