Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 27321
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: PLOBBES [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.13
Fixed in: (no value)



Subject: multiple result sets using fetchall_arrayref fails
Download (untitled) / with headers
text/plain 2.1k
DBI::VERSION 1.53 DBD::ODBC::VERSION 1.13 I have a stored procedure that returns multiple (4) result sets. 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) 2) using fetchall_arrayref(undef, $max_count) with any $max_count > 0 flattens my 4 result sets to 1 large result set. 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_*: $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 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. 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) ) {}; 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) ) {}; 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?
Download (untitled) / with headers
text/plain 2.7k
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. > > 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) > > 2) using fetchall_arrayref(undef, $max_count) with any $max_count > 0 > flattens my 4 result sets to 1 large result set. > > 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 > > 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. > > 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) ) {}; > > 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) ) {}; > > 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? Can you tell me: 1. what odbc driver you are using 2. what platform you are running 3. version of perl 4. send me a cut down version of your procedure or some other self contained procedure which demonstrates the problem. I am working on more_results for the next release. If you go to cpan now you'll find a developer release 1.14_1 with loads of fixes/changes. Martin -- Martin J. Evans Wetherby, UK
From: martin.evans [...] easysoft.com
Download (untitled) / with headers
text/plain 4.4k
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
Subject: Re: [rt.cpan.org #27321] multiple result sets using fetchall_arrayref fails
Date: Thu, 19 Jul 2007 11:57:06 -0400
To: bug-DBD-ODBC [...] rt.cpan.org
From: Phil Lobbes <phil [...] perkpartners.com>
Download (untitled) / with headers
text/plain 502b
[ sorry missed the first comments on RT ] Indeed, I definitely shouldn't have been using while() around the unqualified fetchall and also checking for results returned when using $max_rows sorry for the trouble. I was doing some quick benchmarking and glossed right over that, dooh! As it turned out I decided to bind columns and saw excellent performance and abandoned using other methods. I should have slowed down to look at what I was doing a bit closer, sorry. Thanks for following up! Phil


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.