mercredi 30 décembre 2015

Is there a difference between prepared queries in PDO and SQLite3?

I've been tearing my hair out all day with this. I see the same behaviour on both Windows and Linux.

Simple examples on this site and others work perfectly well demonstrating how parameterized queries work with SQLite (using the SQLite3 class). However the same examples do not work when using the PDO class - they return zero rows. I cannot find any reason for this.

Here's my test PHP script which effectively does the same thing twice - once connecting via SQLite3 and then with PDO. The first returns the inserted row - the second does not, although the inserted row exists in the database. What I am doing wrong?

<?php
echo "connecting via SQLite3<BR>";

unlink('mysqlitedb.db');
$db = new SQLite3('mysqlitedb.db');

$db->exec('CREATE TABLE foo (id INTEGER, bar STRING)');
$db->exec("INSERT INTO foo (id, bar) VALUES (1, 'This is a test')");

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

$result = $stmt->execute();
var_dump($result->fetchArray());

echo "<P>Connecting via PDO<BR>";

unlink('mysqlitepdo.db');
$db = new PDO('sqlite:mysqlitepdo.db');

$db->exec('CREATE TABLE foo (id INTEGER, bar STRING)');
$db->exec("INSERT INTO foo (id, bar) VALUES (1, 'This is a test')");

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

$result = $stmt->execute();
var_dump($result->fetchArray());

?>

When this script is executed it returns:

connecting via SQLite3
array(2) { [0]=> string(14) "This is a test" ["bar"]=> string(14) "This is a test" }
Connecting via PDO

Fatal error: Call to a member function fetchArray() on a non-object in D:\docs\LRRSA\LRRSA_site\MCC\foobar.php on line 28

I assume the fatal error is caused because no rows are returned for the varDump.

I'm sure this is something obvious, but if so it's too obvious for me :-)

Aucun commentaire:

Enregistrer un commentaire