1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
|
#!/usr/bin/perl
use strict;
use Test::More;
use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
use DBI;
unshift @INC ,'t';
require 'nchar_test_lib.pl';
## ----------------------------------------------------------------------------
## 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 $dsn = oracle_test_dsn();
my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';
my $dbh;
eval {$dbh = DBI->connect($dsn, $dbuser, '', { RaiseError=>1,
AutoCommit=>1,
PrintError => 0 })};
if ($dbh) {
plan tests => 32;
} 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();
$dbh->do(qq{
CREATE TABLE $table (
id INTEGER )
});
my ($sql, $sth,$value);
my $i=0;
$sql = "INSERT INTO ".$table." VALUES (?)";
$sth =$dbh-> prepare($sql);
for ($i=1;$i<=10;$i++){
$sth-> bind_param(1, $i);
$sth->execute();
}
$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
for($i=1;$i<=10;$i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_NEXT,0);
cmp_ok($value->[0], '==', $i, '... 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');
#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;
$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,2);
cmp_ok($value->[0], '==', 7, '... we should get the 7th record');
#now -3 records relative from the present position of 6;
$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,-3);
cmp_ok($value->[0], '==', 4, '... we should get the 4th record');
#now get the 9th record from the start
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,9);
cmp_ok($value->[0], '==', 9, '... we should get the 9th record');
#now get the last record
$value = $sth->ora_fetch_scroll(OCI_FETCH_LAST,0);
cmp_ok($value->[0], '==', 10, '... we should get the 10th record');
#now get the ora_scroll_position
cmp_ok($sth->ora_scroll_position(), '==', 10, '... we should get the 10 for the ora_scroll_position');
#now back to the first
$value = $sth->ora_fetch_scroll(OCI_FETCH_FIRST,0);
cmp_ok($value->[0], '==', 1, '... we should get the 1st record');
#check the ora_scroll_position one more time
cmp_ok($sth->ora_scroll_position(), '==', 1, '... we should get the 1 for the ora_scroll_position');
$sth->finish();
drop_table($dbh);
$dbh->disconnect;
1;
|
