eXtremeSQL Binary Data

eXtremeSQL provides fixed length BINARY<n> and variable length VARBINARY or BLOB storage options for arrays of bytes containing character or non-character data.

For an overview see page SQL Data Types

As explained in page Binary Fields, binary data is stored in eXtremeDB fields of type blob, binary<n> or varbinary. For binary data of sizes less than 64 Kb, CHAR<n>, VARCHAR, LONGVARCHAR or STRING declarations can be used. However, binary data stored in BINARY and VARBINARY fields is sorted differently from character data which is compared character-to-character according to the defined collation. Instead, binary fields are sorted by the numeric value of the actual data in the byte array.

For fields that must store more than 64 Kb of data, a BLOB declaration is recommended. However, an index cannot include BLOB fields.

Fixed Size Binary Fields

Fixed size binary arrays must be declared with a specified length: such as BINARY(n), VARBINARY(n). For example, the following statement creates a table with two fixed length binary fields:

 
    create table t( b binary(30), vb varbinary(30) );
 

The amount of storage space allocated for fixed size binary arrays is the specified number of bytes. For example, the following Metatable query shows the actual storage allocation for the declaration above:

 
    select FieldName, FieldSize from Metatable where TableName = 't';
    FieldName       FieldSize
    ----------------------------------------------------------------------
    b       30
    vb      30
 

Variable Size Binary Fields

Variable size binary fields are declared by not specifying a length in the BINARY or VARBINARY declaration. BLOB fields do not allow a size specification. For example, in the following table definition, all of the field declarations are equivalent (except that the BLOB field is not limited to 64 Kb and cannot be indexed):

 
    create table t(b binary, vb varbinary, bl blob);
     
    select FieldName, FieldTypeName, FieldSize from Metatable where TableName = 't';
    FieldName       FieldTypeName   FieldSize
    ------------------------------------------------------------------------------
    b       tpBinary        0
    vb      tpBinary        0
    bl      Blob    0
     

Note that the FieldSize of 0 indicates that no storage is allocated for variable length fields (apart from the 2 or 4 byte address of the variable sized storage) until values are actually inserted.