# Data Files

Keyed Files |

This is the most common file format used in PxPlus. It contains at least one unique (primary key) field that identifies each record on the file. PxPlus supports internally and externally defined keys with record sizes up to 2GB. A wide variety of key segment options is available.

Keyed files support fixed length records (FLR), variable length records (VLR), and the PxPlus enhanced file format (EFF). Up to 16 keys and 96 segments are supported for FLR and VLR based files, and EFF is up to 255 keys and 255 segments per key.

For information on EFF files, see Enhanced File Format. For an explanation of EFF vs. VLR file formats, see EFF vs. VLR File Formats.

Three types of Keyed files can be created:

| Direct Files | Consist of an external key (FLR/VLR, EFF)
| Sort Files | Consist of keys but no data (FLR/VLR, EFF)
| Multi-Keyed Files | Consist of one or more keys (FLR/VLR, EFF)

All PxPlus I/O directives work with keyed files. See Processing Data Files.

The READ directive without a KEY= option reads the record with the next higher key. When reading a file with an external key, it is normal to first retrieve the key of the next record.

Example:

> k$=KEY(1,end=done)
> READ (1,key=k$)

The WRITE directive without a KEY= option is invalid unless the record was previously extracted or has an imbedded key. See Multi-Keyed Files.

The REMOVE directive deletes the record whose key matches the key specified. When the record is deleted, its associated disk space is returned for reuse within the file.

Accessing or attempting to access a record whose key does not exist positions the file pointer to the next higher existing key. The only exception is when using the FIND directive. This directive is a variation of the READ verb that does not update the current file pointer if the key is not found.

Example:

This assumes that the file ACTNME has a key of name with a data field of account number.

> 0010 OPEN (2) "ACTNME" ! Assign ACTNME to file 2
> 0020 INPUT "Enter name:",NM$
> 0030 IF NM$="" THEN END
> 0040 K$=NM$
> 0050 READ (2,KEY=K$,DOM=0070) A_NUM$ ! Read record info
> 0060 PRINT A_NUM$, " ", K$
> 0070 K$=KEY(2,END=0020) ! Get next higher key
> 0080 IF K$<=NM$+$FF$ THEN GOTO 0050
> 0090 CLOSE (2)

## Direct Files

A direct file is a keyed file with an external key. The key size must be specified along with the file name. The DIRECT directive is used to create a direct file:

> DIRECT filename$, max_len [, max_recs [, rec_size ]]

Where:

filename$ | File name of the DIRECT (Keyed) file. String expression.
max_len | Maximum length of the key for all records in the file. Numeric expression, integer.
max_recs | Maximum number of records in the file. Optional numeric expression. Use a comma with no value to set the default (zero - unlimited). If a positive value is supplied, PxPlus creates and pre-allocates disk space for the file (for FLR/VLR formats). With a negative value, PxPlus allocates sufficient disk space for the file (for FLR/VLR formats), but will set max_recs back to zero.
rec_size | Maximum size of the data portion of each record (excluding the key). Optional. Numeric expression. You can use:
No Value | Default is VLR with maximum size of 256.
Positive Integer | FLR of size specified.
Negative Integer | VLR with maximum length specified.

Example:

> DIRECT "CSTFLE",6

## Sort Files

The sort file is a type of keyed file that consists of a key only with no data record portion. Key size must be specified, along with the file name. The SORT directive is used to create a sort file:

> SORT filename$, max_keysize [, max_rec ]

Where:

filename$ | Name of the SORT file to create. String expression.
max_keysize | Maximum key size to be maintained for this file. Numeric expression.
max_rec | Estimated number of records that the file is to contain. Default is no initial allocation of file space with no limit on final size. Numeric expression.

Example:

> SORT "CSTFLE",6

## Multi-Keyed Files

The KEYED directive can create a file with one or more keys. The primary key may be external or internal. If the first field after the file name is a number, an external file key is created; if it contains a key definition (enclosed within [ ] ), then only internal key fields may be used.

The first key specified is considered the primary key. Every record must have a unique primary key. You can have duplicate secondary keys from record to record.

A multi-keyed file is a keyed file with one primary record key and up to 15/255 secondary keys. The primary key must be unique within the file, while the secondary keys may be duplicated between records; e.g. where two JONES may exist. Multi-keyed files are defined like any keyed file except that the key definition contains a series of comma-separated key declarations. The first key declaration is for the primary key; the remaining declarations are for secondary keys.

The KEYED directive is used to create a file with one or more keys:

> KEYED filename$,[,extkey_len][,key_def$][,max_recs[,rec_size]][,fileopt]

Where:

filename$ | Name of the Keyed file to create. String expression.
extkey_len | Numeric expression. Length of the external key for all records in the file.
key_def$ | String expression defining the key. The Keyed file can be single or multi-keyed. A key definition is made up of one or more key field definitions, ranging from 0 to 15 for FLR/VLR files or 0 to 255 for EFF files. Use integers for specific field numbers, 0 (zero for record-based offsets) or KEY to reference a value based on an external key. The key definition formats are as follows:
Single Key Field: | [["keyname":]field: offset: len[:"attr"] ]
Composite Key Fields (Using the + Operator): | [["keyname":]field: offset: len[:"attr"] ]+[field: offset: len[:"attr"] ]
Multi-Keyed Alternate Key Fields are Comma-Delimited: | [["keyname":]field: offset: len[:"attr"] ], [["keyname":]field: offset: len[:"attr"] ]
#### Note: The outer set of square brackets in the above formats are part of the syntax; the inner brackets indicate optional syntax items (i.e. the brackets enclosing the optional [:"attr"] are not part of the syntax).
Where:
keyname | Name of key assigned for use in KNO=name$ options.
field | Integer, 1 to number of fields, (0 = record-based offset) or KEY to reference a value based on an external key.
offset | Starting position within the field.
len | Length, number of characters in the key field (integer). #### Note: The maximum key field length, including the field offset value, is 3839.
"attr" | Attribute characters.
: | Colon - the separator for elements in a key segment.
max_recs | Maximum number of records the file is allowed. Optional numeric expression. The default is zero (no limit). Use a comma with no value to set the default. If a positive value is supplied, PxPlus creates and pre-allocates disk space for the file (for FLR/VLR formats). With a negative value, PxPlus allocates sufficient disk space for the file (for FLR/VLR formats) but will set the max_recs count back to zero (unlimited).
rec_size | Maximum size of the data portion of the record (excluding the key). A negative value creates a variable length record (VLR) data file with the maximum record length equal to the positive value of this field. A positive value creates a fixed length record (FLR) formatted file. If you do not specify size, the default is VLR with a maximum record size of 256. The maximum record size for a VLR file is 31000 bytes. Attempting to create a VLR file with a record size more than 31000 bytes results in an FLR file with the requested record size.
fileopt | Supported file options:
BSZ=num | Block size. Numeric expression (1 to 31K for VLR, 1 to 63K for EFF).
ERR=stmtref | Error transfer.
SEP=char$ |* | Default field separator character. Hex or ASCII string value.
OPT=char$ | Single character parameter:
"C" | Compressed. Adds simple compression to record data.
"U" | Enable UpdatePlus™ logic for file. (UpdatePlus™ was added to PxPlus version 7.65) Only supported on VLR and FLR files - no EFF support. (as of build 9180)
"X" | Extended Record Size. Extends record sizes up to 2GB per record.
"Z" | Set ZLib Compression for VLR and EFF Files. #### Note: "Z" and "C" together will result in an Error #32.
"0" | Create VLR/FLR files (default if 'KF'=0).
"1" | Create EFF Files with 2GB limit.
"2" | Create EFF Files without 2GB limit (supported platforms). #### Note: OPT="2" generates Error #99: Feature not supported on platforms that do not offer Large File Support (LFS).

## Key Definitions

The key_definitions parameter contains one or more key declarations, each comma separated. The first key declaration is for the primary key; the remaining declarations are for the secondary keys. If an external key field is to be created, it must be a primary key and its length given as the first key declaration.

#### Note:
The keys must always reside within the defined record size. For example, if a file is defined with a record size of 1000, then all keys must be located within the first 1000 bytes, regardless of whether the Extended Record Size flag is enabled. The reason for this is that extended records are stored in multiple linked chunks, but internally, the key segments can only be built from the first chunk.

Keys that exist with the data portion of the record are defined by a key declaration in the following format:

> [{"keyname":}field_no:offset:length{:"options"}]

Where:

keyname | This is an optional name for the key. Within the program, the key can be accessed either by its number (primary key is zero) or by using its key name.
field_no | This value indicates the field number within the data record. If the field_no is specified as zero, then the complete data record is considered a field. If desired, field_no can be specified as KEY indicating the external key.
offset | This field contains the offset within the field to be used. One (1) indicates the first position.
length | This field contains the length of data to be used. If the actual field_no specified is longer than the length, it will be truncated within the key value. If the field is shorter, it will be padded with nulls.
options | This optional string can consist of one or more of the following values:
"#" | Auto-increment - Space filled string. (See Note below.)
"+" | Auto-increment - Zero filled string. (See Note below.)
"-" | Signed binary integers key segments. This option cannot be used in conjunction with the following segment options on the same segment: "#", "+", "B", "C", "D", "I", "L", "T", "U", "Z" string.
"A" | Ascending key (assumed).
"B[.n]" | Indicates that the segment contains a numeric value. Field will internally be converted to its binary equivalent and stored into the data file. Negative numbers will occur before position numbers in ordinary numeric value sequence. If '.n' is provided, the value 'n' is assumed to be the number of digits to the right of the decimal point that are to be maintained. Before the internal value is created, the value will be TRUNCATED to the number of decimal places specified. (The "B" segment attribute was added to PxPlus in version 6.30.)
"C" | Force uppercase for individual key segments to create case-insensitive keys. Example: KEYED "filename",[1:1:10:"C"],[2:1:40:"L"]+[1:1:10:"C"] #### Note: The conversion tables for upper/lowercase conversions are located in the PxPlus message file *MLFILE.EN. You can set these tables using the DEF UCS and DEF LCS directives and retrieve values using the UCS(*) and LCS(*) functions.
"D" | Descending order (Default: Ascending).
"I" | Binary auto-increment. Field (1, 2, or 4 bytes in length) will be auto-incremented as records are added to the file. Key must be record-based; i.e. field is 0 and offset is location in record. (See Note below.)
"K[:n]" | Null key suppression. n is the Hex value of the character to suppress if all characters match (defaults to $00$ if not specified). If the entire key evaluates to null, the key will not be a part of this key tree. This cannot be used on a primary key or in conjunction with the "N" option.
"L" | Force lowercase for individual key segments to create lowercase insensitive keys.
"N[:n]" | Null segment suppression. n is the Hex value of the character to suppress if all characters match. If any segment within the key evaluates to null, the key will not be a part of this key tree. This cannot be used on a primary key or in conjunction with the "K" option.
"S" | Swapped (same as SWP( ) type 7, Intel x86 style swapping only).
"T" | Force automatic accent translation for individual key segments (i.e. to translate accented/extended characters to their non-accented counterparts). You will find this useful for sorting multilingual characters into a single unified sort sequence. Example: KEYED "filename",[1:1:40:"T"],[2:1:40:"TCD"] #### Note: The conversion tables for accent translations are located in the PxPlus message file *MLFILE.EN. You can set this table using the DEF CVS directive and retrieve values using the CVS(*) function.
"U" | If you declare an alternate key segment as unique, then no duplicate keys are allowed on writing a record. PxPlus generates Error #11: Record not found or Duplicate key on write. If you designate any segment in a key definition as unique, then the entire key will be unique. #### Note: The primary key must always be unique.
"Z" | Zero terminated strings. This will consider a null byte ($00$) in a string as the end of the data within the field, and will ignore any data between the null byte and the end of the key field. (Also known in C as a Z-String.)
#### Note: Only one of the auto-increment options ("#", "+" or "I") can be used per file.

If desired, multiple key descriptors can be specified for a single key. This will generate a composite key. The data fields will be concatenated together to form a complete key. When defining a composite key, the individual key descriptors are to be separated by a (+) plus sign.

Example:

Given customer file with an external key as follows:

External Key | Field #1 | Field #2 | Field #3 | . . .
CUSTNO | NAME | ADDRESS | TYPE | . . .

The following KEYED directive would be used to create the above file with CUSTNO as the primary key, a secondary key of NAME and a composite secondary key of TYPE and CUSTNO:

> KEYED "CUSTDB",5,[1:1:20],[3:1:1]+[KEY:1:5],,200

This directive would create the file "CUSTDB" with a five-character external key (CUSTNO), a 20-character secondary key of the first 20 characters of field 1 (NAME), and a six-character secondary key of the TYPE (1 ch), followed by the primary key. The record size would be 200 bytes each with no limit to the number of records.

#### Note:
If, when generating a key, the descriptor references data outside the data field (either by an offset outside of field specified or a length which exceeds the field length) and the descriptor is other than the last descriptor within a composite key, the key is padded with Hex $00$ in order to achieve the length specified.

There is a limit of 96 total data fields allowable in a file for use within keys (on FLR/VLR files). This means that the total number of data fields that can be used to define keys must be no more than 96 with the maximum number of keys being 1 primary and 15 secondary. If, for example, one key consists of 20 data fields, the maximum number of additional fields would now be limited to 76 for a total of 15 keys, 96 data fields.

If no external key is desired on the file, only key descriptors would be specified with the first key descriptor being considered the primary key.

## Correcting Duplicate Unique Keys

If, during the process of building a key tree structure for a data file, the system detects that a duplicate key exists, the system will display the following message:

> Duplicate key found at nnnnn

The value displayed in nnnnn is the logical record address or index that can be used to locate and read the affected record.

To correct this problem, read the record using an IND=nnnnn clause, change/correct the key field(s), and issue a WRITE to update the record. The system will remove the incorrect key pointer and insert the appropriate new key pointer.

Example:

Let us create a file that will ultimately have duplicate keys:

> ->keyed "keyfle",[1:1:6]
> ->open (1) "keyfle"
> ->write (1) "000001","123456"
> ->write (1) "000002","123456"
> ->close (1)

> Add the index, which will cause the problem:

> ->add index [2:1:6:"U"] to "keyfle"
> Duplicate key found at index 2050
> Done - loaded key number 1 with 2 keys

During the addition of the new key, the system will attempt to load the key trees. Any errors will be reported both on the screen and on the PxPlus system logfile.

#### Note:
Whenever a key table is loaded, a log is sent of the rebuild process (including errors) to the logfile specified in the INI file for the system. The contents of this logfile can be used to keep track of any records with keys that need to be corrected. On the Windows version, the output is also sent to the Trace window, which itself could be saved to a disk file if necessary.

To repair the records, read by the record index and change the key:

> ->open (1) "keyfle"
> ->read (1,ind=2050) a$,b$
> ->print a$,b$
> 000002123456
> ->b$="999999"
> ->write (1) a$,b$

A reload of the keys is not required but can be used to verify the correction:

> ->keyed load (1)
> Done - loaded key number 0 with 2 keys
> Done - loaded key number 1 with 2 keys

#### Note:
Whenever a key table is loaded, a log is sent of the rebuild process (including errors) to the logfile specified in the INI file for the system. The contents of this logfile can be used to keep track of any records with keys that need to be corrected. On the Windows version, the output is also sent to the Trace window, which itself could be saved to a disk file if necessary.

## Using the Secondary Keys

For the purposes of accessing multi-keyed data files, each key is assigned a key number. The primary key is assigned the key number of zero (0), the first secondary key is assigned one (1), and so on. When reading a multi-keyed file, the system performs the read based on the current key number being used. The key number is specified by the KNO= option in the WRITE, FIND, EXTRACT directives or any of the key functions; i.e. KEC( ), KEF( ), KEL( ), KEN( ), KEP( ), KEY( ). Once KNO= is specified, it remains the current key number until changed by a subsequent KNO= option. When a file is initially opened, the key number is set to zero (primary key).

All file input directives function basically the same on multi-keyed files as they do on normal single-keyed files. One exception is the READ (n,KEY=xxx) directive on secondary keys. This directive will read the first record that contains the desired key. Specifying a KEY= on an alternate key that contains duplicates will result in what appears to be an endless loop; i.e., the read will continue to re-position the key pointer to the first of the duplicates. Therefore, do not use the KEY= option in this case.

## Updating Multi-Keyed Files

Adding or updating records on a multi-keyed file uses the same WRITE directive as does a single-keyed file. The record to be changed or added is determined by the primary key. If the file contains an external primary key, the KEY= option must be specified in the WRITE statement. If the file does not contain an external key, then the key will be determined by the data fields presented in the WRITE statement.

#### Note:
If a KEY= option is specified in a WRITE directive and no external key exists, or conversely, if no KEY= option is specified and an external key does exist, an Error #80: Invalid key definition, number or name is generated.

The REMOVE statement is used to delete a record from a multi-keyed file. The KEY= option is recommended on the REMOVE directive. The key specified must be the primary key of the record to be removed, regardless of whether the key is external or not. If the KEY= option is not specified, then the last record read or extracted will be removed.

## Multi-Keyed Memory Files

Memory files (*memory*) can now have alternate keys specified. When opening a memory file, you can add a KEYDEF= option that defines the alternate key definition. Standard PxPlus alternate key definitions can be used.

Example:

The following will create a memory file with a six-character external key and two alternate keys based on fields 2 and 3 of the data:

> OPEN (1) "*memory*;KEYDEF=6,[2:1:6:""U""],[3:1:30]"

If desired, the key definition for a memory file can include Key names.

#### Note:
Unlike standard *MEMORY* files, *MEMORY* files created with key definitions cannot have record inserted by record index. The key fields are always used.