Defining Table Structure on UNIX and Linux

Because text files do not all have the same structure, the driver provides the option to define the structure of an existing file. Although defining the structure is not mandatory, because the driver can attempt to guess the names and types of the columns, this feature is extremely useful.

To define the structure of a text file, you create a QETXT.INI file using any plain text editor, such as vi. The file name must be in uppercase. All of the tables you want to define are specified in the QETXT.INI file. When you specify table attributes in QETXT.INI, you override the attributes specified in the system information file (odbc.ini) or in the connection string.

To define the QETXT.INI file:

1	Create a [Defined Tables] section and list all of the tables you are defining. Specify the text file name (in either upper or lowercase, depending on the file) followed by the name you want to give the table, for example:

emptext.txt=EMP

Table names can be up to 32 characters in length and cannot be the same as another defined table in the database. This name is returned by SQLTables. By default, it is the file name without its extension.

2	For each table listed in the [Defined Tables] section, you must specify the text file (FILE=), the table type (TT=), whether the first line of the file contains column names (FLN=), and the delimiter character (DC=).

■	Specify the text file name. For example:

FILE=emptext.txt

■	To define the table type, specify how the fields are separated (comma, tab, fixed, or character). For example:

TT=COMMA

■	If the table type is CHARACTER, specify the delimiter character. The value can be any printable character except single and double quotes. For example, if the fields are separated by comma:

DC=,

■	Specify whether the first line of the file contains column names, using 1 for yes and 0 for no. For example:

FLN=0

3	Define the fields in the table, beginning with FIELD1. For each field, specify the field name, field type, precision, scale, length, offset (for fixed tables), and date/time mask. See “Date Masks” for information about masks.

Separate the values with commas. For example, to define two fields:

FIELD1=EMP_ID,VARCHAR,6,0,6,0,

FIELD2=HIRE_DATE,DATE,10,0,10,0,m/d/yy

4	Save the file as QETXT.INI. The driver looks for this file in the directory specified by the Database attribute in odbc.ini, or in the current directory.

Example of QETXT.INI

The following is an example of a QETXT.INI file. This file defines the structure of the emptext.txt file, which is a sample data file shipped with the DataDirect ODBC Text file.

[Defined Tables]

emptext.txt=EMP

 

[EMP]

FILE=emptext.txt

FLN=1

TT=Comma

FIELD1=FIRST_NAME,VARCHAR,10,0,10,0,

FIELD2=LAST_NAME,VARCHAR,9,0,9,0,

FIELD3=EMP_ID,VARCHAR,6,0,6,0,

FIELD4=HIRE_DATE,DATE,10,0,10,0,m/d/yy

FIELD5=SALARY,NUMERIC,8,2,8,0,

FIELD6=DEPT,VARCHAR,4,0,4,0,

FIELD7=EXEMPT,VARCHAR,6,0,6,0,

FIELD8=INTERESTS,VARCHAR,136,0,136,0,