User Contributed Notes 6 notes

Note that as of the date of this post, PDO_SQLITE will not interact with database files created with the current version of the SQLite console application, sqlite-3.3.6.

It is currently necessary to obtain version 3.2.8, available from http://www.sqlite.org/ but only by entering the URI manually, as there is no link. Go to http://www.sqlite.org/download.html and find the URI of the version you're looking for, then make the appropriate version number substitution.

Instead of compiling an old version of SQLite to create a database using an older database format that the version of SQLite bundled with PDO can handle, you can (much more easily) just run the query "PRAGMA legacy_file_format = TRUE;" BEFORE creating the database (if you have an existing database, run ".dump" from the sqlite shell on your database, run the sqlite shell on a new database, run the PRAGMA, then paste the contents of the .dump). That will ensure SQLite creates a database readable by SQLite 3.0 and later.

If you get an error reporting "invalid resource" when trying to query the database table and looping through it, the version of the SQLite extension compiled in to PHP might be incompatible with the version that had created the database (like SQLite 2.x vs 3.x).

The database open itself might be successful, failing only when querying.

After wrestling with "General error: 5 database is locked" errors for a highly concurrent project I finally wrapped the PDO transaction code with a semaphore. No errors since...

Obviously only works if all processes use the subclass and wrap database modifying statements in beginTransaction() .. commit(). The same could be achieved with flock() if semaphore is not available on your system but will be slower.

This is interesting as I am running SQLite version 3.3.5 which the databases were created in. I see that the SQLite PDO source in the php source is : # cat ext/pdo_sqlite/sqlite/VERSION 3.2.8

My solution was then to find a version of sqlite that was =< 3.1.4. I found source for 3.1.3, compiled, recreated my database using that version (NOTE: the databases are unsupported between 3.1.x and 3.2.x versions of SQLite). Once I did this it worked.