1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
--TEST--
Basic XMLType test #2
--SKIPIF--
<?php
$target_dbs = array('oracledb' => true, 'timesten' => false); // test runs on these DBs
require(dirname(__FILE__).'/skipif.inc');
if (!extension_loaded("simplexml")) die ("skip no simplexml extension");
?>
--FILE--
<?php
require(dirname(__FILE__).'/connect.inc');
// Initialization
$stmtarray = array(
"drop table xmltype_02_tab",
"create table xmltype_02_tab (warehouse_id number, warehouse_spec xmltype)",
);
oci8_test_sql_execute($c, $stmtarray);
// Run Test
$id = 1;
// Delete any current entry
$s = oci_parse($c, "delete from xmltype_02_tab where warehouse_id = :id");
oci_bind_by_name($s, ':id', $id);
oci_execute($s);
// XML data to be inserted
$xml =<<<EOF
<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<WarehouseName>Southlake, Texas</WarehouseName>
<Building>Owned</Building>
<Area>25000</Area>
<Docks>2</Docks>
<DockType>Rear load</DockType>
<WaterAccess>true</WaterAccess>
<RailAccess>N</RailAccess>
<Parking>Street</Parking>
<VClearance>10</VClearance>
</Warehouse>
EOF;
echo "Test 1 Insert new XML data using a temporary CLOB\n";
$s = oci_parse($c,
"insert into xmltype_02_tab (warehouse_id, warehouse_spec)
values (:id, XMLType(:clob))");
oci_bind_by_name($s, ':id', $id);
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob', $lob, -1, OCI_B_CLOB);
$lob->writeTemporary($xml);
oci_execute($s);
$lob->close();
// Query the row back
$s = oci_parse($c, 'select xmltype.getclobval(warehouse_spec)
from xmltype_02_tab where warehouse_id = :id');
$r = oci_bind_by_name($s, ':id', $id);
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
var_dump($row);
echo "Test 2 Manipulate the data using SimpleXML\n";
$sx = simplexml_load_string((binary)$row[0]->load());
$row[0]->free();
var_dump($sx);
$sx->Docks -= 1; // change the data
var_dump($sx);
echo "Test 3: Update changes using a temporary CLOB\n";
$s = oci_parse($c, 'update xmltype_02_tab
set warehouse_spec = XMLType(:clob)
where warehouse_id = :id');
oci_bind_by_name($s, ':id', $id);
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob', $lob, -1, OCI_B_CLOB);
$lob->writeTemporary($sx->asXml());
oci_execute($s);
$lob->close();
// Query the changed row back and print it
$s = oci_parse($c, 'select xmltype.getclobval(warehouse_spec)
from xmltype_02_tab where warehouse_id = :id');
$r = oci_bind_by_name($s, ':id', $id);
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
var_dump($row[0]->load());
$row[0]->free();
// Clean up
$stmtarray = array(
"drop table xmltype_02_tab"
);
oci8_test_sql_execute($c, $stmtarray);
?>
===DONE===
<?php exit(0); ?>
--EXPECTF--
Test 1 Insert new XML data using a temporary CLOB
array(1) {
[0]=>
object(OCI-Lob)#%d (1) {
["descriptor"]=>
resource(%d) of type (oci8 descriptor)
}
}
Test 2 Manipulate the data using SimpleXML
object(SimpleXMLElement)#%d (10) {
["WarehouseId"]=>
string(1) "1"
["WarehouseName"]=>
string(16) "Southlake, Texas"
["Building"]=>
string(5) "Owned"
["Area"]=>
string(5) "25000"
["Docks"]=>
string(1) "2"
["DockType"]=>
string(9) "Rear load"
["WaterAccess"]=>
string(4) "true"
["RailAccess"]=>
string(1) "N"
["Parking"]=>
string(6) "Street"
["VClearance"]=>
string(2) "10"
}
object(SimpleXMLElement)#%d (10) {
["WarehouseId"]=>
string(1) "1"
["WarehouseName"]=>
string(16) "Southlake, Texas"
["Building"]=>
string(5) "Owned"
["Area"]=>
string(5) "25000"
["Docks"]=>
string(1) "1"
["DockType"]=>
string(9) "Rear load"
["WaterAccess"]=>
string(4) "true"
["RailAccess"]=>
string(1) "N"
["Parking"]=>
string(6) "Street"
["VClearance"]=>
string(2) "10"
}
Test 3: Update changes using a temporary CLOB
string(%d) "<?xml version="1.0"?>
<Warehouse>
%sWarehouseId>1</WarehouseId>
%sWarehouseName>Southlake, Texas</WarehouseName>
%sBuilding>Owned</Building>
%sArea>25000</Area>
%sDocks>1</Docks>
%sDockType>Rear load</DockType>
%sWaterAccess>true</WaterAccess>
%sRailAccess>N</RailAccess>
%sParking>Street</Parking>
%sVClearance>10</VClearance>
</Warehouse>
"
===DONE===
|
