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