Handling BLOBs

This document introduces you to Binary Large Objects (BLOB) in Symbian SQL.

Purpose

A BLOB is an SQL database container object for binary large objects, usually images or audio files but may also include other binary files such as an application executable.

Features of SQL BLOB

BLOBS offer the following features:

read from and write to BLOB objects in a RAM-efficient manner -- enables an overall reduction of RAM usage,
reduced read latency (compared to previous versions) -- improving the responsiveness of client applications,
larger BLOB objects can be stored and retrieved (>16Mb). Before version 9.5 there was a limit on the size of a BLOB object supported by Symbian SQL. This limit was dictated by the amount of server-side RAM available. Applications that needed to store large BLOB objects in their database (for example, MP3 files) are no longer restricted by this limit.
The maximum possible length for a blob is 2147483647 bytes.

Streaming

The classes RSqlBlobReadStream and RSqlBlobWriteStream allow direct access to BLOB content. These classes do not require the whole BLOB to be loaded into RAM before streaming can begin. These classes match the standard Symbian RReadStream /RWriteStream idiom.

The server intelligently chooses the size of an internal buffer to incrementally read/write the requested blob data in blocks. The size of the buffer is calculated based on a combination of the size of the request and the value of aBlockSizeHint (if specified).

aBlockSizeHint is provided by the client to provide a hint to the server about the size of block that it is intending to use to read/write the blob data. This is advance information about the intended behaviour of the client that the server may use to its benefit to read/write the blob data.

Whole-blob access

The class TSqlBlob allows one-shot storage and retrieval of whole blobs. This class avoids the need for double buffering of the blob in both the client and the server.

there are two versions of the BLOB get functions. One is non-leaving and the other is leaving.

Use the non-leaving variant when the client knows that the blob will either:

all be of a fixed size,
not exceed an upper size limit.

In these situations the client can pre-allocate a buffer and avoid the overhead of memory allocation.

Use the leaving variant when the client does not have prior knowledge of the BLOB size (or if the size varies wildly). In this situation the server allocates a buffer and passes ownership to the client.

RAM benefits

Symbian SQL has been improved to eliminate the need for a 2 MB server-side buffer. Data is transferred to the client in blocks so for example, the server-side buffer may be reduced to 32 KB.

Previous to version 9.5, both whole-blob and streaming use cases require up to 3MB of server-side RAM. The page cache size is capped at 1MB due to a built-in limiting mechanism. But, a 2MB buffer must be allocated to hold the entire blob. A critical side-effect of this is that, due to heap exhaustion, there is a maximum limit on the size of a blob object that can be written to or read from a database.

In the following diagram the top two sections illustrate whole-blob retrieval in which the entire blob is retrieved in one go using RSqlStatement. The bottom section illustrates streaming retrieval in which the blob is retrieved in blocks specified by the client using RSqlColumnReadStream.