PHP version: I've tried with both 5.1.2 and 5.1.3-dev
PDO version: Bundled, I think it's 1.0.2
MYSQL_PDO: I've tried with both 0.9 (on php 5.1.2) and 1.0.1 (on php 5.1.3-dev)
Hi, I'm having some difficulty with PDO's bindParam (and related) methods. It is easiest to see the problem with a small example.
<?php
try {
$db = new PDO(/*removed*/);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT id, name, description FROM recipe";
$stmt = $db->prepare( $sql );
$stmt->execute();
$res = $stmt->fetch();
print_r($res);
} catch(PDOException $e) {
die("PDO Error: ".$e->getMessage()."\n");
}
?>
gives
Array
(
[id] => 1
[0] => 1
[name] => Test 1
[1] => Test 1
[description] => This is my first test recipe, it was originally created to test the caching architecture but now can be anything.
[2] => This is my first test recipe, it was originally created to test the caching architecture but now can be anything.
)
However if I try to add some parameter bindings to the query it all falls down. Here's the same example modified.
<?php
try {
$db = new PDO(/*removed*/);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT id, name, description FROM recipe WHERE id=:id";
$stmt = $db->prepare( $sql );
$id = 1;
$stmt->bindParam(':id', $id);
$stmt->execute();
$res = $stmt->fetch();
var_dump($res);
} catch(PDOException $e) {
die("PDO Error: ".$e->getMessage()."\n");
}
?>
gives
bool(false)
I have tried using bindValue and passing an associative array into execute. I have also tried using non-named parameters but none of them seem to work.
I've tried it on multiple setups so I'm pretty sure it's something in my code. Anyone got any ideas?I can't answer your question, but my efforts with PDO were pretty shortlived when a simple query caused a semi-random crash in Apache on Windows. Just that was enough to make me get rid of PDO and use the trusty ADODB for data access. No nasty surprises on Windows or Linux.I've used PDO quite a bit - I've never used named bound parameters though - I totally disagree with what Shrike says - PDO is definitely the future way, PEAR:
B and ADODB are outdated and should not be used.
I've used positional parameters (with a ?) in PDO with MySQL and sqlite (3) and never had a problem. Named parameters are different however.
I assume that you have error_reporting(E_ALL), a sensible error handler and you've enabled PDO errors via exceptions (which I now realise you have) ?
Otherwise, errors could be happening left, right and centre and you'd not notice them.
I also recommend that you don't use die() in that way. If you want to abort when an exception happens, then *don't catch it*. Otherwise you'll not see the stack trace (which doesn't matter in this example, but would do in a much larger app).
die() is a mistake made my programmers who think that PHP is Perl. die() has some special semantics in Perl but it's not useful in PHP. In PHP, you should either call exit (Normal exit), user_error (To trigger an error deliberately), or in PHP5+, just throw an exception.
MarkADODB may be old but it's proven and has the features which make PDO 'cool'. I do really like the idea of PDO but was really turned off by a random Apache crash. I would use it over ADODB otherwise. By the way the crash occurred when using named parameters
I've used PDO quite a bit - I've never used named bound parameters though - I totally disagree with what Shrike says - PDO is definitely the future way, PEAR:B and ADODB are outdated and should not be used.
Quite confident there. I'd be carefull about being so dismissive. PEAR DB and ADODB are still perfectly viable sollutions.
I've used positional parameters (with a ?) in PDO with MySQL and sqlite (3) and never had a problem. Named parameters are different however.
I'm having the same issue with positional parameters.
I assume that you have error_reporting(E_ALL), a sensible error handler and you've enabled PDO errors via exceptions (which I now realise you have) ?
Yes (E_ALL|E_STRICT in fact), just outputing to screen at this stage and yes (as you noted).
Otherwise, errors could be happening left, right and centre and you'd not notice them.
I actually thought of this and forced a few errors just to check there wasn't anything odd going on in the error reporting.
Shrike, I'm certainly going to look at ADODB if my problem persists.
I've carried on with this problem for most of the evening and tried a number of different things.
I found BUG#35671 (<!-- m --><a class="postlink" href="http://bugs.php.net/bug.php?id=35671&edit=1">http://bugs.php.net/bug.php?id=35671&edit=1</a><!-- m -->) which sounds similar so I tried installing the 5.1.0 release from cvs but failed ... on pdo_mysql :grr:
Then I noticed that on my desktop (Debian Sarge) it's working fine, so I've recompiled the stock php-5.1.2 and I'm back to square one. I'm starting to think that it's something to do with the MySQL install as that seems to be the only major difference (the OS should be relatively similar). The MySQL installs on both the Debian machine and on my Kubuntu laptop are stock installs through apt.
Debian : mysql Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386)
Kubuntu : mysql Ver 14.7 Distrib 4.1.12, for pc-linux-gnu (i486) using readline 4.3
It's late so I'm going to sleep on it now, if anyone has any more ideas I'd love to hear them.
Cheers
BubbleOk, I'm (mostly) out of ideas. What you're doing looks to me as if it should work, and I've been doing similar stuff with success.
Looking at my code, I've mostly used execute($params) rather than bindParam. However, there is one case where I'm using bindParam and it works fine.
bindValue should be looked at also - it's exactly like bindParam I think, but works by value rather than reference.
What must be happening is that it's failing to pick up the value of $id. Have you tried putting in a record in your DB with an id=0 (I know that id values of 0 are usually frowned upon) ?
If the id isn't coming through correctly, it's proabbly being substituted by 0.
Another thing to try is to enable logging in MySQL and see what's being sent to the DB. Are you using MySQL 4.1+ ? I've been using 4.0 - therefore presumably PDO prepares SQL itself rather than passing the placeholders into MySQL.
Mark
PDO version: Bundled, I think it's 1.0.2
MYSQL_PDO: I've tried with both 0.9 (on php 5.1.2) and 1.0.1 (on php 5.1.3-dev)
Hi, I'm having some difficulty with PDO's bindParam (and related) methods. It is easiest to see the problem with a small example.
<?php
try {
$db = new PDO(/*removed*/);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT id, name, description FROM recipe";
$stmt = $db->prepare( $sql );
$stmt->execute();
$res = $stmt->fetch();
print_r($res);
} catch(PDOException $e) {
die("PDO Error: ".$e->getMessage()."\n");
}
?>
gives
Array
(
[id] => 1
[0] => 1
[name] => Test 1
[1] => Test 1
[description] => This is my first test recipe, it was originally created to test the caching architecture but now can be anything.
[2] => This is my first test recipe, it was originally created to test the caching architecture but now can be anything.
)
However if I try to add some parameter bindings to the query it all falls down. Here's the same example modified.
<?php
try {
$db = new PDO(/*removed*/);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT id, name, description FROM recipe WHERE id=:id";
$stmt = $db->prepare( $sql );
$id = 1;
$stmt->bindParam(':id', $id);
$stmt->execute();
$res = $stmt->fetch();
var_dump($res);
} catch(PDOException $e) {
die("PDO Error: ".$e->getMessage()."\n");
}
?>
gives
bool(false)
I have tried using bindValue and passing an associative array into execute. I have also tried using non-named parameters but none of them seem to work.
I've tried it on multiple setups so I'm pretty sure it's something in my code. Anyone got any ideas?I can't answer your question, but my efforts with PDO were pretty shortlived when a simple query caused a semi-random crash in Apache on Windows. Just that was enough to make me get rid of PDO and use the trusty ADODB for data access. No nasty surprises on Windows or Linux.I've used PDO quite a bit - I've never used named bound parameters though - I totally disagree with what Shrike says - PDO is definitely the future way, PEAR:
B and ADODB are outdated and should not be used.I've used positional parameters (with a ?) in PDO with MySQL and sqlite (3) and never had a problem. Named parameters are different however.
I assume that you have error_reporting(E_ALL), a sensible error handler and you've enabled PDO errors via exceptions (which I now realise you have) ?
Otherwise, errors could be happening left, right and centre and you'd not notice them.
I also recommend that you don't use die() in that way. If you want to abort when an exception happens, then *don't catch it*. Otherwise you'll not see the stack trace (which doesn't matter in this example, but would do in a much larger app).
die() is a mistake made my programmers who think that PHP is Perl. die() has some special semantics in Perl but it's not useful in PHP. In PHP, you should either call exit (Normal exit), user_error (To trigger an error deliberately), or in PHP5+, just throw an exception.
MarkADODB may be old but it's proven and has the features which make PDO 'cool'. I do really like the idea of PDO but was really turned off by a random Apache crash. I would use it over ADODB otherwise. By the way the crash occurred when using named parameters
I've used PDO quite a bit - I've never used named bound parameters though - I totally disagree with what Shrike says - PDO is definitely the future way, PEAR:B and ADODB are outdated and should not be used.Quite confident there. I'd be carefull about being so dismissive. PEAR DB and ADODB are still perfectly viable sollutions.
I've used positional parameters (with a ?) in PDO with MySQL and sqlite (3) and never had a problem. Named parameters are different however.
I'm having the same issue with positional parameters.
I assume that you have error_reporting(E_ALL), a sensible error handler and you've enabled PDO errors via exceptions (which I now realise you have) ?
Yes (E_ALL|E_STRICT in fact), just outputing to screen at this stage and yes (as you noted).
Otherwise, errors could be happening left, right and centre and you'd not notice them.
I actually thought of this and forced a few errors just to check there wasn't anything odd going on in the error reporting.
Shrike, I'm certainly going to look at ADODB if my problem persists.
I've carried on with this problem for most of the evening and tried a number of different things.
I found BUG#35671 (<!-- m --><a class="postlink" href="http://bugs.php.net/bug.php?id=35671&edit=1">http://bugs.php.net/bug.php?id=35671&edit=1</a><!-- m -->) which sounds similar so I tried installing the 5.1.0 release from cvs but failed ... on pdo_mysql :grr:
Then I noticed that on my desktop (Debian Sarge) it's working fine, so I've recompiled the stock php-5.1.2 and I'm back to square one. I'm starting to think that it's something to do with the MySQL install as that seems to be the only major difference (the OS should be relatively similar). The MySQL installs on both the Debian machine and on my Kubuntu laptop are stock installs through apt.
Debian : mysql Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386)
Kubuntu : mysql Ver 14.7 Distrib 4.1.12, for pc-linux-gnu (i486) using readline 4.3
It's late so I'm going to sleep on it now, if anyone has any more ideas I'd love to hear them.
Cheers
BubbleOk, I'm (mostly) out of ideas. What you're doing looks to me as if it should work, and I've been doing similar stuff with success.
Looking at my code, I've mostly used execute($params) rather than bindParam. However, there is one case where I'm using bindParam and it works fine.
bindValue should be looked at also - it's exactly like bindParam I think, but works by value rather than reference.
What must be happening is that it's failing to pick up the value of $id. Have you tried putting in a record in your DB with an id=0 (I know that id values of 0 are usually frowned upon) ?
If the id isn't coming through correctly, it's proabbly being substituted by 0.
Another thing to try is to enable logging in MySQL and see what's being sent to the DB. Are you using MySQL 4.1+ ? I've been using 4.0 - therefore presumably PDO prepares SQL itself rather than passing the placeholders into MySQL.
Mark