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