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