1--TEST--
2PDO_OCI: PDOStatement->getColumnMeta
3--SKIPIF--
4<?php
5if (!extension_loaded('pdo') || !extension_loaded('pdo_oci')) die('skip not loaded');
6require(__DIR__ . '/../../pdo/tests/pdo_test.inc');
7PDOTest::skip();
8?>
9--FILE--
10<?php
11
12echo "Preparations before the test\n";
13
14require(__DIR__ . '/../../pdo/tests/pdo_test.inc');
15try {
16	$db = PDOTest::factory();
17	$db->exec(<<<SQL
18BEGIN
19   EXECUTE IMMEDIATE 'DROP TABLE test';
20EXCEPTION
21   WHEN OTHERS THEN
22      IF SQLCODE != -942 THEN
23	 RAISE;
24      END IF;
25END;
26SQL
27);
28	$db->exec("CREATE TABLE test(id INT)");
29
30	$db->beginTransaction();
31
32	$stmt = $db->prepare('SELECT id FROM test ORDER BY id ASC');
33
34	echo "Test 1. calling function with invalid parameters\n";
35
36	// execute() has not been called yet
37	// NOTE: no warning
38	if (false !== ($tmp = $stmt->getColumnMeta(0)))
39		printf("[002] Expecting false got %s\n", var_export($tmp, true));
40
41	$stmt->execute();
42	// Warning: PDOStatement::getColumnMeta() expects exactly 1 parameter, 0 given in
43	if (false !== ($tmp = @$stmt->getColumnMeta()))
44		printf("[003] Expecting false got %s\n", var_export($tmp, true));
45
46	// invalid offset
47	if (false !== ($tmp = @$stmt->getColumnMeta(-1)))
48		printf("[004] Expecting false got %s\n", var_export($tmp, true));
49
50	// Warning: PDOStatement::getColumnMeta() expects parameter 1 to be int, array given in
51	if (false !== ($tmp = @$stmt->getColumnMeta(array())))
52		printf("[005] Expecting false got %s\n", var_export($tmp, true));
53
54	// Warning: PDOStatement::getColumnMeta() expects exactly 1 parameter, 2 given in
55	if (false !== ($tmp = @$stmt->getColumnMeta(1, 1)))
56		printf("[006] Expecting false got %s\n", var_export($tmp, true));
57
58	// invalid offset
59	if (false !== ($tmp = $stmt->getColumnMeta(1)))
60		printf("[007] Expecting false because of invalid offset got %s\n", var_export($tmp, true));
61
62	echo "Test 2. testing return values\n";
63	echo "Test 2.1 testing array returned\n";
64
65	$stmt = $db->prepare('SELECT id FROM test ORDER BY id ASC');
66	$stmt->execute();
67	$native = $stmt->getColumnMeta(0);
68	if (count($native) == 0) {
69		printf("[008] Meta data seems wrong, %s / %s\n",
70			var_export($native, true), var_export($emulated, true));
71	}
72
73
74	function test_return($meta, $offset, $native_type, $pdo_type){
75		if (empty($meta)) {
76			printf("[%03d + 2] getColumnMeta() failed, %d - %s\n", $offset,
77				$stmt->errorCode(), var_export($stmt->errorInfo(), true));
78			return false;
79		}
80		$elements = array('flags', 'scale', 'name', 'len', 'precision', 'pdo_type');
81		foreach ($elements as $k => $element)
82			if (!isset($meta[$element])) {
83				printf("[%03d + 3] Element %s missing, %s\n", $offset,
84					$element, var_export($meta, true));
85				return false;
86			}
87
88		if (!is_null($native_type)) {
89			if (!isset($meta['native_type'])) {
90				printf("[%03d + 4] Element native_type missing, %s\n", $offset,
91					var_export($meta, true));
92				return false;
93			}
94
95			if (!is_array($native_type))
96				$native_type = array($native_type);
97
98			$found = false;
99			foreach ($native_type as $k => $type) {
100				if ($meta['native_type'] == $type) {
101					$found = true;
102					break;
103				}
104			}
105
106			if (!$found) {
107				printf("[%03d + 5] Expecting native type %s, %s\n", $offset,
108					var_export($native_type, true), var_export($meta, true));
109				return false;
110			}
111		}
112
113		if (!is_null($pdo_type) && ($meta['pdo_type'] != $pdo_type)) {
114			printf("[%03d + 6] Expecting PDO type %s got %s (%s)\n", $offset,
115				$pdo_type, var_export($meta, true), var_export($meta['native_type']));
116			return false;
117		}
118
119		return true;
120	}
121
122
123	function test_meta(&$db, $offset, $sql_type, $value, $native_type, $pdo_type) {
124
125		$db->exec(<<<SQL
126BEGIN
127   EXECUTE IMMEDIATE 'DROP TABLE test';
128EXCEPTION
129   WHEN OTHERS THEN
130      IF SQLCODE != -942 THEN
131         RAISE;
132      END IF;
133END;
134SQL
135);
136
137		$sql = sprintf('CREATE TABLE test(id INT, label %s)', $sql_type);
138		$stmt = $db->prepare($sql);
139		$stmt->execute();
140
141		if (!$db->exec(sprintf("INSERT INTO test(id, label) VALUES (1, '%s')", $value))) {
142			printf("[%03d] + 1] Insert failed, %d - %s\n", $offset,
143				$db->errorCode(), var_export($db->errorInfo(), true));
144			return false;
145		}
146
147		$stmt = $db->prepare('SELECT id, label FROM test');
148		$stmt->execute();
149		$meta = $stmt->getColumnMeta(1);
150		return test_return($meta, $offset, $native_type, $pdo_type);
151	}
152
153	echo "Test 2.2 testing numeric columns\n";
154
155	test_meta($db, 20, 'NUMBER'         , 0                    , 'NUMBER', PDO::PARAM_STR);
156	test_meta($db, 30, 'NUMBER'         , 256                  , 'NUMBER', PDO::PARAM_STR);
157	test_meta($db, 40, 'INT'            , 256                  , 'NUMBER', PDO::PARAM_STR);
158	test_meta($db, 50, 'INTEGER'        , 256                  , 'NUMBER', PDO::PARAM_STR);
159	test_meta($db, 60, 'NUMBER'         , 256.01               , 'NUMBER', PDO::PARAM_STR);
160	test_meta($db, 70, 'NUMBER'         , -8388608             , 'NUMBER', PDO::PARAM_STR);
161
162	test_meta($db, 80, 'NUMBER'         , 2147483648           , 'NUMBER', PDO::PARAM_STR);
163	test_meta($db, 90, 'NUMBER'         , 4294967295           , 'NUMBER', PDO::PARAM_STR);
164
165	test_meta($db, 100, 'DEC'             , 1.01               , 'NUMBER'       , PDO::PARAM_STR);
166	test_meta($db, 110, 'DECIMAL'         , 1.01               , 'NUMBER'       , PDO::PARAM_STR);
167	test_meta($db, 120, 'FLOAT'           , 1.01               , 'FLOAT'        , PDO::PARAM_STR);
168	test_meta($db, 130, 'DOUBLE PRECISION', 1.01               , 'FLOAT'        , PDO::PARAM_STR);
169	test_meta($db, 140, 'BINARY_FLOAT'    , 1.01               , 'BINARY_FLOAT' , PDO::PARAM_STR);
170	test_meta($db, 150, 'BINARY_DOUBLE'   , 1.01               , 'BINARY_DOUBLE', PDO::PARAM_STR);
171
172	echo "Test 2.3 testing temporal columns\n";
173
174	$db->exec("alter session set nls_date_format='YYYY-MM-DD'");
175	test_meta($db, 160, 'DATE'           , '2008-04-23'        , 'DATE', PDO::PARAM_STR);
176
177	echo "Test 2.4 testing string columns\n";
178
179	test_meta($db, 170, 'CHAR(1)'       , 'a'                  , 'CHAR'     , PDO::PARAM_STR);
180	test_meta($db, 180, 'CHAR(10)'      , '0123456789'         , 'CHAR'     , PDO::PARAM_STR);
181	test_meta($db, 190, 'CHAR(255)'     , str_repeat('z', 255) , 'CHAR'     , PDO::PARAM_STR);
182	test_meta($db, 200, 'VARCHAR(1)'    , 'a'                  , 'VARCHAR2' , PDO::PARAM_STR);
183	test_meta($db, 210, 'VARCHAR(10)'   , '0123456789'         , 'VARCHAR2' , PDO::PARAM_STR);
184	test_meta($db, 220, 'VARCHAR(255)'  , str_repeat('z', 255) , 'VARCHAR2' , PDO::PARAM_STR);
185	test_meta($db, 230, 'VARCHAR2(1)'   , 'a'                  , 'VARCHAR2' , PDO::PARAM_STR);
186	test_meta($db, 240, 'VARCHAR2(10)'  , '0123456789'         , 'VARCHAR2' , PDO::PARAM_STR);
187	test_meta($db, 250, 'VARCHAR2(255)' , str_repeat('z', 255) , 'VARCHAR2' , PDO::PARAM_STR);
188
189	test_meta($db, 260, 'NCHAR(1)'      , 'a'                  , 'NCHAR'    , PDO::PARAM_STR);
190	test_meta($db, 270, 'NCHAR(10)'     , '0123456789'         , 'NCHAR'    , PDO::PARAM_STR);
191	test_meta($db, 280, 'NCHAR(255)'    , str_repeat('z', 255) , 'NCHAR'    , PDO::PARAM_STR);
192	test_meta($db, 290, 'NVARCHAR2(1)'  , 'a'                  , 'NVARCHAR2', PDO::PARAM_STR);
193	test_meta($db, 300, 'NVARCHAR2(10)' , '0123456789'         , 'NVARCHAR2', PDO::PARAM_STR);
194	test_meta($db, 310, 'NVARCHAR2(255)', str_repeat('z', 255) , 'NVARCHAR2', PDO::PARAM_STR);
195
196	echo "Test 2.5 testing lobs columns\n";
197
198	test_meta($db, 320, 'CLOB'          , str_repeat('b', 255) , 'CLOB'    , PDO::PARAM_LOB);
199	test_meta($db, 330, 'BLOB'          , str_repeat('b', 256) , 'BLOB'    , PDO::PARAM_LOB);
200	test_meta($db, 340, 'NCLOB'         , str_repeat('b', 255) , 'NCLOB'   , PDO::PARAM_LOB);
201
202	test_meta($db, 350, 'LONG'          , str_repeat('b', 256) , 'LONG'    , PDO::PARAM_STR);
203	test_meta($db, 360, 'LONG RAW'      , str_repeat('b', 256) , 'LONG RAW', PDO::PARAM_STR);
204	test_meta($db, 370, 'RAW(256)'      , str_repeat('b', 256) , 'RAW'     , PDO::PARAM_STR);
205
206
207	$db->exec(<<<SQL
208BEGIN
209   EXECUTE IMMEDIATE 'DROP TABLE test';
210EXCEPTION
211   WHEN OTHERS THEN
212      IF SQLCODE != -942 THEN
213         RAISE;
214      END IF;
215END;
216SQL
217);
218	echo "Test 2.6 testing function return\n";
219
220	$stmt = $db->query('SELECT count(*) FROM dual');
221	$meta = $stmt->getColumnMeta(0);
222	test_return($meta, 380, 'NUMBER', PDO::PARAM_STR);
223	$stmt = $db->query("SELECT TO_DATE('2008-04-23') FROM dual");
224	$meta = $stmt->getColumnMeta(0);
225	test_return($meta, 390, 'DATE', PDO::PARAM_STR);
226	$stmt = $db->query("SELECT TO_CHAR(542) FROM dual");
227	$meta = $stmt->getColumnMeta(0);
228	test_return($meta, 400, 'VARCHAR2', PDO::PARAM_STR);
229
230
231	echo "Test 2.7 testing flags returned\n";
232
233	$sql = sprintf('CREATE TABLE test(id INT NOT NULL, label INT NULL)');
234	$stmt = $db->prepare($sql);
235	$stmt->execute();
236	$db->exec('INSERT INTO test(id, label) VALUES (1, 1)');
237	$stmt = $db->query('SELECT id, label FROM test');
238	$meta = $stmt->getColumnMeta(0);
239	// verify the flags array countains a not_null flag and not nullable flags
240	if (!isset($meta['flags'])) {
241		printf("[1001] No flags contained in metadata %s\n", var_export($meta, true));
242	} else {
243		$flags = $meta['flags'];
244		$found = false;
245		foreach ($flags as $k => $flag) {
246			if ($flag == 'not_null')
247				$found = true;
248			if ($flag == 'nullable')
249				printf("[1003] Flags seem wrong %s\n", var_export($meta, true));
250		}
251		if (!$found)
252			printf("[1002] Flags seem wrong %s\n", var_export($meta, true));
253	}
254	$meta = $stmt->getColumnMeta(1);
255	// verify the flags array countains a nullable flag and not not_null flags
256	if (!isset($meta['flags'])) {
257		printf("[1003] No flags contained in metadata %s\n", var_export($meta, true));
258	} else {
259		$flags = $meta['flags'];
260		$found = false;
261		foreach ($flags as $k => $flag) {
262			if ($flag == 'not_null')
263				printf("[1004] Flags seem wrong %s\n", var_export($meta, true));
264			if ($flag == 'nullable')
265				$found = true;
266		}
267		if (!$found)
268			printf("[1005] Flags seem wrong %s\n", var_export($meta, true));
269	}
270
271} catch (PDOException $e) {
272	// we should never get here, we use warnings, but never trust a system...
273	printf("[001] %s, [%s} %s\n",
274		$e->getMessage(), $db->errorInfo(), implode(' ', $db->errorInfo()));
275}
276
277$db->exec(<<<SQL
278BEGIN
279   EXECUTE IMMEDIATE 'DROP TABLE test';
280EXCEPTION
281   WHEN OTHERS THEN
282      IF SQLCODE != -942 THEN
283         RAISE;
284      END IF;
285END;
286SQL
287);
288print "done!";
289?>
290--EXPECT--
291Preparations before the test
292Test 1. calling function with invalid parameters
293Test 2. testing return values
294Test 2.1 testing array returned
295Test 2.2 testing numeric columns
296Test 2.3 testing temporal columns
297Test 2.4 testing string columns
298Test 2.5 testing lobs columns
299Test 2.6 testing function return
300Test 2.7 testing flags returned
301done!
302