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