Blob Database Storage (Embedded SQL Guide)
Go Up to How are Blob Data Stored?
Because Blob data are typically large, variably-sized objects of binary or text data, InterBase stores them most efficiently using a method of segmentation. It would be an inefficient use of disk space to store each Blob as one contiguous mass. Instead, InterBase stores each Blob in segments that are indexed by a handle that InterBase generates when you create the Blob. This handle is known as the Blob ID and is a quadword (64-bit) containing a unique combination of table identifier and Blob identifier.
The Blob ID for each Blob is stored in its appropriate field in the table record. The Blob ID points to the first segment of the Blob, or to a page of pointers, each of which points to a segment of one or more Blob fields. You can retrieve the Blob ID by executing a SELECT statement that specifies the Blob as the target, as in the following example:
EXEC SQL DECLARE BLOBDESC CURSOR FOR SELECT GUIDEBOOK FROM TOURISM WHERE STATE = 'CA';
You define Blob columns the same way you define non-Blob columns.
The following SQL code creates a table with a Blob column called PROJ_DESC. It sets the sub-type parameter to 1, which denotes a TEXT Blob, and sets the segment size to 80 bytes:
CREATE TABLE PROJECT ( PROJ_ID PROJNO NOT NULL, PROJ_NAME VARCHAR(20) NOT NULL UNIQUE, PROJ_DESC BLOB SUB_TYPE 1 SEGMENT SIZE 80, TEAM_LEADER EMPNO, PRODUCT PRODTYPE, ... );
The following diagram shows the relationship between a Blob column containing a Blob ID and the Blob data referenced by the Blob ID:
Rather than store Blob data directly in the table, InterBase stores a Blob ID in each row of the table. The Blob ID, a unique number, points to the first segment of the Blob data that is stored elsewhere in the database, in a series of segments. When an application creates a Blob, it must write data to that Blob one segment at a time. Similarly, when an application reads of Blob, it reads a segment at a time. Because most Blob data are large objects, most Blob management is performed with loops in the application code.