Data types

Types of data for FairCom DB table fields

A field type is the data type of a field in a table. In a FairCom server, data is stored in fields, records, tables, and databases. A record contains one or more fields. A table contains zero or more records. Each field has a predefined field type.

A FairCom server provides strongly and flexibly typed fields that can be freely mixed into any table. Strongly typed fields make queries predictable and fast.

The most common strongly typed fields are NUMBER, INTEGER, VARCHAR, LVARBINARY, BIT, DATE, TIME, and TIMESTAMP. The remaining strongly typed fields have specialty purposes.

A FairCom server also supports JSON fields, which may flexibly contain any JSON type.

A FairCom table may have up to 2500 fields, and zero or more of those fields may have a JSON type. Each field (or group of fields) may be indexed by one or more indexes, with a maximum of 500 indexed fields. One or more individual properties inside a JSON field may also be indexed.

The data types listed in Table 1, “Field types” are available in JSON DB API. All FairCom’s SQL types are supported. Each field in a table and each property in JSON must be one of the listed field types. Each field type may be defined as nullable or non-nullable. The JSON DB API provides response options that return numeric and binary field types as JSON strings.

Table 1. Field types

SQL field type	JSON DB API field type	Description	Length	Scale	ISAM type number	ISAM type name	JSON type
BIGINT	"bigint"	`BIGINT` is an 8-byte, signed integer number with a range of -9,223,372,036,854,770,000 to 9,223,372,036,854,770,000.			231	CT_INT8	integer or string
BINARY	"binary"	`BINARY` is a fixed-length series of bytes with `0x00` padding. Its length property defines the fixed number of bytes that it contains. Because the value does not contain a two- or four-byte length header, it is efficient for storing small binary values that vary little in length.	Required: 1 to 65,500		128	CT_ARRAY	array of bytes or a string containing Base64 or Hex
BIT	"bit"	`BIT` is a Boolean value of `0` or `1`. It is always stored as 1 byte. Consider using TINYINT to store a larger range of values in the same space.			8	CT_BOOL	`false` for 0 and `true` for 1
CHAR	"char"	`CHAR` is a fixed-length UTF-8 string with padding. Its length property defines the fixed number of bytes (not characters) that it contains. Because the value does not contain a two- or four-byte length header, it is efficient for storing small strings that vary little in length.	Required: 1 to 65,500		144	CT_FSTRING	string
DATE	"date"	`DATE` is a specific day, month, and year stored as a 4-byte structure.			75	CT_DATE	string
FLOAT `DOUBLE`	"float"	`FLOAT` is an 8-byte, IEEE 754, base-two floating point number. DOUBLE is a synonym for FLOAT. It is an approximate base-two number. Use NUMBER for a precise base-ten number.			103	CT_DFLOAT	number or string
INTEGER	"integer"	`INTEGER` is a 4-byte, signed integer number with a range of -2,147,483,648 to 2,147,483,647.			51	CT_INT4	integer or string
JSON	"json"	`JSON` is a variable-length UTF-8 string that can contain any valid JSON value. You can optionally specify a maximum length between 1 and 65,500 or omit it to store up to 2 GB.	Optional: 1 to 65,500		259	CT_JSON	Any JSON value
LVARBINARY	"lvarbinary"	`LVARBINARY` is a variable-length series of bytes up to 2 GB in size. Each value has a four-byte header specifying its length, followed by its binary contents. It is useful for large binary values that vary greatly in length.			170	CT_4STRING	array of bytes or a string containing Base64 or Hex
LVARCHAR	"lvarchar"	`LVARCHAR` is a variable-length UTF-8 string up to 2 GB in size. Each value has a four-byte header specifying its length, followed by its contents. It is useful for large strings that vary greatly in length.			170	CT_4STRING	string
MONEY	"money"	`MONEY` is a 19-byte, signed, base-ten number with 32 digits of precision and a scale specified as 2 or 4. The fixed number of digits and scale are specified when the field is created.		Optional: 2 or 4; defaults to 4	105	CT_NUMBER	number or string
NUMBER `NUMERIC` `DECIMAL`	"number"	`NUMBER` is a 19-byte, signed, fixed-point, base-ten number with 32 digits of precision and user-defined length and scale. You may create it to store up to 32 digits to the right of the decimal point. You may also limit the number of digits to the left of the decimal point by reducing its length, but FairCom does not recommend it because it does not reduce the storage size. NUMERIC and DECIMAL are synonyms for NUMBER.	Optional: 1 to 32	Optional: 1 to 32; defaults to 0	105	CT_NUMBER	number or string
REAL	"real"	`REAL` is a 4-byte, IEEE 754, base-two floating point number. It is an approximate base-two number. Use NUMBER for a precise base-ten number.			91	CT_SFLOAT	number or string
SMALLINT	"smallint"	`SMALLINT` is a 2-byte, signed integer number with a range of -32,768 to 32,767.			33	CT_INT2	integer or string
TIME	"time"	`TIME` is a specific millisecond in a day stored as a 4-byte structure.			258	CT_TIME_MS	string
TIMESTAMP	"timestamp"	`TIMESTAMP` combines a `DATE` and `TIME`, stored as an 8-byte structure.			257	CT_TIMES_MS	string
TINYINT	"tinyint"	`TINYINT` is a 1-byte, signed integer number with a range of -128 to 127.			16	CT_CHAR	integer or string
VARBINARY	"varbinary"	`VARBINARY` is a variable-length series of bytes. You can set its length to the maximum number of bytes that it may contain. Because each value has a two-byte header specifying its length, it is efficient for storing large binary values that vary in length.	Required: 1 to 65,500		162	CT_2STRING	array of bytes or a string containing Base64 or Hex
VARCHAR	"varchar"	`VARCHAR` is a variable-length UTF-8 string. Its maximum length is specified when the field is created. You can set its length to the maximum number of bytes (not characters) that it may contain. Because each value has a two-byte header specifying its length, it is efficient for storing large strings that vary in length.	Required: 1 to 65,500		162	CT_2STRING	string
VARIANT	"variant"	`VARIANT` is a variable-length field up to 2 GB in length that may contain any type of value, including user-defined types. Each value has a header containing four bytes for its length and four bytes for its type.				CT_VARIANT	JSON

The driver for each language has specific constants for field types:

Essential information

FairCom’s NUMBER and MONEY types allow values that can exceed the numeric limits of JavaScript and some JSON parsers. Thus, the JSON DB API provides the option to embed the numeric values of number types, such as INTEGER, NUMBER, and MONEY, in a JSON string. This ensures JavaScript and JSON parsers have no problems parsing JSON containing extra-large numbers.
The JSON DB API automatically converts CHAR fields to and from JSON's variable-length strings. This makes CHAR fields behave like variable-length strings. You can include spaces when you want to pad its value.
SQL pads fixed-length strings with the space character. If the number of characters in a JSON string is less than the fixed length, the database adds a space character to the end of the string until it fills the fixed length. The following SQL query demonstrates this padding by returning quotes surrounding the value of a fixed-length field named "mychar".
```
SELECT '"' || mychar || '"' from mytable;
```
All variable-length fields must be assigned a maximum length. FairCom's indexes and SQL use this length.
- If a value exceeds the length, the operation returns an error.
- You can set a VARCHAR string to its maximum possible length of 65,500 bytes as long as you do not index the field. Notice that the maximum length is bytes, not characters, because a UTF-8 character may contain multiple bytes.
FairCom indexes the maximum length of strings; thus, do not make a string larger than necessary.
- The index copies a variable-length string into a fixed-length buffer that is the maximum length of the string. The buffer is initialized with spaces. Then, the index uses run-length encoding to compress repeated characters.
- This can cause side effects when a string contains trailing spaces:
  - Keys are identical when the only difference is the number of trailing spaces.
  - The number of trailing spaces has no impact on key comparisons.
  - A key being added to a unique index will fail if the only difference between it and an existing key is the number of trailing spaces.
In the JSON DB API, all binary values are represented as a string encoded as a base64 or hexadecimal value.
- When receiving a property containing a binary value, the JSON DB API converts the base64 or hex encoded value into a binary value and stores it in the field as binary data.
- When returning data, it removes the binary value from a field, encodes it as a base64 or hex string, and puts it in the JSON property.
  Note
  Encoding a binary value into a variable-length JSON string creates a string that is typically 1.3 to 2 times larger than the field's binary length. This is expected and is not an issue for JSON or the JSON DB API.

Data types

Types of data for FairCom DB table fields

Essential information

Note

Data types for the JSON DB API

data types

JSON

JSON DB

JSON DB API

FairCom DB

field types

DB field types

table types

table field

table field types