FILE and INSERT Statements: Character-Position Form

The examples in this section are based on an input data file, cust_loc_data, which contains the last four columns (city, state, zipcode, and phone) of the customer table. Fields in the input file are padded with blanks to create data rows in which the location of data fields and the number of characters are the same across all rows. The definitions for these fields are CHAR(15), CHAR(2), CHAR(5), and CHAR(12), respectively. Figure 3 displays the character positions and five example data rows from the cust_loc_data file.

Figure 3. A Sample Data File

The following example of a dbload command file illustrates the character-position form of the FILE and INSERT statements. The example includes two new tables, cust_address and cust_sort, to receive the data. For the purpose of this example, cust_address contains four columns, the second of which is omitted from the column list. The cust_sort table contains two columns.

FILE cust_loc_data
   (city 1-15,
    state 16-17,
    area_cd 23-25 NULL = 'xxx',
    phone 23-34 NULL = 'xxx-xxx-xxxx',
    zip 18-22,
    state_area 16-17 : 23-25);
INSERT INTO cust_address (col1, col3, col4)
   VALUES (city, state, zip);
INSERT INTO cust_sort 
   VALUES (area_cd, zip);

Syntax for the Character-Position Form

The following diagram shows the syntax of the character-position FILE statement.

                      .-,----------------------------------------------------------------.
                      |         .-:--------------------.                                 |
                      V         V                      |                                 |
>>-FILE--filename--(----fieldn----+------------------+-+--+----------------------------+-+--)-><
                                  '-start--+-------+-'    '-NULL--=--'--null string--'-'
                                           '- -end-'
 

You can repeat the same character position in a data-field definition or in different fields.

The null string scope of reference is the data field for which you define it. You can define an explicit null string for each field that allows null entries.

Inserted data types correspond to the explicit or default column list. If the data-field width is different from its corresponding character column, inserted values are padded with blanks if the column is wider or are truncated if the field is wider.

If the number of columns named is fewer than the number of columns in the table, dbload inserts the default value that is specified for the unnamed columns. If no default value is specified, dbload attempts to insert a null value. If the attempt violates a not-null restriction or a unique constraint, the insert fails, and an error message is returned.

If the INSERT statement omits the column names, the default INSERT specification is every column in the named table. If the INSERT statement omits the VALUES clause, the default INSERT specification is every field of the previous FILE statement.

An error results if the number of column names listed (or implied by default) does not match the number of values listed (or implied by default).

The syntax of dbload INSERT statements resembles INSERT statements in SQL, except that in dbload, INSERT statements cannot incorporate SELECT statements. The following diagram shows the syntax of the dbload INSERT statement for character-position form.

>>-INSERT INTO--+----------+--table--+------------------+------->
                '-owner--.-'         |    .-,------.    |
                                     |    V        |    |
                                     '-(----column-+--)-'
 
>--+------------------------+--;-------------------------------><
   |                   (1)  |
   '-| VALUES clause |------'
 

1. See IBM Informix: Guide to SQL Syntax

The syntax for character-position form is identical to the syntax for delimiter form.

The user who executes dbload with this command file must have the Insert privilege on the named table.

How to Write a dbload Command File in Character-Position Form

The first FILE and INSERT statement set in the character-position example on page *** is repeated in the following example:

FILE cust_loc_data
   (city 1-15,
    state 16-17,
    area_cd 23-25 NULL = 'xxx',
    phone 23-34 NULL = 'xxx-xxx-xxxx',
    zip 18-22,
    state_area 16-17 : 23-25);
INSERT INTO cust_address (col1, col3, col4)
   VALUES (city, state, zip);

The FILE statement defines six data fields from the cust_loc_data table data rows. The statement names the fields and uses character positions to define the length of each field. Compare the FILE statement in the preceding example with the data rows in Figure 4.

Figure 4. A Sample Data File

The FILE statement defines the following data fields, which are derived from the data rows in Figure 4.

The null strings that are defined for the phone and area_cd fields generate the null values in those columns, but they do not affect the values that are stored in the state_area column.

The INSERT statement uses the field names and values that are derived from the FILE statement as the value-list input. Consider the following INSERT statement:

INSERT INTO cust_address (col1, col3, col4)
   VALUES (city, state, zip);

The INSERT statement uses the data in Figure 4 and the FILE statement on page *** to put the following information into the cust_address table.

Because the second column (col2) in cust_address is not named, the new data row contains a null (assuming that the column permits nulls).

Consider the following INSERT statement:

INSERT INTO cust_sort 
   VALUES (area_cd, zip);

This INSERT statement inserts the following data rows into the cust_sort table.

Because no column list is provided, dbload reads the names of all the columns in cust_sort from the system catalog. (You cannot insert data into a temporary table because temporary tables are not entered into the system catalog.) Field names from the previous FILE statement specify the values to load into each column. You do not need one FILE statement for each INSERT statement.