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