add a note add a note

User Contributed Notes 4 notes

5 years ago
As of PHP 5.4 support for Sqlite2 has been removed. I have a large web app that was built with sqlite2 as the database backend and thus it exploded when I updated PHP. If you're in a similar situation I've written a few wrapper functions that will allow your app to work whilst you convert the code to sqlite3.

Firstly convert your DB to an sqlite3 db.

sqlite OLD.DB .dump | sqlite3 NEW.DB

Then add the following functions to your app:

function sqlite_open($location,$mode)
$handle = new SQLite3($location);
$array['dbhandle'] = $dbhandle;
$array['query'] = $query;
$result = $dbhandle->query($query);
#Get Columns
$i = 0;
    while (
$columns[ ] = $result->columnName($i);
$resx = $result->fetchArray(SQLITE3_ASSOC);

They're not perfect by any stretch but they seem to be working ok as a temporary measure while I convert the site.
Hope that helps someone
3 years ago
As reported here: https://bugs.php.net/bug.php?id=65216 there are two undocumented and useful methods available:

SQLite3::createCollation(collation name, callback function) appeared in PHP 5.3.11 and allows to define a custom collation method.

SQLite3Stmt::readOnly(void) appeared in PHP 5.3.5 and returns true if a statement doesn't write in the database.

See the documentation pages on the corresponding classes for my comments showing example use of those methods.
2 years ago
Another undocumented feature of the PHP SQLite3 object, available since 2009 is openBlob. Basically it's a function that will return a stream pointer to a blob value in a table. Very very useful when you are dealing with files stored in a SQLite3 database.

Source code says:

proto resource SQLite3::openBlob(string table, string column, int rowid [, string dbname])
Open a blob as a stream which we can read / write to.

But despite that it's not possible to write to the blob, only to read (the write method is empty in the extension source code: it won't return any error but no change will be recorded).

One example of use:


= new SQLite3('files.sqlite');
$db->exec('CREATE TABLE files (id INTEGER PRIMARY KEY, filename TEXT, content BLOB);');

$statement = $db->prepare('INSERT INTO files (filename, content) VALUES (?, ?);');
$statement->bindValue('filename', 'Archive.zip');
$statement->bindValue('content', file_get_contents('Archive.zip'));

$fp = $db->openBlob('files', 'content', $id);

while (!



You can also seek in the stream. This is pretty useful for saving large files from the database too, this way you can use stream_copy_to_stream, it will be faster and more memory-efficient than dumping the file in memory before writing it to the disk.

Please note that openBlob() won't work on VIRTUAL FTS4 compressed tables.
alan at chandlerfamily dot org dot uk
6 years ago
PHP 5.3.3 introduced sqlite3::busyTimeout(int milliseconds) which does not currently seem to be documented.

It believe it acts like sqlite::busyTimeout - that is it tells sqlite3 to call an internal busyHandler if SQLITE_BUSY is returned from any call which waits a short period and then retries.  It continues to do this until milliseconds milliseconds have elapsed and then returns the SQLITE_BUSY status.

I don't know whether the default 60 second value is in place if this function is not called.
To Top