| Prev | SQL Engine Reference | Next |
Btrieve Key Data Types
This section 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.
AUTOINC
The AUTOINC key type is a signed Intel integer that can be either two or four bytes long. Internally, AUTOINC keys are stored in Intel binary integer format, with the high-order and low-order bytes reversed within a word. The MicroKernel sorts AUTOINC keys by their absolute (positive) values, comparing the values stored in different records a word at a time from right to left. AUTOINC 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 datatype is signed.
Values that have been deleted from the file are not re-used 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 AUTOINC. This feature allows you to prepare for an AUTOINC 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 auto-increment value associated with each open file containing an AUTOINC key. This value is established and increments only when an INSERT operation occurs for a record with ASCII zeros in the AUTOINC field. The value is used by all clients so that concurrent changes can take place, taking advantage of Key Page Concurrency.
The next AUTOINC value for a file is raised whenever any INSERT occurs that uses the previous AUTOINC 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:
- The highest AUTOINC value found in the key is lower than the next AUTOINC value for the file
- No other client has a pending transaction affecting the page that contains the highest AUTOINC value
- The key page containing the highest AUTOINC value is not already pending by the client doing the INSERT.
In other words, only the first INSERT within a transaction can lower the next available AUTOINC value. After that, the next available AUTOINC value just keeps increasing.
An example helps clarify how an AUTOINC value may be lowered. Assume an autoincrement file exists with records 1, 2, 3 and 4. The next available AUTOINC value is 5.
Client1 begins a transaction and inserts two new records, raising the next available AUTOINC value to 7. (Client1 gets values 5 and 6). Client2 begins a transaction and also inserts two new records. This raises the next available AUTOINC value to 9. (Client 2 gets values 7 and 8).
Client1 the deletes records 4, 5, and 6. The next AUTOINC value remains the same since it is adjusted only on INSERTS. 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. Client2 then inserts another record, which becomes record 9. The next available AUTOINC 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 AUTOINC 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 AUTOINC value is still 11.
If either client inserts another record, whether in a transaction or not, the next available AUTOINC value will be lowered to 4. This occurs because all of the conditions required for lowering the value are true.
If a resulting auto-increment 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.
Restrictions
The following restrictions apply to AUTOINC keys:
- The key must be defined as unique.
- The key cannot be segmented. However, an AUTOINC key can be included as an integer segment of another key, as long as the AUTOINC key has been defined as a separate, single key first, and the AUTOINC key number is lower than the segmented key number.
- The key cannot overlap another key.
- All keys must be ascending.
The MicroKernel treats AUTOINC key values as follows when you insert records into a file:
- If you specify a value of binary 0 for the AUTOINC key, the MicroKernel assigns a value to the key based on the following criteria:
- If you specify a nonzero value for the AUTOINC key, the MicroKernel inserts the record into the file and uses the specified value as the key value. If a record containing that value already exists in the file, the MicroKernel returns an error status code, and does not insert the record.
It is a good idea not to insert negative numbers directly into a table using IDENTITY, because any attempt by the SRDE to optimize on the index will return unexpected results.
BFLOAT
The BFLOAT key type is a single or double-precision real number. A single-precision real number is stored with a 23-bit mantissa, an 8-bit exponent biased by 128, and a sign bit. The internal layout for a 4-byte float is as follows:
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.
BLOB
The Binary Large Object (BLOB) type provides support for binary data fields up to 2 GB in size. This type consists of 2 parts:
- an 8-byte header in the fixed-length portion of the record. The header contains a 4-byte integer that identifies the beginning of the data in the variable-length portion of the record, and a 4-byte integer that specifies the size of the field.
- the binary data itself within the variable-length portion of the record. The size of all BLOB and CLOB fields must sum to 2 GB or less, because the offset pointer into the variable-length portion of the record is limited to 2 GB maximum offset. To store the maximum BLOB size of 2 GB, you may have only 1 BLOB or CLOB field defined in the record.
For additional information, see BINARY and LONGVARBINARY and Limitations on LONGVARCHAR and LONGVARBINARY .
CHAR
Note
In previous versions of Pervasive.SQL, this data type was referred to as STRING
The CHAR 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.
CLOB
The Character Large Object (CLOB) type provides support for character data fields up to 2 GB in size. This type consists of 2 parts:
- an 8-byte header in the fixed-length portion of the record. The header contains a 4-byte integer that identifies the beginning of the data in the variable-length portion of the record, and a 4-byte integer that specifies the size of the field.
- the character data itself within the variable-length portion of the record. The size of all BLOB and CLOB fields must sum to 2 GB or less, because the offset pointer into the variable-length portion of the record is limited to 2 GB maximum offset. To store the maximum BLOB size of 2 GB, you may have only 1 BLOB or CLOB field defined in the record.
For additional information, see CHAR, VARCHAR, and LONGVARCHAR and Limitations on LONGVARCHAR and LONGVARBINARY .
CURRENCY
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.
DATE
The DATE key type is stored internally as a 4-byte value. The day and the month are each stored in 1-byte binary format. The year is a 2-byte binary number that represents the entire year value. The MicroKernel places the day into the first byte, the month into the second byte, and the year into a two-byte word following the month.
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.
DECIMAL
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 sign nibble is either 0xF or 0xC for positive numbers and 0xD for negative numbers. Pervasive ODBC and Pervasive ActiveX controls always use 0xF for the positive sign nibble. The decimal point 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 the values for a DECIMAL key type must have the same number of decimal places in order for the MicroKernel 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.
FLOAT
Caution
Precision beyond that supported by the C-language definitions for the FLOAT (4-byte) or DOUBLE (8-byte) data type will be lost. If you require precision to many decimal points, consider using the DECIMAL type.
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:
INTEGER
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.
Table A-6 INTEGER Key TypeLOGICAL
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.
LSTRING
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's 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 MicroKernel ignores any values beyond the specified length of the string when sorting values. The LSTRING type is commonly used in Pascal applications.
MONEY
The MONEY key type has the same internal representation as the DECIMAL type, with an implied two decimal places.
NUMERIC
NUMERIC values are stored as ASCII strings, right justified with leading zeros. Each digit occupies one byte internally. The rightmost byte of the number includes an embedded sign with an EBCDIC value. Table A-7 indicates how the rightmost digit is represented when it contains an embedded sign for positive and negative numbers.
Table A-7 Rightmost Digit with Embedded SignFor positive numbers, the rightmost digit can be represented by 1 through 0 instead of A through {. The MicroKernel processes positive numbers represented either way. The NUMERIC type is commonly used in COBOL applications.
NUMERICSA
The NUMERICSA key type (sometimes called NUMERIC SIGNED ASCII) is a COBOL data type that is the same as the NUMERIC data type, except that the embedded sign has an ASCII value instead of an EBCDIC value.
NUMERICSTS
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.
REAL
The REAL type is defined as a 4-byte FLOAT.
TIME
The TIME key type is stored internally as a 4-byte value. Hundredths of a second, second, minute, and hour values are each stored in 1-byte binary format. The MicroKernel places the hundredths of a second value into the first byte, followed respectively by the second, minute, and hour values.
TIMESTAMP
The TIMESTAMP data type represents a time and date value. In SQL applications, use this data type to stamp a record with the time and date of the last update to the record.
According to the ODBC standard, scalar functions such as CURRENT_TIME() or NOW() ignore the portion of the data type that represents fractional seconds. For this reason, TIMESTAMP values generated from scalar functions always have zero fractional seconds. For example: 2002-06-11 16:15:50.0000000. If you require your TIMESTAMP values to have significance in fractions of a second, do not use scalar functions to generate values to be inserted or updated.
TIMESTAMP is intended to cover time and data values made up of the following components: year, month, day, hour, minute, and second. The following table indicates the valid values of each of these components:
Table A-8 TIMESTAMP Data TypeThe value is stored internally as a TIMESTAMP MicroKernel key, an 8-byte long field that contains the complete date and time value converted to the fractions of seconds that timestamp precision specifies. For example, it is converted to microseconds when timestamp precision is 6, and it is converted to milliseconds when timestamp precision is 3. You provide the value of a TIMESTAMP in local time and SRDE converts it to Coordinated Universal Time (UTC), formerly Greenwich Mean Time (GMT), before storing it in a MicroKernel record. When you request the value of a TIMESTAMP, SRDE converts it from UTC to local time before returning the data.
It is critical that you correctly set time zone information on the computer where the database engine runs. If you move across time zones or change time zone information, the returned data will change when it is converted from UTC to local time. The local time/UTC conversions occur in SRDE using the time zone information where SRDE is running. The time zone information for sessions that are in different time zones than SRDE engine are not used in the local time/UTC conversions.
For example, assume it is October 15, and you enter a timestamp value to track an appointment on November 15 at 10 a.m. Assume you are in the U. S. Central Time Zone. When the SRDE 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 on the last Sunday of 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 SRDE 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.
UNSIGNED
The MicroKernel sorts UNSIGNED keys as unsigned INTEGER keys. An UNSIGNED key could contain any even number of bytes. The MicroKernel compares UNSIGNED keys from right to left.
An UNSIGNED key is sorted in the same manner as an INTEGER key. The differences between an UNSIGNED key and an INTEGER key are that an INTEGER has a sign bit, while an UNSIGNED type does not, and an UNSIGNED key can be longer than 4 bytes.
WSTRING
WSTRING is a Unicode string that is not null-terminated. The length of the string is determined by the field length.
WZSTRING
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.
ZSTRING
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 characters, 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.
| Prev Legacy Data Types |
Contents Up Check for Revisions | Next SQL Reserved Words |