John Scoles wrote: > Martin Evans wrote: > > If I am not mistaken RowsInCache should return the # of records left > before another fetch (from server) takes place? > The way I implemented it in DBD::Oracle is a simple countdown from n. > > So you would like it to be a countdown from n-1 like this > > dbh/rcs 3, dbh/ric 2 > dbh/rcs 3, dbh/ric 1 > dbh/rcs 3, dbh/ric 0 > dbh/rcs 3, dbh/ric 2 > dbh/rcs 3, dbh/ric 1 > dbh/rcs 3, dbh/ric 0 > > n being the # of records fetched? >
but unless I am mistaken John, my test code demonstrates this is not the case in the 1.24 rc1 since I set RowCacheSize = 3 and get RowsInCache=3 after the first fetch when I expected 2 (as your example above). If I change my example to print the row* stuff before the first fetch I get: dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef <-- 3 before fetch dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef <-- 3 after fetch dbh/rcs 3, dbh/ric 2, sth/rcs undef, sth/ric undef dbh/rcs 3, dbh/ric 1, sth/rcs undef, sth/ric undef <-- no rows left dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef <-- 2 rows left dbh/rcs 3, dbh/ric 2, sth/rcs undef, sth/ric undef dbh/rcs 3, dbh/ric 1, sth/rcs undef, sth/ric undef I have not looked at the code yet but I am guessing that when there are no rows in the cache DBD::Oracle retrieves some rows and sets RowsInCache to the retrieved rows. When fetch is called again and the cache is not empty it decrements RowsInCache. This would explain the behavior I am seeing. > Of course this is assuming that RowCacheSize is set to 3 meaning I want > a cache of 3 rows. > > Perhaps we should expand DBI so we can have both a > > RowCacheSize which we have. > and a > > RowsToFetch which is RowsInCache as it is now isn't it? > that would tell the server how many rows to get at a single fetch?? > > Most SQL servers can to this now if I am not mistaken? > > cheers > John Scoles >> Hi, What about RowsInCache being undef from a statement handle but defined from the connection handle - this seems to contradict DBI - so one or the other seems wrong. Perhaps I've misunderstood your point - if so, I appologise. BTW, I wondered if you had got anywhere with the fixes in 1.24 I reported earlier this week. If I had a commit bit I would be happy to fix them myself if you are struggling to find time. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com >> I was attempting to check DBD::Oracle 1.24 rc1 fixed the issue >> "RowCacheSize is being ignored slowing down fetches a lot", >> http://rt.cpan.org/Public/Bug/Display.html?id=46763 and came across a >> possible bug/inconsistency in DBI or DBD::Oracle. >> >> DBI says RowCacheSize is a connection attribute and RowsInCache is a >> statement attribute - this seems to imply to me there can only be a >> single cache per connection OR you cannot have different cache sizes per >> statement AND the cache size must be set before creating a statement. >> Was is the intention of the DBI spec to disallow setting the cache size >> per statement? >> >> In DBD::Oracle setting and retrieving RowCacheSize on a connection >> handle works (as per DBI spec) but retrieving the RowsInCache only works >> from a connection handle and not as documented from the statement handle >> e.g., >> >> use 5.010; >> use strict; >> use warnings; >> use DBI; >> >> my $h = DBI->connect('dbi:Oracle:host=xxx;sid=xxx', 'xxx', 'xxx', >> {PrintError => 0, RaiseError => 1}); >> my ($s, $field); >> >> eval {$h->do('drop table martin');}; >> eval {$h->do('create table martin (a int)');}; >> >> $h->{AutoCommit} = 0; >> >> $s = $h->prepare('insert into martin values(?)'); >> for (my $i = 1; $i <= 6; $i++) { >> $s->execute($i); >> } >> $h->commit; >> $h->{AutoCommit} = 1; >> >> $h->{RowCacheSize} = 3; >> >> my @results; >> $s = $h->prepare('select a from martin'); >> $s->execute; >> $s->bind_col(1, \$field); >> while ($s->fetch) { >> push @results, $field; >> say "dbh/rcs ", >> ($h->{RowCacheSize} // 'undef'), ', ', >> "dbh/ric ", ($h->{RowsInCache} // 'undef'), ', ', >> "sth/rcs ", ($s->{RowCacheSize} // 'undef'), ', ', >> "sth/ric ", ($s->{RowsInCache} // 'undef'); >> } >> print scalar(@results) . " rows fetched\n"; >> >> produces: >> >> dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef >> dbh/rcs 3, dbh/ric 2, sth/rcs undef, sth/ric undef >> dbh/rcs 3, dbh/ric 1, sth/rcs undef, sth/ric undef >> dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef >> dbh/rcs 3, dbh/ric 2, sth/rcs undef, sth/ric undef >> dbh/rcs 3, dbh/ric 1, sth/rcs undef, sth/ric undef >> >> in otherwords the RowsInCache is only retrievable from the connection >> handle (contrary to DBI docs) thus re-enforcing my guess the cache is >> per connection and not what I expected which was per stmt. >> >> Also, DBI says for RowsInCache: >> >> "If the driver supports a local row cache for SELECT statements, then >> this attribute holds the number of un-fetched rows in the cache" >> >> and it looks like DBD::Oracle is reporting 1 more than it should i.e., >> before the fetch instead of after it. >> >> Any ideas/opinions on what it right/wrong here and what should happen? >> >> I'll rt it when I have a better idea of what is at fault - if anything. >> >> BTW, the links on CPAN in the DBI docs for RowCacheSize/RowsInCache and >> a number of others do not work as the title of the sections is >> "RowsInCache (integer)" and not "RowsInCache" as used in the L<> >> references. I am prepared to fix that Tim if you just confirm you want >> the section to stay as "XXX (integer)". >> >> Martin >> > >
