BLOB Data Type - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Purpose

Represents a large binary string of raw bytes. A binary large object (BLOB) column can store binary objects, such as graphics, video clips, files, and documents.

Syntax



n
The number of bytes to allocate for the BLOB column. The maximum number of bytes is 2097088000, which is the default if n is not specified.
K
n is specified in kilobytes (KB). When K is specified, n cannot exceed 2047937.
M
n is specified in megabytes (Mb). When M is specified, n cannot exceed 1999.
G
n is specified in gigabytes (GB). When G is specified, n must be 1.
attribute
Appropriate data type, column storage, or column constraint attributes.
A BLOB column supports the following attributes:
  • NOT NULL
  • FORMAT
  • TITLE
For details on NOT NULL, see Default Value Control Phrases. For details on FORMAT and TITLE, see Data Type Formats and Format Phrases.

ANSI Compliance

BLOB is ANSI SQL:2011 compliant.

External Representation

The following table lists the client representations for the Teradata SQL BLOB data type.

Define the length of the BLOB string as k, where 0 ≤ k ≤ n.

Client CPU Architecture Client Internal Data Format
IBM mainframe Eight bytes (16-bit SMALLINT) length count k followed by k bytes of BYTE data.
  • UTS
  • RISC
  • Motorola 68000
  • WE 32000
  • Intel
Eight bytes (16-bit SMALLINT) length count k followed by k bytes of BYTE data.

Restrictions

A table can have a maximum of 32 LOB columns.

A LOB column cannot be a component of an index. Because of this restriction, a table must define at least one non-LOB column.

Queue tables cannot have BLOB columns.

Functions That Operate on BLOBs

The following are some functions and operators that support BLOB types:
  • BYTES
  • Concatenation operator (||)
  • SUBSTRING/SUBSTR
  • TYPE
  • Explicit data type conversion (CAST and Teradata conversion syntax)
  • User-defined functions (UDFs)
  • Stored procedures
  • External stored procedures

Example: BLOB Data Type

The following example creates a table that defines a BLOB column named blarge:

CREATE TABLE t1
  (id INTEGER
  ,blarge BLOB(128K));

Example: Inserting BLOB Data

The following example shows a stored procedure that inserts part of a BLOB into one table and the remaining part of the BLOB into another table:

CREATE TABLE LocalData(ld_ID INTEGER, ld_DATA BLOB);
CREATE TABLE GlobalData (gd_ID INTEGER, gd_DATA BLOB);

CREATE PROCEDURE DataSplitter(IN local_ID  INTEGER,
                              IN global_ID INTEGER,
                              IN all_DATA  BLOB)

BEGIN

   INSERT LocalData (local_ID, SUBSTRING(all_DATA FROM 1 FOR 128546));
   INSERT GlobalData (global_ID, SUBSTRING(all_DATA FROM 128547));

END;

Related Topics

FOR information on … SEE …
functions and operators that support BLOB types Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.
implementing UDFs and external stored procedures that operate on BLOBs Teradata Vantage™ SQL External Routine Programming , B035-1147 .
implementing stored procedures that use BLOB local variables or parameters
  • “CREATE PROCEDURE” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 .