FILE and INSERT Statements: Delimiter Form

The following example of a dbload command file illustrates a simple delimiter form of the FILE and INSERT statements. The example is based on the stores_demo database. An UNLOAD statement created the three input data files, stock.unl, customer.unl, and manufact.unl. To see the .unl input data files, refer to the directory $INFORMIXDIR/demo/prod_name (UNIX or Linux) or %INFORMIXDIR%\demo\prod_name (Windows).

FILE stock.unl DELIMITER '|' 6;
INSERT INTO stock;
FILE customer.unl DELIMITER '|' 10;
INSERT INTO customer;
FILE manufact.unl DELIMITER '|' 3;
INSERT INTO manufact;

Syntax for the Delimiter Form

The following diagram shows the syntax of the delimiter FILE statement.

>>-FILE--filename--DELIMITER--'c'--nfields---------------------><
 

The dbload utility assigns the sequential names f01, f02, f03, and so on to fields in the input file. You cannot see these names, but if you refer to these fields to specify a value list in an associated INSERT statement, you must use the f01, f02, f03 format. For details, refer to How to Write a dbload Command File in Delimiter Form.

Two consecutive delimiters define a null field. As a precaution, you can place a delimiter immediately before the new-line character that marks the end of each data row. If the last field of a data row has data, you must use a delimiter. If you omit this delimiter, an error results whenever the last field of a data row is not empty.

Inserted data types correspond to the explicit or default column list. If the data field width is different from its corresponding character column width, the data is made to fit. That is, inserted values are padded with blanks if the data is not wide enough for the column or truncated if the data is too wide for the column.

If the number of columns named is fewer than the number of columns in the table, dbload inserts the default value that was specified when the table was created 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.

For example, the following dbload command is not supported:

FILE "testtbl2.unl" DELIMITER '|' 1; 
            INSERT INTO testtbl 
                 (testuser, testtime, testfield) 
              VALUES 
                 ('kae', CURRENT, f01);

Load the existing data first and then write an SQL query to insert or update the data with the current time, date, or user login. You could write the following SQL statement:

INSERT INTO testtbl  
               (testuser, testtime, testfield) 
            VALUES  
               ('kae', CURRENT, f01);

The CURRENT keyword returns the system date and time. The TODAY keyword returns the system date. The USER keyword returns the user login name.

The following diagram shows the syntax of the dbload INSERT statement for delimiter form.

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

1. See IBM Informix Guide to SQL: Syntax

Users who execute dbload with this command file must have the Insert privilege on the named table.

How to Write a dbload Command File in Delimiter Form

The first FILE and INSERT statement set in the delimiter example on page FILE and INSERT Statements: Delimiter Form is repeated in the following example:

FILE stock.unl DELIMITER '|' 6;
INSERT INTO stock;

The FILE statement describes the stock.unl data rows as composed of six fields, each separated by a vertical bar (|) as the delimiter. Two consecutive delimiters define a null field. As a precaution, you can place a delimiter immediately before the new-line character that marks the end of each data row. If the last field of a data row has data, you must use a delimiter. If you omit this delimiter, an error results.

Compare the FILE statement with the data rows in the following example, which appear in the input file stock.unl. (Because the last field is not followed by a delimiter, an error results if any data row ends with an empty field.)

1|SMT|baseball gloves|450.00|case|10 gloves/case
2|HRO|baseball|126.00|case|24/case
3|SHK|baseball bat|240.00|case|12/case

The example INSERT statement contains only the required elements. Because the column list is omitted, the INSERT statement implies that values are to be inserted into every field in the stock table. Because the VALUES clause is omitted, the INSERT statement implies that the input values for every field are defined in the most recent FILE statement. This INSERT statement is valid because the stock table contains six fields, which is the same number of values that the FILE statement defines. The following example shows the first data row that is inserted into stock from this INSERT statement.

The FILE and INSERT statement in the following example illustrates a more complex INSERT statement syntax:

FILE stock.unl DELIMITER '|' 6;
INSERT INTO new_stock (col1, col2, col3, col5, col6)
   VALUES (f01, f03, f02, f05, 'autographed');

In this example, the VALUES clause uses the field names that dbload assigns automatically. You must reference the automatically assigned field names with the letter f followed by a number: f01, f02, f10, f100, f999, f1000, and so on. All other formats are incorrect.

The user changed the column names, the order of the data, and the meaning of col6 in the new stock table. Because the fourth column in new_stock (col4) is not named in the column list, the new data row contains a null in the col4 position (assuming that the column permits nulls). If no default is specified for col4, the inserted value is null.

The following table shows the first data row that is inserted into new_stock from this INSERT statement.

Column

Value

col1

1

col2

baseball gloves

col3

SMT

col4

null

col5

case

col6

autographed