note 114885 deleted from book.sqlite3 by cmb
| From: | cmb@php.net | Date: | Fri, 02 Sep 2016 14:36:41 +0000 |
| Subject: | note 114885 deleted from book.sqlite3 by cmb | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-206477@lists.php.net to get a copy of this message | ||
Note Submitter: bohwaz
----
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:
<?php
$db = 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'));
$statement->execute();
$fp = $db->openBlob('files', 'content', $id);
while (!feof($fp))
{
echo fgets($fp);
}
fclose($fp);
?>
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.