xref: /PHP-8.1/ext/oci8/tests/bug40415.phpt (revision b5a14e6c) 
1--TEST--
2Bug #40415 (Using oci_fetchall with nested cursors)
3--EXTENSIONS--
4oci8
5--SKIPIF--
6<?php
7$target_dbs = array('oracledb' => true, 'timesten' => false);  // test runs on these DBs
8require(__DIR__.'/skipif.inc');
9?>
10--FILE--
11<?php
12
13require __DIR__."/connect.inc";
14
15// Setup
16
17$create_1 = "CREATE TABLE t1 (id1 INTEGER)";
18$create_2 = "CREATE TABLE t2 (id2 INTEGER)";
19$drop_1 = "DROP TABLE t1";
20$drop_2 = "DROP TABLE t2";
21
22$s1 = oci_parse($c, $drop_1);
23$s2 = oci_parse($c, $drop_2);
24@oci_execute($s1);
25@oci_execute($s2);
26
27$s1 = oci_parse($c, $create_1);
28$s2 = oci_parse($c, $create_2);
29oci_execute($s1);
30oci_execute($s2);
31
32for($i=1; $i < 4; $i++) {
33    $insert = "INSERT INTO t1 VALUES(1".$i.")";
34    $s = oci_parse($c, $insert);
35    oci_execute($s);
36}
37
38for($i=1; $i < 4; $i++) {
39    $insert = "INSERT INTO t2 VALUES(2".$i.")";
40    $s = oci_parse($c, $insert);
41    oci_execute($s);
42}
43
44
45function do_assoc($c)
46{
47    $query = "SELECT t1.*, CURSOR( SELECT * FROM t2 ) AS CURSOR FROM t1";
48
49    $stmt = oci_parse($c, $query);
50    oci_execute($stmt);
51
52    while ($row = oci_fetch_assoc($stmt)) {
53        print "Got row \"".$row['ID1']."\". Now getting nested cursor:\n";
54        var_dump(oci_execute($row['CURSOR']));
55        while ($row_n = oci_fetch_assoc($row['CURSOR']) ) {
56            var_dump($row_n);
57        }
58    }
59}
60
61function do_all($c)
62{
63    $query = "SELECT t1.*, CURSOR( SELECT * FROM t2 ) AS CURSOR FROM t1";
64
65    $stmt = oci_parse($c, $query);
66    oci_execute($stmt);
67
68    $rc1 = oci_fetch_all($stmt, $res);
69
70    echo "Rows returned $rc1\n";
71
72    var_dump($res);
73
74    foreach ($res['CURSOR'] as $cv) {
75        echo "Getting nested cursor\n";
76        var_dump(oci_execute($cv));
77        $rc2 = oci_fetch_all($cv, $res2);
78        var_dump($res2);
79    }
80}
81
82
83
84echo "Test 1: Associate fetch of nested cursor\n";
85do_assoc($c);
86
87echo "\nTest 2: fetchall of nested cursor\n";
88do_all($c);
89
90
91// Cleanup
92$s1 = oci_parse($c, $drop_1);
93$s2 = oci_parse($c, $drop_2);
94@oci_execute($s1);
95@oci_execute($s2);
96
97echo "Done\n";
98?>
99--EXPECTF--
100Test 1: Associate fetch of nested cursor
101Got row "11". Now getting nested cursor:
102bool(true)
103array(1) {
104  ["ID2"]=>
105  string(2) "21"
106}
107array(1) {
108  ["ID2"]=>
109  string(2) "22"
110}
111array(1) {
112  ["ID2"]=>
113  string(2) "23"
114}
115Got row "12". Now getting nested cursor:
116bool(true)
117array(1) {
118  ["ID2"]=>
119  string(2) "21"
120}
121array(1) {
122  ["ID2"]=>
123  string(2) "22"
124}
125array(1) {
126  ["ID2"]=>
127  string(2) "23"
128}
129Got row "13". Now getting nested cursor:
130bool(true)
131array(1) {
132  ["ID2"]=>
133  string(2) "21"
134}
135array(1) {
136  ["ID2"]=>
137  string(2) "22"
138}
139array(1) {
140  ["ID2"]=>
141  string(2) "23"
142}
143
144Test 2: fetchall of nested cursor
145Rows returned 3
146array(2) {
147  ["ID1"]=>
148  array(3) {
149    [0]=>
150    string(2) "11"
151    [1]=>
152    string(2) "12"
153    [2]=>
154    string(2) "13"
155  }
156  ["CURSOR"]=>
157  array(3) {
158    [0]=>
159    resource(%d) of type (oci8 statement)
160    [1]=>
161    resource(%d) of type (oci8 statement)
162    [2]=>
163    resource(%d) of type (oci8 statement)
164  }
165}
166Getting nested cursor
167bool(true)
168array(1) {
169  ["ID2"]=>
170  array(3) {
171    [0]=>
172    string(2) "21"
173    [1]=>
174    string(2) "22"
175    [2]=>
176    string(2) "23"
177  }
178}
179Getting nested cursor
180bool(true)
181array(1) {
182  ["ID2"]=>
183  array(3) {
184    [0]=>
185    string(2) "21"
186    [1]=>
187    string(2) "22"
188    [2]=>
189    string(2) "23"
190  }
191}
192Getting nested cursor
193bool(true)
194array(1) {
195  ["ID2"]=>
196  array(3) {
197    [0]=>
198    string(2) "21"
199    [1]=>
200    string(2) "22"
201    [2]=>
202    string(2) "23"
203  }
204}
205Done
206