A BLOB <data type> is defined by a descriptor that contains two pieces of information:

  1. The <data type>'s name: BINARY LARGE OBJECT.
  2. The <data type>'s maximum length in octets.

BLOB

The required syntax for a BINARY LARGE OBJECT <data type> specification is:

BINARY LARGE OBJECT <data type> ::=
{ BINARY LARGE OBJECT | BLOB } [ (length) ]

BINARY LARGE OBJECT may be abbreviated as BLOB and is a variable length binary string, up to "length" octets long; it defines a set of binary string values that are any correctly sized string of octets that are not associated with a Character set or a Collation. For example, these <BLOB literal>s:

   X'49FE'

   X'178FA3A8'

are both valid values for this <data type> specification:

   BLOB(8)

The optional length, if specified, is an unsigned positive integer, possibly followed by a letter code (either K, M or G), that defines the maximum octet length of acceptable values in the BLOB field. The minimum length allowed is 1.

  • If the length n is not followed by a letter code, the BLOB may hold up to n octets.
  • The length may include the letter code "K" (kilobyte), "M" (megabyte), or "G" (gigabyte). If the length is defined as nK, the BLOB may hold up to "n*1024" octets. If the length is defined as "nM", the BLOB may hold up to "n*1,048,576" octets. If the length is defined as "nG", the BLOB may hold up to "n*1,073,741,824" octets.

For example, this <data type> specification defines a set of binary string values that may range from zero to 20 octets:

BLOB(20)

(Zero length binary strings can be stored in a BLOB field.)

This <data type> specification defines a set of binary string values that may range from zero to 2048 octets:

BLOB(2K)

This <data type> specification defines a set of binary string values that may range from zero to 2,097,152 octets:

BLOB(2M)

And this <data type> specification defines a set of binary string values that may range from zero to 2,147,483,648 octets:

BLOB(2G)

[NON-PORTABLE] The default length and the maximum length for BLOB are non-standard because the SQL Standard requires implementors to define BLOB's default and maximum lengths. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the length of BLOB to range from 1 to 32 and sets the default length of a BLOB <data type> to 1K. For example, these two <data type> specifications are equivalent: both define a set of binary string values that may range from zero to 1024 octets:

BLOB
BLOB(1K)

Now that we've described SQL's BLOB <data type>, let's look at some example SQL statements that put it to use.

These SQL statements make a Table with one binary string Column, insert a row, then search for any binary string equal to the bit string 01000100.

CREATE TABLE Binary_Examples (
      occurrence_binary BLOB(2K));

INSERT INTO Binary_Examples (
      occurrence_binary)
      VALUES (X'4D');

SELECT occurrence_binary,
FROM   Binary_Examples
WHERE  occurrence_binary = X'44';

Comments

Comments loading...