Table Definitions

INI Definition

Structured text files (INI files) may be used to manually define data that is not normalized (i.e. data sources with more than one record type) or cannot be handled by the PxPlus Data Dictionary. INIs are typically used to define files from legacy systems that were not created using the NOMADS Data Dictionary facilities.

These definition files consist of a table declaration section that assigns a logical table name to the physical path of each file. The logical names become section headings for column definitions. The maximum line length in an INI definition is 255 characters. The INI contents are described in the following sections.

Note:
The square brackets enclosing section headings are part of the INI syntax. Other square brackets in the format examples below indicate optional elements.

Table Declaration

The [*tables*] declaration section is used to assign a logical name to a database's physical filename.

Example:

[*tables*]
INVOICELINE=\INVOICE\INVLINE
CLIENT==%c$+"cstfile"

The [*tables*] section heading is not case sensitive; however, square brackets, asterisks, and the word tables are all part of the required syntax. The syntax for assigning a logical table appears as follows:

table_name=path_filename[ ,alternate.INI ] [ ,SORTTABLE ]

Where:

table_name	Logical name assigned to the physical file. *Example:* invoiceline is the logical name for the physical file invline in the invoice directory: [tables] invoiceline=\invoice\invline
path_filename	Physical location and file name of database in the system. Either absolute or relative path names can be specified. Relative path names are resolved based on the database directory setting in the PxPlus SQL ODBC Driver configuration. If the first character of the path is an equals sign =, the PxPlus SQL ODBC Driver treats the path as an expression and replaces all instances of %c$ with company, %u$ with user ID, and %s with session ID that are supplied during the connection. *Example:* Client==%c$+"cstfile" In this example, if ABC is entered in the Company field of the PxPlus SQL ODBC Driver, then Client would be evaluated to ABCcstfile.
alternate	Optional alternate INI definition file. Early Windows systems had a limit on the amount of information that could be stored in a single INI file. This option allows the definition to be spread over multiple INI files to keep the size of any one file below 64K. It is now more commonly used to specify that the INI definition overrides the embedded dictionary definition.
SORTTABLE	Optional entry informing the PxPlus SQL ODBC Driver that the column definitions are not defined in the file in physical order. The physical order is controlled through the use of the FIELD= keyword. The default is that all fields are defined in the physical order that they exist in the file.

Column Declaration

The record descriptors define logical columns extracted from the PxPlus data file with each entry consisting of:

The column name as it appears to the user.
Additional parameters separated by commas.

The minimum information required is a column name and its length. All columns default to string, delimited. The column descriptors can be in any order and are comma delimited. Only the first 3 characters of the keywords are required. Invalid keywords are ignored.

Column descriptors have the following format:

[table_name]
column_name = LENGTH=length,[type, formatting, attributes]

Where:

column_name

Logical name of the column.

Example:

[Client]
CustomerID=STRING,LENGTH=6,FIELD=1,OFFSET=0
Name=LEN=20

In this example, CustomerID is the first column in the logical table, Client and Name is the second.

length

Mandatory value. Use a numeric expression or integer for length.

Example:

LEN=30

If desired, you can set the number of digits to the right of the decimal.

Example:

LEN=5.2

type

Optional type. The following keywords set the type of the data:

BNR	Numeric values stored as a signed binary.
LOGICAL	Logical field - resulting output type is SQL_BIT.
*MAS90YEAR**	Special year only format used in Sage MAS 90 and Sage MAS 200.
NUMERIC	Numeric value - in a PxPlus file, this is an ASCII representation of the number.
STRING	ASCII string (Default).
UNI	Data is an unsigned integer stored as a binary.
UNSIGNEDBINARY	Numeric value stored as unsigned binary.

formatting

Optional format mask. The following keywords describe the layout of data in the file:

BINARY	Numeric value stored as a signed binary as a sub-string of a longer field.
DECIMAL	Sub-stringed numeric with an embedded decimal. Numerics are right justified.
DELIMITED	Alternate description for PADDED with the exception of how numerics are handled. If the field is a numeric, then it will be space-padded, right justified.
FIXED	Fixed length with no separator, trailing spaces stripped on read. Numerics are right justified.
I86	Swapped. On Intel machines numbers are natively stored as swapped; e.g., 00 01 is stored as 01 00.
NOSTRIP	Sub-string - trailing spaces are never stripped. Same as the Data Dictionary formats Padded and Substring.
PADDED	Fixed length, padded to length and with a field delimiter. Same as the Data Dictionary format: Last Substring.
SIGNED	Same as NUMERIC,FIXED except the first character of the field will have a negative sign ( - ).
SUBSTRING	Same as NOSTRIP. Added for consistency with Data Dictionary.
VARIABLE	Variable length delimited by $8A$ (Default).

attributes

Optional attributes that are not handled by the Data Dictionary:

CLASS=str	Class declaration. See Classes.
FIELD=nnn	Logical column number in the record. Zero indicates "from start of record". The INI default is "in sequential order by position in the list".
FORMAT=value	Mask to be applied to the data when returned to the calling application. Maximum is 39 characters.
HIDE	Can be used to hide data when data has been duplicated do to an external key. See External Keys. Can also be used to hide filler data.
KEY	Defines external key fields.
*MUSTBE="str"* *MUSTBE<"str"* *MUSTBE>"str"*	String comparison for filtering data. If the condition is not met, the record is skipped. Maximum is 80 characters. See Record Selection.
NOSHOW	Field in Data Dictionary, data never returned.
OFFSET=nnn	Defines the offset (zero based) in the field.
RECTYPE=value or *RECTYPE	Flattens data. See Record Selection.
SAMEAS	Used to link duplicate columns. This attribute is designed for columns, which comprise an external key, and the data is duplicated in the record. *Example:* CustId=len=6 CustId_dup=len=6, sameas=CustId,hide During insert/update operations, the data is copied from the column referenced to the target column.
SEPARATOR=nnn	Delimiter for variable length field. Use the decimal value of your delimiter character. *Example:* For the LF character ($0A$), you would use either SEPARATOR=10 or SEP=10.

Table Definitions

Note: The square brackets enclosing section headings are part of the INI syntax. Other square brackets in the format examples below indicate optional elements.

Table Declaration

Column Declaration

Note:
The square brackets enclosing section headings are part of the INI syntax. Other square brackets in the format examples below indicate optional elements.