12 <concept xml:lang="en" id="GUID-4EE4E79E-F6D5-5F14-BA8D-4DD10D229B74"><title>Handling BLOBs</title><prolog><metadata><keywords/></metadata></prolog><conbody><p>This document introduces you to Binary Large Objects (BLOB) in Symbian SQL. </p> <section><title>Purpose</title> <p>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. </p> </section> <section><title>Features of SQL BLOB</title> <p>BLOBS offer the following features: </p> <ul><li id="GUID-50CE608F-04BA-5D0F-BA59-E608F4B10989"><p>read from and write to BLOB objects in a RAM-efficient manner -- enables an overall reduction of RAM usage, </p> </li> <li id="GUID-60702664-4ABF-5F90-9DDE-C77969C0DC0B"><p>reduced read latency (compared to previous versions) -- improving the responsiveness of client applications, </p> </li> <li id="GUID-72DAFF78-CF0D-50F0-8C29-BF95920D2CA0"><p>larger BLOB objects can be stored and retrieved (&gt;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. </p> </li> <li id="GUID-D66B611B-8908-59B1-A7BA-BED5D6702509"><p>The maximum possible length for a blob is 2147483647 bytes. </p> </li> </ul> </section> <section><title>Streaming</title> <p>The classes <xref href="GUID-03426E55-3C0A-322B-9CE7-556D63FD762F.dita"><apiname>RSqlBlobReadStream</apiname></xref> and <xref href="GUID-9D7747B6-E090-3450-9906-845B549F966F.dita"><apiname>RSqlBlobWriteStream</apiname></xref> 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 <xref href="GUID-81056EAD-722C-373F-A8CF-4CC67A5B4468.dita"><apiname>RReadStream</apiname></xref> /<xref href="GUID-ADAC48A2-3D24-306B-9473-70201F4D351D.dita"><apiname>RWriteStream</apiname></xref> idiom. </p> <p>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 <codeph>aBlockSizeHint</codeph> (if specified). </p> <p> <codeph>aBlockSizeHint</codeph> 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. </p> </section> <section><title>Whole-blob access</title> <p>The class <xref href="GUID-78544D11-1972-3492-9BD7-D794240895E1.dita"><apiname>TSqlBlob</apiname></xref> 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. </p> <p>there are two versions of the BLOB get functions. One is <i>non-leaving</i> and the other is <i>leaving</i>. </p> <p>Use the non-leaving variant when the client knows that the blob will either: </p> <ul><li id="GUID-557D1A55-1E94-56F6-8794-1257ED5D2E17"><p>all be of a fixed size, </p> </li> <li id="GUID-7C5CA733-C030-5761-A2C0-5EAA7A3B50FD"><p>not exceed an upper size limit. </p> </li> </ul> <p>In these situations the client can pre-allocate a buffer and avoid the overhead of memory allocation. </p> <p>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. </p> </section> <section><title>RAM benefits</title> <p>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. </p> <p>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. </p> <p>In the following diagram the top two sections illustrate whole-blob retrieval in which the entire blob is retrieved in one go using <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref>. The bottom section illustrates streaming retrieval in which the blob is retrieved in blocks specified by the client using <xref href="GUID-35BF7B4A-C4F7-3215-B5DF-6D0682247976.dita"><apiname>RSqlColumnReadStream</apiname></xref>. </p> <fig id="GUID-60AEFF09-235C-55F3-BBEF-D484964A4A82"><title>

    12 <concept id="GUID-4EE4E79E-F6D5-5F14-BA8D-4DD10D229B74" xml:lang="en"><title>Handling BLOBs</title><prolog><metadata><keywords/></metadata></prolog><conbody>

    13              Reading a 2MB blob. 

    13 <p>This document introduces you to Binary Large Objects (BLOB) in

    14           </title> <image href="GUID-739556F5-A2F3-5548-943C-7D50785DFB48_d0e360653_href.png" placement="inline"/></fig> </section> </conbody><related-links><link href="GUID-5C3005B3-7008-5209-A2BD-D52B8B422D58.dita"><linktext>SQL Security

    14 Symbian SQL. </p>

    15                 Policies</linktext> </link> <link href="GUID-CB33868A-139C-5976-B021-709E663FA21B.dita"><linktext>Database file

    15 <section id="GUID-EAAD8374-EFB5-496A-993E-84D310F7CDFE"><title>Purpose</title> <p>A BLOB is an SQL database container

    16                 format</linktext> </link> <link href="GUID-DA1D327C-141C-524A-AD57-5E246745808D.dita"><linktext>SQLite

    16 object for binary large objects, usually images or audio files but

    17                 Modules</linktext> </link> <link href="GUID-A64625D4-F101-5DB7-896B-F91A21BABE01.dita"><linktext>Free Space

    17 may also include other binary files such as an application executable. </p> </section>

    18                 Reclaimation</linktext> </link> <link><linktext/></link><link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> </link> </related-links></concept>

    18 <section id="GUID-A53CDAF5-D5A4-4230-90DA-9F942F26CED3"><title>Features of SQL BLOB</title> <p>BLOBS offer the following

    19 features: </p> <ul>

    20 <li id="GUID-50CE608F-04BA-5D0F-BA59-E608F4B10989"><p>read from and

    21 write to BLOB objects in a RAM-efficient manner -- enables an overall

    22 reduction of RAM usage, </p> </li>

    23 <li id="GUID-60702664-4ABF-5F90-9DDE-C77969C0DC0B"><p>reduced read

    24 latency (compared to previous versions) -- improving the responsiveness

    25 of client applications, </p> </li>

    26 <li id="GUID-72DAFF78-CF0D-50F0-8C29-BF95920D2CA0"><p>larger BLOB

    27 objects can be stored and retrieved (>16Mb). Before version 9.5 there

    28 was a limit on the size of a BLOB object supported by Symbian SQL.

    29 This limit was dictated by the amount of server-side RAM available.

    30 Applications that needed to store large BLOB objects in their database

    31 (for example, MP3 files) are no longer restricted by this limit. </p> </li>

    32 <li id="GUID-D66B611B-8908-59B1-A7BA-BED5D6702509"><p>The maximum

    33 possible length for a blob is 2147483647 bytes. </p> </li>

    34 </ul> </section>

    35 <section id="GUID-E2F1B6E4-D146-4467-AE17-169C55579012"><title>Streaming</title> <p>The classes <xref href="GUID-03426E55-3C0A-322B-9CE7-556D63FD762F.dita"><apiname>RSqlBlobReadStream</apiname></xref> and <xref href="GUID-9D7747B6-E090-3450-9906-845B549F966F.dita"><apiname>RSqlBlobWriteStream</apiname></xref> allow direct access to

    36 BLOB content. These classes do not require the whole BLOB to be loaded

    37 into RAM before streaming can begin. These classes match the standard

    38 Symbian <xref href="GUID-81056EAD-722C-373F-A8CF-4CC67A5B4468.dita"><apiname>RReadStream</apiname></xref> /<xref href="GUID-ADAC48A2-3D24-306B-9473-70201F4D351D.dita"><apiname>RWriteStream</apiname></xref> idiom. </p> <p>The server intelligently chooses the size of an internal

    39 buffer to incrementally read/write the requested blob data in blocks.

    40 The size of the buffer is calculated based on a combination of the

    41 size of the request and the value of <codeph>aBlockSizeHint</codeph> (if specified). </p> <p> <codeph>aBlockSizeHint</codeph> is provided

    42 by the client to provide a hint to the server about the size of block

    43 that it is intending to use to read/write the blob data. This is advance

    44 information about the intended behaviour of the client that the server

    45 may use to its benefit to read/write the blob data. </p> </section>

    46 <section id="GUID-1DBAF5CA-1FD4-4D94-BD28-B00E8049C7DC"><title>Whole-blob access</title> <p>The class <xref href="GUID-78544D11-1972-3492-9BD7-D794240895E1.dita"><apiname>TSqlBlob</apiname></xref> allows one-shot storage and retrieval of whole blobs. This class

    47 avoids the need for double buffering of the blob in both the client

    48 and the server. </p> <p>there are two versions of the BLOB get functions.

    49 One is <i>non-leaving</i> and the other is <i>leaving</i>. </p> <p>Use the non-leaving variant when the client knows that the blob will

    50 either: </p> <ul>

    51 <li id="GUID-557D1A55-1E94-56F6-8794-1257ED5D2E17"><p>all be of a

    52 fixed size, </p> </li>

    53 <li id="GUID-7C5CA733-C030-5761-A2C0-5EAA7A3B50FD"><p>not exceed an

    54 upper size limit. </p> </li>

    55 </ul> <p>In these situations the client can pre-allocate a buffer

    56 and avoid the overhead of memory allocation. </p> <p>Use the leaving

    57 variant when the client does not have prior knowledge of the BLOB

    58 size (or if the size varies wildly). In this situation the server

    59 allocates a buffer and passes ownership to the client. </p> </section>

    60 <section id="GUID-DEA6CB21-B0E7-4CCD-A0FD-44C979352009"><title>RAM benefits</title> <p>Symbian SQL has been improved

    61 to eliminate the need for a 2 MB server-side buffer. Data is transferred

    62 to the client in blocks so for example, the server-side buffer may

    63 be reduced to 32 KB. </p> <p>Previous to version 9.5, both whole-blob

    64 and streaming use cases require up to 3MB of server-side RAM. The

    65 page cache size is capped at 1MB due to a built-in limiting mechanism.

    66 But, a 2MB buffer must be allocated to hold the entire blob. A critical

    67 side-effect of this is that, due to heap exhaustion, there is a maximum

    68 limit on the size of a blob object that can be written to or read

    69 from a database. </p> <p>In the following diagram the top two sections

    70 illustrate whole-blob retrieval in which the entire blob is retrieved

    71 in one go using <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref>. The bottom section

    72 illustrates streaming retrieval in which the blob is retrieved in

    73 blocks specified by the client using <xref href="GUID-35BF7B4A-C4F7-3215-B5DF-6D0682247976.dita"><apiname>RSqlColumnReadStream</apiname></xref>. </p> <fig id="GUID-60AEFF09-235C-55F3-BBEF-D484964A4A82">

    74 <title>              Reading a 2MB blob.            </title>

    75 <image href="GUID-739556F5-A2F3-5548-943C-7D50785DFB48_d0e389657_href.png" placement="inline"/>

    76 </fig> </section>

    77 </conbody><related-links>

    78 <link href="GUID-5C3005B3-7008-5209-A2BD-D52B8B422D58.dita"><linktext>SQL

    79 Security  Policies</linktext></link>

    80 <link href="GUID-CB33868A-139C-5976-B021-709E663FA21B.dita"><linktext>Database

    81 file  format</linktext></link>

    82 <link href="GUID-DA1D327C-141C-524A-AD57-5E246745808D.dita"><linktext>SQLite

    83  Modules</linktext></link>

    84 <link href="GUID-A64625D4-F101-5DB7-896B-F91A21BABE01.dita"><linktext> Reclaiming

    85 Free Space </linktext></link>

    86 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL

    87 Overview</linktext></link>

    88 </related-links></concept>