Introduction
There are three data types that can be used in order to store large objects (LOBs) in a Mimer SQL database:
- BINARY LARGE OBJECT (BLOB)
- CHARACTER LARGE OBJECT (CLOB)
- NATIONAL CHARACTER LARGE OBJECT (NCLOB)
Description
A BLOB can be used to store all kinds of unformatted binary data, e.g. pictures, sounds or even video. CLOBs can be used for storing large documents or other large text objects. NCLOBs are used for storing large documents using Unicode character sets.
The BLOB/CLOB/NCLOB functionality in Mimer SQL is totally compliant with the SQL-2016 standard from ISO. The maximum size for a BLOB/CLOB/NCLOB data element when stored in a Mimer SQL 10 database is 8 TB. In version 11 can, depending on platform, even larger LOBs be stored. Usually the maximum length of a LOB is determined by the amount of disk space available for its storage.
The implementation of BLOB/CLOB/NCLOB support in Mimer SQL is optimized for extremely efficient access and storage and is totally integrated in the transaction management.
Function
The BLOB/CLOB/NCLOB data types stores binary string values, character string values and national character string values, respectively. The length can vary up to the maximum specified as the large object length (n[K|M|G]). The large object length is n, optionally multiplied by K|M|G.
LOBs can be used as follows:
- Retrieving LOBs with simple column references in the SELECT clause of a SELECT statement
- Assigning LOBs using INSERT statements with a VALUES clause
- Assigning LOBs using UPDATE statements
- Adding LOB columns using CREATE TABLE or ALTER TABLE
- Dropping LOB columns using ALTER TABLE
- Altering LOB column data types using ALTER TABLE
There are some restrictions associated with using LOBs. The only comparison supported for LOB values is using the NULL predicate. A LOB column may not be part of any primary key, index, unique constraint or primary key constraint.
The comparison restrictions also prevent LOB columns from being used in DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT, EXCEPT and DISTINCT FROM clauses.
Please note, you can currently not create a databank shadow, using the Mimer SQL shadowing functionality, on a databank that holds tables including LOB columns.
