ID: 40417 User updated by: exaton at free dot fr Reported By: exaton at free dot fr -Status: Feedback +Status: Open Bug Type: PDO related Operating System: Windows XP Pro SP2 PHP Version: 5.2.1 New Comment:
All right, here you go, but it's really because I love you guys :) So, still comparing PHP 5.2.0 and PHP 5.2.1 on Windows XP Pro SP2 with a PostgreSQL 8.1.5 backend. >From phpinfo() concerning pdo_pgsql : PHP 5.2.0 : PostgreSQL(libpq) Version 8.1.4 Module version 1.0.2 Revision $Id: pdo_pgsql.c,v 1.7.2.11 2006/03/14 10:49:18 edink Exp $ PHP 5.2.1 : PostgreSQL(libpq) Version 8.1.4 Module version 1.0.2 Revision $Id: pdo_pgsql.c,v 1.7.2.11.2.1 2007/01/01 09:36:05 sebastian Exp $ So the difference is just in the revision of pdo_pgsql.c . Now for the test case ; I'll even give you a test table : CREATE TABLE t ( id INTEGER PRIMARY KEY, s TEXT NOT NULL ); INSERT INTO t (id, s) VALUES (1, 'foo'); INSERT INTO t (id, s) VALUES (2, 'bar'); INSERT INTO t (id, s) VALUES (3, 'doh'); INSERT INTO t (id, s) VALUES (4, 'duh'); And here's the PHP code : // Connect to database // Let $DATA be the resulting PDO object $sta = $DATA -> prepare('SELECT id, s FROM t WHERE id = :id OR id = :id'); // notice 2 identical ':id' tokens $sta -> bindValue(':id', 2, PDO :: PARAM_INT); // bind ':id' a single time, of course $sta -> execute(); // this is line #12 $arr = $sta -> fetch(PDO :: FETCH_ASSOC); echo "<pre>"; print_r($arr); echo "</pre>"; /* Expected (as obtained in PHP 5.2.0) : Array ( [id] => 2 [s] => bar ) */ /* Obtained in PHP 5.2.1 : PDOException thrown at ...\bug.php(12) SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 ...\bug.php(12): PDOStatement->execute() #1 {main} */ Binding an extra token (to make up the 2 tokens in the prepared statement, even though they are identical) will work around the problem : $sta -> bindValue(':xyz', 42, PDO :: PARAM_INT); I think it should complain that the :xyz token is not to be found in the statement in the first place, but anyway (it doesn't make that complaint in PHP 5.2.0 either). By adding that line before the call to execute(), the expected result is obtained. Hope this helps ! Previous Comments: ------------------------------------------------------------------------ [2007-02-09 16:58:09] [EMAIL PROTECTED] Thank you for this bug report. To properly diagnose the problem, we need a short but complete example script to be able to reproduce this bug ourselves. A proper reproducing script starts with <?php and ends with ?>, is max. 10-20 lines long and does not require any external resources such as databases, etc. If the script requires a database to demonstrate the issue, please make sure it creates all necessary tables, stored procedures etc. Please avoid embedding huge scripts into the report. ------------------------------------------------------------------------ [2007-02-09 16:39:14] exaton at free dot fr Description: ------------ I have just upgraded from PHP 5.2.0 to PHP 5.2.1, and one of my scripts has broken on the following point (note, the backend database is PostgreSQL 8.1.5) : Consider this prepared statement query, automatically generated as part of a basic search engine operating on a table of shops : SELECT indx, name, town FROM shops WHERE enabled AND (lower(name) LIKE :word0 OR lower(address) LIKE :word0 OR lower(town) LIKE :word0 OR lower(company) LIKE :word0 OR lower(description) LIKE :word0) ORDER BY name; You notice that 5 ':word0' tokens are defined. I then proceed to bind ':word0' to a certain value (individual $word taken from a search field), *a single time* of course : $shops -> bindValue(':word'.$i, '%'.$word.'%'); // $i = 0 Up to PHP 5.2.0, this worked as expected. Now in PHP 5.2.1 I am getting a PDOException : "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens". To work around this problem, I indeed have to call bindValue() as many times as there are tokens (5 in this example), even though those tokens are identical. As a consequence, the name of the extra *fictitious* bound tokens does not matter, except that binding 5 times the same token name (e.g. 5 times ':word0') does not work. But binding ':word0' to ':word4' does, for instance. I have noticed some similarity with PHP bug #33886, but I believe this to be a slightly different situation (bindValue() as opposed to on-the-fly binding), not to mention that it breaks existing scripts. Thank you in advance for your feedback on this issue. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=40417&edit=1