The control file syntax has the following characteristics:

The control file begins with the statement LOAD [DATA] followed by several statements that describe the data to be loaded. Only comments or the OPTIONS statement can optionally precede the LOAD [DATA] statement.

The solidDB^® Speed Loader reserved words must be enclosed in double quotation marks if they are used as data dictionary objects, that is, table or column names. The following list contains all reserved words for the solidDB^® Speed Loader control file:

The CHARACTERSET keyword is used to define the character set used in the input file. If the CHARACTERSET keyword is not used or if it is used with the parameter NOCONVERT or NOCNV, no conversions are made.

Use the parameter as follows:

The DATE, TIME, and TIMESTAMP keywords can be used in two places with different functionality:

Data must be of the same type in the import-file, the mask, and the column in the table into which the data is loaded.

The following example uses the POSITION keyword.

The following example uses the FIELDS TERMINATED BY keyword.

The PRESERVE BLANKS keyword is used to preserve all blanks in text fields.

The into_table_part element is used to define the name of the table and columns that the data is inserted into.

The FIELDS ENCLOSED BY clause defines delimiting characters around each field. The delimiter can be a single character or two separate characters that precede and follow each data field in the input file. You might use a single character (such as the double quotation mark character) or a pair of characters (such as left and right parentheses) to delimit your fields. If you use double quotation marks as the delimiter and the comma as the terminator/separator, your input might look like the following:

If you use left and right parentheses, your input might look like the following:

If the keyword OPTIONALLY is used, the delimiters are optional and do not need to appear around every single piece of data.

If you specify a character value, it must be enclosed in single or double quotation marks. For example, the following examples have the same effect:

You can even use the single quotation marks to surround one enclosing character and double quotation marks to surround the other, for example: ENCLOSED BY '(' AND ")"

Because using two conventions is potentially confusing, it is not recommended. Instead, use single quotation marks unless you are using a single quotation mark itself as the enclosing character, for example: ENCLOSED BY "'" AND "'"

If you are using single quotation marks as the enclosing characters, you must double the apostrophes as shown in the clause above. For example, to produce ‘Didn’t I warn you?’ in the database, the input must be as follows:

Almost any printable characters can be used as the “enclosing” characters. The enclosing characters can also be specified using the hexadecimal format. For example, if a hexadecimal string is used, the format is:

For example, X'3a' means 3A hexadecimal value and specifies the colon (:).

The opening and closing characters in an enclosing pair can be identical. For example, the following is valid inside the control file:

If both the opening and closing characters are the same, the ENCLOSED BY clause needs to show the character only once. For example, the following clauses have the same effect:

The following examples show the input in the control file and the corresponding values stored in the table:

You can use enclosing characters in the column data itself (embedded field separators). If you use embedded field separators, you can use the TERMINATED BY clause together with the OPTIONALLY ENCLOSED BY clause to ensure that the column data is enclosed correctly.

This section contains basic rules and examples when using enclosing characters. Each example, unless stated otherwise, contains the following control file lines:

The enclosing characters are parentheses and the separator (terminator) character is the colon — hexadecimal 3A specifies the colon (:).

The data is to be loaded into a table with two columns. The first column is of type VARCHAR and the second of type INTEGER.

The ENCLOSED BY characters themselves can occur within the data. However, when occurring within the data, each of the enclosing characters needs to occur twice in the input for each occurrence in the database.

If the input file contains (David Bowie ((born David Jones)) released 'Space Oddity"):1972, it produces the following format in the database:

Deeply nested parentheses work the same way. If the input file contains (You((can((safely((try))this))at))home.):2, it produces the following value in the first column of the table:

The final enclosing character must occur an odd number of times at the end of the input. For example to get the format American Pie (The Day The Music Died) in the database, the input file must contain the following:

Of the last three closing parentheses, the first two are treated as a single instance of the character, while the last one is treated as the enclosing character.

When enclosing characters are used, newline characters (carriage return or line feed) can be embedded within a string. For example:

If the field separator (colon) is not used in the data and if there is no need to preserve new lines in the input data, only the field separator (not the enclosing characters) is required in the input data.

If your data is fixed-width, you do not need either the separator or the enclosing characters.

The FIELDS TERMINATED BY clause is used to define the separator character that distinguishes where fields end in the input file. The character must be specified in one of the following three ways:

When using hexadecimal format, the quotation marks must be single quotation marks, not double quotation marks.

The FIELDS TERMINATED BY clause specifies a separator, not a true terminator. The specified character is not required after the last field. For example, if the colon is the separator, the following two data file formats are equivalent and valid:

or

The trailing colon after the final field is accepted but not required.

The OPTIONALLY ENCLOSED BY clause is used after the FIELDS TERMINATED BY clause when the character used to enclose the column data is contained in the column data itself. Following is a control file example:

In the example above, the separator is a comma.

The single quotation mark is defined as the character that encloses embedded field separators (commas) in the data file. The OPTIONALLY ENCLOSED BY clause may use either single or double quotation marks to delimit the enclosing characters.

For example:

The above illustrates the use of both single and double quotation marks for enclose_char in the syntax:

The following example summarizes the use of separators and enclosing characters. In this example, the : (colon) is defined as the separator (FIELDS TERMINATED BY) and the parentheses are used to enclose the : (colon), which is embedded in the field and cannot be interpreted as a separator. The example also contains two fields, the first of which is VARCHAR and the second of which is INTEGER.

The POSITION keyword is used to define position of a field in the logical record. Both the start and the end position must be defined.

The NULLIF keyword is used to give a column a NULL value if the appropriate field has a specified value. An additional keyword specifies the value the field must have. The keyword BLANKS sets a NULL value if the field is empty; the keyword NULL sets a NULL value if the field is the string “NULL”; the definition string sets a NULL value if the field matches the string 'string'; the definition '((start : end) = 'string')' sets a NULL value if a specified part of the field matches the string 'string'.

The following example shows the use of the NULLIF keyword with the keyword BLANKS to set a NULL value if the field is empty. It also shows the use of the keyword NULL to set a NULL value if the field is the string “NULL”.

The following example uses the definition '((start : end) = 'string')' for the third field in the input file. This syntax only works with fixed-width fields because the exact position of the 'string' must be specified.

Note that in this example, the string is case‑sensitive: that is, 'MAKEMENULL' and 'makemenull' are not equivalent.