Skip to content
≡

Administrator

Developer

Tools

External Tools

LOB Data Type

LOB Data Type#

Overview#

The LOB (which stands for Large Object) data type is for holding large amounts of data. Up to 4GB-1byte can be stored in one column of LOB data. Unlike other data types, the length of a LOB column does not need to be specified when a table is created. Additionally, more than one LOB type column can be defined in a table.

The LOB data type is divided into the Binary Large Object (BLOB) type, which is for holding binary data such as image and video files, and the Character Large Object (CLOB) type, which is for holding string data.

The Features of LOB#

The LOB data type provided with Altibase has the following features:

  • Data Storage Functions
  • Partial Read
  • Disk LOB Partitioning

Features of LOB#

The LOB data type provided with Altibase has the following features:

  • Data Storage Functions
  • Partial Read
  • Disk LOB Partitioning

Data Storage Functionality#

CLOB and BLOB data can be stored using the SQLPutLob function in ODBC or the setBlob and setClob methods in JDBC.

Partial Read#

It is possible to read only a desired portion of LOB data. A specific amount of data, offset a specific distance from the beginning of the file, can be read using the SQLGetLob function in Altibase ODBC.

Disk LOB Partitioning#

Disk LOB data can be stored in a disk tablespace other than the one in which the table is stored. This tablespace can be configured in a method similar to partitioning.

Storing LOB Columns#

In most cases, LOB data are stored in a variable area, away from the rest of the record. However, if a small amount of data is stored in the LOB column, the column can be stored in an area that is contiguous with the rest of the record (i.e. in the fixed area) using the 'in row' option. Note that this is possible for memory tables only; regardless of their size, LOB data in disk tables are always stored in a separate, variable area.

Because the amount of LOB column data that is stored in the variable area is typically very large, storing it in the same tablespace as the rest of the record has a negative impact on the efficiency of usage of space.

In a disk table, LOB column data can be stored in a tablespace other than the one containing the table to which the LOB column belongs. However, in a memory table, LOB column data cannot be stored separately, and thus are stored in the same tablespace as the table.

BLOB#

Syntax Diagram#

Syntax#

BLOB [ VARIABLE ( IN ROW size ) ]

Description#

BLOB is a binary data type that can vary in length up to 4GB-1byte and is intended for use in storing large amounts of binary data.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW clauses" sections above.

CLOB#

Syntax Diagram#

Syntax#

CLOB [ VARIABLE ( IN ROW size ) ]

Description#

CLOB is a character data type that can vary in length up to 4GB-1byte and is intended for use in storing large amounts of character data.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW clauses" sections above.

Restriction#

  • LOB type columns can't be used with cursors.
  • LOB type columns can't be used in volatile tables or disk temporary tablespaces.
  • LOB columns associated with tables in discarded tablespaces cannot be accessed.
  • LOB type columns cannot be used for partitioning conditions, because in order to partition a column it must be possible to perform comparisons on the data in the column.
  • Indexes cannot be created for LOB columns.
  • It is possible to define a NOT NULL constraint for a LOB type column. However, when an insert attempt is made, a constraint violation error may be raised while the Altibase server is internally processing the data. Therefore, it is recommended that the NOT NULL constraint not be used with LOB type columns.