Data Types

The smallest unit of data is a value. A value can result from several different origins: a column, a constant, a function, an expression, a host variable, or a sub-query. CONNX interprets data based on its data type.

CONNX supports the following SQL data types:

Data Type	Maximum Precision or Length	Maximum Scale
Char	32000(*1)	N/A
Varchar	32000(*1)	N/A
Longvarchar	2 gigabytes	N/A
Nchar	32000(*1)	N/A
Varnchar	32000(*1)	N/A
Longvarnchar	2 gigabytes	N/A
Binary	32000(*1)
Varbinary	32000(*1)
Longvarbinary	2 gigabytes
Decimal	100(*1)	100(1)(2)
Numeric	100(*1)	100(1)(2)
Bit	1	N/A
Tinyint	3	N/A
Smallint	5	N/A
Integer	10	N/A
Bigint	20	N/A
Date	N/A	N/A
Time	N/A	N/A
Timestamp	N/A	10(*3)
Real	7	N/A
Double	15	N/A
Float	7 or 15 (*5)	N/A
Qfloat(*4)	100	N/A

(*1) The maximum precision or scale of the data source may be smaller that the maximum allowed by CONNX.

(*2) Scale must be less than or equal to precision.

(*3) Scale for timestamp refers to the number of digits allowed in the fractional component of the "seconds field". For example, a scale for 3 would permit a fractional component from .000 to .999 in the timestamp.

(*4) CONNX also supports Qfloat, an internal-only numeric data type. Qfloat is high precision (352bits) and used to perform many internal calculations. Some constants or mathematical expressions may be stored and processed in the Qfloat format, and then downgraded to the requested SQL data type at retrieval or execution time.

(*5) For Adabas, the Float data type will get remapped to either Double, or Real, depending on the precision supplied during create. If no precision is supplied, or the precision is greater than or equal to 21, then Double is used, otherwise, Real is used.

CONNX maps these SQL types to the most appropriate Native type for each data adapter.

All data types can be set to SQL NULL, which is not the same as a string of zero length or a numeric 0.

Variable-Length Character-String Data Types

SQL Data types VARCHAR, LONGVARCHAR, VARNCHAR, and LONGVARNCHAR are variable-length character-strings. These data types contain character sequences; the character-string length is determined by either the data type definition or from the value itself. The maximum length of the data type is derived from the definition of the data type; if the value originates from a column which has been defined as a variable-length character-strings with length 15, the maximum length is always 15, but the length of a particular value may be between zero and 15.

Fixed-Length Character String Data Types

SQL Data types CHAR and NCHAR are fixed-length character strings. The length of a value with the data type fixed length character-string is determined by the definition of the origin of the value; if the value originates from a column which has been defined as a fixed length character-string with length 15, a value originating from this column will always have a length of 15.

CHAR vs. NCHAR

CONNX supports two categories of character data types: CHAR (single byte), and NCHAR (multibyte/UNICODE).

CHAR: Single byte character types use a single "byte" (a value ranging from 0 to 255) to represent each character in the string. With Latin based alphabets for example, the CHAR data type is sufficiently large enough to represent all the characters in the alphabet.

NCHAR: For non-Latin languages such as Chinese and Japanese, there are more than 256 characters in the alphabet. The NCHAR data type, where every character is stored in two bytes (a value ranging from 0 to 65535) is used to represent all the characters in these alphabets. This is also referred to as utf-16, or UNICODE.

Numeric Data Types

Numeric data types are used to specify the representation form of numeric values. CONNX supports four different representation forms of numeric values:

Each numeric value's form of representation has a precision and a sign.

In addition, the decimal forms of representation have a scale. The scale of a numeric value is defined as the number of digits in the fractional part of the number. The scale cannot be larger than the precision nor can it be negative.

Data Type	Description
Bit	Specifies a binary representation of a numeric value with a precision of one bit. The value range of a bit number is 0 to 1.
Tiny Integer	Specifies a binary representation of a numeric value with a precision of seven bits. The value range of a tiny integer number is -128 to +127.
Small Integer	Specifies a binary representation of a numeric value with a precision of 15 bits. The value range of a small integer number is -32768 to +32767.
Integer	Specifies a binary representation of a numeric value with a precision of 31 bits. The value range of an integer number is -2147483648 to +2147483647.
Bigint	Specifies a binary representation of a numeric value with a precision of 63 bits. The value range of an integer number is -9223372036854775808 to +9223372036854775807.
Single Precision Floating Point	Specifies a floating point representation with single precision. The value range of a single precision floating point number depends on the hardware platform.
Double Precision Floating Point	Specifies a floating point representation with double precision. The value range of a double precision floating point number depends on the hardware platform.
Numeric	Specifies an unpacked decimal representation with a user- specified scale and precision. The range of the precision is between 1 - 100. The range of the scale is zero to the value of the precision.
Decimal	Specifies a packed decimal representation with a user- specified scale and precision. The range of the precision is between 1 - 100. The range of the scale is zero to the value of the precision.

The maximum precision/scale of a particular datasource may be much smaller than the CONNX maximum.

Binary Data Type

CONNX supports BINARY, VARBINARY and LONGVARBINARY binary data types. The data stored within the binary data type is not byte swapped nor interpreted in any way.

For Adabas, the binary data type behavior deviates from the ANSI standard. The Adabas binary data type is an unsigned integer and is subject to byte swapping where appropriate. Application programs can use multiple interpretations when accessing the Adabas binary data type; in most cases the Adabas binary data type will be interpreted as a bit pattern. Adabas Binary values have a maximum length of 126 bytes. The maximum possible number of bits is 1008.