Sqlite BLOB Data Type and Internal & External BLOB

A BLOB (Binary Large OBject) is a collection of binary data that gets stored as a single entity in Sqlite. BLOBs are usually images, audios, and multimedia objects that are stored together in bulk. BLOB data in Sqlite, although, have only a limited support.In this write up, we will enlighten our users about Sqlite BLOB data type and also show the differences between internal and external BLOB.

Sqlite BLOB Data Type

In each row of data in Sqlite, only 1 MB of data can be stored. The columns in the database storage are divided by NUL bytes.To easily work with large tables, the Sqlite row size should be kept 250 bytes or above.

For each BLOB created in Sqlite table, the table is comprised of a type field and data field.

The syntax for creating a table in Sqlite is:

Create table mytable (…, blobtype, blobdata)

Each blobtype contains different blobdata. Refer to the below mentioned table to learn more about this:

A blob table consists of following columns:

Create table blobtable (key, serial, type, data)

The functions of different components are:

key: it is the reference key that is used with the reference type.

serial:it is used to generate order in the blob table

type:it is the plain text

data:it is encoded binary data

Difference Between Internal & External BLOBs IN Sqlite

Internal BLOBs gets stored inside a database tablespace such that the entire space is optimized and efficient access is provided. The internal BLOBs can be recovered in events such as media or transaction failure. All the changes made to an internal BLOB can be rolled back.

On the other hand, the external BLOBs are data objects that are stored outside the database tablespace. These files are used as a reference for the Sqlite database. In tis, each BLOB is stored in a separate file and only the filename to which itbelongs, is then storedin the database.

View BLOB Data With Sqlite Viewer

In order to view BLOB data separately in a much-organized way, the users can deploy third party tools such as Free Sqlite Viewer. The software gives the provision to view all the attributes like table, byte code, structure, etc. In addition, the user can also view items like images, videos present within the BLOB data, in order to completely analyze the BLOB data. It enables to thoroughly examine the entire BLOB data without any issues.