Edit report at http://bugs.php.net/bug.php?id=52637&edit=1
ID: 52637
User updated by: angelo dot courtel at laposte dot net
Reported by: angelo dot courtel at laposte dot net
Summary: bug in prepare statement
Status: Open
Type: Bug
Package: PDO related
Operating System: Debian Lenny
PHP Version: 5.2.6-1+lenny6
Block user comment: N
New Comment:
Well, but I want find a way to use a prepared statement, without need to
predeclare all paramètres on a sql comment ! It s not a very optimized
solution.
Previous Comments:
------------------------------------------------------------------------
[2010-09-06 15:13:59] u...@php.net
Well, if you comment out your SQL statement, it should work fine
regardless what it may look like... "-- " starts a single line SQL
comment.
------------------------------------------------------------------------
[2010-08-19 10:06:12] angelo dot courtel at laposte dot net
A little example for explain my bug :
This source works correctly on an Windows WAMP PHP5.2.6
But not on Debian :(
<?php
//running on PHP Version 5.2.6-1+lenny6
try {
$dbh = new
PDO('pgsql:host=localhost;port=5432;dbname=db;user=user;password=pass');
} catch (PDOException $e) {
echo 'Connexion failed : ' . $e->getMessage();exit;
}
//list or parameters
$id = 40;
$id2 = 40;
$param = array (':id' => $id, ':id2' => $id2);
//query failed
$sReq = '
select * from t1
left join t2 on t2.id = :id
left join t3 on t3.id = :id
where t1.id = :id2';
$rTest1 = $dbh->prepare($sReq);
if ($rTest1->execute($param))
echo 'OK';
else
var_dump($rTest1->errorInfo());
// --> array(3) { [0]=> string(5) "42P18" [1]=> int(7) [2]=>
string(69) "ERREUR: n'a pas pu déterminer le type de données du
paramètres $2" }
$rTest2 = $dbh->prepare($sReq);
$rTest2->bindParam(':id', $id);
$rTest2->bindParam(':id2', $id2);
if ($rTest2->execute())
echo 'OK';
else
var_dump($rTest2->errorInfo());
// --> array(3) { [0]=> string(5) "42P18" [1]=> int(7) [2]=>
string(69) "ERREUR: n'a pas pu déterminer le type de données du
paramètres $2" }
//query success
$sReq = '
-- :id :id2
select * from t1
left join t2 on t2.id = :id
left join t3 on t3.id = :id
where t1.id = :id2';
$rTest3 = $dbh->prepare($sReq);
if ($rTest3->execute($param))
echo 'OK';
else
var_dump($rTest3->errorInfo());
// --> OK
$rTest4 = $dbh->prepare($sReq);
$rTest4->bindParam(':id', $id);
$rTest4->bindParam(':id2', $id2);
if ($rTest4->execute())
echo 'OK';
else
var_dump($rTest4->errorInfo());
// --> OK
?>
------------------------------------------------------------------------
[2010-08-19 06:55:59] angelo dot courtel at laposte dot net
Hi, I use PostGreSql Pdo driver
Thks
------------------------------------------------------------------------
[2010-08-19 01:17:32] fel...@php.net
What PDO driver are you using?
------------------------------------------------------------------------
[2010-08-18 18:18:03] angelo dot courtel at laposte dot net
Description:
------------
Hi
when I execute a prepared query which use twice, or more, a same
parameters, it
returns an error : in french : ERREUR: n'a pas pu déterminer le type
de données
du paramètres $3
ie. : this query don't works
$sReq = 'select *
from categorie
left join budget on bud_cat = cat_cod and bud_moi = :month and bud_ann
=
:year
left join operation on ope_cat = cat_cod and ope_moi = :month and
ope_ann = :year
where cat_cod = :categorie';
$rCategorie = $oAppli->getDb()->prepare($sReq);
$rCategorie->bindParam(':month', date('m'));
$rCategorie->bindParam(':categorie', $sCategorie);
$rCategorie->bindParam(':year', date('Y'));
if ($rCategorie->execute())
but if I add "-- :year :categorie :month" at the beginning of the
query, it
works !!!
I thinks PDO don't like seeing a repeated parameter before seens all the
parameters (the :month appears twice before :categorie, almost with the
comment
at the beginning, PDO see all parameters at start)
Sorry for my english I'm french guy.
Thks
Test script:
---------------
//don't work
$sReq = 'select *
from categorie
left join budget on bud_cat = cat_cod and bud_moi = :month and bud_ann
= :year
left join operation on ope_cat = cat_cod and ope_moi = :month and
ope_ann = :year
where cat_cod = :categorie';
$rCategorie = $oAppli->getDb()->prepare($sReq);
$rCategorie->bindParam(':month', date('m'));
$rCategorie->bindParam(':categorie', $sCategorie);
$rCategorie->bindParam(':year', date('Y'));
if ($rCategorie->execute())
//works fine
$sReq = '-- :year :categorie :month
select *
from categorie
left join budget on bud_cat = cat_cod and bud_moi = :month and bud_ann
= :year
left join operation on ope_cat = cat_cod and ope_moi = :month and
ope_ann = :year
where cat_cod = :categorie';
$rCategorie = $oAppli->getDb()->prepare($sReq);
$rCategorie->bindParam(':month', date('m'));
$rCategorie->bindParam(':categorie', $sCategorie);
$rCategorie->bindParam(':year', date('Y'));
if ($rCategorie->execute())
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/bug.php?id=52637&edit=1
