On Mon May 28 12:50:40 2007, PLOBBES wrote:
Show quoted text> DBI::VERSION 1.53
> DBD::ODBC::VERSION 1.13
>
> I have a stored procedure that returns multiple (4) result sets.
I presume something like:
drop procedure testproc;
drop table martin;
create table martin (a int);
insert into martin values(1);
insert into martin values(2);
create procedure testproc as begin select * from martin; select * from
martin; select * from martin; select * from martin; end;
since you didn't say.
Show quoted text> Problems I'm noticing using DBD::ODBC:
>
> 1) using an unqualified fetchall_arrayref() or requesting a hashref
> "slice" via fetchall_arrayref({}, $max_count) causes the following
> (fatal) error:
>
> DBD::ODBC::st fetchall_arrayref failed: (DBD: no select statement
> currently executing err=-1)
see below for explanation.
Show quoted text> 2) using fetchall_arrayref(undef, $max_count) with any $max_count > 0
> flattens my 4 result sets to 1 large result set.
see below for explanation.
Show quoted text> 3) (FYI, something perhaps not DBD::ODBC specific and not something any
> reasonable programmer would do :-) using a max_count of 0 appears to
> cause an infinite loop
>
> More info
> ====================================================
>
> As shown in documentation, things work fine if one uses fetch or
fetchrow_*:
Show quoted text>
> $sth->execute;
> do {
> warn("cols: ", join( " ", @{ $sth->{NAME} } ), "\n");
> while ( $sth->fetchrow_arrayref ) {}; # ROW
> } while ( $sth->{odbc_more_results} );
>
> Output is as expected:
>
> cols: Key
> cols: Date
> cols: AttrName
> cols: Key Date AttrName value Category
as fetchrow_arrayref returns false when there are no more rows so
looping on the return value from fetchrow_arrayref is valid.
Show quoted text> However if trying to use fetchall_arrayref things break down:
>
> $sth->execute;
> do {
> warn("cols: ", join( " ", @{ $sth->{NAME} } ), "\n");
> while ( $sth->fetchall_arrayref ) {}; # ALL
> } while ( $sth->{odbc_more_results} );
>
> cols: Key
> DBD::ODBC::st fetchall_arrayref failed: (DBD: no select statement
> currently executing err=-1) at t/util/bench_thin2.pl line 54.
For the case where you use an unqualified fetchall_arrayref I can
understand this given your code as fetchall_arrayref always returns true
as it always returns an array ref although the array itself may have 0
elements. The DBI docs say:
"If there are no rows to return, "fetchall_arrayref" returns a reference
to an empty array"
DBD::ODBC calls SQLMoreResults immediately after SQLFetch returns
SQL_NO_DATA so in ODBC terms the next result-set is set up and ready to
go (but you should drop into the odbc_more_results call to ensure
DBD::ODBC is set up correctly - especially for output bound parameters).
So if you loop whilst fetchall_arrayref is true (which it always is) you
will get your 4 result-sets (before getting anywhere near
odbc_more_results call) and the fifth time DBD::ODBC will tell you there
is no select statement currently running.
In any case, why on earth would you loop on an unqualified
fetchall_arrayref since by its very nature it returns all the rows so
there cannot be any more to loop on.
If you change your code to:
$sth->execute;
do {
warn("cols: ", join( " ", @{ $sth->{NAME} } ), "\n");
$sth->fetchall_arrayref ) {}
} while ( $sth->{odbc_more_results} );
this one works fine.
Show quoted text> If I switch to having a hashref returned for each row I get the same
> fatal error as above. Here's the fetch call:
>
> ... while ( $sth->fetchall_arrayref({}, $max_rows) ) {};
same reason as above if unqualified. If you put $max_rows in then you
need to check what fetchall_arrayref returns and not just test for it
being true. i.e. you need to do:
my $rs;
do {
$rs = $sth->fetchall_arrayref({}, $max_rows);
} while (scalar(@$rs));
then it works fine.
Show quoted text> If I ask for arrayrefs for rows and use a valid $max_rows value things
> don't blow up but they don't quite work either...
>
> ... while ( $sth->fetchall_arrayref(undef, $max_rows) ) {};
same as above.
Show quoted text> The output only shows the 'cols' from the first result set but all
> result sets are fetched (per my own checks and DBI::Profile output and
> no 'active' handle destroyed errors, etc.):
>
> cols: Key
>
> I'm not sure this is DBD::ODBC specific but I don't have any other
> similar setup to test other drivers at the moment.
>
> PS. With a new release of DBD::ODBC will $sth->more_results() be
supported?
I'm thinking about it but it is a fairly big change since the
SQLMoreResults call needs to be made separately so it is a major reorg.
Martin
