view release on metacpan or  search on metacpan

examples/fetchscroll.pl  view on Meta::CPAN

			2  => [ 'current',    0, 20 ],
			3  => [ 'first',      0,  1 ],
			4  => [ 'relative',  +3,  4 ],
			5  => [ 'next',       0,  5 ],
			6  => [ 'next',       0,  6 ],
		    7  => [ 'relative',  -3,  3 ],
		    8  => [ 'prev',       0,  2 ],
		    9  => [ 'last',       0,  24 ],
		    10 => [ 'absolute',  -1,  undef ],
		    11 => [ 'absolute',   7,  7 ],
		    12 => [ 'relative', +23,  undef ]
		   );

my %op2 = (
			1  => [ 'next',       0,  1 ],
			2  => [ 'next',       0,  2 ],
			3  => [ 'next',       0,  3 ],
			4  => [ 'relative',  +3,  6 ],
			5  => [ 'next',       0,  7 ],
			6  => [ 'next',       0,  8 ],
		    7  => [ 'relative',  -3,  5 ],
		    8  => [ 'prev',       0,  4 ],
			9  => [ 'next',       0,  5 ],
			10 => [ 'next',       0,  6 ],
			11 => [ 'next',       0,  7 ],
			12 => [ 'next',       0,  8 ],
			13 => [ 'next',       0,  9 ],
			14 => [ 'next',       0, 10 ],
			15 => [ 'next',       0, 11 ],
			16 => [ 'next',       0, 12 ],
			17 => [ 'next',       0, 13 ],
			18 => [ 'next',       0, 14 ],
			19 => [ 'next',       0, 15 ],
			20 => [ 'next',       0, 16 ],
			21 => [ 'next',       0, 17 ],
			22 => [ 'next',       0, 18 ],
			23 => [ 'next',       0, 19 ],
			24 => [ 'next',       0, 20 ],
			25 => [ 'next',       0, 21 ],
			26 => [ 'next',       0, 22 ],
			27 => [ 'next',       0, 23 ],
			28 => [ 'next',       0, 24 ],
			29 => [ 'next',       0, undef ],
			30 => [ 'prev',       0, 24 ],
			31 => [ 'prev',       0, 23 ],
			32 => [ 'prev',       0, 22 ],
			33 => [ 'prev',       0, 21 ],
			34 => [ 'prev',       0, 20 ],
		    35 => [ 'first',      0,  1 ],
			36 => [ 'next',       0,  2 ],
			37 => [ 'prev',       0,  1 ],
			38 => [ 'prev',       0, undef ],
		   );

my @ops = (\%op1, \%op2);

my $sth = $dbh->prepare(qq{SELECT tabid, tabname FROM $tab});

my($nseqs, $ttest, $tfail, $ttwf) = (0, 0, 0, 0, 0);

foreach my $opset (@ops)
{
	$nseqs++;
	print "== Test Sequence $nseqs\n";
	$sth->execute;
	my ($test, $fail) = test_scroll_query_sequence($sth, %$opset);
	$ttest += $test;
	$tfail += $fail;
	$ttwf++ if $fail;
}

print "== Test Sequences $nseqs";
print " ($ttwf with failures)" if $ttwf;
print " Fetch Tests $ttest";
print " (with $tfail failures)" if $tfail;
print "\n";
print (($tfail) ? "== FAILED ==\n" : "== PASSED ==\n");

$dbh->do(qq{drop table $tab});
$dbh->disconnect;

__END__

=head2 "Scroll cursors"

	$sth->setattr(SQL_STMT_CURSOR_ATTR, SQL_CURSOR_SCROLL);
	$sth->execute([@vals]);

The $sth->execute method is the familiar function, but the $sth->setattr
function (which is modelled after the ODBC function SQLStmtSetAttr()) is
new.  If the DBMS being accessed by the driver does not support scroll
cursors (determined via $dbh->getinfo(...)) or the driver has not
implemented the interface to the feature, then you will get a default
implementation (emulation) of scroll cursors shown above.  When
$sth->setattr() is called, it ensures that $sth->execute creates a
scroll cursor for the statement, which must be one that returns values
($sth->{NUM_OF_FIELDS} > 0).

    $row = $sth->fetchrow_scroll_arrayref($move, $offset);
    @row = $sth->fetchrow_scroll_array($move, $offset);
	$ref = $sth->fetchrow_scroll_hashref($move, $offset);

These methods can be used to fetch rows of data via a statement that was
executed with $sth->execute_scroll.  (Optionally: if the $move is
'next', then the fetchrow_scroll_* methods map to the corresponding
regular fetchrow_* method;any $move other than 'next' is rejected.)  The
acceptable values of $move are:

    'next'      == equivalent to 'relative' +1
    'prev'      == equivalent to 'relative' -1
    'current'   == equivalent to 'relative'  0
    'first'     == equivalent to 'absolute'  1
    'last'      == equivalent to 'absolute'  N (N = total rows)
    'relative'  == equivalent to 'absolute'  C + $offset (C = current)
    'absolute'

The value of $offset is only relevant for 'relative' and 'absolute'; it
is ignored for other $move values, but should be set to zero.  For $move
of 'absolute', the value of $offset should be a positive (non-negative)
number, and it should be smaller than the number of rows in the result
set.  For $move of 'relative', the value of $offset can be positive or
negative or zero and the magnitude should be smaller than the number of
rows in the result set.

Previously fetched rows are cached (possibly in the server, possibly in
the client).  When a row is to be fetched, if it is already in the