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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
#!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;
$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' );
# rt 76695 - fetch after fetch scroll maintains offset
# now fetch forward 2 places then just call fetch
# it should give us the 4th rcord and not the 5th
$value = $sth->ora_fetch_scroll( OCI_FETCH_RELATIVE, 2 );
cmp_ok( $value->[0], '==', 3, '... we should get the 3rd record rt76695' );
($value) = $sth->fetchrow;
cmp_ok( $value, '==', 4, '... we should get the 4th record rt 76695' );
# rt 76410 - fetch after fetch absolute always returns the same row
$value = $sth->ora_fetch_scroll( OCI_FETCH_ABSOLUTE, 2 );
cmp_ok( $value->[0], '==', 2, '... we should get the 2nd row rt76410_2' );
($value) = $sth->fetchrow;
cmp_ok( $value, '==', 3, '... we should get the 3rd row rt76410_2' );
$sth->finish();
drop_table($dbh, $table);
|
