# Loader

## Key Elements

| ## Prerequisites Prior to using the external loader for any data transfer you first have to create an export or import script in order to define query values for the table data to be transferred. The name of the export/import script is free choice, but you have to make sure the file ending is "*.exp" or "*.imp". In the following the export and import scripts are described in detail.
| ## Export Script The parameters (query values) which might appear in an export script are as follows:
export.bin | Name of the export file (default: export.bin)
. | File width (default: 80) ignored
Y | Query values Yes/No (if No: the values until the tables are ignored!)
USER1 | Query user1
. | Query user2 or "." = end of user list
GROUP1 | Query group1
. | Query group2 or "." = end of group list
. | Query since creation date or "." = no value (Boolean: >=) Example for the date format: 31.11.2007@12:37:57
. | Query since update date or "." = no value (Boolean: >=)
Table_1 Table_2 Table_n . | Table names Table name or "." = end of table list
| Please note that for reasons of compatibility either a point or a number has to be entered as parameter for the file width although a number indicated won't have an impact on the file width!
| As a general rule as how to handle the query values in an export script, it is necessary to enter "Y" to point out that query values are following. Possible query values are user name(s), group assignment(s) or creation and update date. This is an effective way to limit the amount of the exported data. No matter how many query values you will define, it is required to indicate those tables in the export script where you know changes were conducted. If the tables are not known exactly, you may use the SQL-trace function to find them out.
| Please note that several usernames as well as groups are linked as an "OR"-combination whereas the different query options (User, Group, Cre-Date, Upd-Date) are linked as "AND"-combination! Usually the number of records can be significantly reduced using the "AND"-combination!
| In the example below, "N" is entered for the query values. This implies that no further query values are defined. The export script will look more concise and the loader file will contain all entries from the table T_MESSAGE (except the system messages) independent from user, group assignment or creation and update date. export.bin . N T_MESSAGE .
| ## Import Script The parameters (query values) which might appear in an import script are as follows:
export.bin | Name of the import file (default: export.bin)
Y | Import all tables? Yes/No
I | Import mode
| Please note that if "N" is entered for the query value as depicted in the example below, the table data will be automatically imported in "X" import mode (dump loader mode) which implies a direct import without checking/changing the C_ID values. Therefore no id conflict handling will be provided!
| The following import modes are available: I = insert U = update O = overwrite (insert and update) R = replace (means delete everything existing before loading) In case you want to import directly, but don't want to import all tables, the import script may look as follows:
export.bin | Name of the import file (default: export.bin)
N | Import all tables? Yes/No
Y | Load data directly? Yes/No
Table_1 Table_2 Table_n . | Table names Table name or "." = end of table list
In this case only the following import mode is available: X = direct import without checking/changing the C_ID value
| Please note that in the X-modus (dump loader modus) C-ID's won't be replaced. Therefore no id conflict handling will happen!
| ## Loader file A loader file is automatically created when table records are exported from a database. It is the file the exported table records are written to. In case of a data export the loader file is also referred to as "export file" (import = import file). You can see from the first export script depicted above that the default name of the loader file (export.bin) is listed in the first line. You may change the default name to a name of your choice, but you need to make sure the file ending is correct according to the kind of loader you want to transfer table data with. As an example, if you want to use the binary loader for a data transfer the loader file ending must be "*.bin". The external loader also offers the additional suffix "*.xml". Different loader file endings than "*.bin" and "*.xml" are declined (internal loader) or always corrected to "*.bin" (external loader). The loader file is stored in the dmp directory of your application.
| ### Structure of the loader file Depending on the file format (*.bin, *.xml) the export file has a different structure.
| #### Binary loader file The binary loader file consists of the following five parts: 1. Encoding part The encoding part contains information about the encoding, the version and the type of loader file. Example: ENC/*** ENC->UTF-8 VER->1.0 TYP->BIN CTRL-V 2. Query The query part contains all the query definitions. Example: QUE/*** 31|C_UPD_DAT:>=20.11.2003@00:00:00 31|C_CRE_DAT:>=20.12.2003@00:00:00 9|C_GIC:201 9|C_UIC:201 3. Text The text part contains column names and a corresponding value. The loader looks for the column name in every table and replaces the existing value with the value indicated behind the column name. Example: TXT/*** 10|C_UIC:1006 4. Description The description part simply lists all the column names of the tables. Example: DSC/T_MASTER_DAT C_ID C_VERSION C_LOCK C_UIC C_GIC C_CRE_DAT C_UPD_DAT C_ACC_OGW C_NAME* ... 5. Data In the data part only the values for the column names are listed, follwing the order in the description part. The gaps in between indicate an empty field. Example: DAT/T_MASTER_DAT 1976674548 4 0 1009 200 2008-01-23 07:57:38 2008-01-23 10:07:28 3|dwr 17|EDB-CHKNOTALLOWED ...
| Please note that the characters indicated after a pipe are read as one string including line breaks!
| #### xml loader file The xml loader file consists of the following six parts: 1. Information This section contains information about the version and the file type of the loader file. Because the encoding information is part of the xml file header, it is not mentionned here. Example: <information> <version VER="1.4"/> <type TYP="XML"/> </information> 2. Query This section contains the query condition names and their values. Example: <query> <condition fieldName="C_UIC" queryValue="1007 | 202 | 1008"/> </query> 3. Replacements This section contains the replacements. Example: <defaults> <default fieldName="C_CRE_DAT" defaultValue="2010-03-27 11:11:11"/> </defaults> 4. Definition The definition section contains the table names with relations and the type of relation. In case the file is converted from a binary loader file, this part remains empty. Example: <definition> <table name="T_MASTER_DAT"> <relation type="REF" table="T_MASTER_HIS"/> 5. Description The description section contains the table name and the column names included as well as the type and length and optionally the primary key flag. Example: <description> <table name="T_MASTER_DAT"> <column name="C_ID" type="I" length="10"/> <column name="C_VERSION" type="I" length="10"/> <column name="C_LOCK" type="I" length="10"/> <column name="C_ACC_OGW" type="S" length="9"/> <column name="C_NAME" type="S" length="60" primaryKey="true"/> ... </description> 6. Data Part The data part contains the entries for the table names listed in the description part. Example: <data> <table name="T_MASTER_DAT"> <record C_ID="1976674548" C_VERSION="2" C_LOCK="0" C_ACC_OGW="dwr" C_NAME="EDB-CHKNOTALLOWED"... ... </table>
| Please note that in the data part long text fields and blobs are Base64 encoded!
| Since all record data is written in one line, those lines can be very long!

##