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.
Table 135
Btrieve Key Data Types and Type Codes  
Data Type
Type Code
AUTOINC
15
BFLOAT
9
STRING
0
CURRENCY
19
DATE
3
DECIMAL
5
FLOAT
2
GUID
27
INTEGER
1
LOGICAL
7
LSTRING
10
MONEY
6
NUMERIC
8
NUMERICSA
18
NUMERICSLB
28
NUMERICSLS
29
NUMERICSTB
31
NUMERICSTS
17
TIME
4
TIMESTAMP
20
UNSIGNED BINARY
14
WSTRING
25
WZSTRING
26
ZSTRING
11
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 data type 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 (7 and 8 are not yet committed). 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 are inserting the first record in the file, the MicroKernel assigns the value of 1 to the AUTOINC key.
•
If records already exist in the file, the MicroKernel assigns the key a value that is one number higher than the highest existing absolute value in the file.
•
If you specify a positive, 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.
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. New database definitions should use FLOAT rather than BFLOAT.
CHAR
*Note: In previous versions of Pervasive PSQL, 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. CHAR data is expected to be padded with blanks to the full size of the key.
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:
TYPE dateField {
char day;
char month;
integer year;
}
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 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 the 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 interface 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 Pervasive 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 Pervasive PSQL ActiveX access method always uses 0xF for the positive sign nibble regardless of the Registry setting for CommonCOBOLDecimalSign.
Windows
Run the Registry editor provided with the operating system. Change the value of CommonCOBOLDecimalSign to “YES” for the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\PERVASIVE SOFTWARE\SQL RELATIONAL ENGINE
In most operating systems, the “Pervasive Software” key is HKEY_LOCAL_MACHINE\SOFTWARE\PERVASIVE SOFTWARE. However, its location under HKEY_LOCAL_MACHINE\SOFTWARE can vary depending on the operating system.
*Caution: Editing the Registry is an advanced procedure. If done improperly, the editing could cause your operating system not to boot. If necessary, obtain the services of a qualified technician to perform the editing. Pervasive Software does not accept responsibility for a damaged Registry.
Linux
For Linux 32-bit operating systems, run the psregedit utility as follows:
./psregedit -set -key PS_HKEY_CONFIG/SOFTWARE/"Pervasive Software"/"SQL Relational Engine" -value "CommonCOBOLDecimalSign" -type PS_REG_STR "YES"
For Linux 64-bit operating systems, run the psregedit utility as follows:
./psregedit -set -key PS_HKEY_CONFIG_64/SOFTWARE/"Pervasive Software"/"SQL Relational Engine" -value "CommonCOBOLDecimalSign" -type PS_REG_STR "YES"
See also psregedit in Pervasive PSQL User's Guide.
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:
GUID
The GUID data 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 interface is UNIQUEIDENTIFIER. (See UNIQUEIDENTIFIER.)
Note that GUID requires a file format of 9.5 or greater.
GUID Keys
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 Blocks in Btrieve API Guide in the Developer Reference.
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 136
INTEGER Key Type
Length in Bytes
Value Ranges
1
0 – 255
2
-32768 – 32767
4
-2147483648 – 2147483647
8
-9223372036854775808 – 9223372036854775807
LOGICAL
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 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.
MONEY
The MONEY key type has the same internal representation as the DECIMAL type, with an implied two decimal places.
NUMERIC
Each digit of a NUMERIC data type occupies one byte. NUMERIC values are stored as ASCII strings and right justified with leading zeros. The rightmost byte of the number includes an embedded sign with an EBCDIC value. By default, the sign value for positive NUMERIC data types is an unsigned numeric number. The NUMERIC type is commonly used in COBOL applications.
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.
Table 137
Comparison of Shifted and Unshifted Sign Values for NUMERICS
Digit
Default (unshifted) Sign Value
Shifted Sign Value
 
Positive
Negative
Positive
Negative
1
1
J
A
J
2
2
K
B
K
3
3
L
C
L
4
4
M
D
M
5
5
N
E
N
6
6
O
F
O
7
7
P
G
P
8
8
Q
H
Q
9
9
R
I
R
0
0
}
{
}
Enabling the Shifted Format
You must manually specify a setting on the machine running the Pervasive PSQL database engine to enable the shifted format. The setting, DBCobolNumeric, must be set to “yes.” The following table summarizes how to specify the setting depending on the engine server platform. See NUMERIC in SQL Engine Reference for the complete steps.
Table 138
Specifying the DBCobolNumeric Setting for Different Platforms
Server Platform
Setting Specification
Windows 32-bit
Using the Registry editor provided with the operating system, add the DBCobolNumeric setting to the Registry as a new string value to the following key:
HKEY_LOCAL_MACHINE/SOFTWARE/PERVASIVE SOFTWARE/DATABASE NAMES/VERSION x/ SETTINGS
(where x represents the version number)
Set the string value to “yes.”
Note: In most Windows operating systems, the ‘Pervasive Software’ key is HKEY_LOCAL_MACHINE\SOFTWARE\PERVASIVE SOFTWARE. However, its location under HKEY_LOCAL_MACHINE\SOFTWARE can vary depending on the operating system.
Caution: Editing the Registry is an advanced procedure. If done improperly, the editing could cause your operating system not to boot. If necessary, obtain the services of a qualified technician to perform the editing. Pervasive Software does not accept responsibility for a damaged Registry.
Stop and restart the database engine or the engine services. See Starting and Stopping the Server Engine on a Windows Server and Starting and Stopping the Workgroup Engine on Windows, both in Pervasive PSQL User's Guide.
Linux
Add the DBCobolNumeric setting to bti.ini below the [Database Names] entry:
[Database Names]
DBCobolNumeric=yes
By default, bti.ini is located in the /usr/local/psql/etc directory.
Stop and restart the database engine. Starting and Stopping the Database Engine on Linux in Pervasive PSQL User's Guide.
Consistent Sign Values for Positive NUMERIC Data
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 the 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.
See UPDATE.
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.
Table 139
Sign Values for NUMERICSA
Digit
Default Sign Value
 
Positive
Negative
1
1 or Q
q
2
2 or R
r
3
3 or S
s
4
4 or T
t
5
5 or U
u
6
6 or V
v
7
7 or W
w
8
8 or X
x
9
9 or Y
y
0
0 or P
p
NUMERICSLB
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.
Table 140
Sign Values for NUMERICSLB
Digit
Default Sign Value
 
Positive
Negative
1
1
A
2
2
B
3
3
C
4
4
D
5
5
E
6
6
F
7
7
G
8
8
H
9
9
I
0
0
@
NUMERICSLS
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.
NUMERICSTB
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.
Table 141
Sign Values for NUMERICSTB
Digit
Default Sign Value
 
Positive
Negative
1
1
A
2
2
B
3
3
C
4
4
D
5
5
E
6
6
F
7
7
G
8
8
H
9
9
I
0
0
@
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. 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).
*Note: According to the ODBC standard, scalar functions such as CURRENT_TIMESTAMP() or NOW() ignore the portion of the data type that represents fractional seconds. It is important to note that when these functions are used, Pervasive PSQL does not ignore fractional seconds and displays three digits for milliseconds.
TIMESTAMP is intended to cover time and data values made up of the following components: year, month, day, hour, minute, second, and millisecond. The following table indicates the valid values of each of these components:
Table 142
TIMESTAMP Components
YEAR
0001 to 9999
MONTH
01 to 12
DAY
01 to 31, constrained by the value of MONTH and YEAR in the Gregorian calendar.
HOUR
00 to 23
MINUTE
00 to 59
SECOND
00 to 59
MILLISECOND
000 to 999
The 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.
*Caution: 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.
Because timestamp 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 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 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 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 BINARY
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
WSTRING is a Unicode string that is not null-terminated. The length of the string is determined by the field length. WSTRING is not supported in SQL.
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. WZSTRING is not supported in SQL.
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.