SAND CDBMS SQL Reference Guide
SAND CDBMS SQL Database Objects and Language Elements

 

Previous Topic:
Dependencies Among Database Objects
Chapter Index
 Next Topic:
Data Type Compatibility

 

SAND CDBMS Data Types


All the data values represented in columns in a SAND database are actually stored in domains. All domains have an associated data type to which all values stored in the domain must conform. SAND CDBMS SQL supports a variety of different character string, numeric, and date/time data types, which are described here.

For information about the date, time, and timestamp interval data types, refer to the Other Topics: Date/Time Arithmetic chapter.


CHAR [ (length) ]
CHARACTER [ (length) ]

The character string data type declares a column or domain to hold fixed-length character strings.  The optional length parameter determines the maximum permissible length of character string data; it has a minimum value of 1 (also the default value) and a maximum value of 4056. Character strings must be enclosed by single quotation marks when referenced in SQL statements. If the character string assigned to a column has fewer characters than specified by the length parameter, the string value is padded with blanks until the length is reached.

Character strings may not contain interior ASCII nuls (0) or other "special" non-printing characters.


VARCHAR [ (maximum-length) ]
CHARACTER VARYING [ (maximum-length) ]
CHAR VARYING [ (maximum-length) ]

The variable-length character string data type declares a column or domain to hold variable-length character strings up to the length indicated by the maximum-length argument.  The maximum-length argument is optional.  If maximum-length is not specified, a default value of 4056 is assumed. The largest allowable length is 4056, while the smallest is 1. SAND CDBMS records a maximum string length for the domain as a whole, and maintains a string length for each individual string in the domain. If an input value contains any trailing spaces, they are removed when the value is stored in the column/domain.

As with the CHAR/CHARACTER data type, character strings must be surrounded by single quotation marks when referenced in SQL statements, and may not contain interior ASCII nuls (0) or other “special” non-printing characters.


DECIMAL [ ( precision [ , scale] ) ]
DEC [ ( precision [ , scale] ) ]
NUMERIC [ ( precision [ , scale] ) ]

The DECIMAL or DEC data type (equivalent to the NUMERIC data type) declares a column or domain to hold numeric values. The optional precision argument refers to the total number of digits of precision (both to the left and to the right of the decimal point). The precision value defaults to 5 decimal digits if not specified. The maximum value permitted for the precision argument is 50.  The scale argument, also optional, refers to the number of digits following the decimal point.  The default value for scale is 0.  If there is a scale specification, there must be a precision specification, and the scale value must be less than or equal to the precision value.  A precision argument may appear without a scale argument.


INTEGER
INT

The INTEGER data type declares a column or domain to hold signed integer values within the range of 4-byte binary integers, that is, -2,147,483,647 to 2,147,483,647.


SMALLINT

The SMALLINT data type declares a column or domain to hold signed or unsigned values within the range of 2-byte binary integers, that is, -32,767 to 32,767.


UNSIGNED

The UNSIGNED data type declares a column or domain to hold unsigned values within the range of 63-bit binary integers, that is, 0 to 9,223,372,036,854,775,805.


FLOAT [ ( precision ) ]

The FLOAT data type declares a column or domain to hold numeric floating point values where the precision argument (precision) is optional and is expressed as the number of significant decimal digits. If the precision argument is not specified, FLOAT uses a default value of 15. The minimum value permitted for the precision argument is 1; the maximum is 50.

Note that FLOAT(6) is equivalent to the REAL data type, while FLOAT(15) or FLOAT without a precision argument is equivalent to the DOUBLE PRECISION data type.


REAL

The REAL data type declares a column or domain to hold numeric floating point values having 6 digits of precision. 

Equivalent Expression: float(6)

DOUBLE PRECISION

The DOUBLE PRECISION data type declares a column or domain to hold numeric floating point values having precision of 15 digits.

Equivalent Expression: float(15)

DATE

The DATE data type declares a column or domain to hold a date value consisting of a three-part string, including year, month, and day. The ranges for each component are as follows:

year: 0001 - 9999
month: 1 - 12
day: 1 - x (where x = 28, 29, 30, or 31, depending on the month and year)

A date value is referenced in the same manner as character strings; that is, with enclosing single quotes. A date value must be entered using one of the following formats:

where mm is the month, dd the day, and yyyy the year.

Users may define their own date format (or picture) in the nucleus.ini file, which dictates how the date value will appear in query results. As well, date constants may be referenced in SQL statements using the defined date picture, in addition to the standard date formats. Refer to the Nucleus.ini Reference Page for more information on how to customize the date picture.


TIME

The TIME data type declares a column or domain to hold a time value consisting of a three-part string, including hours, minutes, and seconds. The ranges for each component are as follows:

hour: 0 - 23
minute: 0 - 59
second: 0 - 59

A time value is referenced in the same manner as character strings; that is, with enclosing single quotes. A time value must be entered using one of the following formats:

where hh is the hour, mm the minutes, and ss the seconds.

Users may define their own time format (or picture) in the nucleus.ini file, which dictates how the time value will appear in query results. Refer to the Nucleus.ini Reference Page for more information on how to customize the time picture.


TIMESTAMP

The TIMESTAMP data type declares a column or domain to hold a date and time value consisting of a seven-part string, including year, month, day, hour, minute, second, and microsecond. The ranges for timestamp components are as follows:

year: 0001 - 9999
month: 1 - 12
day: 1 - x (where x = 28, 29, 30, or 31, depending on the month and year)
hour: 0 - 23
minute: 0 - 59
second: 0 - 59
microsecond: 000000 - 999999

A timestamp value is referenced in the same manner as character strings; that is, with enclosing single quotes. The format for referencing and displaying timestamp values is yyyy-mm-dd-HH.MM.SS.NNNNNN, where yyyy is the year, mm the month, dd the day, HH the hour, MM the minutes, SS the seconds, and NNNNNN the microseconds. The microseconds portion may be omitted when entering a timestamp value into a table, in which case the microseconds part of the timestamp will be padded with zeroes.


BLOB

The BLOB (Binary Large OBject) data type declares a column or domain to hold binary values up to the following maximum sizes:

The BLOB data type is unique in that BLOB fields cannot be compared or grouped with any other fields or value expressions, even if they also consist of BLOB data. BLOB columns can be tested for null values with the IS [NOT] NULL Boolean predicate, but no other direct Boolean comparisons can be made. As well, the only functions that can be executed against a BLOB field are the COUNT functions and (if text analytics has been enabled for the BLOB domain) RELEVANCE().

Character strings can be inserted directly into BLOB fields if they are enclosed in single quotes. Binary data can be loaded into a BLOB field using ndlm. When using ndlm to load BLOBs that contain unprintable characters, include the -z 2 option in the loader invocation to instruct ndlm to accept all unprintable characters.

A BLOB column can be updated to a specified character string using a standard UPDATE command. Alternatively, it can be updated using the data contained in a text or binary file (up to 8 MB in size) through a special UPDATE...FROM 'file' command.

When using nisqlm to select BLOB fields, issue the .HEX command to set nisqlm to display BLOB data in hexadecimal format. Unprintable BLOB characters are output as periods (‘.’).

Note that if extended character sets are stored in BLOB fields, the ODBC Driver manager will not perform character conversion when retrieving information.

 

Previous Topic:
Dependencies Among Database Objects
Chapter Index
 Next Topic:
Data Type Compatibility