CHAPTER 5: Exporting and Importing Data

There are three utility programs related to the exporting and importing of data. These three utilities are:

• empexpt exports tables from an Empress database.
• empimpt imports tables to an Empress database using the data generated by empexpt.
• empdbld imports data from an operating system file into Empress databases.

These utility programs may be used to export and import database tables across operating systems.

empexpt exports tables from a database. The syntax for empexpt is:

   empexpt [-dehvx] outfile database [table {table}]

where:

The command options are:

Please note that:

1. The tables named on the command line are exported. Comments, lock levels, indices, range checks, and referential constraints are exported with the data, but not privileges. Table names may not be preceded by the keyword TABLE and may not take the form database:table (as in Query Language conventions). Table names must be separated by blanks.

2. If no tables are given on the command line, all user tables are exported, including the comments, lock levels, and indices on the system tables sys_tables and sys_attrs.

3. The command options may be grouped together after a dash (-) given individually with each preceded by a dash and separated by spaces, or given in several groups, each preceded by a dash and separated by spaces. The options may appear in any order.

4. You should not attempt to explicitly export data from the data dictionary. However, you may export comments, lock levels, and indices on data dictionary tables using the -d option.

5. If views are named for export, their underlying tables should be exported first.

6. Pressing the <Interrupt> key will not interrupt the program during critical phases. Pressing it several times will usually stop the program.

7. You must have Empress DISPLAY privilege to export a table's definition, and both DISPLAY and SELECT privileges to export its data. Data may not be exported for the table without these privileges.

8. Files should be transferred in binary mode.

9. The file tabzero is not exported by empexpt.

empimpt imports tables from an Empress export file into a database. The syntax is:

   empimpt [-cdeghiklprtvx] infile database [table {table}]

where:

The command options are:

Please note that:

1. Table names may not be preceded by the keyword TABLE. If no tables are given on the command line all tables in the input file are imported. Table names must be separated by blanks.

2. The command options may be grouped together after a dash (-), given individually with each preceded by a dash and separated by spaces, or given in several groups, each preceded by a dash and separated by spaces. The options may appear in any order.

3. The sequence of actions taken by the program in importing a table is:

   • Create the table
   • Insert the data
   • Create the indices
   • Define the lock levels
   • Create the table comments
   • Create the range checks
   • Create the attribute comments

4. An error occurs if a table (other than a system table) to be imported already exists in the database. If the -e option is not used, the following points apply.

   • Indices, comments, lock levels, range checks, and referential constraints are not created for the table. No message is printed.

   • If the table has any level of locking defined for it, the table is locked at the table level during the insertion. If the table cannot be locked (as when it is in use) no insertions are attempted for the table and a message is printed. You may try to insert the data for the table later.

   • If the insertion proceeds, data will be mapped to attributes with the same names as the original attributes. The presence of data mapped to non-existent attributes causes the program to skip to the next table. Additional attributes in the table will be set to null; if they are defined as NOT NULL the program skips to the next table.

   • Individual records will be inserted if the data are not incompatible with the data types of the corresponding attributes in the table. Conversion to the appropriate data type will be done automatically.

     For instance, data originating from an INTEGER attribute will be inserted into a LONGINTEGER attribute, but data originating from a LONGINTEGER attribute will fail to be inserted into an INTEGER attribute if the numbers are too large.

     Similarly, data originating from an INTEGER attribute will be inserted into a CHAR attribute of sufficient length, but data originating from a CHAR attribute will not be inserted into an INTEGER attribute unless they are digits. Again, data from a CHAR Type 3 attribute will be inserted into a TEXT attribute, but data from a TEXT attribute will be inserted into a CHAR Type 3 attribute only if the CHAR attribute is large enough for the value.

   • If the first record to be inserted to a table contains data that is incompatible with the data type of the attribute to which it is mapped, and the insertion fails due to a data conversion error or range check, the program assumes that the data is incompatible with the table and no further insertions are attempted on that table. Incompatible data in subsequent records causes the records to be skipped. If inserting any record would cause the allowable number of duplicate tuples to be exceeded, the record is skipped. Care should be taken to avoid having some records inserted while others are rejected.

5. Table ownership is not preserved in the export/import process. Imported tables are created in the name of the user executing the import program.

6. All imported tables are created in the database directory.

7. To import tables you must have the privilege to create tables in the database, i.e., INSERT privilege in sys_tables. If any of the tables to be imported exists, you must have INSERT privilege on them.

8. You should not attempt to explicitly import data to the data dictionary. You may import comments and lock levels for all the data dictionary tables if you have ALTER privilege on them, and you may import indices for these tables if you have INDEX privilege on them.

9. Pressing the <Interrupt> key will not stop the program during critical phases. Pressing it several times will usually stop the program.

10. Files should be transferred in binary mode.

11. If the imported data is related to the Empress 4GL application, you must compile and link the applications using emp4gl with, respectively, the -c (compile) and -l (link) options.

12. If no tables are listed in the command line for empexpt, any comments, lock levels, and indices on the system tables sys_tables and sys_attrs will be exported. To import this information successfully to another database, you must have the appropriate privileges on these tables in the second database; that is, ALTER privilege to import comments and lock levels, and INDEX privilege to import indices.

    Note that lock levels and indices used for implicit searches of system tables will only be effective if the appropriate variables are set properly in the file tabzero in the database directory. A message to this effect is displayed if lock levels or indices are imported.

The utility, empdbld, is a general purpose program for loading data from an operating system file into Empress databases. The loader accepts fixed format ASCII data and allows you to:

• Load data from a single input file into multiple tables in multiple databases.
• Load data into all the attributes of a table, or into only some attributes, with the remaining attributes being set to null.
• Load data into any Empress data types, with automatic data type conversion.
• Map input record fields to particular attributes.
• Generate log files for error messages and summary statistics.
• Generate a reject file of records that cannot be loaded.
• Examine the input file for invalid data.

The syntax of empdbld is:

   empdbld [-c] [-d [COUNT]] [-r reject_file] database spec_file [data_file]

where:

The command line options are:

Please note that:

1. Records are read from the data file and loaded into the database according to the instructions in the specification file.

2. The data file must contain fixed format ASCII data. The specification file must conform to the syntax described in the next section.

3. An error occurs if any data to be loaded is incompatible with the data type of the attribute for which it is intended, or if excess duplicates are inserted for an indexed attribute. If the input specifications cause a null value to be inserted for an attribute defined as NOT NULL, the program prints a message and stops before loading data.

4. On completion, empdbld prints a count of records read and records rejected to standard output.

5. You must have INSERT privilege on the tables to be loaded.

6. Pressing the <Interrupt> key will stop the program.

The specification file spec_file describes how fields of data are to be mapped to databases, tables, and attributes. The syntax for this file is:

   LENGTH record_length table_definition {table_definition}

where:

The syntax of a table_definition is:

   INTO [TABLE] [database:]table

table is the name of a table. It may be preceded by the keyword TABLE. The optional database (database name) prefix is used to override the default database specified on the command line. Listing a table more than once causes an error. A view into a table may be used instead of a table. However, at most one view into any given table may be used.

attribute is the name of an attribute in the table. It may be preceded by the keyword ATTRIBUTE, but may not take the form table.attribute or database:table.attribute (as in Empress SQL conventions). Listing an attribute more than once causes an error. Attributes of the table that are not named will have null values inserted. If an unnamed attribute is defined as NOT NULL, an error is reported and the program stops before loading data.

The keyword POSITION (optionally POS) must precede the field specification of the data being inserted into the attribute. The field specification consists of two integers, start and end. These integers determine the starting and ending positions for the data in the record. (The first position in each input record is position 1 and the last is record_length.) A POSITION is always given relative to the beginning of the input record, and end must be less than or equal to record_length.

Data from the field will be inserted into the attribute with automatic data conversion to the attribute's data type. Where appropriate, explicit decimal points are recognized, and floating point exponents may be indicated by "e", any letter (in upper- or lowercase), or a "+/-" sign.

The record length, record_length, in the specification file, spec_file, deserves some special attention. Note that the data file, datafile, will have often been created by the user either through an editor (like vi) or from a program. In either case, the non-printing end-of-line character(s) must be included in determining record_length. Note that the typical end-of-line characters is "\n".

Here, \n is linefeed (hex a), and \r is carriage return (hex d). Thus, a file containing three printing characters 123 would likely have a record_length of 5 in Windows, and 4 in UNIX. Also note that if the file has been created with an editor like vi, the last line of a file is often one character shorter than those preceding it. You can get around this by simply adding a \n character (or even a space) to the end. The important point is that all lines in the file be of the same length, and that record_length is calculated taking the non-printing characters into account.

A FORMAT clause is an option that allows you to support implied data formats. It does not override explicit formats (such as decimal points and exponents) in the automatic conversion to the attribute's data type.

The syntax of the FORMAT clause is:

   FORMAT (|DECIMAL (n)     |)
           |DEC (n)         |
           |FLOAT (n, e)    |
           |DATE (picture)  |

The keyword DECIMAL (optionally DEC) is used to supply the number of implied decimal digits (given by n) in a numeric field. The last n bytes of the field are taken as the decimal digits. The data must be right-justified in the field.

Note that this format specification is ignored if the data contains an explicit decimal point.

The keyword FLOAT is used to supply the number of decimal digits n and the number of exponent digits e in a floating point field. The last e bytes of the field are taken as the exponent digits, and the n bytes before these as the decimal digits. The data must be right-justified in the field.

Note that the entire format specification will be ignored if the data contains an explicit decimal point. The exponent part of the format specification will be ignored if any letter or sign (other than a leading sign) appears in the field.

The keyword DATE is used to supply a picture for interpreting fixed format date fields. The picture is a quoted string in which special characters are used to control how corresponding characters in the input field are treated. The length of the picture may not exceed the length of the field. The picture uses the following conventions:

Table 5-1: Date Picture Conventions for the FORMAT Clause

A NULLIF clause allows you to specify the conditions under which NULL is to be entered as an attribute value. These conditions always depend on specific data values in the input record.

Specifying a NULLIF clause for an attribute defined as NOT NULL causes an error. The program will print a message and stop before data loading commences.

The syntax of the NULLIF clause is:

   NULLIF (null_spec {, null_spec})

Each null_spec specifies a condition for entering a null value. The conditions are implicitly OR'd together.

A null_spec is:

   |POSITION| (start:end) = "string"
   |POS     |

The condition specifies that the field starting at position start and ending at position end of the input record be equal to the quoted string. The field positions (start and end) are not given as offsets within the part of the data record mapped to the attribute. They are given as offsets from the start of the input record. (A position is always given relative to the beginning of the input record, and end must be less than or equal to record_length.). The length of the string must equal the length of the field, (i.e., end - start + 1).

The examples below are based on the database repairs. It is assumed that these tables are empty at the start of each example.

Example 1

Given the table personnel:

   number        integer
   name          char (25, 1)
   phone         char (15, 1)
   credit_limit  dollar (6, 1)

Load the following data into personnel:

   10Kilroy  426-9681 500.00
   5 Mosca   544-2243 750.00
   3 Jones   667-2951 500.00
   8 Peterson978-6060 250.00
   9 Jordan  964-3335 250.00

The length of each record is 26 (including 1 character for the new line). The specification file is:

   LENGTH 26
   INTO personnel
        (number POS (1:2), name POS (3:10),
         phone POS (11:18), credit_limit POS (20:25))

If the specification file is spec.1 and the data is in data.1, run a check with the command at the operating system prompt:

   empdbld -c repairs spec.1 data.1

It produces the following output:

     *** Summary ***
   No. records read = 5
   No. rejected     = 0

To load the data with the following command at the operating system prompt:

   empdbld repairs spec.1 data.1

It produces the following output:

     *** Summary ***
   No. records read = 5
   No. rejected     = 0

Example 2

If the information in personnel is split into two tables as:

       *** pers1 ***                 *** pers2 ***
   
   number     integer            number         integer
   name       char (25, 1)       credit_limit   dollar (6, 1)
   phone      char (15, 1)

Then you can load the same data using the specification file:

   LENGTH 26
   INTO pers1
        (number POS (1:2), name POS (3:10), phone POS (11:18))
   INTO pers2
        (number POS (1:2), credit_limit POS (20:25))

Note that when loading more than one table each table must accept the same number of records.

Example 3

Suppose that in addition to the personnel table there is a loans table:

   name      char (25, 1)
   date      date (1)
   amount    dollar (6, 1)

The data to be loaded into personnel and loans is in the file data.3 containing:

   10Kilroy  426-9681 500.00 16/02/92 250.00
   5 Mosca   544-2243 750.00 02/02/92 150.00
   5 Mosca   544-2243 750.00 04/05/92 200.00
   3 Jones   667-2951 500.00 07/02/92  33.95
   3 Jones   667-2951 500.00 03/04/92  25.00
   3 Jones   667-2951 500.00 12/08/92 300.00
   8 Peterson978-6060 250.00 06/06/92  50.00

Only one record for each name should be loaded to personnel, but the loan information in each record should be loaded to loans. The data may be loaded in either of two ways:

1. Load one table at a time using the same data file. This means running empdbld twice using the appropriate specification file each time.

2. Load the data into a temporary table, then use the Query Language to select data into the two tables.

Loading data one table at a time:

To see how empdbld handle rejected records, let's use the Query Language to place a unique index on the number attribute of the personal table. This will reject any duplicate records (there are three). For instance:

   CREATE UNIQUE INDEX ON personnel.number;

The specification file spec.3 would be:

   length 42
   into personnel
        (number pos (1:2), name pos (3:10),
         phone pos (11:18), credit_limit pos (20:25))

When you run empdbld you will see error messages due to the duplicate records:

   empdbld repairs spec.3 data.3
   
   *** Data Error:
   duplicate value for attribute 'number', record 3 rejected
   
   *** Data Error:
   duplicate value for attribute 'number', record 5 rejected
   
   *** Data Error:
   duplicate value for attribute 'number', record 6 rejected
   
        *** Summary ***
   No. records read = 7
   No. rejected     = 3

To load loans, use the specification file:

   LENGTH 42
   INTO loans
        (name POS (3:10), 'date' POS (27:34)
         FORMAT (date ("dd mm yy")),
         amount POS (36:41))

The above example specifies a format for the date to be sure that the data is loaded correctly.

Loading data using a temporary table:

The following SQL command creates a temporary table combining the attributes of the two tables:

   SELECT name, number, phone, credit_limit,
        date, amount FROM loans, personnel INSERT INTO temp;

This will create the table temp with the following definition:

   name          char (25, 1)
   number        integer
   phone         char (15, 1)
   credit_limit  dollar (6, 1)
   date          date (1)
   amount        dollar (6, 1)
   

Next, use empdbld to load data into the temporary table according to the following specification file:

   LENGTH 42
   INTO temp
        (number POS (1:2), name POS (3:10),
         'date' POS (27:34) FORMAT (date ("dd mm yy")),
         phone POS (11:18), credit_limit POS (20:25),
         amount POS (36:41))

Note that the order of the attributes does not matter. Finally, use the Query Language to move the data from temp to personnel and loans:

   SELECT UNIQUE number, name, phone, credit_limit FROM temp
        INSERT INTO personnel;
        SELECT name, date, amount FROM temp
        INSERT INTO loans;

Example 4

If the data file uses implied data formats, use a FORMAT clause. Suppose the following data is to be inserted into loans:

   Kilroy  25000Feb16'92
   Mosca   15000Feb 2'92
   Mosca   20000May 4'92
   Jones    3395Feb 7'92
   Jones    2500Apr 3'92
   Jones   30000Aug12'92
   Peterson 5000Jun 6'92

Here, position (1:8) is the name, position (9:13) is the amount with two implied decimal places (i.e., 25000 represents $250.00), and position (14:21) is the date.

The specification file to load this data is:

   LENGTH 22
   INTO loans
   (name POS (1:8), amount POS (9:13) FORMAT (dec(2)),
    'date' POS (14:21) FORMAT (date("aaadd yy")))

Example 5

If the loans table kept loan amounts as FLOAT data, as in:

   name     char (25, 1)
   date     date (1)
   amount   float

And the data could be read in as:

   Kilroy  2.500000e+02Feb16'92
   Mosca   1.500000e+02Feb 2'92
   Mosca   2.000000e+02May 4'92
   Jones   3.395000e+01Feb 7'92
   Jones   2.500000e+01Apr 3'92
   Jones   3.000000e+02Aug12'92
   Peterson5.000000e+01Jun 6'92

the specification file as follow:

   LENGTH 28
   INTO loans
        (name POS (1:8), amount POS (9:20),
         'date' POS (21:28) FORMAT (date("aaadd yy")))

If the data format for amount is implicit:

   Kilroy   250000002Feb16'92
   Mosca    150000002Feb 2'92
   Mosca    200000002May 4'92
   Jones    339500001Feb 7'92
   Jones    250000001Apr 3'92
   Jones    300000002Aug12'92
   Peterson 500000001Jun 6'92

the specification file becomes:

   LENGTH 26
   INTO loans
        (name POS (1:8), amount POS (9:17) FORMAT (float(6,2)),
         'date' POS (18:25) FORMAT (date("aaadd yy")))

Example 6

The NULLIF clause specifies conditions under which an attribute takes a null value. Suppose data being loaded into personnel is:

   10Kilroy    426-9681  500.00
   5 Mosca     544-2243  750.00
   3 Jones     667-2951  500.00
   8 Peterson  978-6060  250.00
   9 Jordan    964-3335  250.00

The last column represents credit limits to be loaded. Suppose there is no credit limit for Jones and Mosca. Then, the specification file becomes:

   LENGTH 26
   INTO personnel
        (number POS (1:2), name POS (3:10), phone POS (11:18),
         credit_limit POS (20:25) nullif (POS (3:7)="Jones",
                                          POS (3:8)="Kilroy"))