view release on metacpan or  search on metacpan

t/51scroll.t  view on Meta::CPAN

#!perl

use strict;
use warnings;

use lib 't/lib';
use DBDOracleTestLib qw/ oracle_test_dsn table drop_table db_handle force_drop_table /;

use Test::More;
use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
use DBI;

## ----------------------------------------------------------------------------
## 51scroll.t
## By John Scoles, The Pythian Group
## ----------------------------------------------------------------------------
##  Just a few checks to see if one can use a scrolling cursor
##  Nothing fancy.
## ----------------------------------------------------------------------------

# create a database handle
my $dbh = eval { db_handle( {
            RaiseError => 1,
            AutoCommit => 1,
            PrintError => 0
        })};
if ($dbh) {
    plan skip_all => 'Scrollable cursors new in Oracle 9'
      if $dbh->func('ora_server_version')->[0] < 9;
    plan tests => 37;
}
else {
    plan skip_all => 'Unable to connect to Oracle';
}
ok( $dbh->{RowCacheSize} = 10 );

# check that our db handle is good
isa_ok( $dbh, 'DBI::db' );

my $table = table();

eval { force_drop_table( $dbh, $table ) };
$dbh->do(qq{ CREATE TABLE $table ( id INTEGER ) });

my ( $sql, $sth, $value );
my $i = 0;
$sql = "INSERT INTO $table VALUES (?)";

$sth = $dbh->prepare($sql);

$sth->execute($_) foreach ( 1 .. 10 );

$sql = "select * from $table";
ok(
    $sth = $dbh->prepare(
        $sql,
        {
            ora_exe_mode        => OCI_STMT_SCROLLABLE_READONLY,
            ora_prefetch_memory => 200
        }
    )
);
ok( $sth->execute() );

#first loop all the way forward with OCI_FETCH_NEXT
foreach ( 1 .. 10 ) {
    $value = $sth->ora_fetch_scroll( OCI_FETCH_NEXT, 0 );
    is( $value->[0], $_, '... we should get the next record' );
}
$value = $sth->ora_fetch_scroll( OCI_FETCH_CURRENT, 0 );
cmp_ok( $value->[0], '==', 10, '... we should get the 10th record' );

# fetch off the end of the result-set
$value = $sth->ora_fetch_scroll( OCI_FETCH_NEXT, 0 );
is( $value, undef, 'end of result-set' );

#now loop all the way back
for ( $i = 1 ; $i <= 9 ; $i++ ) {
    $value = $sth->ora_fetch_scroll( OCI_FETCH_PRIOR, 0 );
    cmp_ok( $value->[0], '==', 10 - $i, '... we should get the prior record' );
}

#now +4 records relative from the present position of 0;

$value = $sth->ora_fetch_scroll( OCI_FETCH_RELATIVE, 4 );
cmp_ok( $value->[0], '==', 5, '... we should get the 5th record' );

#now +2 records relative from the present position of 4;