ODBC4x
INI Definition Table Definitions
  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 ProvideX 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. For 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. For 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 ODBC driver configuration.

If the first character of the path is an equals sign =, the ProvideX 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.

For Example:

Client==%c$+"cstfile"

In this example, if ABC is entered in the company field of the 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.
SORTTABLE Optional entry informing the 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 ProvideX 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; e.g,

[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; e.g.,

LEN=30.

If desired, you can set the number of digits to the right of the decimal; e.g., 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
MAS90*YEAR Special year only format used in Sage MAS 90 and Sage MAS 200.
NUMERIC Numeric value - in a ProvideX 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., 0001 is stored as 0100.
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 1st 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 below.
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 Field is not in Data Dictionary (use for fields duplicated in key) and not available to user (use for filler values).
KEY Defines external key fields.
MUSTBE="str" String comparison for filtering data. If the condition is
MUSTBE<"str" not met, the record is skipped. Maximum is 80
MUSTBE>"str" 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; e.g.,

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; e.g., for the LF character ($0A$) you would use either SEPARATOR=10 or SEP=10.