1--TEST-- 2MySQL PDO->exec(), affected rows 3--SKIPIF-- 4<?php 5require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'skipif.inc'); 6require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc'); 7MySQLPDOTest::skip(); 8?> 9--FILE-- 10<?php 11 function exec_and_count($offset, &$db, $sql, $exp = NULL) { 12 13 try { 14 15 $ret = $db->exec($sql); 16 if (!is_null($exp) && ($ret !== $exp)) { 17 printf("[%03d] Expecting '%s'/%s got '%s'/%s when running '%s', [%s] %s\n", 18 $offset, $exp, gettype($exp), $ret, gettype($ret), $sql, 19 $db->errorCode(), implode(' ', $db->errorInfo())); 20 return false; 21 } 22 23 } catch (PDOException $e) { 24 printf("[%03d] '%s' has failed, [%s] %s\n", 25 $offset, $sql, $db->errorCode(), implode(' ', $db->errorInfo())); 26 return false; 27 } 28 29 return true; 30 } 31 32 require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc'); 33 $db = MySQLPDOTest::factory(); 34 MySQLPDOTest::createTestTable($db, MySQLPDOTest::detect_transactional_mysql_engine($db)); 35 36 /* affected rows related */ 37 try { 38 39 exec_and_count(2, $db, 'DROP TABLE IF EXISTS test', 0); 40 exec_and_count(3, $db, sprintf('CREATE TABLE test(id INT NOT NULL PRIMARY KEY, col1 CHAR(10)) ENGINE=%s', PDO_MYSQL_TEST_ENGINE), 0); 41 exec_and_count(4, $db, "INSERT INTO test(id, col1) VALUES (1, 'a')", 1); 42 exec_and_count(5, $db, "INSERT INTO test(id, col1) VALUES (2, 'b'), (3, 'c')", 2); 43 exec_and_count(6, $db, "UPDATE test SET id = 4 WHERE id = 3", 1); 44 exec_and_count(7, $db, "INSERT INTO test(id, col1) VALUES (1, 'd') ON DUPLICATE KEY UPDATE id = 3", 2); 45 exec_and_count(8, $db, "UPDATE test SET id = 5 WHERE id = 5", 0); 46 exec_and_count(9, $db, "INSERT INTO test(id, col1) VALUES (5, 'e') ON DUPLICATE KEY UPDATE id = 6", 1); 47 exec_and_count(10, $db, "REPLACE INTO test(id, col1) VALUES (5, 'f')", 2); 48 exec_and_count(11, $db, "REPLACE INTO test(id, col1) VALUES (6, 'g')", 1); 49 exec_and_count(12, $db, 'DELETE FROM test WHERE id > 2', 4); 50 exec_and_count(13, $db, 'DROP TABLE test', 0); 51 exec_and_count(14, $db, 'SET @myvar = 1', 0); 52 53 exec_and_count(15, $db, 'THIS IS NOT VALID SQL, I HOPE', false); 54 printf("[016] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo())); 55 56 exec_and_count(36, $db, sprintf('CREATE TABLE test(id INT NOT NULL PRIMARY KEY, col1 CHAR(10)) ENGINE=%s', PDO_MYSQL_TEST_ENGINE), 0); 57 exec_and_count(37, $db, "INSERT INTO test(id, col1) VALUES (1, 'a')", 1); 58 // Results may vary. Typically you will get 1. But the MySQL 5.1 manual states: Truncation operations do not return the number of deleted rows. 59 // Don't rely on any return value! 60 exec_and_count(38, $db, 'TRUNCATE TABLE test', NULL); 61 62 } catch (PDOException $e) { 63 printf("[001] %s, [%s] %s\n", 64 $e->getMessage(), 65 $db->errorCode(), implode(' ', $db->errorInfo())); 66 } 67 68 69 /* CREATE, DROP, CALL SP and SF */ 70 if (MySQLPDOTest::getServerVersion($db) > 50000) { 71 // let's try to play with stored procedures 72 try { 73 $ignore_exception = true; 74 exec_and_count(18, $db, 'DROP PROCEDURE IF EXISTS p', 0); 75 exec_and_count(19, $db, 'CREATE PROCEDURE p(OUT ver_param VARCHAR(255)) BEGIN SELECT VERSION() INTO ver_param; END;', 0); 76 // we got this far without problems. If there's an issue from now on, its a failure 77 $ignore_exception = false; 78 exec_and_count(20, $db, 'CALL p(@version)', 0); 79 $stmt = $db->query('SELECT @version AS p_version'); 80 $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC); 81 if (count($tmp) > 1 || !isset($tmp[0]['p_version'])) { 82 printf("[022] Data seems wrong, dumping\n"); 83 var_dump($tmp); 84 } else { 85 $p_version = $tmp[0]['p_version']; 86 } 87 88 $stmt = $db->query('SELECT VERSION() AS _version'); 89 $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC); 90 if (count($tmp) > 1 || !isset($tmp[0]['_version'])) { 91 printf("[023] Data seems wrong, dumping\n"); 92 var_dump($tmp); 93 } else { 94 if ($p_version !== $tmp[0]['_version']) { 95 printf("[024] Found different version strings, SP returned '%s'/%s, SELECT returned '%s'/%s\n", 96 $p_version, gettype($p_version), 97 $tmp[0]['_version'], gettype($tmp[0]['_version'])); 98 } 99 } 100 exec_and_count(25, $db, 'DROP PROCEDURE IF EXISTS p', 0); 101 102 } catch (PDOException $e) { 103 // ignore it, we might not have sufficient permissions 104 if (!$ignore_exception) 105 printf("[021] %s, [%s] %s\n", 106 $e->getMessage(), 107 $db->errorCode(), implode(' ', $db->errorInfo())); 108 } 109 110 // stored function 111 try { 112 $ignore_exception = true; 113 exec_and_count(27, $db, 'DROP FUNCTION IF EXISTS f', 0); 114 exec_and_count(28, $db, 'CREATE FUNCTION f( ver_param VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC RETURN ver_param;', 0); 115 // we got this far without problems. If there's an issue from now on, its a failure 116 $ignore_exception = false; 117 $stmt = $db->query('SELECT f(VERSION()) AS f_version'); 118 $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC); 119 if (count($tmp) > 1 || !isset($tmp[0]['f_version'])) { 120 printf("[029] Data seems wrong, dumping\n"); 121 var_dump($tmp); 122 } else { 123 $f_version = $tmp[0]['f_version']; 124 } 125 $stmt = $db->query('SELECT VERSION() AS _version'); 126 $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC); 127 if (count($tmp) > 1 || !isset($tmp[0]['_version'])) { 128 printf("[030] Data seems wrong, dumping\n"); 129 var_dump($tmp); 130 } else { 131 if ($f_version !== $tmp[0]['_version']) { 132 printf("[031] Found different version strings, SF returned '%s'/%s, SELECT returned '%s'/%s\n", 133 $f_version, gettype($f_version), 134 $tmp[0]['_version'], gettype($tmp[0]['_version'])); 135 } 136 } 137 exec_and_count(32, $db, 'DROP FUNCTION IF EXISTS f', 0); 138 139 } catch (PDOException $e) { 140 // ignore it, we might not have sufficient permissions 141 if (!$ignore_exception) 142 printf("[026] %s, [%s] %s\n", 143 $e->getMessage(), 144 $db->errorCode(), implode(' ', $db->errorInfo())); 145 } 146 } 147 148 // multi query 149 try { 150 151 $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1); 152 $exp = 0; 153 154 $tmp = @$db->exec(sprintf('DROP TABLE IF EXISTS test; CREATE TABLE test(id INT) ENGINE=%s', PDO_MYSQL_TEST_ENGINE)); 155 if ($exp !== $tmp) 156 printf("[034] Expecting %s/%s got %s/%s, [%s] %s\n", 157 $exp, gettype($exp), 158 $tmp, gettype($tmp), 159 $db->errorCode(), var_export($db->errorInfo(), true)); 160 161 // this is interesting: if we get sort of affected rows, what will happen now? 162 $tmp = @$db->exec('INSERT INTO test(id) VALUES (1); INSERT INTO test(id) VALUES (2)'); 163 printf("[035] With emulated PS it works but makes no sense given that exec() returns sort of affected rows...\n"); 164 165 166 } catch (PDOException $e) { 167 printf("[033] %s, [%s] %s\n", 168 $e->getMessage(), 169 $db->errorCode(), implode(' ', $db->errorInfo())); 170 } 171 $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0); 172 173 print "done!"; 174?> 175--CLEAN-- 176<?php 177require dirname(__FILE__) . '/mysql_pdo_test.inc'; 178$db = MySQLPDOTest::factory(); 179@$db->exec('DROP TABLE IF EXISTS test'); 180?> 181--EXPECTF-- 182Warning: PDO::exec(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THIS IS NOT VALID SQL, I HOPE' at line 1 in %s on line %d 183[016] [42000] 42000 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THIS IS NOT VALID SQL, I HOPE' at line %d 184[035] With emulated PS it works but makes no sense given that exec() returns sort of affected rows... 185done! 186
