eXtremeSQL Character Data

The eXtremeSQL character data types provide fixed length and variable length storage options for arrays of single-byte (e.g. ascii) or double-byte (e.g. unicode) characters. The size of character arrays cannot exceed 64 Kb. For fields that must store more than 64 Kb of data, a BLOB can be used.

For an overview see page SQL Data Types

Fixed Length Character Fields

Fixed length character arrays must be declared with a specified length: such as CHAR(n), VARCHAR(n), LONGVARCHAR(n) or UNICODE(n). (Note that data type names are not case sensitive; i.e. CHAR and char are equivalent). For example, the following statement creates a table with two fixed length character fields:

 
    create table t( c char(30), u unicode(30) );
 

The amount of storage space allocated for fixed length character arrays is the specified number of bytes for CHAR(n) but two times the specified length for UNICODE(n). 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
    ----------------------------------------------------------------------
    c       30
    u       60
 

The storage for a single field cannot exceed 64 Kb (65,536 bytes). Some additional bytes of overhead are required for each database field. Thus, the maximum length n for a CHAR(n) declaration is 65527; and for UNICODE(n) the maximum value for n is 32763.

If character data longer than the fixed length of a CHAR(n) field is inserted, the value stored is truncated to the declared length. For example:

 
    create table t(c char(3), u unicode(3));
    insert into t values('1234567', '1234567');
     
    select * from t;
    c       u
    -------------------------------------------
    123    123
     

Variable Length Character Fields

Variable length character arrays are declared by not specifying a length for CHAR or UNICODE types or by declaring the field of type VARCHAR, LONGVARCHAR or STRING. In fact, LONGVARCHAR and STRING are effectively aliases for VARCHAR. All variable length character arrays are mapped internally to the eXtremeDB string (single-byte) and nstring (double-byte) types; the Metatable as FieldTypeName is String or Unicode. For example, in the following table definition, all of the field declarations are equivalent:

 
    create table t(c char, v varchar, lv longvarchar, s string);
     
    select FieldName, FieldTypeName, FieldSize from Metatable where TableName = 't';
    FieldName       FieldTypeName   FieldSize
    ------------------------------------------------------------------------------
    c       String  0
    v       String  0
    lv      String  0
    s       String  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.

(See page Base Data Types for further details.) And, as for fixed length UNICODE fields, variable length UNICODE fields store double-byte character arrays. Note the difference in the FieldSize in the following declaration:

 
    create table t2( u unicode(30), uv unicode);
     
    select FieldName, FieldTypeName, FieldSize from Metatable where TableName = 't2';
    FieldName       FieldTypeName   FieldSize
    ------------------------------------------------------------------------------
    u       Unicode 60
    uv      Unicode 0