sqlite databases won't open in anything else than PHP

Question

I recently ported one of my PHP applications from MySQL to SQLite to make it easier to administrate. For some reason, I can access a the sqlite database from PHP and it works perfectly, but if I try to open it with something else, like sqlitemanager, or the sqlite firefox plugin, the database just seems blank, or it won't open it.

Any ideas?

Answer 1

php has the old sqlite lib by default I believe, unless you use PDO. perhaps its a db version issue.

Answer 2

Sqlite is available in (at least) two major versions, 2 and 3, which are incompatible. Maybe there is some mismatch between php and the other clients. E.g. the sqlite_ prefixed functions use the outdated version 2, while Sqlite3 is the proper php class for the current version 3.

Answer 3

It seems that the problem was PHP not closing the database at the end of the PHP script. As far as I know, PHP automatically closes mySQL connections at the end of a script. It doesn't do the same for sqlite. So you don't need to explicitly close mySQL connections, but because Sqlite is actually a file handle, you need to explicitly close it. Could somebody confirm this just so I'm sure?

Answer 4

PHP automatically closes SQLite connections as well. I agree with mtvee that it's probably the version. There's a very big difference between 2 and 3. PDO can do both, but takes 3 as default. You can use 2 and 3 with separate classes (without PDO) as well: new SqliteDatabase('db.sqlite2') and new Sqlite3('db.sqlite3'). I very much recommend using PDO (and the standard version 3).