Re: DBI and fetchall_arrayref with max_rows?

On 06/12/10 16:12, Jonathan Leffler wrote:
> 
> On Mon, Dec 6, 2010 at 07:20, Tim Bunce <[email protected] <mailto:[email protected]>> wrote:
> 
>     On Mon, Dec 06, 2010 at 12:54:57AM -0800, Jonathan Leffler wrote:
>     > Two related questions (prompted by a question from people using
>     > DBD::Informix):
>     >
>     > 1.  Are there any known problems with $sth->fetchall_arrayref({}, 50);?
> 
>     Nope.
> 
> 
> OK - thanks for the quick look, Tim (and Martin).  That quickly isolates the problem to DBD::Informix.
>  
> 
>     > 2.  Are there any requirements on a DBD module to make the limit on
>     > fetchall_arrayref({}, 50) notation work, or is it purely a DBI feature?
> 
>     The DBI provides a default fetchall_arrayref method in DBI.pm that
>     implements fetching, slicing and maxrows.
> 
>     There's also a fetchall_arrayref method in Driver.xst for added speed.
>     That one falls-back to the perl version if a slice is specified, but
>     otherwise expects dbdxst_fetchall_arrayref() to handle maxrows.
>     dbdxst_fetchall_arrayref() is defined in Driver_xst.h
> 
> 
> AFAIK, I'm using the DBI-provided fetchall; the only reference to fetchall in Informix.pm is in POD.  It appears in Informix.xsi and Informix.c, but that is stuff from DBI and the driver support code.
> 
> I've attached the output of:
> 
>     DBI_TRACE=9 perl fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl>
> 
> The fetchall_arrayref call was:
> 
>     fetchall_arrayref(undef, 50);
> 
> I'm not sure how many problems that is revealing.  The most immediately relevant section looks like:
> 
>     >> execute     DISPATCH (DBI::st=HASH(0x1009177a8) rc1/1 @1 g2 ima1041 pid#74162) at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 8
>     -> execute for DBD::Informix::st (DBI::st=HASH(0x1009177a8)~0x1009176d0)
>     -->> DBD::Informix::dbd_ix_st_execute()
>     -->> DBD::Informix::dbd_ix_open()
>     <<-- DBD::Informix::dbd_ix_open()
>     <<-- DBD::Informix::dbd_ix_st_execute()
>     <- execute= ( '0E0' ) [1 items] at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 8
>     >> fetchall_arrayref DISPATCH (DBI::st=HASH(0x1009177a8) rc1/1 @3 g2 ima1 pid#74162) at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 16
>     -> fetchall_arrayref for DBD::Informix::st (DBI::st=HASH(0x1009177a8)~0x1009176d0 undef 50)

dbd_st_fetch should get called here via $sth->fetch.

The code in DBI seems fairly straight forward:

    sub fetchall_arrayref {	# ALSO IN Driver.xst
	my ($sth, $slice, $max_rows) = @_;

        # when batch fetching with $max_rows were very likely to try to
        # fetch the 'next batch' after the previous batch returned
        # <=$max_rows. So don't treat that as an error.
        return undef if $max_rows and not $sth->FETCH('Active');

	my $mode = ref($slice) || 'ARRAY';
	my @rows;
	my $row;
	if ($mode eq 'ARRAY') {
	    # we copy the array here because fetch (currently) always
	    # returns the same array ref. XXX
	    if ($slice && @$slice) {
                $max_rows = -1 unless defined $max_rows;
		push @rows, [ @{$row}[ @$slice] ]
		    while($max_rows-- and $row = $sth->fetch);
	    }
	    elsif (defined $max_rows) {
		push @rows, [ @$row ]
		    while($max_rows-- and $row = $sth->fetch); <---- why didn't you get here?
	    }
	    else {
		push @rows, [ @$row ] while($row = $sth->fetch);
	    }
	}

Is your $sth Active?

>     <- fetchall_arrayref= ( undef ) [1 items] row-1 at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 16
>     >> disconnect  DISPATCH (DBI::db=HASH(0x1009175c8) rc1/1 @1 g2 ima10c01 pid#74162) at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 30
>     -> disconnect for DBD::Informix::db (DBI::db=HASH(0x1009175c8)~0x1009174c0)
> 
> It seems that the DISPATCH code is doing something, but not reaching DBD::Informix code (the DBD::Informix debugging usually has the double-headed arrows (-->> or <<--) but it doesn't show any activity.
> 
> By contrast, when I used the variant with just a slice (fetchall_arrayref([0,1,2])), I get to see:
> 
>     <- execute= ( '0E0' ) [1 items] at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 8
>     >> fetchall_arrayref DISPATCH (DBI::st=HASH(0x1009177d8) rc1/1 @2 g2 ima1 pid#74507) at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 12
>     -> fetchall_arrayref for DBD::Informix::st (DBI::st=HASH(0x1009177d8)~0x100917700 ARRAY(0x1009174a8))
>     >> fetch       DISPATCH (DBI::st=HASH(0x100917700) rc1/2 @1 g2 ima0 pid#74507) at /Users/jleffler/perl/v5.13.4/lib/site_perl/5.13.4/darwin-2level/DBI.pm line 2016 via  at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 12
> 1   -> fetch for DBD::Informix::st (DBI::st=HASH(0x100917700)~INNER)
>         -->> DBD::Informix::dbd_ix_st_fetch()
>         -->> DBD::Informix::dbd_ix_blobs()
>         ---- dbd_ix_blobs(): 0 BYTE/TEXT blobs
>         <<-- DBD::Informix::dbd_ix_blobs()
>         ---- dbd_ix_st_fetch: FETCH c_000000000 into d_000000000
>     dbih_setup_fbav alloc for 7 fields
>     dbih_setup_fbav now 7 fields
>         ---- dbd_ix_st_fetch colno 1: coltype = 2
>         ---- dbd_ix_st_fetch colno 2: coltype = 0
>         ---- dbd_ix_st_fetch colno 3: coltype = 0
>         ---- dbd_ix_st_fetch colno 4: coltype = 5
>         ---- dbd_ix_st_fetch colno 5: coltype = 1
>         ---- dbd_ix_st_fetch colno 6: coltype = 0
>         ---- dbd_ix_st_fetch colno 7: coltype = 0
>         <<-- DBD::Informix::dbd_ix_st_fetch()
> 1   <- fetch= ( [ '1' 'H  ' 'Hydrogen            ' '1.0079' '1' '1 ' 'Y' ] ) [1 items] row1 at /Users/jleffler/perl/v5.13.4/lib/site_perl/5.13.4/darwin-2level/DBI.pm line 2016 via  at fetchall_maxrows_bug.pl <http://fetchall_maxrows_bug.pl> line 12
> 
> Can you see anything to indicate what I've got mis-initialized that is causing the breakage, Tim (or anyone)?  Superficially, it looks like DBI decided not to call the DBD::Informix fetch functions when maxrows was in effect.  There must be a reason, but I've no idea what to look for.

Also double-check you rebuilt DBD::Informix after installing DBI 1.615 -
although I think DBI catches that now.

> 
>     p.s. I took a quick look at DBD::Informix source and ran away fast! :)
> 
> 
> Fair enough...it probably needs a complete rewrite.
> 
> I had been going to release a new DBD::Informix with support for Perl 5.13 / 5.14.  I probably need to hold off until I've resolved this problem.
> 
> Strictly, I'm using DBD::Informix version 2010.0914.20100929; however, it is essentially the same as DBD::Informix 2008.0513 - especially in this area.
>  
> 
>     > my $dbh = DBI->connect('dbi:Informix:stores', '', '') or die "Horribly 1";
>     > my $sth = $dbh->prepare(q{SELECT * FROM Elements}) or die "Horribly 2";
>     > $sth->execute;
>     >
>     > #my $res = $sth->fetchall_arrayref;        # Works
>     > #my $res = $sth->fetchall_arrayref({}, 50);    # Fails: no data
>     > #my $res = $sth->fetchall_arrayref([], 50);    # Fails: no data
>     > #my $res = $sth->fetchall_arrayref([0,1,2]);    # Works
>     > my $res = $sth->fetchall_arrayref([0,1,2], 50);   # Fails: no data
>     > #my $res = $sth->fetchall_arrayref({});    # Works
>     > #my $res = $sth->fetchall_arrayref(undef, 50);  # Fails: no data
>     > foreach my $row (@{$res})
>     > {
>     >     # Use this section for printing arrays
>     >     foreach my $value (@{$row})
>     >     {
>     >         printf "%s ", $value;
>     >     }
>     >     print "\n";
>     >     # Use this section for printing hashes
>     >     #foreach my $key (sort keys %{$row})
>     >     #{
>     >     #    printf "%-15s = %s\n", $key, $row->{$key};
>     >     #}
>     > }
>     >
>     > $dbh->disconnect;
>     >
>     > Choose your own driver and database, etc; choose your own table (my table of
>     > elements has entries for hydrogen through ununoctium - 1..118; 50 is about
>     > half the table).  It seems that fetchall_arrayref() works fine with no
>     > arguments and with slice arguments, but none of the slice versions with a
>     > maximum count does anything.
>     >
>     > Am I missing something?  Or is there a bug?
>     >
>     > Perl 5.13.4 on MacOS X 10.6.5; DBI 1.615, DBD::Informix 2008.0513.
> 
> 
> 
> -- 
> Jonathan Leffler <[email protected] <mailto:[email protected]>>  #include <disclaimer.h>
> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com