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(dirname(__FILE__).'/skipif.inc');
7?>
8--FILE--
9<?php
10
11require(dirname(__FILE__).'/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===DONE===
272<?php exit(0); ?>
273--EXPECTF--
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===DONE===
367