#41622 [Fbk->Opn]: no binding with prepare/execute
ID: 41622
User updated by: andrea dot spacca at gmail dot com
Reported By: andrea dot spacca at gmail dot com
-Status: Feedback
+Status: Open
Bug Type: PDO related
Operating System: Linux
PHP Version: 5.2.3
New Comment:
> mysql> CREATE TABLE tbl (field);
> ERROR 1064 (42000): 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 ')' at line 1
please, pay attention: i'm using sqlite
Previous Comments:
[2007-06-07 12:53:02] [EMAIL PROTECTED]
mysql> CREATE TABLE tbl (field);
ERROR 1064 (42000): 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 ')' at line 1
[2007-06-07 12:49:10] andrea dot spacca at gmail dot com
> Works just fine here.
i've no doubt, otherwise you'd open the bug before me
> (And apparently everywhere else, since this is a very basic
> functionality and you're the only one complaining.)
that's false: i'm not the only one complaining, see
http://pecl.php.net/bugs/bug.php?id=6030 and
http://bugs.php.net/bug.php?id=36788. the fact that the complaints were
ignored doesn't mean that the bug doesn't exist
> Sorry, I don't understand what you're talking about.
i'll be more explicit:
[code]
try {
$createSql = 'CREATE TABLE tbl (field)';
$insertSql = array();
$insertSql[1] = 'INSERT INTO tbl (field) VALUES (1)';
$insertSql[2] = 'INSERT INTO tbl (field) VALUES (2)';
$insertSql[3] = 'INSERT INTO tbl (field) VALUES (3)';
$insertSql[4] = 'INSERT INTO tbl (field) VALUES (4)';
$insertSql[5] = 'INSERT INTO tbl (field) VALUES (5)';
$insertSql[6] = 'INSERT INTO tbl (field) VALUES (6)';
$insertSql[7] = 'INSERT INTO tbl (field) VALUES (7)';
$selectSql = 'SELECT field FROM tbl WHERE field BETWEEN 3 AND
5';
$bindedSql = 'SELECT field FROM tbl WHERE field BETWEEN :first
AND :last';
$bindedValues = array(':first' => 3, ':last' => 5);
$db = new PDO('sqlite:test.db');
$db->exec($createSql);
foreach ($insertSql as $query) {
$db->exec($query);
}
$testNoBind = $db->query($selectSql);
$rowsNoBind = $testNoBind->fetchAll();
var_dump($rowsNoBind);
$testWithBind = $db->prepare($bindedSql);
$testWithBind->execute($bindedValues);
$rowsWithBind = $testWithBind->fetchAll();
var_dump($rowsWithBind);
$db = NULL;
} catch (Exception $e) {
var_dump($e);
}
[/code]
[expected]
array(3) {
[0]=>
array(2) {
["field"]=>
string(1) "3"
[0]=>
string(1) "3"
}
[1]=>
array(2) {
["field"]=>
string(1) "4"
[0]=>
string(1) "4"
}
[2]=>
array(2) {
["field"]=>
string(1) "5"
[0]=>
string(1) "5"
}
}
array(3) {
[0]=>
array(2) {
["field"]=>
string(1) "3"
[0]=>
string(1) "3"
}
[1]=>
array(2) {
["field"]=>
string(1) "4"
[0]=>
string(1) "4"
}
[2]=>
array(2) {
["field"]=>
string(1) "5"
[0]=>
string(1) "5"
}
}
[/expected]
[result]
array(3) {
[0]=>
array(2) {
["field"]=>
string(1) "3"
[0]=>
string(1) "3"
}
[1]=>
array(2) {
["field"]=>
string(1) "4"
[0]=>
string(1) "4"
}
[2]=>
array(2) {
["field"]=>
string(1) "5"
[0]=>
string(1) "5"
}
}
array(0) {
}
[/result]
is it more clear now?
[2007-06-07 10:20:05] [EMAIL PROTECTED]
>i thought that PDOStatement::queryString will change will executing
> the PDOStatement.
Well, you were wrong.
>obviously it isn't so, otherwise i hadn't open the bug
>i haven't yet tested it on another enviroment, but on the one i'm
>working it doesn't work.
Works just fine here.
(And apparently everywhere else, since this is a very basic
functionality and you're the only one complaining.)
>have i to open another bug showing the difference making the query
>strainght with no binding and without it? (the first works, not the
>latter)
Sorry, I don't understand what you're talking about.
[2007-06-07 10:15:16] andrea dot spacca at gmail dot com
> Binds are processed by the database drivers (where available), so
> $tmp->queryString contains original query, not the processed one.
i thought that PDOStatement::queryString will change will executing the
PDOStatement.
> And you code works just fine when table
> exists and contains requested data.
obviously it isn't so, otherwise i hadn't open the bug
i haven't yet tested it on another enviroment, bu
#41622 [Fbk->Opn]: no binding with prepare/execute
ID: 41622
User updated by: andrea dot spacca at gmail dot com
Reported By: andrea dot spacca at gmail dot com
-Status: Feedback
+Status: Open
Bug Type: PDO related
Operating System: Linux
PHP Version: 5.2.3
New Comment:
> Works just fine here.
i've no doubt, otherwise you'd open the bug before me
> (And apparently everywhere else, since this is a very basic
> functionality and you're the only one complaining.)
that's false: i'm not the only one complaining, see
http://pecl.php.net/bugs/bug.php?id=6030 and
http://bugs.php.net/bug.php?id=36788. the fact that the complaints were
ignored doesn't mean that the bug doesn't exist
> Sorry, I don't understand what you're talking about.
i'll be more explicit:
[code]
try {
$createSql = 'CREATE TABLE tbl (field)';
$insertSql = array();
$insertSql[1] = 'INSERT INTO tbl (field) VALUES (1)';
$insertSql[2] = 'INSERT INTO tbl (field) VALUES (2)';
$insertSql[3] = 'INSERT INTO tbl (field) VALUES (3)';
$insertSql[4] = 'INSERT INTO tbl (field) VALUES (4)';
$insertSql[5] = 'INSERT INTO tbl (field) VALUES (5)';
$insertSql[6] = 'INSERT INTO tbl (field) VALUES (6)';
$insertSql[7] = 'INSERT INTO tbl (field) VALUES (7)';
$selectSql = 'SELECT field FROM tbl WHERE field BETWEEN 3 AND
5';
$bindedSql = 'SELECT field FROM tbl WHERE field BETWEEN :first
AND :last';
$bindedValues = array(':first' => 3, ':last' => 5);
$db = new PDO('sqlite:test.db');
$db->exec($createSql);
foreach ($insertSql as $query) {
$db->exec($query);
}
$testNoBind = $db->query($selectSql);
$rowsNoBind = $testNoBind->fetchAll();
var_dump($rowsNoBind);
$testWithBind = $db->prepare($bindedSql);
$testWithBind->execute($bindedValues);
$rowsWithBind = $testWithBind->fetchAll();
var_dump($rowsWithBind);
$db = NULL;
} catch (Exception $e) {
var_dump($e);
}
[/code]
[expected]
array(3) {
[0]=>
array(2) {
["field"]=>
string(1) "3"
[0]=>
string(1) "3"
}
[1]=>
array(2) {
["field"]=>
string(1) "4"
[0]=>
string(1) "4"
}
[2]=>
array(2) {
["field"]=>
string(1) "5"
[0]=>
string(1) "5"
}
}
array(3) {
[0]=>
array(2) {
["field"]=>
string(1) "3"
[0]=>
string(1) "3"
}
[1]=>
array(2) {
["field"]=>
string(1) "4"
[0]=>
string(1) "4"
}
[2]=>
array(2) {
["field"]=>
string(1) "5"
[0]=>
string(1) "5"
}
}
[/expected]
[result]
array(3) {
[0]=>
array(2) {
["field"]=>
string(1) "3"
[0]=>
string(1) "3"
}
[1]=>
array(2) {
["field"]=>
string(1) "4"
[0]=>
string(1) "4"
}
[2]=>
array(2) {
["field"]=>
string(1) "5"
[0]=>
string(1) "5"
}
}
array(0) {
}
[/result]
is it more clear now?
Previous Comments:
[2007-06-07 10:20:05] [EMAIL PROTECTED]
>i thought that PDOStatement::queryString will change will executing
> the PDOStatement.
Well, you were wrong.
>obviously it isn't so, otherwise i hadn't open the bug
>i haven't yet tested it on another enviroment, but on the one i'm
>working it doesn't work.
Works just fine here.
(And apparently everywhere else, since this is a very basic
functionality and you're the only one complaining.)
>have i to open another bug showing the difference making the query
>strainght with no binding and without it? (the first works, not the
>latter)
Sorry, I don't understand what you're talking about.
[2007-06-07 10:15:16] andrea dot spacca at gmail dot com
> Binds are processed by the database drivers (where available), so
> $tmp->queryString contains original query, not the processed one.
i thought that PDOStatement::queryString will change will executing the
PDOStatement.
> And you code works just fine when table
> exists and contains requested data.
obviously it isn't so, otherwise i hadn't open the bug
i haven't yet tested it on another enviroment, but on the one i'm
working it doesn't work.
have i to open another bug showing the difference making the query
strainght with no binding and without it? (the first works, not the
latter)
[2007-06-07 10:04:02] [EMAIL PROTECTED]
>$tmp->quetryString haven't the :placeholder binded to the value
>passed in execute(), so the query won't be a valid one and
>$tmp->fetchAll() will be empty
Binds are processed by the database drivers (where available), so
$tmp->queryString contains original query, not the processed one.
And you code works just fine when table exists and contains requested
data.
#41622 [Fbk->Opn]: no binding with prepare/execute
ID: 41622
User updated by: andrea dot spacca at gmail dot com
Reported By: andrea dot spacca at gmail dot com
-Status: Feedback
+Status: Open
Bug Type: PDO related
Operating System: Linux
PHP Version: 5.2.3
New Comment:
> Binds are processed by the database drivers (where available), so
> $tmp->queryString contains original query, not the processed one.
i thought that PDOStatement::queryString will change will executing the
PDOStatement.
> And you code works just fine when table
> exists and contains requested data.
obviously it isn't so, otherwise i hadn't open the bug
i haven't yet tested it on another enviroment, but on the one i'm
working it doesn't work.
have i to open another bug showing the difference making the query
strainght with no binding and without it? (the first works, not the
latter)
Previous Comments:
[2007-06-07 10:04:02] [EMAIL PROTECTED]
>$tmp->quetryString haven't the :placeholder binded to the value
>passed in execute(), so the query won't be a valid one and
>$tmp->fetchAll() will be empty
Binds are processed by the database drivers (where available), so
$tmp->queryString contains original query, not the processed one.
And you code works just fine when table exists and contains requested
data.
[2007-06-07 10:03:59] andrea dot spacca at gmail dot com
i haven't put the example in the code but the problem raises both with
named and "anonymous" placeholders
[2007-06-07 09:54:57] andrea dot spacca at gmail dot com
in expected result the var_dumped array should be:
array(1) {
[0]=>
array(2) {
["field"]=>
string(1) "1"
[0]=>
string(1) "1"
}
}
[2007-06-07 09:53:44] andrea dot spacca at gmail dot com
Description:
Binding params to placeholders in prepared query don't work
(i tested the script both cli and in apache enviroment)
[pdo_mysql]
PDO Driver for MySQL, client library version => 5.0.34
[pdo_sqlite]
PDO Driver for SQLite 3.x => enabled
PECL Module version => 1.0.1
SQLite Library => 3.3.17
Reproduce code:
---
try {
$sql = 'SELECT field FROM table WHERE field = :placeholder
LIMIT 0, 1';
$db = new PDO([...]);
$tmp = $db->prepare($sql);
$tmp->execute(array(':placeholder' => 1));
var_dump($tmp->queryString);
var_dump($tmp->fetchAll());
$db = NULL;
} catch (Exception $e) {
var_dump($e);
}
Expected result:
$tmp->queryString should contain the :placeholder binded to the value
passed in execute(), and (as far as the query will produce row result
from the db) $tmp->fetchAll() should contain the resulted row
expected output:
string(61) "SELECT field FROM table WHERE field = 1 LIMIT 0, 1"
array(1) {
[0]=>
array(2) {
["field"]=>
string(1) "1"
[0]=>
string(1) "1"
["urlString"]=>
string(44) "http://comequandofuoripiove.leonardo.it/foto";
[1]=>
string(44) "http://comequandofuoripiove.leonardo.it/foto";
["scheme"]=>
string(4) "http"
[2]=>
string(4) "http"
["dominio"]=>
string(32) "comequandofuoripiove.leonardo.it"
[3]=>
string(32) "comequandofuoripiove.leonardo.it"
["insertTimestamp"]=>
string(13) "1177596562453"
[4]=>
string(13) "1177596562453"
["diffChk"]=>
string(1) "1"
[5]=>
string(1) "1"
}
}
Actual result:
--
$tmp->quetryString haven't the :placeholder binded to the value passed
in execute(), so the query won't be a valid one and $tmp->fetchAll()
will be empty
the same happens using PDOStatement::bindValue() or
PDOStatement::bindParam()
the result seems not being related to a specific db driver (i tested
with mysql and sqlite)
no exception is raised
script output:
string(61) "SELECT * FROM table WHERE field = :placeholder"
array(0) {
}
ps: if i remove the placeholder from prepare() and then try to still
bind a value in execute() an exception is raised: "SQLSTATE[HY000]:
General error: 25 bind or column index out of range"
--
Edit this bug report at http://bugs.php.net/?id=41622&edit=1
