This appendix describes the data types and data type mappings offered by Zen through the MicroKernel and relational engines.

The following table maps the transactional and relational data types supported by Zen. It is useful for developers of SQL applications that access data in Btrieve data files.

The following table lists the value ranges for the Zen data types and their increments where appropriate.

Expressions may have multiple operators, which are performed in order of precedence. Zen uses the following order, with level 1 highest and level 9 lowest. A higher operator is evaluated before a lower one.

In an expression, operators with equal precedence are evaluated left to right. For example, in SET :Counter = 12 / 4 * 7, the division is evaluated before the multiplication to return a result of 21.

You can use parentheses to override the precedence of operators in an expression. Everything within the parentheses is evaluated first to yield a value that is then used by an operator outside of the parentheses. For example, in the following statement, the division operator would ordinarily be evaluated before the addition operator. The result would be 12 (that is, 8 + 4). However, the addition is performed first because of the parentheses, so the procedure returns a result of 4.

If an expression has nested parentheses, the deepest nested expression is evaluated first, followed by the next deepest, and so on. For example, in the following statement, the addition is performed first, then the multiplication, then the subtraction, and finally the division. The result is a value of 5.

Data type precedence determines the result when two expressions of different types are combined by an operator. The data type with lower precedence is converted to the data type with higher precedence.

Relational numeric data types use the following precedence:

Relational character data types use the following precedence:

If you concatenate an NCHAR or NVARCHAR with a NLONGVARCHAR, the result is an NLONGVARCHAR.

If you concatenate an NCHAR with a LONGVARCHAR, the result is an NLONGVARCHAR.

If you concatenate a CHAR or VARCHAR with a LONGVARCHAR, the result is a LONGVARCHAR.

If you concatenate a CHAR with a VARCHAR, the result is the type of the first data type in the concatenation, moving left to right. For example, if c1 is a CHAR and c2 is a VARCHAR, the result of (c1 + c2) is a CHAR. The result of (c2 + c1) is a VARCHAR.

The BINARY, LONGVARBINARY, and UNIQUEIDENTIFIER data types have no precedence because operations to combine them are not allowed.

No date and time data type may be combined with any other date and time data type.

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. The number 909.777 has a precision of 6 and a scale of 3, for instance.

The maximum precision of NUMERIC, NUMERICSA, and DECIMAL data types is 64. The maximum precision of NUMERICSTS and NUMERICSLS is 63 because it reserves one byte for the plus or minus sign.

Precision and scale are fixed for all numeric data types except DECIMAL. An arithmetic operation on two expressions of the same data type results in the same data type, with the precision and scale for that type. If the operation involves expressions with different data types, the precedence rules determine the data type of the result. The result has the precision and scale defined for its data type.

The result is a DECIMAL for operations under the following conditions:

Table 114 defines how precision and scale are derived when the result of an operation is of data type DECIMAL. Exp stands for expression, s stands for scale, and p stands for precision.

For example, if you add or subtract two fields defined as DECIMAL(8,2) and DECIMAL(7,4), the resulting field is DECIMAL(11,4).

In time stamp data types, scale is the number of digits to the right of the decimal point in the fractional second part of the time stamp. For instance, 2019-12-31 23:59:59.782 has a scale of 3, or milliseconds.

Starting in Zen 14.10, you can choose the scale for the TIMESTAMP and TIMESTAMP2 data types. For example, the following SQL script creates a table with four columns, the first two using TIMESTAMP and TIMESTAMP2 with default scale, and the second two setting the scale to one decimal point:

The SELECT statement returns the following row:

Note that shortening the scale does not round fractional seconds.

The following table lists Zen data types that support date and time stamps with scale.

The following table lists Zen scalar functions that return date and time stamp values with scale.

If the time stamp returned by a function has smaller scale than the data type of the column to which it is written, then trailing decimal places are filled with zeros. For example, the value 2019-12-10 14:23:46.292000000 is returned by CURRENT_TIMESTAMP() in a TIMESTAMP2 column.

If your application runs against different SQL DBMS products, you may encounter the following issues pertaining to truncation.

In certain situations, some SQL DBMS products prevent insertion of data because of truncation, while Zen allows the insertion of that same data. Additionally, reporting of SQL_SUCCESS_WITH_INFO and the information being truncated differs in Zen from some SQL DMBS products in certain scenarios based on when the message is reported.

Numeric string data and true numeric data are always truncated by Zen, whereas other SQL DBMS products round the data. For example, if you have a numeric string or true numeric value of 123.457 and you insert it into a 6-byte string column or precision 2 numeric column, Zen always inserts 123.45. Other DBMS products, by comparison, may insert a value of 123.46.

This topic covers various behaviors and key information regarding the available data types.

See also Limitations on LONGVARCHAR, NLONGVARCHAR and LONGVARBINARY.

Zen supports multiple LONGVARCHAR and LONGVARBINARY columns per table. The data is stored according to the offset in the variable length portion of the record. The variable length portion of data can vary from the column order of the data depending on how the data is manipulated. Consider the following example:

On disk, the physical record would normally look like this:

Now alter column Nbr to a LONGVARCHAR column:

On disk, the physical record now looks like this:

As you can see, the variable length portion of the data is not in the column order for the existing data.

For newly inserted records, however, the variable length portion of the data is in the column order for the existing data. This assumes that all columns have data assigned (the columns are not NULL).

See also Limitations on LONGVARCHAR, NLONGVARCHAR and LONGVARBINARY.

The following limitations apply to the LONGVARCHAR and LONGVARBINARY data types:

The DATETIME data type represents a date and time value. This type is stored internally as two 4-byte integers. The first four bytes store the number of days before or after the base date of January 1, 1900. The other four bytes store the time of day, represented as the number of milliseconds after midnight.

The DATETIME data type can be indexed. The accuracy of DATETIME is 1 millisecond.

DATETIME is a relational data type only. No corresponding Btrieve data type is available.

The format for DATETIME is YYYY-MM-DD HH:MM:SS.mmm. If you need to truncate the millisecond portion, the CONVERT function offers parameter to do so. The following table gives the data components and their range of values for DATETIME.

If you need to perform addition or subtraction involving date and time data types, we recommend using the scalar functions TIMESTAMPADD(), DATEADD(), TIMESTAMPDIFF(), and DATEDIFF(). The use of these functions is required if your expression includes the newer AUTOTIMESTAMP and TIMESTAMP2 data types. Other data types can in some cases be used directly in expressions with operators. For example, the following statements are valid:

Some queries may return "incompatible types" or "error in expression" messages if you try to add or subtract values that are not compatible, or if the result would not be valid. For example, the following statements return such errors:

The CONVERT and CAST functions can be used with DATE, DATETIME, TIME, and TIMESTAMP in the ways shown in the following tables.

The UNIQUEIDENTIFIER data type is a 16-byte binary value known as a globally unique identifier (GUID). A GUID is useful when a row must be unique among other rows.

UNIQUEIDENTIFIER requires a file format of 9.5 or higher.

You can initialize a column or local variable of UNIQUEIDENTIFIER the following ways:

If you provide a quoted string, all 32 digits are required. The database engine does not pad a partial string.

You may use only the following comparison operators with UNIQUEIDENTIFIER values:

Note that ordering is not implemented by comparing the bit patterns of the two values.

You may declare variables of the UNIQUEIDENTIFIER data type and set the variable value with the SET statement.

The UNIQUEIDENTIFER can be converted with the CAST or CONVERT scalar functions to any of the following data types:

For conversion examples, see Conversion Functions.

When Zen is required by an application to represent infinity, it can do so in either a 4-byte (C float type) or 8-byte (C double type) form, and in either a hexadecimal or character representation, as shown in the following table.

Some older (legacy) data types are not supported in the current release of Zen. The following table shows the new data type to use in place of the legacy data type.

Existing databases that use these data types are supported and function correctly. New databases cannot create tables with the legacy data types. However, a table with legacy data types can be added to the DDFs for a new database with the IN DICTIONARY clause. See CREATE TABLE.

This topic discusses the Btrieve data types that can be indexed (key types). Internally, the MicroKernel compares string keys on a byte-by-byte basis, from left to right. By default, the MicroKernel sorts string keys according to their ASCII value. You may, however, define string keys to be case insensitive or to use an alternate collating sequence (ACS).

The MicroKernel compares unsigned binary keys one word at a time. It compares these keys from right to left because the Intel 8086 family of processors reverses the high and low bytes in an integer.

If a particular data type is available in more than one size (for example, both 4- and 8-byte FLOAT values are allowed), the Key Length parameter (used in the creation of a new key) defines the size that will be expected for all values of that particular key. Any attempt to define a key using a key length that is not allowed results in a status 29 (Invalid Key Length).

The following table lists the key types and their associated codes. Following the table is a discussion of the internal storage formats for each key type.

The AUTOINCREMENT key type is a signed Intel integer that can be either 2, 4, or 8 bytes long. Internally, autoincrement keys are stored in Intel binary integer format, with the high-order and low-order bytes reversed within a word. The MicroKernel sorts autoincrement keys by their absolute (positive) values, comparing the values stored in different records a word at a time from right to left. Autoincrement keys may be used to automatically assign the next highest value when a record is inserted into a file. Because the values are sorted by absolute value, the number of possible records is roughly half what you would expect given that the data type is signed.

Values that have been deleted from the file are not reused automatically. If you indicate that you want the database engine to assign the next value by entering a zero (0) value in an insert or update, the database simply finds the highest number, adds 1, and inserts the resulting value.

You can initialize the value of a field in all or some records to zero and later add an index of type AUTOINCREMENT. This feature allows you to prepare for an autoincrement key without actually building the index until it is needed.

When you add the index, the MicroKernel changes the zero values in each field appropriately, beginning its numbering with a value equal to the greatest value currently defined in the field, plus one. If nonzero values exist in the field, the MicroKernel does not alter them. However, the MicroKernel returns an error status code if nonzero duplicate values exist in the field.

The MicroKernel maintains the highest previously used autoincrement value associated with each open file containing an autoincrement key. This value is established and increments only when an INSERT operation occurs for a record with ASCII zeros in the autoincrement field. The value is used by all clients so that concurrent changes can take place, taking advantage of key page concurrency.

The next autoincrement value for a file is raised whenever any INSERT occurs that uses the previous autoincrement value. This happens whether or not the INSERT is in a transaction or the change is committed.

However, this value may be lowered during an INSERT if all of the following are true:

In other words, only the first INSERT within a transaction can lower the next available autoincrement value. After that, the next available autoincrement value simply keeps incrementing.

An example helps clarify how an autoincrement value may be lowered. Assume an autoincrement file exists with records 1, 2, 3, and 4. The next available autoincrement value is 5.

Client1 begins a transaction and inserts two new records, raising the next available autoincrement value to 7. (Client1 gets values 5 and 6). Client2 begins a transaction and also inserts two new records. This raises the next available autoincrement value to 9. (Client 2 gets values 7 and 8).

Client1 the deletes records 4, 5, and 6. The next autoincrement value remains the same, since it is adjusted only on INSERT. Client1 then commits. The committed version of the file now contains records 1, 2, and 3.

For Client2, the file contains records 1, 2, 3, 7, and 8 (7 and 8 are not yet committed). Client2 then inserts another record, which becomes record 9. The next available autoincrement value is raised to 10. Client2 deletes records 3, 7, 8, and 9. For Client2, the file now contains only the committed records 1 and 2.

Next Client2 inserts another record, which becomes record 10. The next available autoincrement value is raised to 11. The next autoincrement value is not lowered to 3 since the page containing the change has other changes pending on it.

Client2 then aborts the transaction. The committed version of the file now contains records 1, 2, and 3, but the next available autoincrement value is still 11.

If either client inserts another record, whether or not in a transaction, the next available autoincrement value is lowered to 4. This occurs because all of the conditions required for lowering the value are true.

If a resulting autoincrement value is out of range, a Status Code 5 results. The database engine does not attempt to “wrap” the values and start again from zero. You may, however, insert unused values directly if you know of gaps in the autoincrement sequence where a previously inserted value has been deleted.

The following restrictions apply to keys of type AUTOINCREMENT:

The MicroKernel treats autoincrement key values as follows when you insert records into a file:

The AUTOTIMESTAMP key type is an 8-byte unsigned integer for tracking time in nanoseconds based on the Unix epoch. A value of zero prompts the database engine to replace it automatically with the current time when a new record is inserted or the first time an existing record is updated. A nonzero value is allowed and is interpreted as a number of nanoseconds since 1970 UTC.

This key type is available starting in Zen v14 for file formats 9.5 and 13.0. Older database engines that attempt to open a file that has a record that uses this type will return status code 30 for an unrecognized Microkernel file.

The range of AUTOTIMESTAMP values is 1970-01-01 00:00:00.000000000 to 2554-07-21 23:34:33.709551615.

Current Linux and Android system clocks provide true nanosecond resolution. On Windows the highest resolution is septaseconds (10-7 second), and on macOS the highest is microseconds. When an AUTOTIMESTAMP key is written on systems that do not support nanosecond resolution, the value is padded with zeros. Accordingly, inserts or updates on these systems without nanosecond resolution can result in duplicate values. However, if the index is set to be unique and the database engine detects a match with the most recent previously generated time stamp, then it adds 1 nanosecond. Duplication can also result from a manually inserted time stamp value or from the resetting of the system clock. In both of these cases, Insert (2) or Update (3) fails with status code 5.

Insert (2) and Update (3) operations handle a zero value for an AUTOTIMESTAMP key by retrieving the current time stamp from the system clock on the database engine server. The engine then uses this value in the current record for every AUTOTIMESTAMP key that contains a zero.

For Insert Extended (40), the engine retrieves a new time stamp value for each specified record in the operation. If the AUTOTIMESTAMP key is unique, the engine avoids time stamp duplication among the records by incrementing the generated value by 1 nanosecond if it matches a previously generated time stamp within the operation. As with Insert (2), the time stamp generated for each record is used for all AUTOTIMESTAMP keys in that record.

When an Update Chunk (53) operation attempts to write to the fixed portion of a record that includes an AUTOTIMESTAMP key, the engine does not retrieve a new time stamp. Instead, the provided key value is accepted as is and placed in the record. Therefore, using an Update Chunk operation with a zero value to update an AUTOTIMESTAMP key results in storing the zero value in the record, which is then interpreted as 1970 UTC. If you wish to update the key with an automatically generated time stamp, use the Update (3) operation to update the fixed portion of a record.

The following restrictions apply when you create a key of type AUTOTIMESTAMP:

The use of AUTOTIMESTAMP keys in Function Executor or the Maintenance tool is similar to AUTOINCREMENT keys. For the files that use them, the key type is listed as Atstamp, which also appears in the output of butil <filename> -stat and is used for the key type in the description file for a butil -create command.

The representation of a double-precision real number is the same as that for a single-precision real number, except that the mantissa is 55 bits instead of 23 bits. The least significant 32 bits are stored in bytes 0 through 3.

The BFLOAT type is commonly used in legacy BASIC applications. Microsoft refers to this data type as MBF (Microsoft Binary Format), and no longer supports this type in the Visual Basic environment. New database definitions should use FLOAT rather than BFLOAT.

The STRING key type is a sequence of characters ordered from left to right. Each character is represented in ASCII format in a single byte, except when the MicroKernel is determining whether a key value is null. STRING data is expected to be padded with blanks to the full size of the key.

The CURRENCY key type represents an 8-byte signed quantity, sorted and stored in Intel binary integer format. Therefore, its internal representation is the same as an 8-byte INTEGER data type. The CURRENCY data type has an implied four digit scale of decimal places, which represents the fractional component of the currency data value.

An example of C structure used for date fields would be:

The year portion of a date field is expected to be set to the integer representation of the entire year. For example, 2,001 for the year 2001.

The DECIMAL key type is stored internally as a packed decimal number with two decimal digits per byte. The internal representation for an n-byte DECIMAL field is as follows:

The decimal point for DECIMAL is implied. No decimal point is stored in the DECIMAL field. Your application is responsible for tracking the location of the decimal point for the value in a DECIMAL field. All values for a DECIMAL key type must have the same number of decimal places for the database engine to collate the key correctly. The DECIMAL type is commonly used in COBOL applications.

An eight-byte decimal can hold 15 digits plus the sign. A ten-byte decimal can hold 19 digits plus the sign. The decimal value is expected to be left-padded with zeros.

The sign nibble is either 0xF or 0xC for positive numbers and 0xD for negative numbers. By default, the Relational Engine and the SDK access methods that use it always write 0xF as the positive sign nibble for a DECIMAL. They can interpret both 0xF and 0xC as being positive on a read operation.

A setting in the registry (Windows Registry and Zen Registry) controls what the database engine uses for the positive sign nibble for a DECIMAL. If you need to change the default positive sign nibble to 0xC, edit the registry as explained below. Note that the Zen ActiveX access method always uses 0xF for the positive sign nibble regardless of the registry setting for CommonCOBOLDecimalSign.

In Registry Editor, change the value of CommonCOBOLDecimalSign to yes for the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Actian\Zen\SQL Relational Engine

In most Windows systems, the key is under HKEY_LOCAL_MACHINE\SOFTWARE\Actian\Zen. However, its location below HKEY_LOCAL_MACHINE\SOFTWARE can vary depending on the operating system.

For Linux 32-bit operating systems, run the psregedit utility as follows:

For Linux and macOS 64-bit operating systems, run the psregedit utility as follows:

See also psregedit in Zen User’s Guide.

The FLOAT key type is consistent with the IEEE standard for single and double-precision real numbers. The internal format for a 4-byte FLOAT consists of a 23-bit mantissa, an 8-bit exponent biased by 127, and a sign bit, as follows:

A FLOAT key with 8 bytes has a 52-bit mantissa, an 11-bit exponent biased by 1023, and a sign bit. The internal format is as follows:

The GUID key type is a 16-byte number that is stored internally as a 16-byte binary value. Its extended data type value is 27.

GUIDs are commonly used as globally unique identifiers. The corresponding data type for the Relational Engine is UNIQUEIDENTIFIER.

Note that GUID requires a file format of 9.5 or higher.

The sort order for the bytes composing the GUID are compared in the following sequence: 10, 11, 12, 13, 14, 15, 8, 9, 6, 7, 4, 5, 0, 1, 2, 3.

The key segment length for a GUID must be 16 bytes. See Key Specification Block in Btrieve API Guide.

The INTEGER key type is a signed whole number and can contain any number of digits. Internally, INTEGER fields are stored in Intel binary integer format, with the high-order and low-order bytes reversed within a word. The MicroKernel evaluates the key from right to left. The sign must be stored in the high bit of the rightmost byte. The INTEGER type is supported by most development environments.

The LOGICAL key type is stored as a 1 or 2-byte value. The MicroKernel collates LOGICAL key types as strings. Doing so allows your application to determine the stored values that represent true or false.

The LSTRING key type has the same characteristics as a regular STRING type, except that the first byte of the string contains the binary representation of the string length. The LSTRING key type is limited to a maximum size of 255 bytes. The length stored in byte 0 of an LSTRING key determines the number of significant bytes. The database engine ignores any values beyond the specified length of the string when sorting or searching for values. The LSTRING type is commonly used in legacy Pascal applications.

The MONEY key type has the same internal representation as the DECIMAL type, with an implied two decimal places.

Each digit of a NUMERIC key type occupies one byte. NUMERIC values are stored as ASCII strings right-aligned with leading zeros. The rightmost byte includes an embedded sign with an EBCDIC value. By default, the sign value for positive NUMERIC data types is an unsigned numeric number.

Optionally, you may specify that you want to shift the value of the sign for positive NUMERIC data types. The following table compares the sign values in the default (unshifted) and shifted states.

You must manually specify a setting on the machine running the Zen database engine to enable the shifted format. The setting DBCobolNumeric must be set to yes. The rest of this topic summarizes use of this setting on Windows 32-bit, Linux, and macOS platforms.

Using the Registry Editor, add the DBCobolNumeric setting as a string value to the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Actian\Zen\Database Names

Set the string value for DBCobolNumeric to yes.

In most Windows systems, the key is HKEY_LOCAL_MACHINE\SOFTWARE\Actian\Zen, but its location under HKEY_LOCAL_MACHINE\SOFTWARE varies depending on the operating system.

Stop and restart the database engine or the engine services.

Add the DBCobolNumeric setting to bti.ini below the [Database Names] entry:

By default, bti.ini is located in the /usr/local/actianzen/etc directory.

Stop and restart the database engine.

You may already have positive NUMERIC data with the sign value in the default (unshifted) format. If you set DBCobolNumeric to yes and continue adding data to the same table, mixed formats result. Leaving your data with mixed formats for the sign value is not recommended.

To correct or prevent a condition of mixed formats, use the UPDATE statement to update NUMERIC columns to themselves. For example, suppose that table t1 contains column c1 that is a NUMERIC data type. After you set DBCobolNumeric to yes, update c1 as follows: UPDATE TABLE t1 SET c1 = c1.

The NUMERICSA key type (sometimes called NUMERIC SIGNED ASCII) is a COBOL data type identical to NUMERIC, except that the embedded sign has an ASCII instead of an EBCDIC value.

The NUMERICSLB key type (sometimes called SIGN LEADING with COBOL compiler option -dcb) is a COBOL data type that has values resembling those of the NUMERIC data type. NUMERICSLB values are stored as ASCII strings and right justified with leading zeros.

The NUMERICSLS key type (sometimes called SIGN LEADING SEPARATE) is a COBOL data type that has values resembling those of the NUMERIC data type. NUMERICSLS values are stored as ASCII strings and left justified with leading zeros. However, the leftmost byte of a NUMERICSLS string is either “+” (ASCII 0x2B) or “-” (ASCII 0x2D). This differs from NUMERIC values that embed the sign in the rightmost byte along with the value of that byte.

The NUMERICSTB key type (sometimes called SIGN TRAILING with COBOL compiler option -dcb) is a COBOL data type that has values resembling those of the NUMERIC data type. NUMERICSTB values are stored as ASCII strings and right justified with leading zeros.

The NUMERICSTS key type (sometimes called SIGN TRAILING SEPARATE) is a COBOL data type that has values resembling those of the NUMERIC data type. NUMERICSTS values are stored as ASCII strings and right justified with leading zeros. However, the rightmost byte of a NUMERICSTS string is either “+” (ASCII 0x2B) or “-” (ASCII 0x2D). This differs from NUMERIC values that embed the sign in the rightmost byte along with the value of that byte.

The TIMESTAMP key type represents a time and date value. In SQL applications, use this data type to stamp a record with the current time and date of the last update to the record. TIMESTAMP values are stored in 8-byte unsigned values representing septaseconds (10^-7 second) since January 1, 0001 in a Gregorian calendar, Coordinated Universal Time (UTC). Supported values range from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999.

Unlike AUTOTIMESTAMP, a value of zero is not automatically replaced with the current time when a new record is inserted or the first time an existing record is updated.

TIMESTAMP supports time and data values made up of the following components: year, month, day, hour, minute, second, and millisecond. The following table indicates the range of valid values for each of these components.

Each TIMESTAMP value contains a complete date and time value with the maximum scale supported by the local operating system, filled if needed with trailing zeros. When this value is returned, it uses the scale set for the time stamp. For example, the value is returned in milliseconds when the scale is 3 and microseconds when it is 6.

For more information about scale for date and time data types, see Scale of Time Stamp Data Types and Returned Function Values.

You provide the value of a TIMESTAMP in local time and the Relational Engine converts it to Coordinated Universal Time (UTC) before storing it in a record. When you request a TIMESTAMP value, the Relational Engine returns it converted back to local time.

Because time stamp data is converted to UTC before it is stored, the TIMESTAMP type is inappropriate for use with local time and local date data that reference events external to the database itself, particularly in time zones where seasonal time changes take place, such as Daylight Savings Time in the United States.

For example, assume it is October 15, and you enter a time stamp value to track an appointment on November 15 at 10 a.m. Assume you are in the U. S. Central Time Zone. When the Relational Engine stores the value, it converts it to UTC using current local time information (UTC-5 hours for CDT). So it stores the hour value 15. Assume, on November 1, you check the time of your appointment. Your computer is now in Standard Time, because of the switch that occurred in October, so the conversion is (UTC-6 hours). When you extract the appointment time, it will show 9 a.m. local time (15 UTC - 6 CST), which is not the correct appointment time.

The same type of issue will occur if a database engine is moved from one time zone to another.

Because the Relational Engine does not convert DATE and TIME values to UTC, you should almost always use DATE and TIME columns to record external data. The only reason to use a TIMESTAMP column is a need for the specific ability to determine the sequential time order of records entered into the database.

The use of TIMESTAMP keys in Function Executor or the Maintenance tools is similar to AUTOINCREMENT keys. For the files that use them, the key type is listed as Tstamp, which also appears in the output of butil <filename> -stat and is used for the key type in the description file for a butil -create command.

The TIMESTAMP2 key type tracks time in nanoseconds based on the Unix epoch. In SQL applications, use this data type to stamp a record with the current time and date of the last update to the record. Values are stored in 8-byte unsigned values representing nanoseconds (10^-9 second) since January 1, 1970 in a Gregorian calendar, Coordinated Universal Time (UTC). Supported values range from 1970-01-01 00:00:00.000000000 to 2554-07-21 23:34:33.709551615.

Unlike AUTOTIMESTAMP, a value of zero is not automatically replaced with the current time when a new record is inserted or the first time an existing record is updated.

This key type is available starting in Zen v14 SP1 for file formats 9.5 and 13.0. Older database engines that attempt to open a file that has a record that uses this type will return status code 30 for an unrecognized Microkernel file.

TIMESTAMP2 supports time and data values made up of the following components: year, month, day, hour, minute, second, and nanosecond. The following table indicates the range of valid values for each of these components.

Each TIMESTAMP2 value contains a complete date and time value with the maximum scale supported by the local operating system, filled if needed with trailing zeros. When this value is returned, it uses the scale set for the time stamp. For example, the value is returned in milliseconds when the scale is 3 and microseconds when it is 6.

For more information about scale for date and time data types, see Scale of Time Stamp Data Types and Returned Function Values.

You provide the value of a TIMESTAMP2 in local time and the Relational Engine converts it to Coordinated Universal Time (UTC) before storing it in a record. When you request a TIMESTAMP2 value, the Relational Engine returns it converted back to local time.

Because time stamp data is converted to UTC before it is stored, the TIMESTAMP2 type is inappropriate for use with local time and local date data that reference events external to the database itself, particularly in time zones where seasonal time changes take place (such as Daylight Savings Time in the United States).

For example, assume it is October 15, and you enter a time stamp value to track an appointment on November 15 at 10 a.m. Assume you are in the U. S. Central Time Zone. When the Relational Engine stores the value, it converts it to UTC using current local time information (UTC-5 hours for CDT). So it stores the hour value 15. Assume, on November 1, you check the time of your appointment. Your computer is now in Standard Time, because of the switch that occurred in October, so the conversion is (UTC-6 hours). When you extract the appointment time, it will show 9 a.m. local time (15 UTC - 6 CST), which is not the correct appointment time.

The same type of issue will occur if a database engine is moved from one time zone to another.

Because the Relational Engine does not convert DATE and TIME values to UTC, you should almost always use DATE and TIME columns to record external data. The only reason to use a TIMESTAMP2 column is a need for the specific ability to determine the sequential time order of records entered into the database.

The use of TIMESTAMP2 keys in Function Executor or the Maintenance tools is similar to AUTOINCREMENT keys. For the files that use them, the key type is listed as TS2, which also appears in the output of butil <filename> -stat and is used for the key type in the description file for a butil -create command.

UNSIGNED BINARY keys can be any number of bytes up to the maximum key length of 255. UNSIGNED keys are compared byte-for-byte from the most significant byte to the least significant byte. The first byte of the key is the least significant byte. The last byte of the key is the most significant.

The database engine sorts UNSIGNED BINARY keys as unsigned INTEGER keys. The differences are that an INTEGER has a sign bit, while an UNSIGNED BINARY type does not, and an UNSIGNED BINARY key can be longer than 4 bytes.

WSTRING is a Unicode string that is not null-terminated. The length of the string is determined by the field length.

WZSTRING is a Unicode string that is double null-terminated. The length of this string is determined by the position of the Unicode NULL (two null bytes) within the field. This corresponds to the ZSTRING type supported in Btrieve.

The ZSTRING key type corresponds to a C string. It has the same characteristics as a regular string type except that a ZSTRING type is terminated by a binary 0. The MicroKernel ignores any values beyond the first binary 0 it encounters in the ZSTRING, except when the MicroKernel is determining whether a key value is null.

The maximum length of a ZSTRING type is 255 bytes, including the null terminator character. If used as a key for a nullable column, only the first 254 bytes of the string are used in the key. This minor limitation occurs because the key is limited to 255 bytes total length, and one byte is occupied by the null indicator for the column, leaving only 254 bytes for the key value.

This topic discusses the internal storage formats of data types that cannot be indexed (used as Btrieve keys).

The Binary Large Object (BLOB) type provides support for binary data fields up to 2 GB in size. This type consists of 2 parts:

For additional information, see BINARY and LONGVARBINARY and Limitations on LONGVARCHAR, NLONGVARCHAR and LONGVARBINARY.

The Character Large Object (CLOB) type provides support for character data fields up to 2 GB in size. This type consists of 2 parts:

For additional information, see CHAR, NCHAR, VARCHAR, NVARCHAR, LONGVARCHAR, and NLONGVARCHAR and Limitations on LONGVARCHAR, NLONGVARCHAR and LONGVARBINARY.