PHP PDO SQLite3 issue using "WITH" clause

Question

I'm working on a PHP project where I have to execute queries in a SQLite3 database. I'm using PHP PDO for that. All of my queries work like a charm, except one. The one I'm talking about is a recursive SQLite CTE.

Here is the statement:

WITH data 
AS ( 
    SELECT 1 AS Level, id, number_of_processors nop, processor_type pt 
    FROM   devices 
    UNION ALL 
    SELECT level + 1, data.id, nop, pt 
    FROM   data INNER JOIN devices d ON (data.id = d.id) 
    WHERE  level < nop )
SELECT id, level, pt AS processor_type FROM data ORDER BY id, level

I have tested this statement with "SQLite Manager" (Firefox extension) and there it works but not with PDO and not with php_sqlite3.dll.

My environment is configured like this:

• Windows Server 2012 R2
• Apache Webserver 2.4.10
• PHP 5.4.28 for Windows

The relevant PHP class is this:

use \PDO;
use \PDOException;
class SQLiteDBConnector {

    private $dbname = NULL;

    private $db = NULL;

    public function __construct( $dbname ) {
        $this->dbname = $dbname;

        return $this;
    }

    private function connect() {
        try {
            $this->db = new PDO( 'sqlite:' . $this->dbname );
            $this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        } catch ( PDOException $e ) {
            throw $e;
        }
    }

    public function getConnection() {
        if ( isset( $this->db ) === FALSE ) {
            $this->connect();
        }

        return $this->db;
    }

    public function query( $stmt ) {
        $resultset[] = array();

        try {
            $conn = $this->getConnection();
            $prepStmt = $conn->prepare( $sqlStmt );

            $prepStmt->execute();
            while ( $row = $prepStmt->fetch( PDO::FETCH_ASSOC ) ) {
                $resultset[] = $row;
            }
        } catch ( PDOException $e ) {
            throw $e;
        }

       return $resultset;
    }       
}

And this is the answer from PDO:

SQLSTATE[HY000]: General error: 1 near "WITH": syntax error

Does anyone know why the SQLSTATE error is thrown?

Answer 1

Odds are good that the version of the SQLite library PHP is compiled with on the Windows server doesn't support common table expressions, which were introduced fairly recently.

To check the version of the library, execute this on the server.

<?php print_r(SQLite3::version()); ?>

This is not necessarily the same version number you might see if you execute SQLite from the command line on the server. I think you need at least version 3.8.3.

Think about upgrading PHP on the server; the current version is 5.6+.