Search code examples
phppdosqliteparameterized-query

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 :-)

Solution

  • The libraries (SQLite vs PDO) are semantically pretty much the same, but under the hood there are some differences.

    For example, with PDO the execute() method of the PDOStatement returns either false or true and not the resultset. So when you do:

    $result->fetchArray();

    You're basically doing:

    true->fetchArray();

    Which is of course not a valid method (since true doesn't have any methods, being a boolean). You can fetch the results from an executed PDO statement with the fetchAll() method on the statement itself:

    $stmt->fetchAll();