xref: /PHP-5.6/ext/oci8/tests/imp_res_field.phpt (revision bfc3c223)
1--TEST--
2Oracle Database 12c Implicit Result Sets: field tests
3--SKIPIF--
4<?php
5if (!extension_loaded('oci8')) die ("skip no oci8 extension");
6$target_dbs = array('oracledb' => true, 'timesten' => false);  // test runs on these DBs
7require(dirname(__FILE__).'/skipif.inc');
8preg_match('/.*Release ([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)*/', oci_server_version($c), $matches);
9if (!(isset($matches[0]) && $matches[1] >= 12)) {
10    die("skip expected output only valid when using Oracle Database 12c or greater");
11}
12preg_match('/^[[:digit:]]+/', oci_client_version(), $matches);
13if (!(isset($matches[0]) && $matches[0] >= 12)) {
14    die("skip works only with Oracle 12c or greater version of Oracle client libraries");
15}
16?>
17--FILE--
18<?php
19
20require(dirname(__FILE__).'/connect.inc');
21
22// Initialization
23
24$stmtarray = array(
25    "drop table imp_res_field_tab_1",
26    "create table imp_res_field_tab_1 (c1_number number, c2_varchar210 varchar2(10))",
27    "insert into imp_res_field_tab_1 values (1111, 'abcde')",
28
29    "drop table imp_res_field_tab_2",
30    "create table imp_res_field_tab_2 (c3_varchar21 varchar2(4))",
31    "insert into imp_res_field_tab_2 values ('tttt')",
32
33    "drop table imp_res_field_tab_3",
34    "create table imp_res_field_tab_3 (c4_number52 number(5,2))",
35    "insert into imp_res_field_tab_3 values (33)",
36    "insert into imp_res_field_tab_3 values (NULL)",
37
38    "create or replace procedure imp_res_field_proc as
39      c1 sys_refcursor;
40    begin
41      open c1 for select * from imp_res_field_tab_1 order by 1;
42      dbms_sql.return_result(c1);
43
44      open c1 for select * from imp_res_field_tab_2 order by 1;
45      dbms_sql.return_result(c1);
46
47      open c1 for select * from imp_res_field_tab_3 order by 1;
48      dbms_sql.return_result(c1);
49    end;"
50);
51
52oci8_test_sql_execute($c, $stmtarray);
53
54function print_fields($s)
55{
56    echo "num fields : " . oci_num_fields($s) . "\n";
57    for ($i = 1; $i <= oci_num_fields($s); $i++) {
58        $is_null = oci_field_is_null($s, $i) ? "T" : "F";
59        $name = oci_field_name($s, $i);
60        $precision = oci_field_precision($s, $i);
61        $scale = oci_field_scale($s, $i);
62        $size = oci_field_size($s, $i);
63        $typeraw = oci_field_type_raw($s, $i);
64        $type = oci_field_type($s, $i);
65        echo "$name\t: is_null $is_null, precision $precision, scale $scale, size $size, typeraw $typeraw, type $type\n";
66    }
67}
68
69// Run Test
70
71echo "Test 1 - can't get IRS fields from parent\n";
72$s = oci_parse($c, "begin imp_res_field_proc(); end;");
73oci_execute($s);
74print_fields($s);
75
76echo "\nTest 2 - can't get IRS fields from parent when fetching\n";
77$s = oci_parse($c, "begin imp_res_field_proc(); end;");
78oci_execute($s);
79while (($r = oci_fetch_row($s))) {
80    var_dump($r);
81    print_fields($s);
82}
83
84echo "\nTest 3 - get IRS fields\n";
85$s = oci_parse($c, "begin imp_res_field_proc(); end;");
86oci_execute($s);
87while (($s1 = oci_get_implicit_resultset($s))) {
88    print_fields($s1);
89}
90
91echo "\nTest 4 - get IRS fields before fetching rows\n";
92$s = oci_parse($c, "begin imp_res_field_proc(); end;");
93oci_execute($s);
94$i = 0;
95while (($s1 = oci_get_implicit_resultset($s))) {
96    echo "===> Result set ".++$i."\n";
97    print_fields($s1);
98    while (($r = oci_fetch_row($s1)) !== false) {
99        var_dump($r);
100    }
101}
102
103echo "\nTest 5 - get IRS fields when fetching rows\n";
104$s = oci_parse($c, "begin imp_res_field_proc(); end;");
105oci_execute($s);
106$i = 0;
107while (($s1 = oci_get_implicit_resultset($s))) {
108    echo "===> Result set ".++$i."\n";
109    while (($r = oci_fetch_row($s1)) !== false) {
110        var_dump($r);
111        print_fields($s1);
112    }
113}
114
115// Clean up
116
117$stmtarray = array(
118    "drop procedure imp_res_field_proc",
119    "drop table imp_res_field_tab_1",
120    "drop table imp_res_field_tab_2",
121    "drop table imp_res_field_tab_3"
122);
123
124oci8_test_sql_execute($c, $stmtarray);
125
126?>
127===DONE===
128<?php exit(0); ?>
129--EXPECTF--
130Test 1 - can't get IRS fields from parent
131num fields : 0
132
133Test 2 - can't get IRS fields from parent when fetching
134array(2) {
135  [0]=>
136  string(4) "1111"
137  [1]=>
138  string(5) "abcde"
139}
140num fields : 0
141array(1) {
142  [0]=>
143  string(4) "tttt"
144}
145num fields : 0
146array(1) {
147  [0]=>
148  string(2) "33"
149}
150num fields : 0
151array(1) {
152  [0]=>
153  NULL
154}
155num fields : 0
156
157Test 3 - get IRS fields
158num fields : 2
159C1_NUMBER	: is_null F, precision 0, scale -127, size 22, typeraw 2, type NUMBER
160C2_VARCHAR210	: is_null F, precision 0, scale 0, size 10, typeraw 1, type VARCHAR2
161num fields : 1
162C3_VARCHAR21	: is_null F, precision 0, scale 0, size 4, typeraw 1, type VARCHAR2
163num fields : 1
164C4_NUMBER52	: is_null F, precision 5, scale 2, size 22, typeraw 2, type NUMBER
165
166Test 4 - get IRS fields before fetching rows
167===> Result set 1
168num fields : 2
169C1_NUMBER	: is_null F, precision 0, scale -127, size 22, typeraw 2, type NUMBER
170C2_VARCHAR210	: is_null F, precision 0, scale 0, size 10, typeraw 1, type VARCHAR2
171array(2) {
172  [0]=>
173  string(4) "1111"
174  [1]=>
175  string(5) "abcde"
176}
177===> Result set 2
178num fields : 1
179C3_VARCHAR21	: is_null F, precision 0, scale 0, size 4, typeraw 1, type VARCHAR2
180array(1) {
181  [0]=>
182  string(4) "tttt"
183}
184===> Result set 3
185num fields : 1
186C4_NUMBER52	: is_null F, precision 5, scale 2, size 22, typeraw 2, type NUMBER
187array(1) {
188  [0]=>
189  string(2) "33"
190}
191array(1) {
192  [0]=>
193  NULL
194}
195
196Test 5 - get IRS fields when fetching rows
197===> Result set 1
198array(2) {
199  [0]=>
200  string(4) "1111"
201  [1]=>
202  string(5) "abcde"
203}
204num fields : 2
205C1_NUMBER	: is_null F, precision 0, scale -127, size 22, typeraw 2, type NUMBER
206C2_VARCHAR210	: is_null F, precision 0, scale 0, size 10, typeraw 1, type VARCHAR2
207===> Result set 2
208array(1) {
209  [0]=>
210  string(4) "tttt"
211}
212num fields : 1
213C3_VARCHAR21	: is_null F, precision 0, scale 0, size 4, typeraw 1, type VARCHAR2
214===> Result set 3
215array(1) {
216  [0]=>
217  string(2) "33"
218}
219num fields : 1
220C4_NUMBER52	: is_null F, precision 5, scale 2, size 22, typeraw 2, type NUMBER
221array(1) {
222  [0]=>
223  NULL
224}
225num fields : 1
226C4_NUMBER52	: is_null T, precision 5, scale 2, size 22, typeraw 2, type NUMBER
227===DONE===
228