Writing large blob of unknown size into SQLite

Question

I am implementing a stream interface that takes binary data from a stream (e.g. a HTTP download ord Android content:// share) and stores it into a SQLite blob.

Is there a way to use the sqlite3_blob_open and sqlite3_blob_write interfaces without knowing the end size in advance?

sqlite3_blob_open states that:

Use the sqlite3_blob_bytes() interface to determine the size of the opened blob. The size of a blob may not be changed by this interface. Use the UPDATE SQL command to change the size of a blob.

My current code looks like this. Set initial size (1):

// set size of attachment
{
    auto stmt = session_->prepare(
                "UPDATE attachments_content "
                "SET content = zeroblob(:size) "
                "WHERE rowid = :rowid");
    stmt.bind(":size", size_);
    stmt.bind(":rowid", rowid);
    stmt.execWithoutResult();
}

Get blob handle (2), this is a wrapper around sqlite3_blob_open:

    auto blob = session_->openBlob("main", "attachments_content",
                                  "content", rowid,
                                  SmartSqlite::Blob::READWRITE);

Write a piece of data (3):

        blob.write(data.data(), data.size(),
                   progress.bytesProcessed /* offset */);

Given that the blob object cannot resize the blob, is there any other way to incrementally increase the blob size then receiving more data?

CL. · Accepted Answer · 2016-11-23 19:55:20Z

4

The only three ways of modifying blobs are

to use SQL statements, or
to use the sqlite3_blob_*() interface, which cannot resize blobs, or
to modify the database file directly.

Due to SQLite's record format, changing the size of a blob can require rewriting the entire row. Therefore, what you want cannot be done efficiently.

I'd consider writing the stream into a temporary file and handling it afterwards.

edited Nov 23, 2016 at 19:55

answered Nov 21, 2016 at 17:04

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Warta Over a year ago

Sounds plausible, thanks. But I still have some hope that someone has a workaround for this limitation.

Collectives™ on Stack Overflow

Writing large blob of unknown size into SQLite

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related