Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

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

Report information
The Basics
Id: 84170
Status: resolved
Priority: 0/
Queue: DBD-Oracle

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

Bug Information
Severity: Important
Broken in: 1.58
Fixed in: (no value)



Subject: after a fetch_scroll(), fetch() never returns undef after the last row
Download (untitled) / with headers
text/plain 800b
Yet another problem with scrollable cursors, similar to #76695 and #76410 : if we use ora_fetch_scroll() to go to some specific row, and then loop over the remaing rows with one of the fetch_* methods, then we get stuck on the last row (fetch() never returns undef). Ex: my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; my $sth = $dbh->prepare($sql, {ora_exe_mode => OCI_STMT_SCROLLABLE_READONLY}); $sth->execute; $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); for (1 .. 5) { # purposedly trying beyond the last row my $r = $sth->fetch; # should get undef at some point .. but no say @$r; } Yields: WRI$_ADV_ASA_RECO_DATA WRR$_REPLAY_CALL_FILTER WRR$_REPLAY_CALL_FILTER WRR$_REPLAY_CALL_FILTER WRR$_REPLAY_CALL_FILTER ...
Download (untitled) / with headers
text/plain 1.3k
On Sat Mar 23 19:40:23 2013, DAMI wrote: Show quoted text
> Yet another problem with scrollable cursors, similar to #76695 and > #76410 : if we use ora_fetch_scroll() to go to some specific row, > and then loop over the remaing rows with one of the fetch_* > methods, then we get stuck on the last row (fetch() never returns > undef). > > > Ex: > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; > my $sth = $dbh->prepare($sql, {ora_exe_mode => > OCI_STMT_SCROLLABLE_READONLY}); > $sth->execute; > $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); > $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); > for (1 .. 5) { # purposedly trying beyond the last row > my $r = $sth->fetch; # should get undef at some point .. but no > say @$r; > } > > Yields: > WRI$_ADV_ASA_RECO_DATA > WRR$_REPLAY_CALL_FILTER > WRR$_REPLAY_CALL_FILTER > WRR$_REPLAY_CALL_FILTER > WRR$_REPLAY_CALL_FILTER > ...
Thank you for this report. I'll try and find some tuits to look in to it soon but my focus is elsewhere right now and I did not write scrollable cursor support (John Scoles did). You could perhaps help yourself by enabling ora_verbose=6 in the connect method call and looking at the trace output. My recollection is that the code is not that complex and you'll find most of it in oci8.c. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 2.9k
On Mon Mar 25 14:41:22 2013, MJEVANS wrote: Show quoted text
> On Sat Mar 23 19:40:23 2013, DAMI wrote:
> > Yet another problem with scrollable cursors, similar to #76695 and > > #76410 : if we use ora_fetch_scroll() to go to some specific row, > > and then loop over the remaing rows with one of the fetch_* > > methods, then we get stuck on the last row (fetch() never returns > > undef). > > > > > > Ex: > > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; > > my $sth = $dbh->prepare($sql, {ora_exe_mode => > > OCI_STMT_SCROLLABLE_READONLY}); > > $sth->execute; > > $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); > > $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); > > for (1 .. 5) { # purposedly trying beyond the last row > > my $r = $sth->fetch; # should get undef at some point .. but no > > say @$r; > > } > > > > Yields: > > WRI$_ADV_ASA_RECO_DATA > > WRR$_REPLAY_CALL_FILTER > > WRR$_REPLAY_CALL_FILTER > > WRR$_REPLAY_CALL_FILTER > > WRR$_REPLAY_CALL_FILTER > > ...
> > Thank you for this report. > > I'll try and find some tuits to look in to it soon but my focus is > elsewhere right now and I did not write scrollable cursor support > (John Scoles did). > > You could perhaps help yourself by enabling ora_verbose=6 in the > connect method call and looking at the trace output. My recollection > is that the code is not that complex and you'll find most of it in > oci8.c. > > Martin
ok, I saved someone (perhaps me) some time on this one: use DBI; use strict; use warnings; use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes); my $h = DBI->connect("dbi:Oracle:host=xxx;sid=xxx","xxx","xxx", {RaiseError => 1, ora_verbose => 6}); eval { $h->do(q/drop table mje/); }; $h->do(q/create table mje (a int)/); foreach (1..5) { $h->do(qq/insert into mje values($_)/); } my $sql = "SELECT * from mje"; my $sth = $h->prepare($sql, {ora_exe_mode => OCI_STMT_SCROLLABLE_READONLY}); $sth->execute; $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); for (1 .. 5) { # purposedly trying beyond the last row my $r = $sth->fetch; # should get undef at some point .. but no print @$r, "\n"; } produces trace output: 4 dbd_st_fetch 1 fields... Scrolling Fetch, position before fetch=4, Orientation = OCI_FETCH_NEXT , Fetchoffset =1 OCIStmtFetch(8473a74,8467e34,1,2,1)=SUCCESS <--- NOTICE SUCCESS OCIAttrGet(8473a74,OCI_HTYPE_STMT,848aaf0,0,OCI_ATTR_CURRENT_POSITION,8467e34)=SUCCESS Scrolling Fetch, postion after fetch=5 dbd_st_fetched 1 fields with status of 0(SUCCESS) field #1 with rc=0(OK) 821b6c0 (field=0): '5' 5 dbd_st_fetch 1 fields... Scrolling Fetch, position before fetch=5, Orientation = OCI_FETCH_NEXT , Fetchoffset =1 OCIStmtFetch(8473a74,8467e34,1,2,1)=NO_DATA <--- NOTICE NO_DATA Seems like the scrollable cursor code is not paying attention to NO_DATA. Martin -- Martin J. Evans Wetherby, UK
On Mon Mar 25 15:08:00 2013, MJEVANS wrote: Show quoted text
> On Mon Mar 25 14:41:22 2013, MJEVANS wrote:
> > On Sat Mar 23 19:40:23 2013, DAMI wrote:
> > > Yet another problem with scrollable cursors, similar to #76695 and > > > #76410 : if we use ora_fetch_scroll() to go to some specific
> row,
> > > and then loop over the remaing rows with one of the fetch_* > > > methods, then we get stuck on the last row (fetch() never
> returns
> > > undef). > > > > > > > > > Ex: > > > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name
> ";
> > > my $sth = $dbh->prepare($sql, {ora_exe_mode => > > > OCI_STMT_SCROLLABLE_READONLY}); > > > $sth->execute; > > > $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); > > > $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); > > > for (1 .. 5) { # purposedly trying beyond the last row > > > my $r = $sth->fetch; # should get undef at some point .. but no > > > say @$r; > > > } > > > > > > Yields: > > > WRI$_ADV_ASA_RECO_DATA > > > WRR$_REPLAY_CALL_FILTER > > > WRR$_REPLAY_CALL_FILTER > > > WRR$_REPLAY_CALL_FILTER > > > WRR$_REPLAY_CALL_FILTER > > > ...
> > > > Thank you for this report. > > > > I'll try and find some tuits to look in to it soon but my focus is > > elsewhere right now and I did not write scrollable cursor support > > (John Scoles did). > > > > You could perhaps help yourself by enabling ora_verbose=6 in the > > connect method call and looking at the trace output. My recollection > > is that the code is not that complex and you'll find most of it in > > oci8.c. > > > > Martin
> > ok, I saved someone (perhaps me) some time on this one: > > > use DBI; > use strict; > use warnings; > use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes); > > my $h = DBI->connect("dbi:Oracle:host=xxx;sid=xxx","xxx","xxx", > {RaiseError => 1, ora_verbose => 6}); > eval { > $h->do(q/drop table mje/); > }; > > $h->do(q/create table mje (a int)/); > > foreach (1..5) { > $h->do(qq/insert into mje values($_)/); > } > my $sql = "SELECT * from mje"; > my $sth = $h->prepare($sql, {ora_exe_mode => > OCI_STMT_SCROLLABLE_READONLY}); > $sth->execute; > $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); > $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); > > for (1 .. 5) { # purposedly trying beyond the last row > my $r = $sth->fetch; # should get undef at some point .. but no > print @$r, "\n"; > } > > produces trace output: > > 4 > dbd_st_fetch 1 fields... > Scrolling Fetch, position before fetch=4, Orientation = > OCI_FETCH_NEXT , Fetchoffset =1 > OCIStmtFetch(8473a74,8467e34,1,2,1)=SUCCESS <--- NOTICE > SUCCESS > OCIAttrGet(8473a74,OCI_HTYPE_STMT,848aaf0,0,OCI_ATTR_CURRENT_POSITION,8467e34)=SUCCESS > Scrolling Fetch, postion after fetch=5 > dbd_st_fetched 1 fields with status of 0(SUCCESS) > field #1 with rc=0(OK) > > 821b6c0 (field=0): '5' > 5 > dbd_st_fetch 1 fields... > Scrolling Fetch, position before fetch=5, Orientation = > OCI_FETCH_NEXT , Fetchoffset =1 > OCIStmtFetch(8473a74,8467e34,1,2,1)=NO_DATA <--- NOTICE > NO_DATA > > Seems like the scrollable cursor code is not paying attention to > NO_DATA. > > Martin
This code in oci8.c pays no attention to the status returned from OCIFetch: if (DBIc_DBISTATE(imp_sth)->debug >= 4 || dbd_verbose >= 4 ) PerlIO_printf( DBIc_LOGPIO(imp_sth), " Scrolling Fetch, position before fetch=%d, " "Orientation = %s , Fetchoffset =%d\n", imp_sth->fetch_position, oci_fetch_options(imp_sth->fetch_orient), imp_sth->fetch_offset); OCIStmtFetch_log_stat(imp_sth, imp_sth->stmhp, imp_sth->errhp,1, imp_sth->fetch_orient,imp_sth->fetch_offset, status); /*this will work without a round trip so might as well open it up for all statments handles*/ /* default and OCI_FETCH_NEXT are the same so this avoids miscaluation on the next value*/ OCIAttrGet_stmhp_stat(imp_sth, &imp_sth->fetch_position, 0, OCI_ATTR_CURRENT_POSITION, status); NOTE: status is never examined! Martin -- Martin J. Evans Wetherby, UK
Try applying this change: $ svn diff Index: oci8.c =================================================================== --- oci8.c (revision 15588) +++ oci8.c (working copy) @@ -4002,6 +4002,10 @@ OCIStmtFetch_log_stat(imp_sth, imp_sth->stmhp, imp_sth->errhp,1, imp_sth->fetch_orient,imp_sth->fetch_offset , status); /*this will work without a round trip so might as well open it up for all statments handles*/ /* default and OCI_FETCH_NEXT are the same so this avoids miscaluation on the next value*/ + if (status==OCI_NO_DATA){ + return Nullav; + } + OCIAttrGet_stmhp_stat(imp_sth, &imp_sth->fetch_position, 0, OCI_ATTR_CURRENT_POSITION, status); if (DBIc_DBISTATE(imp_sth)->debug >= 4 || dbd_verbose >= 4 ) With my script above altered to: use DBI; use strict; use warnings; use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes); my $h = DBI->connect("dbi:Oracle:host=alvis.easysoft.local;sid=devel","bet","b3t", {RaiseError => 1, ora_verbose => 0}); eval { $h->do(q/drop table mje/); }; $h->do(q/create table mje (a int)/); foreach (1..5) { $h->do(qq/insert into mje values($_)/); } my $sql = "SELECT * from mje"; my $sth = $h->prepare($sql, {ora_exe_mode => OCI_STMT_SCROLLABLE_READONLY}); $sth->execute; $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0); $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2); for (1 .. 5) { # purposedly trying beyond the last row my $r = $sth->fetch; # should get undef at some point .. but no last if !$r; print @$r, "\n"; } it produces: $ perl -Iblib/lib -Iblib/arch rt_84170.pl 4 5 Let me know how you get on. If it works for you come back to the rt and maybe Yanick could apply the patch. Even better would be if you could add a test case to t/51scroll.t which you can find and enhance if you download the distribution - tests are not installed - mores the pity. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 103b
I've added a test case to 51scroll.t test in subversion trunk. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 192b
Le Lun 25 Mar 2013 15:27:47, MJEVANS a écrit : Show quoted text
> Try applying this change:
[...] Show quoted text
> > Let me know how you get on. >
Hi, thanks for the quick response. Indeed this patch fixes the problem.
Download (untitled) / with headers
text/plain 373b
On Wed Mar 27 03:08:24 2013, DAMI wrote: Show quoted text
> Le Lun 25 Mar 2013 15:27:47, MJEVANS a écrit :
> > Try applying this change:
> [...]
> > > > Let me know how you get on. > >
> > Hi, thanks for the quick response. > Indeed this patch fixes the problem.
Patched in subversion trunk and will be in next release. Thanks for the report. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 245b
Le Jeu 28 Mar 2013 16:23:30, MJEVANS a écrit : Show quoted text
> Patched in subversion trunk and will be in next release.
Hi, I noticed that the patch did not make it to v1.60 because of merge problems. So do you have any idea when v1.61 will be published ?
Download (untitled) / with headers
text/plain 245b
Le Jeu 28 Mar 2013 16:23:30, MJEVANS a écrit : Show quoted text
> Patched in subversion trunk and will be in next release.
Hi, I noticed that the patch did not make it to v1.60 because of merge problems. So do you have any idea when v1.61 will be published ?
Download (untitled) / with headers
text/plain 593b
On Sat Apr 13 15:06:30 2013, DAMI wrote: Show quoted text
> Le Jeu 28 Mar 2013 16:23:30, MJEVANS a écrit : >
> > Patched in subversion trunk and will be in next release.
> > Hi, I noticed that the patch did not make it to v1.60 because of merge > problems. So do you have any idea when v1.61 will be published ?
I don't do the releases, Yanick does. DBD::Oracle is moving to git permanently as the svn.perl.org is going away so may be he'll do a release after that. Generally Yanick does fairly regular releases so long as there is something worthwhile to release. Martin -- Martin J. Evans Wetherby, UK
Subject: Re: [rt.cpan.org #84170] after a fetch_scroll(), fetch() never returns undef after the last row
Date: Mon, 15 Apr 2013 15:38:11 -0400
To: bug-DBD-Oracle [...] rt.cpan.org
From: Yanick Champoux <champoux [...] pythian.com>
Download (untitled) / with headers
text/plain 512b
On 13-04-14 05:50 AM, Martin J Evans via RT wrote: Show quoted text
> I don't do the releases, Yanick does. DBD::Oracle is moving to git permanently as the svn.perl.org is going away so may be he'll do a release after that. Generally Yanick does fairly regular releases so long as there is something worthwhile to release.
And since I actually had a few minutes free this afternoon... v1.61_00 is now on its way to CPAN. As usual, if no issue is found, it'll be promoted to v1.62 in two weeks. Joy, `/anick -- --


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.