I don't use ADO but I do use ODBC. SQL Server normally batches the statements in
a procedure so in ODBC terms for your procedure you have to:
execute
fetchall
SQLMoreResults # moves to the update
rowcount - to get row update count
SQLMoreResults - # moves to the second select
fetchall
SQLMoreResult - returns SQL_NO_DATA
DBD::ODBC used to have a lot of problems with procedures like this including
hitting an issue in the MS SQL Server driver which would return invalid cursor
state when prepare/execute was used but worked fine with SQLExecDirect.
My guess (and it is a guess only) is that DBD::ADO has pulled the rows from the
first result-set and knows there is nothing more (hence handle not active)
because it has not done the equivalent of SQLMoreResults. Of course, for I know
DBD::ADO has a more results method.
BTW, DBD::ODBC calls SQLMoreResults automatically for you sometimes. Basically
if SQLNumResultCols returns 0 then the last statement was an
insert/update/delete so it calls SQLMoreResults for you. If SQLNumResultCols
returns > 0 then there is a result-set and you have to use odbc_more_results
method to move on. DBD::DB2 also had an equivalent of odbc_more_results.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 01-Mar-2005 David N Murray wrote:
> 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
