1--TEST--
2Bind miscellaneous column types using default types
3--SKIPIF--
4<?php
5$target_dbs = array('oracledb' => true, 'timesten' => false);  // test runs on these DBs
6require(__DIR__.'/skipif.inc');
7?>
8--FILE--
9<?php
10
11require(__DIR__.'/connect.inc');
12
13// Initialization
14
15$stmtarray = array(
16
17    "alter session set nls_date_format = 'DD-MON-YY'",
18
19    "drop table bind_misccoltypes_tab",
20
21    "create table bind_misccoltypes_tab (
22        id                number,
23        char_t            char(1),
24        char_t10          char(10),
25        varchar2_t10      varchar2(10),
26        number_t          number,
27        number_t92        number(9,2),
28        number_t6         number(6),
29        date_t            date,
30        timestamp_t       timestamp,
31        float_t           float,
32        binary_float_t    binary_float,
33        binary_double_t   binary_double,
34        decimal_t         decimal,
35        integer_t         integer,
36        nchar_t           nchar(10),
37        nvarchar2_t10     nvarchar2(10),
38        varchar_t10       varchar(10) )",
39);
40
41oci8_test_sql_execute($c, $stmtarray);
42
43function check_col($c, $colname, $id)
44{
45    $s = oci_parse($c, "select $colname from bind_misccoltypes_tab where id = :id");
46    oci_bind_by_name($s, ":id", $id);
47    oci_execute($s);
48    oci_fetch_all($s, $r);
49    var_dump($r);
50}
51
52// Tests
53
54echo "\nTEST86 insert all ORATYPE values\n";
55
56$insert_sql = "INSERT INTO bind_misccoltypes_tab ( id, "
57    . "   char_t, "
58    . "   char_t10, "
59    . "   varchar2_t10, "
60    . "   number_t, "
61    . "   number_t92, "
62    . "   number_t6, "
63    . "   date_t, "
64    . "   timestamp_t, "
65    . "   float_t, "
66    . "   binary_float_t, "
67    . "   binary_double_t, "
68    . "   decimal_t, "
69    . "   integer_t, "
70    . "   nchar_t, "
71    . "   nvarchar2_t10, "
72    . "   varchar_t10) "
73    . " VALUES (:n1, "
74    .         " :c1, "
75    .         " :c2, "
76    .         " :c3, "
77    .         " :n2, "
78    .         " :n3, "
79    .         " :n4, "
80    .         " to_date(:d1, 'YYYY-MM-DD HH24:MI:SS'), "
81    .         " to_timestamp(:d1, 'YYYY-MM-DD HH24:MI:SS'), "
82    .         " :n5, "
83    .         " :n5, "
84    .         " :n5, "
85    .         " :n1, "
86    .         " :n1, "
87    .         " :c4, "
88    .         " :c5, "
89    .         " :c6) ";
90
91
92$n1 = "86";
93$c1 = "C";
94$c2 = "char10";
95$c3 = "varchar210";
96$n2 = "-123.456";
97$n3 = "789.346";
98$n4 = "123456.023";
99$n5 = "12345678901234567890123456789012345678.723";
100$d1 = "2010-03-29 13:09:15";
101$c4 = "nchar10";
102$c5 = "nvarchar2x";
103$c6 = "varchar";
104
105$s = oci_parse($c, $insert_sql);
106oci_bind_by_name($s, ":n1", $n1);
107oci_bind_by_name($s, ":c1", $c1);
108oci_bind_by_name($s, ":c2", $c2);
109oci_bind_by_name($s, ":c3", $c3);
110oci_bind_by_name($s, ":n2", $n2);
111oci_bind_by_name($s, ":n3", $n3);
112oci_bind_by_name($s, ":n4", $n4);
113oci_bind_by_name($s, ":d1", $d1);
114oci_bind_by_name($s, ":n5", $n5);
115oci_bind_by_name($s, ":c4", $c4);
116oci_bind_by_name($s, ":c5", $c5);
117oci_bind_by_name($s, ":c6", $c6);
118
119oci_execute($s);
120
121echo "\nTEST87 SELECT all values using DEFINEs\n";
122$select_sql = "select "
123    . "id, "
124    . "char_t, "
125    . "char_t10, "
126    . "varchar2_t10, "
127    . "number_t, "
128    . "number_t92, "
129    . "number_t6, "
130    . "date_t, "
131    . "timestamp_t, "
132    . "float_t, "
133    . "binary_float_t, "
134    . "binary_double_t, "
135    . "decimal_t, "
136    . "integer_t, "
137    . "nchar_t, "
138    . "nvarchar2_t10, "
139    . "varchar_t10 "
140    . "from bind_misccoltypes_tab where id = 86";
141
142$s = oci_parse($c, $select_sql);
143
144oci_define_by_name($s, "ID", $ID);
145oci_define_by_name($s, "CHAR_T", $CHAR_T);
146oci_define_by_name($s, "CHAR_T10", $CHAR_T10);
147oci_define_by_name($s, "VARCHAR2_T10", $VARCHAR2_T10);
148oci_define_by_name($s, "NUMBER_T", $NUMBER_T);
149oci_define_by_name($s, "NUMBER_T92", $NUMBER_T92);
150oci_define_by_name($s, "NUMBER_T6", $NUMBER_T6);
151oci_define_by_name($s, "DATE_T", $DATE_T);
152oci_define_by_name($s, "TIMESTAMP_T", $TIMESTAMP_T);
153oci_define_by_name($s, "FLOAT_T", $FLOAT_T);
154oci_define_by_name($s, "BINARY_FLOAT_T", $BINARY_FLOAT_T);
155oci_define_by_name($s, "BINARY_DOUBLE_T", $BINARY_DOUBLE_T);
156oci_define_by_name($s, "DECIMAL_T", $DECIMAL_T);
157oci_define_by_name($s, "INTEGER_T", $INTEGER_T);
158oci_define_by_name($s, "NCHAR_T", $NCHAR_T);
159oci_define_by_name($s, "NVARCHAR2_T10", $NVARCHAR2_T10);
160oci_define_by_name($s, "VARCHAR_T10", $VARCHAR_T10);
161
162oci_execute($s);
163
164while (oci_fetch($s)) {
165    echo "ID is "              . "$ID\n";
166    echo "CHAR_T is "          . "$CHAR_T\n";
167    echo "CHAR_T10 is "        . "$CHAR_T10\n";
168    echo "VARCHAR2_T10 is "    . "$VARCHAR2_T10\n";
169    echo "NUMBER_T is "        . "$NUMBER_T\n";
170    echo "NUMBER_T92 is "      . "$NUMBER_T92\n";
171    echo "NUMBER_T6 is "       . "$NUMBER_T6\n";
172    echo "DATE_T is "          . "$DATE_T\n";
173    echo "TIMESTAMP_T is "     . "$TIMESTAMP_T\n";
174    echo "FLOAT_T is "         . "$FLOAT_T\n";
175    echo "BINARY_FLOAT_T is "  . "$BINARY_FLOAT_T\n";
176    echo "BINARY_DOUBLE_T is " . "$BINARY_DOUBLE_T\n";
177    echo "DECIMAL_T is "       . "$DECIMAL_T\n";
178    echo "INTEGER_T is "       . "$INTEGER_T\n";
179    echo "NCHAR_T is "         . "$NCHAR_T\n";
180    echo "NVARCHAR2_T10 is "   . "$NVARCHAR2_T10\n";
181    echo "VARCHAR_T10 is "     . "$VARCHAR_T10\n";
182}
183
184echo "\nTEST52 insert numbers\n";
185
186$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, number_t92) VALUES (52, :n1)");
187$n1 = 3;
188oci_bind_by_name($s, ":n1", $n1);
189oci_execute($s);
190
191check_col($c, 'number_t92', 52);
192
193
194echo "\nTEST53 insert numbers \n";
195
196$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, number_t92) VALUES (53, :n1)");
197$n1 = 8.67;
198oci_bind_by_name($s, ":n1", $n1);
199oci_execute($s);
200
201check_col($c, 'number_t92', 53);
202
203
204echo "\nTEST54 insert numbers \n";
205
206$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, number_t) VALUES (54, :n1)");
207$n1 = 4.67;
208oci_bind_by_name($s, ":n1", $n1);
209oci_execute($s);
210
211check_col($c, 'number_t', 54);
212
213echo "\nTEST55 insert numbers \n";
214
215$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, number_t) VALUES (55, :n1)");
216$n1 = "7.67";
217oci_bind_by_name($s, ":n1", $n1);
218oci_execute($s);
219
220check_col($c, 'number_t', 55);
221
222echo "\nTEST56 insert numbers \n";
223
224$n1 = -5.67;
225
226$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, number_t) VALUES (56, :n1)");
227oci_bind_by_name($s, ":n1", $n1);
228oci_execute($s);
229
230check_col($c, 'number_t', 56);
231
232echo "\nTEST58 insert a VARCHAR2\n";
233
234$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, varchar2_t10) VALUES (58, :c2)");
235$c2 = "Hood";
236oci_bind_by_name($s, ":c2", $c2);
237oci_execute($s);
238
239check_col($c, 'varchar2_t10', 58);
240
241echo "\nTEST59 insert a VARCHAR2\n";
242
243$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, char_t10) VALUES (59, :c2)");
244$c2 = "Hood";
245oci_bind_by_name($s, ":c2", $c2);
246oci_execute($s);
247
248check_col($c, 'char_t10', 59);
249
250echo "\nTEST60 insert a date\n";
251
252$s = oci_parse($c, "INSERT INTO bind_misccoltypes_tab (id, date_t) VALUES (60, to_date(:c2, 'YYYY-MM-DD'))");
253$c2 = '2010-04-09';
254oci_bind_by_name($s, ":c2", $c2);
255oci_execute($s);
256
257check_col($c, 'date_t', 60);
258
259
260// Clean up
261
262$stmtarray = array(
263    "drop table bind_misccoltypes_tab"
264);
265
266oci8_test_sql_execute($c, $stmtarray);
267
268oci_close($c);
269
270?>
271--EXPECT--
272TEST86 insert all ORATYPE values
273
274TEST87 SELECT all values using DEFINEs
275ID is 86
276CHAR_T is C
277CHAR_T10 is char10
278VARCHAR2_T10 is varchar210
279NUMBER_T is -123.456
280NUMBER_T92 is 789.35
281NUMBER_T6 is 123456
282DATE_T is 29-MAR-10
283TIMESTAMP_T is 29-MAR-10 01.09.15.000000 PM
284FLOAT_T is 12345678901234567890123456789012345679
285BINARY_FLOAT_T is 1.23456784E+037
286BINARY_DOUBLE_T is 1.2345678901234568E+037
287DECIMAL_T is 86
288INTEGER_T is 86
289NCHAR_T is nchar10
290NVARCHAR2_T10 is nvarchar2x
291VARCHAR_T10 is varchar
292
293TEST52 insert numbers
294array(1) {
295  ["NUMBER_T92"]=>
296  array(1) {
297    [0]=>
298    string(1) "3"
299  }
300}
301
302TEST53 insert numbers
303array(1) {
304  ["NUMBER_T92"]=>
305  array(1) {
306    [0]=>
307    string(4) "8.67"
308  }
309}
310
311TEST54 insert numbers
312array(1) {
313  ["NUMBER_T"]=>
314  array(1) {
315    [0]=>
316    string(4) "4.67"
317  }
318}
319
320TEST55 insert numbers
321array(1) {
322  ["NUMBER_T"]=>
323  array(1) {
324    [0]=>
325    string(4) "7.67"
326  }
327}
328
329TEST56 insert numbers
330array(1) {
331  ["NUMBER_T"]=>
332  array(1) {
333    [0]=>
334    string(5) "-5.67"
335  }
336}
337
338TEST58 insert a VARCHAR2
339array(1) {
340  ["VARCHAR2_T10"]=>
341  array(1) {
342    [0]=>
343    string(4) "Hood"
344  }
345}
346
347TEST59 insert a VARCHAR2
348array(1) {
349  ["CHAR_T10"]=>
350  array(1) {
351    [0]=>
352    string(10) "Hood      "
353  }
354}
355
356TEST60 insert a date
357array(1) {
358  ["DATE_T"]=>
359  array(1) {
360    [0]=>
361    string(9) "09-APR-10"
362  }
363}
364