You create BLOB tables using the statement CREATE TABLE OF BLOB. These tables are used to store BLOB objects. SESAM/SQL basically handles BLOB tables in the same way as other SQL tables. They are displayed as “normal” base tables in the information schemas. There are no differences from other SQL tables in terms of view and index definitions.
The BLOB values of the BLOB object are saved slice-by-slice in multiple table rows when SESAM-CLI is called. To do this, SESAM/SQL saves slices of 31000 bytes at a time. This value was chosen to make optimum use of the bandwidth during the transfer of the BLOB value between the SQL client and SQL server.
SESAM/SQL defines the structure of the BLOB table at creation time. Users cannot therefore perform any column definitions at this point.
A BLOB table consists of the following columns:
The OBJ_NR column is of data type INTEGER and contains the serial number of the BLOB within the table.
The column SLICE_NUM is of data type INTEGER and contains the sequential number of the BLOB value slice.
The SLICE_VAL column is of type VARCHAR(31000). It contains the individual components of the BLOB value. The entries as of slice number 1 contain the value in slices of 31000 bytes in size. Obviously, the last segment may be shorter than this. The row containing slice number 0 is used to store administrative information on the BLOB. The default values for this column are the user-defined attributes in the OF BLOB clause such as MIME and USAGE. In addition, the defaults contain the attributes CREATED and UPDATED. This attributes specify the date on which the BLOB was created and last updated.
The OBJ_REF column is of type CHAR(237). In the row containing slice number 0, it specifies the REF value of the BLOB. For all other slices, the column value is NULL. The default value for the column is the REF value for the table class. The column is defined with the UNIQUE constraint.
The OBJ_NR and SLICE_NR columns together form the primary key of a BLOB table. As usual, for this primary key constraint, SESAM/SQL assigns internally generated numbers which cannot be re-used within the same schema.
It is possible to append columns using ALTER TABLE. ALTER TABLE also allows you to modify BLOB tables. However, such changes may result in it being impossible for the BLOB table to be accessed by means of CLI calls since the CLI calls are adapted to the structure that is predefined by SESAM/SQL.
BLOB tables can be deleted with DROP TABLE. This call also deletes all the contained BLOB objects.