#45259 [Opn->Fbk]: PDO prepared statements using SQLite can't bind non-text values in expressions

ID: 45259 Updated by: scottmac@php.net Reported By: ahp at byu dot edu -Status: Open +Status: Feedback Bug Type: PDO related Operating System: Ubuntu 8.04 PHP Version: 5.2.6 New Comment: Please try using this CVS snapshot: http://snaps.php.net/php5.3-latest.tar.gz For Windows (zip): http://snaps.php.net/win32/php5.3-win32-latest.zip For Windows (installer): http://snaps.php.net/win32/php5.3-win32-installer-latest.msi 5.3 now binds booleans and integers as the appropriate type, can you try again with the CVS version. Previous Comments: ------------------------------------------------------------------------ [2008-06-13 12:44:46] ahp at byu dot edu Description: ------------ When using PDO with the SQLite backend and prepared SQL statements, it appears to be impossible to bind anything but a text or NULL value to a parameter. The example program prints '3', even though 2 is less than 3. In fact, it will always print "3" no matter what value is bound to the ':value' parameter (with the exception of Null). Although SQLite's type affinity system can automatically convert values compared directly against a table column that has a type affinity, the inability to bind non-string-typed values presents a problem with values used in expressions, comparing against columns (especially computed columns) in views and unions, and comparisons against columns that do not have a type affinity. My specific problem occurred when trying to filter for an ID value against a key column through a view. The only workaround I found on the web is to not use bound parameters and prepared statements, but rather construct the SQL string afresh. Reproduce code: --------------- #!/usr/bin/php <script language="php"> $db=new PDO("sqlite:temp.db"); $q=$db->prepare("SELECT min(3, :value) AS result;"); $q->bindValue(':value', 2, PDO::PARAM_INT); $q->execute(); $row=$q->fetch(); print $row['result']."\n"; // Always prints '3' </script> Expected result: ---------------- The program should print '2' since min(3, 2) is 2. Actual result: -------------- The program prints: 3 ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=45259&edit=1