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