What is Your Type?

What c-treeSQL data type is best for your data? c-treeSQL gives you a a full complement of data types to handle nearly any data storage need you require. From the smallest bit to the largest binary objects, c-treeSQL has you covered.

What c-treeSQL data types are available? Character types, exact and approximate numerical types, date and time types, and binary data types are all available. Remember too, that almost all of your existing ISAM application data types can be imported into a compatible c-treeSQL data type.

Converting from another vendor's SQL? Good choice! c-treeSQL accommodates all industry-standard data types. Even when there is not the specific data type you are accustomed to, c-treeSQL probably has a compatible type. Table 1 lists all of the c-treeSQL data types, along with the identifiers used in other vender implementations. Depending on the vendor you've used before, we can even offer some specific observations and ideas -- give us a call.

FairCom has a long history of working with many different varieties of data that a customer might require. Please contact your nearest FairCom office should you have any questions about which c-treeSQL type is best for your data storage needs.

Character Types

Use these c-treeSQL character types when specifying a string of characters, enclosed in single quotation marks (`).

• CHAR | CHARACTER
• VARCHAR | CHARACTER VARYING | CHAR VARYING
• LVARCHAR | LONG VARCHAR

The default length of any character field is 1. The maximum length of CHAR and VARCHAR fields are 2000 characters. Use a LVARCHAR for text fields requiring more than 2000 characters. The LVARCHAR field has different handling, however, and care should be taken when choosing this field; conditional expressions and functions cannot specify LVARCHAR columns.

Exact Numeric Types

Exact numeric types are used when specifying values that cannot change upon interpretation. Typically, these are integer values. Longer values with precision and scale can be specified with the NUMERIC type, including values specified in scientific notation. An important type of this category is the MONEY type.

• TINYINT
• SMALLINT
• INTEGER
• BIGINT
• NUMERIC | NUMBER | DECIMAL
• MONEY

Approximate Numeric Types

Approximate numeric types are used when performing arithmetic expressions on the data, such as addition, subtraction, multiplication, and division. Scalar functions such as SIN, COS, and SQRT require approximate data types.

• REAL
• DOUBLE PRECISION
• FLOAT

Date and Time Types

Date and times are handled with specific types for input and comparisons. Time can be specified up to the millisecond range, however, c-treeSQL only stores the time with hundreths of a second precision. Keep in mind the time resolution of your operating system, and hardware platform can influence accuracy.

The c-treeSQL TIMESTAMP is a concatenation of the DATE and TIME types.

• DATE
• TIME
• TIMESTAMP

Bit and Binary Data Types

Bit fields are a boolean type which can only contain the values of 0 or 1. Binary data types can contain arbitrary sequences of bytes, stored in binary form.

• BIT
• BINARY
• VARBINARY
• LVARBINARY

The default length of BINARY and VARBINARY fields is 1. The maximum length of BINARY and VARBINARY fields is 2000 bytes. Use the LVARBINARY type for fields requiring more than 2000 bytes. The LVARBINARY field has different handling, however, and care should be taken when choosing this field; conditional expressions and functions cannot specify LVARBINARY columns.

A comparison of c-treeSQL Types with other vendor implementations.