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