Annotation of embedaddon/php/ext/pdo_mysql/tests/pdo_mysql_prepare_native.phpt, revision 1.1.1.1
1.1 misho 1: --TEST--
2: MySQL PDO->prepare(), native PS
3: --SKIPIF--
4: <?php
5: require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'skipif.inc');
6: require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
7: MySQLPDOTest::skip();
8: $db = MySQLPDOTest::factory();
9: ?>
10: --FILE--
11: <?php
12: require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
13: $db = MySQLPDOTest::factory();
14:
15: function prepex($offset, &$db, $query, $input_params = null, $error_info = null, $suppress_warning = false) {
16:
17: try {
18:
19: if ($suppress_warning || (is_array($error_info) && isset($error_info['prepare'])))
20: $stmt = @$db->prepare($query);
21: else
22: $stmt = $db->prepare($query);
23:
24: if (is_array($error_info) && isset($error_info['prepare'])) {
25: $tmp = $db->errorInfo();
26:
27: if (isset($error_info['prepare']['sqlstate']) &&
28: ($error_info['prepare']['sqlstate'] !== $tmp[0])) {
29: printf("[%03d] prepare() - expecting SQLSTATE '%s' got '%s'\n",
30: $offset, $error_info['prepare']['sqlstate'], $tmp[0]);
31: return false;
32: }
33:
34: if (isset($error_info['prepare']['mysql']) &&
35: ($error_info['prepare']['mysql'] !== $tmp[1])) {
36: printf("[%03d] prepare() - expecting MySQL Code '%s' got '%s'\n",
37: $offset, $error_info['prepare']['mysql'], $tmp[0]);
38: return false;
39: }
40:
41: return false;
42: }
43:
44: if (!is_object($stmt))
45: return false;
46:
47: if (is_null($input_params))
48: $input_params = array();
49: // 5.0.18, 5.1.14 @ 15
50: // printf("[%03d]\n", $offset);
51: if ($suppress_warning || (is_array($error_info) && isset($error_info['execute'])))
52: $ret = @$stmt->execute($input_params);
53: else
54: $ret = $stmt->execute($input_params);
55:
56: if (!is_bool($ret))
57: printf("[%03d] PDO::execute() should return a boolean value, got %s/%s\n",
58: var_export($ret, true), $ret);
59:
60: $tmp = $stmt->errorInfo();
61: if (isset($tmp[1]) && ($tmp[1] == 2030)) {
62: // Trying to hack around MySQL Server version dependent features
63: // 2030 This command is not supported in the prepared statement protocol yet
64: return false;
65: }
66:
67: if (is_array($error_info) && isset($error_info['execute'])) {
68:
69: if (isset($error_info['execute']['sqlstate']) &&
70: ($error_info['execute']['sqlstate'] !== $tmp[0])) {
71: printf("[%03d] execute() - expecting SQLSTATE '%s' got '%s'\n",
72: $offset, $error_info['execute']['sqlstate'], $tmp[0]);
73: return false;
74: }
75:
76: if (isset($error_info['execute']['mysql']) &&
77: ($error_info['execute']['mysql'] !== $tmp[1])) {
78: printf("[%03d] execute() - expecting MySQL Code '%s' got '%s'\n",
79: $offset, $error_info['execute']['mysql'], $tmp[0]);
80: return false;
81: }
82:
83: return false;
84:
85: }
86:
87: } catch (PDOException $e) {
88: printf("[%03d] %s, [%s} %s\n",
89: $offset, $e->getMessage(),
90: $db->errorCode(), implode(' ', $db->errorInfo()));
91: return false;
92: }
93:
94: return $stmt;
95: }
96:
97: try {
98: $db->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 0);
99: if (0 != $db->getAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY))
100: printf("[002] Unable to turn off emulated prepared statements\n");
101:
102: // TODO - that's PDO - you can prepare empty statements!
103: prepex(3, $db, '',
104: array(), array('prepare' => array('sqlstate' => '42000')));
105:
106: // lets be fair and do the most simple SELECT first
107: $stmt = prepex(4, $db, 'SELECT 1 as "one"');
108: if (MySQLPDOTest::isPDOMySQLnd())
109: // native types - int
110: $expected = array('one' => 1);
111: else
112: // always strings, like STRINGIFY flag
113: $expected = array('one' => '1');
114:
115: $row = $stmt->fetch(PDO::FETCH_ASSOC);
116: if ($row !== $expected) {
117: printf("[004a] Expecting %s got %s\n", var_export($expected, true), var_export($row, true));
118: }
119:
120: prepex(5, $db, 'DROP TABLE IF EXISTS test');
121: prepex(6, $db, sprintf('CREATE TABLE test(id INT, label CHAR(255)) ENGINE=%s', PDO_MYSQL_TEST_ENGINE));
122: prepex(7, $db, "INSERT INTO test(id, label) VALUES(1, ':placeholder')");
123: $stmt = prepex(8, $db, 'SELECT label FROM test ORDER BY id ASC');
124: var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
125:
126: prepex(9, $db, 'DELETE FROM test');
127: prepex(10, $db, 'INSERT INTO test(id, label) VALUES(1, :placeholder)',
128: array(':placeholder' => 'first row'));
129: prepex(11, $db, 'INSERT INTO test(id, label) VALUES(2, :placeholder)',
130: array(':placeholder' => 'second row'));
131: $stmt = prepex(12, $db, 'SELECT label FROM test ORDER BY id ASC');
132: var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
133:
134: // Is PDO fun?
135: $stmt = prepex(13, $db, 'SELECT label FROM test WHERE :placeholder > 1',
136: array(':placeholder' => 'id'));
137: var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
138:
139: for ($num_params = 2; $num_params < 100; $num_params++) {
140: $params = array(':placeholder' => 'a');
141: for ($i = 1; $i < $num_params; $i++) {
142: $params[str_repeat('a', $i)] = 'some data';
143: }
144: prepex(16, $db, 'SELECT id, label FROM test WHERE label > :placeholder',
145: $params, array('execute' => array('sqlstate' => 'HY093')));
146: }
147:
148: $stmt = prepex(16, $db, 'SELECT id, label FROM test WHERE :placeholder IS NOT NULL',
149: array(':placeholder' => 1));
150: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2)
151: printf("[017] '1' IS NOT NULL evaluates to true, expecting two rows, got %d rows\n", $tmp);
152:
153: $stmt = prepex(18, $db, 'SELECT id, label FROM test WHERE :placeholder IS NULL',
154: array(':placeholder' => 1));
155: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0)
156: printf("[019] '1' IS NOT NULL evaluates to true, expecting zero rows, got %d rows\n", $tmp);
157:
158: prepex(20, $db, 'DROP TABLE IF EXISTS test');
159: prepex(21, $db, 'CREATE TABLE test(id INT, label CHAR(255)) ENGINE=MyISAM');
160: // Not every MySQL Server version supports this
161: if (is_object(prepex(22, $db, 'CREATE FULLTEXT INDEX idx1 ON test(label)', null, null, true))) {
162: prepex(23, $db, 'INSERT INTO test(id, label) VALUES (1, :placeholder)',
163: array(':placeholder' => 'MySQL is the best database in the world!'));
164: prepex(24, $db, 'INSERT INTO test(id, label) VALUES (2, :placeholder)',
165: array(':placeholder' => 'If I have the freedom to choose, I would always go again for the MySQL Server'));
166: $stmt = prepex(25, $db, 'SELECT id, label FROM test WHERE MATCH label AGAINST (:placeholder)',
167: array(':placeholder' => 'mysql'), null, true);
168: if (is_object($stmt)) {
169: /*
170: Lets ignore this
171: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2)
172: printf("[033] Expecting two rows, got %d rows\n", $tmp);
173: */
174: $stmt = prepex(26, $db, 'SELECT id, label FROM test ORDER BY id ASC');
175: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2)
176: printf("[027] Expecting two rows, got %d rows\n", $tmp);
177:
178: if ($tmp[0]['label'] !== 'MySQL is the best database in the world!') {
179: printf("[028] INSERT seems to have failed, dumping data, check manually\n");
180: var_dump($tmp);
181: }
182: }
183: }
184:
185: $db->exec('DELETE FROM test');
186: $db->exec("INSERT INTO test(id, label) VALUES (1, 'row1')");
187: $db->exec("INSERT INTO test(id, label) VALUES (2, 'row2')");
188:
189: $sql = sprintf("SELECT id, label FROM test WHERE (label LIKE %s) AND (id = :placeholder)",
190: $db->quote('%ro%'));
191: $stmt = prepex(29, $db, $sql, array('placeholder' => -1));
192: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0)
193: printf("[030] Expecting zero rows, got %d rows\n", $tmp);
194:
195: $sql = sprintf("SELECT id, label FROM test WHERE (id = :placeholder) OR (label LIKE %s)",
196: $db->quote('%go%'));
197: $stmt = prepex(31, $db, $sql, array('placeholder' => 1));
198: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 1)
199: printf("[032] Expecting one row, got %d rows\n", $tmp);
200:
201: // and now, the same with anonymous placeholders...
202: prepex(33, $db, 'DROP TABLE IF EXISTS test');
203: prepex(34, $db, sprintf('CREATE TABLE test(id INT, label CHAR(255)) ENGINE=%s', PDO_MYSQL_TEST_ENGINE));
204: prepex(35, $db, "INSERT INTO test(id, label) VALUES(1, '?')");
205: $stmt = prepex(36, $db, 'SELECT label FROM test');
206: var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
207:
208: prepex(37, $db, 'DELETE FROM test');
209: prepex(38, $db, 'INSERT INTO test(id, label) VALUES(1, ?)',
210: array('first row'));
211: prepex(39, $db, 'INSERT INTO test(id, label) VALUES(2, ?)',
212: array('second row'));
213: $stmt = prepex(40, $db, 'SELECT label FROM test');
214: var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
215:
216: // Is PDO fun?
217: prepex(40, $db, 'SELECT label FROM test WHERE ? > 1',
218: array('id'));
219: prepex(41, $db, 'SELECT ? FROM test WHERE id > 1',
220: array('id'));
221: prepex(42, $db, 'SELECT ? FROM test WHERE ? > ?',
222: array('id', 'label', 'value'));
223:
224: for ($num_params = 2; $num_params < 100; $num_params++) {
225: $params = array('a');
226: for ($i = 1; $i < $num_params; $i++) {
227: $params[] = 'some data';
228: }
229: prepex(43, $db, 'SELECT id, label FROM test WHERE label > ?',
230: $params, array('execute' => array('sqlstate' => 'HY093')));
231: }
232:
233: prepex(44, $db, 'DELETE FROM test');
234: prepex(45, $db, 'INSERT INTO test(id, label) VALUES (1, ?), (2, ?)',
235: array('row', 'row'));
236: $stmt = prepex(46, $db, 'SELECT id, label FROM test');
237: $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC);
238: $exp = array(
239: 0 => array(
240: "id" => "1",
241: "label" => "row"
242: ),
243: 1 => array(
244: "id" => "2",
245: "label" => "row"
246: ),
247: );
248:
249: if (MySQLPDOTest::isPDOMySQLnd()) {
250: // mysqlnd returns native types
251: $exp[0]['id'] = 1;
252: $exp[1]['id'] = 2;
253: }
254: if ($tmp !== $exp) {
255: printf("[064] Results seem wrong. Please check dumps manually.\n");
256: var_dump($exp);
257: var_dump($tmp);
258: }
259:
260: $stmt = prepex(47, $db, 'SELECT id, label FROM test WHERE ? IS NOT NULL',
261: array(1));
262: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2)
263: printf("[048] '1' IS NOT NULL evaluates to true, expecting two rows, got %d rows\n", $tmp);
264:
265: $stmt = prepex(49, $db, 'SELECT id, label FROM test WHERE ? IS NULL',
266: array(1));
267: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0)
268: printf("[050] '1' IS NOT NULL evaluates to true, expecting zero rows, got %d rows\n", $tmp);
269:
270: prepex(51, $db, 'DROP TABLE IF EXISTS test');
271: prepex(52, $db, 'CREATE TABLE test(id INT, label CHAR(255)) ENGINE=MyISAM');
272: if (is_object(prepex(53, $db, 'CREATE FULLTEXT INDEX idx1 ON test(label)', null, null, true))) {
273: prepex(54, $db, 'INSERT INTO test(id, label) VALUES (1, ?)',
274: array('MySQL is the best database in the world!'));
275: prepex(55, $db, 'INSERT INTO test(id, label) VALUES (1, ?)',
276: array('If I have the freedom to choose, I would always go again for the MySQL Server'));
277: $stmt = prepex(56, $db, 'SELECT id, label FROM test WHERE MATCH label AGAINST (?)',
278: array('mysql'), null, true);
279: /*
280: Lets ignore that
281: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2)
282: printf("[074] Expecting two rows, got %d rows\n", $tmp);
283: */
284: }
285:
286: prepex(57, $db, 'DELETE FROM test');
287: prepex(58, $db, 'INSERT INTO test(id, label) VALUES (1, ?), (2, ?)',
288: array('row1', 'row2'));
289:
290: /*
291: TODO enable after fix
292: $stmt = prepex(37, $db, 'SELECT id, label FROM \'test WHERE MATCH label AGAINST (:placeholder)',
293: array(':placeholder' => 'row'),
294: array('execute' => array('sqlstate' => '42000', 'mysql' => 1064)));
295: */
296:
297: $stmt = prepex(59, $db, 'SELECT id, label AS "label" FROM test WHERE label = ?',
298: array('row1'));
299: $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC);
300: $exp = array(
301: 0 => array("id" => "1", "label" => "row1")
302: );
303:
304: if (MySQLPDOTest::isPDOMySQLnd()) {
305: // mysqlnd returns native types
306: $exp[0]['id'] = 1;
307: }
308: if ($tmp !== $exp) {
309: printf("[065] Results seem wrong. Please check dumps manually.\n");
310: var_dump($exp);
311: var_dump($tmp);
312: }
313:
314: $sql = sprintf("SELECT id, label FROM test WHERE (label LIKE %s) AND (id = ?)",
315: $db->quote('%ro%'));
316: $stmt = prepex(60, $db, $sql, array(-1));
317: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0)
318: printf("[061] Expecting zero rows, got %d rows\n", $tmp);
319:
320: $sql = sprintf("SELECT id, label FROM test WHERE (id = ?) OR (label LIKE %s)",
321: $db->quote('%ro%'));
322: $stmt = prepex(61, $db, $sql, array(1));
323: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2)
324: printf("[062] Expecting two rows, got %d rows\n", $tmp);
325:
326: $sql = "SELECT id, label FROM test WHERE id = ? AND label = (SELECT label AS 'SELECT' FROM test WHERE id = ?)";
327: $stmt = prepex(63, $db, $sql, array(1, 1));
328: if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 1)
329: printf("[064] Expecting one row, got %d rows\n", $tmp);
330:
331: } catch (PDOException $e) {
332: printf("[001] %s [%s] %s\n",
333: $e->getMessage(), $db->errorCode(), implode(' ', $db->errorInfo()));
334: }
335:
336: print "done!";
337: ?>
338: --CLEAN--
339: <?php
340: require dirname(__FILE__) . '/mysql_pdo_test.inc';
341: $db = MySQLPDOTest::factory();
342: $db->exec('DROP TABLE IF EXISTS test');
343: ?>
344: --EXPECTF--
345: array(1) {
346: [0]=>
347: array(1) {
348: ["label"]=>
349: string(12) ":placeholder"
350: }
351: }
352: array(2) {
353: [0]=>
354: array(1) {
355: ["label"]=>
356: string(9) "first row"
357: }
358: [1]=>
359: array(1) {
360: ["label"]=>
361: string(10) "second row"
362: }
363: }
364: array(0) {
365: }
366: array(1) {
367: [0]=>
368: array(1) {
369: ["label"]=>
370: string(1) "?"
371: }
372: }
373: array(2) {
374: [0]=>
375: array(1) {
376: ["label"]=>
377: string(9) "first row"
378: }
379: [1]=>
380: array(1) {
381: ["label"]=>
382: string(10) "second row"
383: }
384: }
385: done!
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>