xref: /PHP-7.3/ext/oci8/tests/lob_null.phpt (revision bdd67d81)
1--TEST--
2Test null data for CLOBs
3--SKIPIF--
4<?php
5$target_dbs = array('oracledb' => true, 'timesten' => false);  // test runs on these DBs
6require(dirname(__FILE__).'/skipif.inc');
7?>
8--FILE--
9<?php
10
11require dirname(__FILE__).'/connect.inc';
12
13// Initialization
14
15$s = oci_parse($c, 'drop table lob_null_tab');
16@oci_execute($s);
17
18$s = oci_parse($c, 'create table lob_null_tab (id number, data clob)');
19oci_execute($s);
20
21$s = oci_parse($c,
22'create or replace procedure lob_null_proc_in (pid in number, pdata in CLOB)
23 as begin
24   insert into lob_null_tab (id, data) values (pid, pdata);
25 end;');
26oci_execute($s);
27
28$s = oci_parse($c,
29'create or replace procedure lob_null_proc_out (pid in number, pdata out clob)
30   as begin
31     select data into pdata from lob_null_tab where id = pid;
32 end;');
33oci_execute($s);
34
35// TEMPORARY CLOB
36
37echo "Temporary CLOB: NULL\n";
38$s = oci_parse($c, "insert into lob_null_tab values (1, :b)");
39$lob = oci_new_descriptor($c, OCI_D_LOB);
40oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
41$lob->writeTemporary(null);
42$r = @oci_execute($s);
43if (!$r) {
44    $m = oci_error($s);
45    echo $m['message'], "\n";
46}
47else {
48    $lob->close();
49}
50
51echo "Temporary CLOB: ''\n";
52$s = oci_parse($c, "insert into lob_null_tab values (2, :b)");
53$lob = oci_new_descriptor($c, OCI_D_LOB);
54oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
55$lob->writeTemporary('');
56$r = @oci_execute($s);
57if (!$r) {
58    $m = oci_error($s);
59    echo $m['message'], "\n";
60}
61else {
62    $lob->close();
63}
64
65echo "Temporary CLOB: text\n";
66$s = oci_parse($c, "insert into lob_null_tab values (3, :b)");
67$lob = oci_new_descriptor($c, OCI_D_LOB);
68oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
69$lob->writeTemporary('Inserted via SQL statement');
70$r = @oci_execute($s);
71if (!$r) {
72    $m = oci_error($s);
73    echo $m['message'], "\n";
74}
75else {
76    $lob->close();
77}
78
79// PROCEDURE PARAMETER
80
81echo "Procedure parameter: NULL\n";
82$s = oci_parse($c, "call lob_null_proc_in(4, :b)");
83$lob = oci_new_descriptor($c, OCI_D_LOB);
84oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
85$lob->writeTemporary(null);
86$r = @oci_execute($s);
87if (!$r) {
88    $m = oci_error($s);
89    echo $m['message'], "\n";
90}
91else {
92    $lob->close();
93}
94
95echo "Procedure parameter: ''\n";
96$s = oci_parse($c, "call lob_null_proc_in(5, :b)");
97$lob = oci_new_descriptor($c, OCI_D_LOB);
98oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
99$lob->writeTemporary('');
100$r = @oci_execute($s);
101if (!$r) {
102    $m = oci_error($s);
103    echo $m['message'], "\n";
104}
105else {
106    $lob->close();
107}
108
109echo "Procedure parameter: text\n";
110$s = oci_parse($c, "call lob_null_proc_in(6, :b)");
111$lob = oci_new_descriptor($c, OCI_D_LOB);
112oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
113$lob->writeTemporary('Inserted via procedure parameter');
114$r = @oci_execute($s);
115if (!$r) {
116    $m = oci_error($s);
117    echo $m['message'], "\n";
118}
119else {
120    $lob->close();
121}
122
123// RETURNING INTO
124
125echo "RETURNING INTO: null\n";
126$s = oci_parse($c, "insert into lob_null_tab values (7, empty_clob()) returning data into :b");
127$lob = oci_new_descriptor($c, OCI_D_LOB);
128oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
129oci_execute($s, OCI_DEFAULT);  // Must have OCI_DEFAULT here so locator is still valid
130$lob->save(null);
131
132echo "RETURNING INTO: ''\n";
133$s = oci_parse($c, "insert into lob_null_tab values (8, empty_clob()) returning data into :b");
134$lob = oci_new_descriptor($c, OCI_D_LOB);
135oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
136oci_execute($s, OCI_DEFAULT);  // Must have OCI_DEFAULT here so locator is still valid
137$lob->save('');
138
139echo "RETURNING INTO: text\n";
140$s = oci_parse($c, "insert into lob_null_tab values (9, empty_clob()) returning data into :b");
141$lob = oci_new_descriptor($c, OCI_D_LOB);
142oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
143oci_execute($s, OCI_DEFAULT);  // Must have OCI_DEFAULT here so locator is still valid
144$lob->save('Inserted with RETURNING INTO');
145
146echo "Fetch as string\n";
147$s = oci_parse ($c, 'select id, data from lob_null_tab order by id');
148oci_execute($s);
149oci_fetch_all($s, $res);
150var_dump($res);
151
152echo "\nFetch as a descriptor\n";
153$s = oci_parse ($c, 'select id, data from lob_null_tab order by id');
154oci_execute($s);
155while ($arr = oci_fetch_assoc($s)) {
156    if (is_object($arr['DATA'])) {
157        echo $arr['ID'] . " is an object: ";
158        $r = $arr['DATA']->load();
159        var_dump($r);
160    }
161    else {
162        echo $arr['ID'] . " is not an object\n";
163    }
164}
165
166echo "\nFetch via the procedure parameter\n";
167for ($i = 1; $i <= 9; $i++)
168{
169    $s = oci_parse ($c, "call lob_null_proc_out($i, :b)");
170    $lob = oci_new_descriptor($c, OCI_D_LOB);
171    oci_bind_by_name($s, ':b', $lob, -1, OCI_B_CLOB);
172    oci_execute($s);
173    if (is_object($lob)) {
174        echo $i . " is an object: ";
175        $r = $lob->load();
176        var_dump($r);
177    }
178    else {
179        echo $i . " is not an object\n";
180    }
181}
182
183// Cleanup
184
185$s = oci_parse($c, 'drop table lob_null_tab');
186@oci_execute($s);
187
188echo "Done\n";
189
190?>
191--EXPECT--
192Temporary CLOB: NULL
193Temporary CLOB: ''
194Temporary CLOB: text
195Procedure parameter: NULL
196Procedure parameter: ''
197Procedure parameter: text
198RETURNING INTO: null
199RETURNING INTO: ''
200RETURNING INTO: text
201Fetch as string
202array(2) {
203  ["ID"]=>
204  array(9) {
205    [0]=>
206    string(1) "1"
207    [1]=>
208    string(1) "2"
209    [2]=>
210    string(1) "3"
211    [3]=>
212    string(1) "4"
213    [4]=>
214    string(1) "5"
215    [5]=>
216    string(1) "6"
217    [6]=>
218    string(1) "7"
219    [7]=>
220    string(1) "8"
221    [8]=>
222    string(1) "9"
223  }
224  ["DATA"]=>
225  array(9) {
226    [0]=>
227    string(0) ""
228    [1]=>
229    string(0) ""
230    [2]=>
231    string(26) "Inserted via SQL statement"
232    [3]=>
233    string(0) ""
234    [4]=>
235    string(0) ""
236    [5]=>
237    string(32) "Inserted via procedure parameter"
238    [6]=>
239    string(0) ""
240    [7]=>
241    string(0) ""
242    [8]=>
243    string(28) "Inserted with RETURNING INTO"
244  }
245}
246
247Fetch as a descriptor
2481 is an object: string(0) ""
2492 is an object: string(0) ""
2503 is an object: string(26) "Inserted via SQL statement"
2514 is an object: string(0) ""
2525 is an object: string(0) ""
2536 is an object: string(32) "Inserted via procedure parameter"
2547 is an object: string(0) ""
2558 is an object: string(0) ""
2569 is an object: string(28) "Inserted with RETURNING INTO"
257
258Fetch via the procedure parameter
2591 is an object: string(0) ""
2602 is an object: string(0) ""
2613 is an object: string(26) "Inserted via SQL statement"
2624 is an object: string(0) ""
2635 is an object: string(0) ""
2646 is an object: string(32) "Inserted via procedure parameter"
2657 is an object: string(0) ""
2668 is an object: string(0) ""
2679 is an object: string(28) "Inserted with RETURNING INTO"
268Done
269