On 06/12/10 16:37, Martin J. Evans wrote:
> On 06/12/10 16:12, Jonathan Leffler wrote:
>>
>> On Mon, Dec 6, 2010 at 07:20, Tim Bunce <tim.bu...@pobox.com
>> <mailto:tim.bu...@pobox.com>> 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);
> }
> }
By here I really meant the equivalent in Driver_xst.h
#ifndef dbd_fetchall_arrayref
static SV *
dbdxst_fetchall_arrayref(SV *sth, SV *slice, SV *batch_row_count)
{
dTHX;
D_imp_sth(sth);
SV *rows_rvav;
if (SvOK(slice)) { /* should never get here */
char errmsg[99];
sprintf(errmsg,"slice param not supported by XS version of
fetchall_arrayref");
sv_setpv(DBIc_ERRSTR(imp_sth), errmsg);
sv_setiv(DBIc_ERR(imp_sth), (IV)-1);
return &PL_sv_undef;
}
else {
IV maxrows = SvOK(batch_row_count) ? SvIV(batch_row_count) : -1;
AV *fetched_av;
AV *rows_av = newAV();
if ( !DBIc_ACTIVE(imp_sth) && maxrows>0 ) {
/* to simplify application logic we return undef without an error
*/
/* if we've fetched all the rows and called with a batch_row_count
*/
return &PL_sv_undef;
}
av_extend(rows_av, (maxrows>0) ? maxrows : 31);
while ( (maxrows < 0 || maxrows-- > 0)
&& (fetched_av = dbd_st_fetch(sth, imp_sth)) <---- here
) {
AV *copy_row_av = av_make(AvFILL(fetched_av)+1,
AvARRAY(fetched_av));
av_push(rows_av, newRV_noinc((SV*)copy_row_av));
}
rows_rvav = sv_2mortal(newRV_noinc((SV *)rows_av));
}
return rows_rvav;
}
#endif
> Is your $sth Active?
still looks suspicious
>> <- 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 <jonathan.leff...@gmail.com
>> <mailto:jonathan.leff...@gmail.com>> #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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
