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.

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.

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.